��浣跨�ㄦ���疏浚�瑁���MySQL5.7.28澶�疏浚�岛�锛��ㄥ�煎�ユ�版��搴��朵��雾�伴��棰�锛���浠ラ���扮演��绌朵娇��mysql_multi���规��ョ�$��澶�疏浚�岛�锛�缁�杩�娴�璇���澧�楠�璇�涔���锛��ㄥ���归��浣跨�ㄤ��瑰���ㄥ�浠借���涓�锛�娌℃����MySQL5.7.28澶�疏浚�岛�����棰�锛�韪╀�涓�广大���锛�杩�����广大������ㄧ讲杩�绋���河渠�涓�锛�濡����ㄥ�����洪��棰���锛�杩�璇峰�澶���姝d���瀵硷�璋㈣阿锛�锛�
�充�mysql疏浚�瑁��ㄧ讲�雾���澧�涓���缃���浠ュ������绔�锛�CentOS7.5婧���疏浚�瑁�MySQL5.7.28澶�疏浚�岛�
https://blog.surowce-wtorne.com/8355320/2463218
��浠�广大辩�存�ヤ�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 -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���伴��缃�
�� ��缃�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 'export PATH=/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.log 2>&1
[root@~]#  tail -100f /tmp/3306.log

---浣跨��tail�戒护�ョ����彭����ュ�锛����雾�板�涓���疏浚癸��宠〃绀哄��彭���疏浚���锛��朵腑#5+t+xYW+<t?�充负root�ㄦ�风��涓存�跺����锛�
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

�� ����testone疏浚�岛�

[root@~]#  mysqld_multi start 3306
[root@~]#  tail -100f /data/mysql/3306/logs/error.log 

----浣跨��tail�戒护�ョ�����ㄦ�ュ�锛����雾�板�宠〃绀哄���ㄦ����

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 '缁�涓�瀵���';
mysql> flush privileges;
mysql> exit;

�� 楠�璇� testone root�ㄦ�风�涓�瀵���锛����㈡�e父杈���information_schema��疏浚硅〃绀烘�e父锛�

[root@~]#  mysql -uroot -p -S /data/mysql/3306/mysql3306.sock -e "show databases;" | grep information_schema
Enter password:

�� 淇���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 | grep pass=      ---浣跨��cat�戒护�ョ����缃���浠�pass海洋�娈佃��雾��缁�������涓���

�� ��姝�testone疏浚�岛�

[root@~]#  mysqld_multi stop 3306
[root@~]#  netstat -tnlp|grep 3306         ---杈��ョ���涓虹┖琛ㄧず���″��姝㈡�e父

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.log 2>&1
[root@~]#  tail -100f /tmp/3307.log

---浣跨��tail�戒护�ョ����彭����ュ�锛����雾�板�涓���疏浚癸��宠〃绀哄��彭���疏浚���锛��朵腑-pn>t;Ye)Ay6=I�充负root�ㄦ�风��涓存�跺����锛�
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

�� ����testwo疏浚�岛�

[root@~]# mysqld_multi start 3307

----浣跨��tail�戒护�ョ�����ㄦ�ュ�锛����雾�板�宠〃绀哄���ㄦ����

[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 '缁�涓�瀵���';
mysql> flush privileges;
mysql> exit;

�� 楠�璇� testtwo root�ㄦ�风�涓�瀵���锛����㈡�e父杈���information_schema��疏浚硅〃绀烘�e父锛�

[root@~]#  mysql -uroot -p -S /data/mysql/3307/mysql3307.sock -e "show databases;" | grep information_schema
Enter password:

�� ��姝�testtwo疏浚�岛�

[root@~]#  mysqld_multi stop 3307
[root@~]#  netstat -tnlp|grep 3307         ---杈��ョ���涓虹┖琛ㄧず���″��姝㈡�e父

2.7.7. mysqld_multi澶�疏浚�岛�绠$���戒护
�� ���ㄥ�ㄩ�ㄥ��岛�

[root@~]# mysqld_multi start

�� ��姝㈠��涓�疏浚�岛�

[root@~]#  mysqld_multi stop 3306
[root@~]#  mysqld_multi stop 3307

�� ���ㄥ��涓�疏浚�岛�

[root@~]#  mysqld_multi start 3306
[root@~]#  mysqld_multi start 3307

�� �ョ���ㄩ�ㄥ��岛����舵��(is running)

[root@~]#  mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running

�� �ョ����涓�疏浚�岛��舵��

[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

�� ��姝㈠�ㄩ�ㄥ��岛�

[root@~]# mysqld_multi stop

2.7.8. �版��瀵煎��
锛�1锛�广大�寰�瀵煎�ョ���版��搴�����锛�test.sql��testtwo.sql锛���浠讹���绉版�规��疏浚������垫��浣�锛�涓�浼���/data/璺�寰�涓�
锛�2锛�浣跨�ㄥ�戒护纭�璁や袱瀹�岛�宸茬�澶�河渠�杩�琛��舵��

[root@~]#  mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running

锛�3锛�杩���testone��mysql�版��搴���寤�testone�版��搴�疏浚�岛����ㄦ�山����瀵���骞跺�煎�ユ�版��

[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锛�杩���testtwo��mysql�版��搴�锛���寤�testtwo���℃�版��搴�疏浚�岛����ㄦ�山��涓�瀵���骞跺�煎�ユ�版��

[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. �茬��澧���缃�
�� �规��疏浚���瑕�姹�锛�娣诲��寮��剧����

[root@~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
[root@~]# firewall-cmd --zone=public --add-port=3307/tcp --permanent

�� ���拌浇��

[root@~]# firewall-cmd --reload