因使用源碼安裝的MySQL5.7.28多實(shí)例，在導(dǎo)入數(shù)據(jù)庫(kù)時(shí)會(huì)出現(xiàn)問題，所以重新研究使用mysql_multi的方法來管理多實(shí)例，經(jīng)過測(cè)試環(huán)境驗(yàn)證之后，在各方面使用上特別在備份還原上，沒有報(bào)MySQL5.7.28多實(shí)例的問題，踩了不少坑，這里我將我的部署過程分享下，如果在哪里出問題的，還請(qǐng)多多指正與指導(dǎo)，謝謝！！
參考文章：Centos7.5安裝mysql5.7.24二進(jìn)制包方式部署
https://www.jb51.net/article/151867.htm
本從就直接從2.7章節(jié)開始安裝mysql多實(shí)例，具體部署過程如下：
2.7 安裝mysql多實(shí)例
2.7.1. 創(chuàng)建軟件安裝目錄(部署路徑請(qǐng)根據(jù)實(shí)際修改)
[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 -c https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
[root@~]# tar zxvf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
[root@~]# mv mysql-5.7.28-linux-glibc2.12-x86_64 /usr/local/mysql
[root@~]# chown -R mysql:mysql /usr/local/mysql
[root@~]# chown -R mysql:mysql /data
2.7.3. MySQL參數(shù)配置
配置my.cnf參數(shù)文件
（1）server_id=3306與server_id=3307數(shù)值請(qǐng)根據(jù)實(shí)際配置，注意配置的id值與局域網(wǎng)內(nèi)其他各實(shí)例所配置的數(shù)值不可以沖突；
（2）max_connections=1000配置MySQL數(shù)據(jù)庫(kù)的最大連接數(shù)，根據(jù)實(shí)際需要配置，其他參數(shù)的優(yōu)化根據(jù)實(shí)際需要修改或添
加；
（3）配置文件全部?jī)?nèi)容如下
[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環(huán)境變量
[root@~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile
[root@~]# tail -1 /etc/profile
[root@~]# source /etc/profile
2.7.5. testone實(shí)例初始化
操作實(shí)例初始化
[root@~]# mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql/ --user=mysql --datadir=/data/mysql/3306/data/ > /tmp/3306.log 2>1
[root@~]# tail -100f /tmp/3306.log
---使用tail命令查看初始化日志，有出現(xiàn)如下內(nèi)容，即表示初始化完成（其中#5+t+xYW+t?即為root用戶的臨時(shí)密碼）
A temporary password is generated for root@localhost: #5+t+xYW+t?
生成ssl文件
[root@~]# mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3306/data
啟動(dòng)testone實(shí)例
[root@~]# mysqld_multi start 3306
[root@~]# tail -100f /data/mysql/3306/logs/error.log
----使用tail命令查看啟動(dòng)日志，有出現(xiàn)即表示啟動(dòng)成功
Version: '5.7.28-log' socket: '/data/mysql/3306/mysql3306.sock' port: 3306 MySQL Community Server (GPL)
修改root密碼
[root@~]# less /tmp/3306.log | grep 'A temporary password'
[root@~]# mysql -uroot -p -S /data/mysql/3306/mysql3306.sock
Enter password:
mysql> alter user 'root'@'localhost' identified by '統(tǒng)一密碼';
mysql> flush privileges;
mysql> exit;
驗(yàn)證 testone root用戶統(tǒng)一密碼（界面正常輸出information_schema內(nèi)容表示正常）
[root@~]# mysql -uroot -p -S /data/mysql/3306/mysql3306.sock -e "show databases;" | grep information_schema
Enter password:
修改my.cnf配置文件，將修改的統(tǒng)一密碼，添加到配置文件中
[root@~]# sed -i "s@^#user=root@user=root@g" /etc/my.cnf
[root@~]# sed -i "s@^#pass=@pass=統(tǒng)一密碼@g" /etc/my.cnf
[root@~]# cat /etc/my.cnf | grep pass= ---使用cat命令查看配置文件pass字段輸出的結(jié)果是否一致
停止testone實(shí)例
[root@~]# mysqld_multi stop 3306
[root@~]# netstat -tnlp|grep 3306 ---輸入結(jié)果為空表示服務(wù)停止正常
2.7.6. testtwo實(shí)例初始化
操作實(shí)例初始化
[root@~]# mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql/ --user=mysql --datadir=/data/mysql/3307/data/ > /tmp/3307.log 2>1
[root@~]# tail -100f /tmp/3307.log
---使用tail命令查看初始化日志，有出現(xiàn)如下內(nèi)容，即表示初始化完成（其中-pn>t;Ye)Ay6=I即為root用戶的臨時(shí)密碼）
A temporary password is generated for root@localhost: -pn>t;Ye)Ay6=I
生成ssl文件
[root@~]# mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3307/data
啟動(dòng)testwo實(shí)例
[root@~]# mysqld_multi start 3307
----使用tail命令查看啟動(dòng)日志，有出現(xiàn)即表示啟動(dòng)成功
[root@~]# tail -100f /data/mysql/3307/logs/error.log
Version: '5.7.28-log' socket: '/data/mysql/3307/mysql3307.sock' port: 3307 MySQL Community Server (GPL)
修改root密碼
[root@~]# less /tmp/3307.log|grep 'A temporary password'
[root@~]# mysql -uroot -p -S /data/mysql/3307/mysql3307.sock
Enter password:
mysql> alter user 'root'@'localhost' identified by '統(tǒng)一密碼';
mysql> flush privileges;
mysql> exit;
驗(yàn)證 testtwo root用戶統(tǒng)一密碼（界面正常輸出information_schema內(nèi)容表示正常）
[root@~]# mysql -uroot -p -S /data/mysql/3307/mysql3307.sock -e "show databases;" | grep information_schema
Enter password:
停止testtwo實(shí)例
[root@~]# mysqld_multi stop 3307
[root@~]# netstat -tnlp|grep 3307 ---輸入結(jié)果為空表示服務(wù)停止正常
2.7.7. mysqld_multi多實(shí)例管理命令
啟動(dòng)全部實(shí)例
[root@~]# mysqld_multi start
停止單個(gè)實(shí)例
[root@~]# mysqld_multi stop 3306
[root@~]# mysqld_multi stop 3307
啟動(dòng)單個(gè)實(shí)例
[root@~]# mysqld_multi start 3306
[root@~]# mysqld_multi start 3307
查看全部實(shí)例的狀態(tài)(is running)
[root@~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
查看單個(gè)實(shí)例狀態(tài)
[root@~]# mysqld_multi report 3306
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
[root@~]# mysqld_multi report 3307
Reporting MySQL servers
MySQL server from group: mysqld3307 is running
停止全部實(shí)例
[root@~]# mysqld_multi stop
2.7.8. 數(shù)據(jù)導(dǎo)入
（1）將待導(dǎo)入的數(shù)據(jù)庫(kù)腳本（test.sql、testtwo.sql）文件（名稱根據(jù)實(shí)際情況操作）上傳至/data/路徑下
（2）使用命令確認(rèn)兩實(shí)例已經(jīng)處于運(yùn)行狀態(tài)
[root@~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
（3）進(jìn)入testone的mysql數(shù)據(jù)庫(kù)創(chuàng)建testone數(shù)據(jù)庫(kù)實(shí)例、用戶名及密碼并導(dǎo)入數(shù)據(jù)
[root@~]# cd /data/
[root@~]# mysql -uroot -p -S /data/mysql/3306/mysql3306.sock
Enter password:
mysql>create database testone default character set utf8 collate utf8_bin;
mysql> grant select,insert,update,delete,create,alter,execute on testone.* to 'testone'@'%' identified by '密碼';
mysql> flush privileges;
mysql> exit
[root@~]# mysql -uroot -p -S /data/mysql/3306/mysql3306.sock testone /data/test.sql
（3）進(jìn)入testtwo的mysql數(shù)據(jù)庫(kù)，創(chuàng)建testtwo服務(wù)數(shù)據(jù)庫(kù)實(shí)例、用戶名與密碼并導(dǎo)入數(shù)據(jù)
[root@~]# mysql -uroot -p -S /data/mysql/3307/mysql3307.sock
Enter password:
mysql> create database testtwo default character set utf8 collate utf8_bin;
mysql> grant select,insert,update,delete,create,alter,execute on testtwo.* to 'testtwo'@'%' identified by '密碼';
mysql> flush privileges;
mysql> exit
[root@~]# mysql -uroot -p -S /data/mysql/3307/mysql3307.sock testtwo /data/testtwo.sql
2.7.9. 防火墻配置
根據(jù)實(shí)際要求，添加開放端口
[root@~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
[root@~]# firewall-cmd --zone=public --add-port=3307/tcp --permanent
重新載入
[root@~]# firewall-cmd --reload
總結(jié)
以上所述是小編給大家介紹的CentOS7.5使用mysql_multi方式安裝MySQL5.7.28多實(shí)例，希望對(duì)大家有所幫助，如果大家有任何疑問請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！如果你覺得本文對(duì)你有幫助，歡迎轉(zhuǎn)載，煩請(qǐng)注明出處，謝謝！
您可能感興趣的文章:- CentOS7.5 安裝 Mysql8.0.19的教程圖文詳解
- Centos7.5安裝mysql5.7.24二進(jìn)制包方式部署
- CentOS7.5 安裝MySql的教程
