社区通用版下载 mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz 582M,mysql server,企业级安装,用的就是这个,这个也够了 mysql-8.0.32-linux-glibc2.12-x86_64.tar 959M,包含以下三个 mysql-test-8.0.32-linux-glibc2.12-x86_64.tar.xz mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz mysql-router-8.0.32-linux-glibc2.12-x86_64.tar.xz mysql-router是一个轻量级的故障切换路由器,相当于HA,切IP用的,是个路由,不涉及数据库内部操作 对于企业来说,IP的切换通常由网络部门处理,都是企业级的,所以mysql-router的应用场景较少
本次安装环境为ubantu,linux安装与之几乎完全一致 用户创建 ---------------------------------------------------------------------------- root用户 这里采用root用户进行安装,服务的启动也使用root用户, 若是企业内首次安装mysql有限用root用户的场景,就解释然后申请开通 添加用户linux groupadd mysql useradd -g mysql mysql 添加用户ubantu 除了用户,还有一个组,在ubantu上这个命令会自动创建一个同名的mysql组 adduser mysql 这里涉及用户是否有目录的问题,企业级创建的用户通常没有创建对应的home目录, 如果是集群服务器节点互信,用mysql可以,用root也可以,或者用已有的互信用户也行, 重点是不影响互信的功能 软件安装目录 ---------------------------------------------------------------------------- 此压缩包,是编辑好的,相关动态库都已经包含其中,解压就可直接使用 安装目录随意,企业级的关键在于不同服务器位置的统一,便于平台统一维护,尤其是后面要创建数据目录 tar -xvf mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz mv mysql-8.0.32-linux-glibc2.12-x86_64 /opt/app/mysql chown -R mysql.mysql /opt/app/mysql 环境变量配置,不是必需,配置是为了使用方便 export PATH=/opt/app/mysql/bin:$PATH root@xt:~# vim /etc/profile root@xt:~# source /etc/profile root@xt:~# which mysql /opt/app/mysql/bin/mysql 数据目录创建 ---------------------------------------------------------------------------- 要求统一,如果可能存在一个服务器多个实例的场景,也要统一规划 主要考虑的还是磁盘大小,要考虑业务未来3年的增长,备份存量,不可能一年玩一次数据库迁移 mkdir -p /opt/data/tmp mkdir -p /opt/data/mysql_13301 mkdir -p /opt/data/logs chown -R mysql.mysql /opt/data/logs chown -R mysql.mysql /opt/data/mysql_13301/ chown -R mysql.mysql /opt/data/tmp/ 第2个实例的数据目录 mkdir -p /opt/data/mysql_13302 chown -R mysql.mysql /opt/data/mysql_13302/ 多个实例之间共用相同的日志目录,日志监控,报警,读取相同的目录 数据实例目录可以通过实例参数读取,也可以脚本匹配/opt/data/mysql_*
[client] port = 13301 socket = /opt/app/mysql/data/logs/my_13301.sock default-character-set = utf8 [mysql] no-auto-rehash prompt =\\u@\\h \\D \\R:\\m:\\s [\\d]> default-character-set =utf8 socket =/opt/app/mysql/data/logs/my_13301.sock [mysqld] user = mysql server-id = 13301 port = 13301 socket = /opt/app/mysql/data/logs/my_13301.sock pid-file = /opt/app/mysql/data/logs/my_13301.pid basedir = /opt/app/mysql datadir = /opt/app/mysql/data/mysql_13301 tmpdir = /opt/app/mysql/data/tmp replica_load_tmpdir = /opt/app/mysql/data/tmp character-set-filesystem = utf8mb4 character_set_server = utf8mb4 collation_server = utf8mb4_bin lower_case_table_names = 1 #skip_name_resolve open_files_limit = 65535 back_log = 103 max_connections = 500 max_connect_errors = 10000 #----log settings----# default-time_zone = '+8:00' log_timestamps = SYSTEM log_error_verbosity = 2 init_connect = 'SET NAMES utf8mb4' #sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' log-error = /opt/app/mysql/data/logs/my_13301_error.log log-bin = /opt/app/mysql/data/mysql_13301/mysql-bin binlog_format = row sync_binlog = 0 binlog_cache_size = 32M max_binlog_cache_size = 512M max_binlog_size = 1024M binlog_expire_logs_seconds = 7200 interactive_timeout = 120 wait_timeout = 120 transaction_isolation = READ-COMMITTED read_only=0 #off relay-log = /opt/app/mysql/data/mysql_13301/relay-bin replica_net_timeout = 60 replica_skip_errors = 1062 log_replica_updates = 1 # innodb_page_size = 16384 #default value innodb_data_file_path = ibdata1:1024M:autoextend innodb_temp_data_file_path = ibtmp1:2048M:autoextend innodb_buffer_pool_size = 300M innodb_buffer_pool_instances = 1 innodb_flush_log_at_trx_commit = 1 innodb_thread_concurrency = 0 innodb_file_per_table = 1 max_allowed_packet = 64M max_heap_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 32 skip-name-resolve ft_min_word_len = 4 default-storage-engine = InnoDB thread_stack = 192K #tmp_table_size = 64M innodb_thread_concurrency = 0 innodb_lock_wait_timeout = 180 [mysqldump] quick max_allowed_packet = 32M
各个操作都是以root用户操作的,添加文件需要重新授权 chown -R mysql.mysql /opt/data
建库 ---------------------------------------------------------------------------- 这里要注意的是配置文件放哪里更合适,有人会放在/etc目录下, 但配置文件决定的是库的创建,也就是说配置文件与库紧密相关, 那么它与数据文件同级放更合适一些,如果你要COPY走一份数据文件,就顺便把与之相关的配置文件也COPY走 mysql5.7或mysql8指定配置文件创建数据库 which mysqld mysqld --defaults-file=/opt/data/my_13301.cnf --initialize --user=mysql 安装过程中查看日志,如果报错可以查询具体错误内容,无错会有一个临时密码 tail -f logs/my_13301_error.log [Server] A temporary password is generated for root@localhost: iE+jJlw!w9?,
启动 nohup mysqld_safe --defaults-file=/opt/data/my_13301.cnf --user=mysql & 关闭方式一 mysqladmin shutdown -uroot -p'rootroot' -S /opt/data/logs/my_13301.sock 关闭方式二 mysql> shutdown; Query OK, 0 rows affected (3.43 sec) 定义别名，方便日常维度 alias mstart="nohup mysqld_safe --defaults-file=/opt/app/mysql/data/my_13301.cnf --user=mysql &" alias min="mysql -uautomng -pAutomng_123 -S /opt/app/mysql/data/logs/my_13301.sock"
初始化密码修改 mysql -uroot -S /opt/data/logs/my_13301.sock -p ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'rootroot'; ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER; mysqladmin -u用户名 -p旧密码 password 新密码 mysqladmin -u root password 'otA8_b19H78rqu' -S /opt/data/logs/my_13301.sock mysql_native_password 执行本地身份验证的插件； 基于本机密码哈希方法实现身份验证。 sha256_password 为用户帐户密码实现SHA-256哈希，比本地身份验证更强； 随着技术的发展，SHA1和其他哈希算法的前身（例如MD5）已被证明非常容易破解； sha2更笼统地指SHA-2类加密算法，其中256位加密是其中的一种256长度的实例。 caching_sha2_password sha256_password实现基本的SHA-256身份验证； caching_sha2_password实现SHA-256身份验证（如sha256_password）, 但在服务器端使用高速缓存可提高性能，并具有其他功能可更广泛地应用。 mysql> show variables like 'default_authentication_plugin'; +-------------------------------+-----------------------+ | Variable_name | Value | +-------------------------------+-----------------------+ | default_authentication_plugin | caching_sha2_password | +-------------------------------+-----------------------+ 1 row in set (0.12 sec)
创建管理用户 drop user 'automng'@'127.0.0.1' ; drop user 'automng'@'localhost' ; drop user 'automng'@'%'; create user 'automng'@'127.0.0.1' ; GRANT ALL PRIVILEGES ON *.* TO 'automng'@'127.0.0.1' WITH GRANT OPTION; flush privileges; ALTER USER 'automng'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY 'Automng_123'; create user 'automng'@'localhost' ; GRANT ALL PRIVILEGES ON *.* TO 'automng'@'localhost' WITH GRANT OPTION; flush privileges; ALTER USER 'automng'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Automng_123'; create user 'automng'@'%' identified by 'Automng_123'; GRANT ALL PRIVILEGES ON *.* TO 'automng'@'%' WITH GRANT OPTION; flush privileges; ALTER USER 'automng'@'%' IDENTIFIED WITH mysql_native_password BY 'Automng_123'; mysql> select user,host,password_expired,password_last_changed,password_lifetime from mysql.user; +------------------+-----------+------------------+-----------------------+-------------------+ | user | host | password_expired | password_last_changed | password_lifetime | +------------------+-----------+------------------+-----------------------+-------------------+ | automng | % | N | 2023-01-29 16:53:46 | NULL | | automng | 127.0.0.1 | N | 2023-01-29 16:53:46 | NULL | | automng | localhost | N | 2023-01-29 16:53:46 | NULL | | mysql.infoschema | localhost | N | 2023-01-29 15:39:40 | NULL | | mysql.session | localhost | N | 2023-01-29 15:39:40 | NULL | | mysql.sys | localhost | N | 2023-01-29 15:39:40 | NULL | | root | localhost | N | 2023-01-29 16:51:59 | 0 | +------------------+-----------+------------------+-----------------------+-------------------+ 刷新权限 flush privileges; 更改用户的加密方式 ALTER USER 'automng'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Automng_123'; ALTER USER 'automng'@'%' IDENTIFIED WITH caching_sha2_password BY 'Automng_123'; mysql> select host,user,plugin from mysql.user; +-----------+------------------+-----------------------+ | host | user | plugin | +-----------+------------------+-----------------------+ | % | automng | caching_sha2_password | | 127.0.0.1 | automng | mysql_native_password | | localhost | automng | caching_sha2_password | | localhost | mysql.infoschema | caching_sha2_password | | localhost | mysql.session | caching_sha2_password | | localhost | mysql.sys | caching_sha2_password | | localhost | root | mysql_native_password | +-----------+------------------+-----------------------+ 7 rows in set (0.00 sec)
免费 这是一个数据库连接的客户端,使用Java编写,有windows,mac,ubantu多个系统的版本,需要先配置JDK环境 它连接数据库的驱动就是Java连接数据库的驱动,如果你是Java程序员,可直接将你代码中连接数据库的驱动拿过来用 如果不是联网环境,需要先下载相关Java连接数据库的驱动,点击编辑驱动 连接mysql数据库因版本不同驱动名称有细微差别,就是Java代码连接池配置的那一套 驱动类型:MySQL 类名:com.mysql.jdbc.Driver (这个Java类名因版本不同会有细微差异) 添加文件,添加java连接数据库的驱动,mysql-connector-java-5.1.49.jar com.mysql.jdbc.Driver，导入的是 mysql-connector-java 5 com.mysql.cj.jdbc.Driver，导入的是 mysql-connector-java 8 驱动这一部分剩下的部分不需要填写,就是导入驱动jar包,然后填写上类名
只想在linux上简单搭建个mysql，能用就行，不要求什么优化配置，能不能快速搞定？ 答案是超简安装 超简安装就是将已经安装好的一个数据库+配置文件打包 ubantu上的一次操作记录,linux此方式及此包同样可行 sudo apt-get update sudo apt-get install libncurses5 不更换数据目录 --------------------------------------------------------------------------------- 操作开始,进入root 用户： 系统上没有mysql用户,adduser提前创建 解压mysql_8.0.32.tar.gz压缩包 （文章结尾可以下载，在参考部分或点击直接下载） cd /opt/app root@ai1:/opt/app# tar -xvf mysql_8.0.32.tar.gz chown -R mysql.mysql /opt/app/mysql 配置环境变量 root@ai1:~# vim /etc/profile export PATH=/opt/app/mysql/bin:$PATH root@ai1:~# source /etc/profile 以root用户启动 nohup mysqld_safe --defaults-file=/opt/app/mysql/data/my_13301.cnf --user=mysql & 或者 nohup /opt/app/mysql/bin/mysqld_safe --defaults-file=/opt/app/mysql/data/my_13301.cnf --user=mysql & 访问 mysql -uroot -prootroot -S /opt/app/mysql/data/logs/my_13301.sock mysql -uautomng -pAutomng_123 -S /opt/app/mysql/data/logs/my_13301.sock 这是一个空库，需要自己提前创建数据库 create database db1; 启动与关闭 alias mstart="nohup mysqld_safe --defaults-file=/opt/app/mysql/data/my_13301.cnf --user=mysql &" alias min="mysql -uautomng -pAutomng_123 -S /opt/app/mysql/data/logs/my_13301.sock" 注意点：先创建mysql用户，然后在/opt/app下解压，这个目录是固定的，必须是这个目录，然后就可以用了 若要换软件安装目录，需要更新配置文件中的basedir目录， 换其他配置更新配置文件中对应的相关参数 更换数据目录 ---------------------------------------------------------------- 数据目录原来在/opt/data目录，现在换为/opt/app/mysql/data，换其他位置参考下面的操作 $ sudo su - root # adduser mysql rsync -rltDv mysql_8.0.32.tar.gz /opt/app/ cd /opt/app tar -xvf mysql_8.0.32.tar.gz 修改配置文件相关路径 将/opt/data目录替换为实际上的/opt/app/mysql/data root@ai1:/opt/app/mysql/data# cp my_13301.cnf my_13301.cnf.bak root@ai1:/opt/app/mysql/data# sed -i 's/\/opt\/data/\/opt\/app\/mysql\/data/g' my_13301.cnf cd /opt/app/mysql/data/mysql_13301 sed -i 's/\/opt\/data/\/opt\/app\/mysql\/data/g' mysql-bin.index chown -R mysql.mysql /opt/app/mysql 启动 root@ai1:~# vim /etc/profile export PATH=/opt/app/mysql/bin:$PATH root@ai1:~# source /etc/profile nohup mysqld_safe --defaults-file=/opt/app/mysql/data/my_13301.cnf --user=mysql & root@ai1:~# mysql -uroot -prootroot -S /opt/app/mysql/data/logs/my_13301.sock mysql> exit Bye root@ai1:~# root@ai1:~# mysql -uautomng -pAutomng_123 -S /opt/app/mysql/data/logs/my_13301.sock mysql> shutdown; Query OK, 0 rows affected (0.01 sec) mysql> exit Bye
超简安装数据目录变更
验证mysql可用后，如果数据量大，可以换一个更大空间目录作为数据目录 mkdir -p /wks/data/mysql chown mysql.mysql /wks/data/mysql/ 关闭mysql,然后同步文件 rsync -rltDv /opt/app/mysql/data/ /wks/data/mysql/ [root@nd /]#ls /wks/data/mysql/ logs my_13301.cnf my_13301.cnf.bak mysql_13301 tmp 用 go/python 实现配置文件的修改得了...
mysql软件本身的安装实际上就是下载解压, 主要工作量在配置文件修改,数据库创建,root密码修改,用户创建等等 方便的前提是提前创建好一个demo,相当于创建一个模板,后续批量建库就是把这个模板COPY过去, 授权 chown -R mysql.mysql /opt/data 然后启动 前面安装的时候有提到过统一规划,规划好了,后续维护就方便,遇到特殊情况,微调一下配置文件就可以了
添加用户mysql groupadd mysql useradd -g mysql mysql cp mysql_8.0.32.tar.gz /data/ tar -xvf mysql_8.0.32.tar.gz cd /data/mysql/data 数据目录 [root@nd data]# ls logs my_13301.cnf my_13301.cnf.bak mysql_13301 tmp 数据总目录： 下面放日志目录，tmp目录，各种实例的配置文件，实例目录 实例目录以端口区分 可通过下面的命令修改数据目录与基目录 python create_conf.py -d /data/mysql/data -b /data/mysql 端口默认13301，如果更换端口要确保实例目录存在 datadir = /data/mysql/data/mysql_13301 这里修改了数据目录，基目录，buffer_pool_size python mysql_create_conf.py -d /data/mysql/data -b /data/mysql -s 2G alias mstart="nohup mysqld_safe --defaults-file=/opt/app/mysql/data/my_13301.cnf --user=mysql &" alias min="mysql -uautomng -pAutomng_123 -S /opt/app/mysql/data/logs/my_13301.sock" vim mysql_13301_start.sh nohup /data/mysql/bin/mysqld_safe --defaults-file=/data/mysql/data/my_13301.cnf --user=mysql & sh mysql_13301_start.sh 无法启动时查看错误日志 /data/mysql/data/logs/my_13301_error.log 进入mysql /data/mysql/bin/mysql -uroot -prootroot -S /data/mysql/data/logs/my_13301.sock /data/mysql/bin/mysql -uautomng -pAutomng_123 -S /data/mysql/data/logs/my_13301.sock 更新root密码 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'w42703_W42703'; ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
python create_conf.py -b /data/mysql -d /data/mysql/data -s 2G -P 3307
进入datadir目录 vim create_conf.py [root@nd data]# python create_conf.py -b /data/mysql -d /data/mysql/data -s 2G -P 3307 cp -r /data/mysql/data/mysql_13301 /data/mysql/data/mysql_3307 nohup /data/mysql/bin/mysqld_safe --defaults-file=/data/mysql/data/my_3307.cnf --user=mysql & [root@nd data]# ls create_conf.py logs my_13301.cnf my_13301.cnf.bak my_3307.cnf mysql_13301 mysql_3307 mysql_start_3307.sh tmp 进入mysql /data/mysql/bin/mysql -uroot -prootroot -S /data/mysql/data/logs/my_3307.sock /data/mysql/bin/mysql -uautomng -pAutomng_123 -S /data/mysql/data/logs/my_3307.sock 更新root密码 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Automng_123'; ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
脚本内容
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import sys, getopt
import os
#读取当前目录下的配置文件
def get_conf_all(file_name = 'my_13301.cnf.bak'):
"""读取配置文件
"""
absolute_path = os.path.abspath(file_name)
content= ''
# 打开文件
with open(absolute_path, 'r') as file:
# 读取文件的所有内容
content = file.read()
return content
def change_bin_index(data_dir,file_name = 'mysql_13301/mysql-bin.index'):
"""读取配置文件
"""
absolute_path = os.path.abspath(file_name)
content= ''
if os.path.exists(absolute_path):
# 打开文件
with open(absolute_path, 'r') as file:
# 读取文件的所有内容
content = file.read()
else:
print(absolute_path+" does not exists")
return
content = content.replace("/opt/app/mysql/data",data_dir)
with open(file_name, "w") as file:
file.write(content)
return content
def main(argv):
base_dir = ''
data_dir = ''
port = 13301
try:
content = get_conf_all()
opts, args = getopt.getopt(argv,"hb:d:P:s:",["bbase_dir=","ddata_dir=","Pport=","spool_size="])
except getopt.GetoptError:
print('create_conf.py -b base_dir -d data_dir -P port -s pool_size')
sys.exit(2)
for opt, arg in opts:
if opt == '-h':
print('create_conf.py -b base_dir -d data_dir -P port -s pool_size')
sys.exit()
elif opt in ("-d", "--ddata_dir"):
data_dir = arg
# print('set data_dir=', data_dir)
if data_dir[-1]=="/":
data_dir = data_dir[:-1]
content = content.replace("/opt/data",data_dir)
bin_index_content = change_bin_index(data_dir)
elif opt in ("-P", "--Pport"):
port = arg
content = content.replace("13301",port)
datadir2 = data_dir+"/mysql_"+port
copy_cmd = "cp -r "+data_dir+"/mysql_13301 "+datadir2
print(copy_cmd)
try:
os.system(copy_cmd)
bin_index_content = bin_index_content.replace("13301",port)
file_name = datadir2+'/mysql-bin.index'
absolute_path = os.path.abspath(file_name)
if os.path.exists(absolute_path):
with open(absolute_path, "w") as file:
file.write(bin_index_content)
except Exception as e:
print(e)
if not os.path.exists(datadir2):
print(datadir2,"The directory does not exist. Please copy the original data directory to this directory")
print("check the mysql-bin.index in ",datadir2)
elif opt in ("-s", "--spool_size"):
pool_size = arg
content = content.replace("300M",pool_size)
elif opt in ("-b", "--bbase_dir"):
base_dir = arg
# print('set base_dir=', base_dir)
if base_dir[-1]=="/":
base_dir = base_dir[:-1]
content = content.replace("/opt/app/mysql", base_dir)
mysql_conf_path = os.path.abspath("my_"+str(port)+".cnf")
# print("mysql_conf_path:",mysql_conf_path)
with open(mysql_conf_path, "w") as file:
file.write(content)
start_msqyl = "nohup "+base_dir+"/bin/mysqld_safe --defaults-file="+data_dir+"/my_"+port+".cnf --user=mysql &"
print(start_msqyl)
mysql_start_shell = os.path.abspath("mysql_start_"+port+".sh")
with open(mysql_start_shell, "w") as file:
file.write(start_msqyl)
os.system("chown -R mysql.mysql "+datadir2)
log_file = data_dir+"/logs/my_"+port+"_error.log"
if not os.path.exists(log_file):
os.system("touch "+log_file)
os.system("chown -R mysql.mysql "+log_file)
if __name__ == "__main__":
main(sys.argv[1:])
mysql_8.0.32.tar.gz 夸克网盘下载