CentOS7.5使用mysql_multi方式安装MySQL5.7.28多实例(详解)

2023-08-02 22:05:03 59

因使用源码安装的MySQL5.7.28多实例，在导入数据库时会出现问题，所以重新研究使用mysql_multi的方法来管理多实例，经过测试环境验证之后，在各方面使用上特别在备份还原上，没有报MySQL5.7.28多实例的问题，踩了不少坑，这里我将我的部署过程分享下，如果在哪里出问题的，还请多多指正与指导，谢谢！！

参考文章：Centos7.5安装mysql5.7.24二进制包方式部署

https://www.nhooo.com/article/151867.htm

本从就直接从2.7章节开始安装mysql多实例，具体部署过程如下：

2.7安装mysql多实例

2.7.1.创建软件安装目录(部署路径请根据实际修改)

[root@~]#mkdir-pv/data/mysql/{3306,3307}
[root@~]#mkdir-v/data/mysql/3306/{logs,data,binlog}
[root@~]#mkdir-v/data/mysql/3307/{logs,data,binlog}

2.7.2.MySQL安装包下载

[root@~]#cd/opt
[root@~]#wget-chttps://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
[root@~]#tarzxvfmysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
[root@~]#mvmysql-5.7.28-linux-glibc2.12-x86_64/usr/local/mysql
[root@~]#chown-Rmysql:mysql/usr/local/mysql
[root@~]#chown-Rmysql:mysql/data

2.7.3.MySQL参数配置

配置my.cnf参数文件

（1）server_id=3306与server_id=3307数值请根据实际配置，注意配置的id值与局域网内其他各实例所配置的数值不可以冲突；

（2）max_connections=1000配置MySQL数据库的最大连接数，根据实际需要配置，其他参数的优化根据实际需要修改或添

加；

（3）配置文件全部内容如下

[root@~]#vim/etc/my.cnf
[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld
mysqladmin=/usr/local/mysql/bin/mysqladmin
log=/data/mysql/mysqld_multi.log
#user=root
#pass=

[mysql]
prompt="\u@jsshapp\R:\m:\s[\d]>"
no-auto-rehash

[mysqld3306]
user=mysql
port=3306
symbolic-links=0
#basedir=/usr/
datadir=/data/mysql/3306/data
socket=/data/mysql/3306/mysql3306.sock
pid-file=/data/mysql/3306/mysqld3306.pid
server_id=3306
character_set_server=utf8
max_connections=1000
skip_name_resolve=1
open_files_limit=65536
thread_cache_size=64
table_open_cache=4096
table_definition_cache=1024
table_open_cache_instances=64
max_prepared_stmt_count=1048576
explicit_defaults_for_timestamp=true
log_timestamps=system

binlog_format=row
log_bin=/data/mysql/3306/binlog/mysql-bin
binlog_rows_query_log_events=on
expire_logs_days=7
binlog_cache_size=4M
max_binlog_cache_size=2G
max_binlog_size=1G
sync_binlog=1
log_bin_trust_function_creators=1

slow_query_log=on
slow_query_log_file=/data/mysql/3306/data/slow.log
log-error=/data/mysql/3306/logs/error.log
log_queries_not_using_indexes=on
long_query_time=1.000000

gtid_mode=on
enforce_gtid_consistency=on

default_storage_engine=innodb
default_tmp_storage_engine=innodb
innodb_data_file_path=ibdata1:12M:autoextend:max:2000M
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:2000M
innodb_buffer_pool_filename=ib_buffer_pool
innodb_log_files_in_group=3
innodb_log_file_size=512M
innodb_online_alter_log_max_size=1024M
innodb_open_files=4096
innodb_page_size=16k
innodb_thread_concurrency=0
innodb_read_io_threads=4
innodb_write_io_threads=4
innodb_purge_threads=4
innodb_page_cleaners=4
innodb_print_all_deadlocks=on
innodb_lock_wait_timeout=20
innodb_spin_wait_delay=128
innodb_autoinc_lock_mode=2
innodb_io_capacity=200
innodb_io_capacity_max=2000
#innodb_flush_neighbors=
innodb_log_buffer_size=8M
innodb_flush_log_at_timeout=1
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=1024M
innodb_buffer_pool_instances=4
autocommit=1
innodb_buffer_pool_dump_pct=25
innodb_buffer_pool_dump_at_shutdown=ON
innodb_buffer_pool_load_at_startup=ON

[mysqld3307]
user=mysql
port=3307
symbolic-links=0
lower_case_table_names=1
#basedir=/usr/
datadir=/data/mysql/3307/data
socket=/data/mysql/3307/mysql3307.sock
pid-file=/data/mysql/3307/mysqld3307.pid
server_id=3307
character_set_server=utf8
max_connections=1000
skip_name_resolve=1
open_files_limit=65536
thread_cache_size=64
table_open_cache=4096
table_definition_cache=1024
table_open_cache_instances=64
max_prepared_stmt_count=1048576
explicit_defaults_for_timestamp=true
log_timestamps=system

binlog_format=row
log_bin=/data/mysql/3307/binlog/mysql-bin
binlog_rows_query_log_events=on
expire_logs_days=7
binlog_cache_size=4M
max_binlog_cache_size=2G
max_binlog_size=1G
sync_binlog=1

slow_query_log=on
slow_query_log_file=/data/mysql/3307/data/slow.log
log-error=/data/mysql/3307/logs/error.log
log_queries_not_using_indexes=on
long_query_time=1.000000

gtid_mode=on
enforce_gtid_consistency=on

default_storage_engine=innodb
default_tmp_storage_engine=innodb
innodb_data_file_path=ibdata1:12M:autoextend:max:2000M
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:2000M
innodb_buffer_pool_filename=ib_buffer_pool
innodb_log_files_in_group=3
innodb_log_file_size=512M
innodb_online_alter_log_max_size=1024M
innodb_open_files=4096
innodb_page_size=16k
innodb_thread_concurrency=0
innodb_read_io_threads=4
innodb_write_io_threads=4
innodb_purge_threads=4
innodb_page_cleaners=4
innodb_print_all_deadlocks=on
innodb_lock_wait_timeout=20
innodb_spin_wait_delay=128
innodb_autoinc_lock_mode=2
innodb_io_capacity=200
innodb_io_capacity_max=2000
#innodb_flush_neighbors=
innodb_log_buffer_size=8M
innodb_flush_log_at_timeout=1
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=1024M
innodb_buffer_pool_instances=4
autocommit=1
innodb_buffer_pool_dump_pct=25
innodb_buffer_pool_dump_at_shutdown=ON
innodb_buffer_pool_load_at_startup=ON

[mysqldump]
quick
max_allowed_packet=32M

2.7.4.配置MySQL环境变量

[root@~]#echo'exportPATH=/usr/local/mysql/bin:$PATH'>>/etc/profile
[root@~]#tail-1/etc/profile
[root@~]#source/etc/profile

2.7.5.testone实例初始化

操作实例初始化

[root@~]#mysqld--defaults-file=/etc/my.cnf--initialize--basedir=/usr/local/mysql/--user=mysql--datadir=/data/mysql/3306/data/>/tmp/3306.log2>&1
[root@~]#tail-100f/tmp/3306.log
---使用tail命令查看初始化日志，有出现如下内容，即表示初始化完成（其中#5+t+xYW+
生成ssl文件
[root@~]#mysql_ssl_rsa_setup--user=mysql--basedir=/usr/local/mysql--datadir=/data/mysql/3306/data
启动testone实例
[root@~]#mysqld_multistart3306
[root@~]#tail-100f/data/mysql/3306/logs/error.log
----使用tail命令查看启动日志，有出现即表示启动成功

Version:'5.7.28-log'socket:'/data/mysql/3306/mysql3306.sock'port:3306MySQLCommunityServer(GPL)

修改root密码
[root@~]#less/tmp/3306.log|grep'Atemporarypassword'
[root@~]#mysql-uroot-p-S/data/mysql/3306/mysql3306.sock
Enterpassword:
mysql>alteruser'root'@'localhost'identifiedby'统一密码';
mysql>flushprivileges;
mysql>exit;
验证testoneroot用户统一密码（界面正常输出information_schema内容表示正常）
[root@~]#mysql-uroot-p-S/data/mysql/3306/mysql3306.sock-e"showdatabases;"|grepinformation_schema
Enterpassword:
修改my.cnf配置文件，将修改的统一密码，添加到配置文件中
[root@~]#sed-i"s@^#user=root@user=root@g"/etc/my.cnf
[root@~]#sed-i"s@^#pass=@pass=统一密码@g"/etc/my.cnf
[root@~]#cat/etc/my.cnf|greppass=---使用cat命令查看配置文件pass字段输出的结果是否一致
停止testone实例
[root@~]#mysqld_multistop3306
[root@~]#netstat-tnlp|grep3306---输入结果为空表示服务停止正常
2.7.6.testtwo实例初始化
操作实例初始化
[root@~]#mysqld--defaults-file=/etc/my.cnf--initialize--basedir=/usr/local/mysql/--user=mysql--datadir=/data/mysql/3307/data/>/tmp/3307.log2>&1
[root@~]#tail-100f/tmp/3307.log
---使用tail命令查看初始化日志，有出现如下内容，即表示初始化完成（其中-pn>t;Ye)Ay6=I即为root用户的临时密码）
Atemporarypasswordisgeneratedforroot@localhost:-pn>t;Ye)Ay6=I
生成ssl文件

[root@~]#mysql_ssl_rsa_setup--user=mysql--basedir=/usr/local/mysql--datadir=/data/mysql/3307/data
启动testwo实例
[root@~]#mysqld_multistart3307
----使用tail命令查看启动日志，有出现即表示启动成功
[root@~]#tail-100f/data/mysql/3307/logs/error.log
Version:'5.7.28-log'socket:'/data/mysql/3307/mysql3307.sock'port:3307MySQLCommunityServer(GPL)

修改root密码
[root@~]#less/tmp/3307.log|grep'Atemporarypassword'
[root@~]#mysql-uroot-p-S/data/mysql/3307/mysql3307.sock
Enterpassword:
mysql>alteruser'root'@'localhost'identifiedby'统一密码';
mysql>flushprivileges;
mysql>exit;
验证testtworoot用户统一密码（界面正常输出information_schema内容表示正常）
[root@~]#mysql-uroot-p-S/data/mysql/3307/mysql3307.sock-e"showdatabases;"|grepinformation_schema
Enterpassword:
停止testtwo实例
[root@~]#mysqld_multistop3307
[root@~]#netstat-tnlp|grep3307---输入结果为空表示服务停止正常
2.7.7.mysqld_multi多实例管理命令
启动全部实例
[root@~]#mysqld_multistart
停止单个实例
[root@~]#mysqld_multistop3306
[root@~]#mysqld_multistop3307
启动单个实例
[root@~]#mysqld_multistart3306
[root@~]#mysqld_multistart3307
查看全部实例的状态(isrunning)
[root@~]#mysqld_multireport
ReportingMySQLservers
MySQLserverfromgroup:mysqld3306isrunning
MySQLserverfromgroup:mysqld3307isrunning


查看单个实例状态
[root@~]#mysqld_multireport3306
ReportingMySQLservers
MySQLserverfromgroup:mysqld3306isrunning
[root@~]#mysqld_multireport3307
ReportingMySQLservers
MySQLserverfromgroup:mysqld3307isrunning
停止全部实例
[root@~]#mysqld_multistop
2.7.8.数据导入
（1）将待导入的数据库脚本（test.sql、testtwo.sql）文件（名称根据实际情况操作）上传至/data/路径下
（2）使用命令确认两实例已经处于运行状态
[root@~]#mysqld_multireport
ReportingMySQLservers
MySQLserverfromgroup:mysqld3306isrunning
MySQLserverfromgroup:mysqld3307isrunning
（3）进入testone的mysql数据库创建testone数据库实例、用户名及密码并导入数据
[root@~]#cd/data/
[root@~]#mysql-uroot-p-S/data/mysql/3306/mysql3306.sock
Enterpassword:
mysql>createdatabasetestonedefaultcharactersetutf8collateutf8_bin;
mysql>grantselect,insert,update,delete,create,alter,executeontestone.*to'testone'@'%'identifiedby'密码';
mysql>flushprivileges;
mysql>exit
[root@~]#mysql-uroot-p-S/data/mysql/3306/mysql3306.socktestone
（3）进入testtwo的mysql数据库，创建testtwo服务数据库实例、用户名与密码并导入数据
[root@~]#mysql-uroot-p-S/data/mysql/3307/mysql3307.sock
Enterpassword:
mysql>createdatabasetesttwodefaultcharactersetutf8collateutf8_bin;
mysql>grantselect,insert,update,delete,create,alter,executeontesttwo.*to'testtwo'@'%'identifiedby'密码';
mysql>flushprivileges;
mysql>exit
[root@~]#mysql-uroot-p-S/data/mysql/3307/mysql3307.socktesttwo
2.7.9.防火墙配置
根据实际要求，添加开放端口
[root@~]#firewall-cmd--zone=public--add-port=3306/tcp--permanent
[root@~]#firewall-cmd--zone=public--add-port=3307/tcp--permanent
重新载入
[root@~]#firewall-cmd--reload
总结
以上所述是小编给大家介绍的CentOS7.5使用mysql_multi方式安装MySQL5.7.28多实例，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。

CentOS7.5使用mysql_multi方式安装MySQL5.7.28多实例(详解)

热门推荐

随机推荐