MySQL 5.7.16多源复制

演示一下在MySQL下搭建多主一从的过程。实验环境： 192.168.24.129:3306 192.168.24.129:3307 192.168.24.129:3308主库操作导出数据分别在3306和3307上导出需要的数据库。3306：登录数据库：[root@localhost 3306]# mysql -uroot -poldboy123 -S /tmp/mysql3306.sock锁表：mysql> flush tables with read lock;状态点：mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000006 | 154 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set （0.00 sec）另开窗口开始导数据：[root@localhost tmp]# mysqldump -uroot -poldboy123 -S /tmp/mysql3306.sock -F -R -x --master-data=2 -A --events|gzip >/tmp/dockerwy.sql.gz在此查看状态点两个要保持一致，否则表没有锁住mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000007 | 154 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set （0.00 sec）解锁表：mysql> unlock tables;3307：登录3307数据库： [root@localhost 3307]# mysql -uroot -poldboy123 -S /tmp/mysql3307.sock锁表：mysql>flush tables with read lock;查看状态点：mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000007 | 154 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set （0.00 sec）另开窗口导数据：[root@localhost 3307]# mysqldump -uroot -poldboy123 -S /tmp/mysql3307.sock -F -R -x --master-data=2 -A --events|gzip >/tmp/dockerwy_2.sql.gz从新查看状态点：mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000008 | 154 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set （0.00 sec）解锁表：mysql> unlock tables;Query OK, 0 rows affected （0.00 sec）建立授权账号分别在3306和3307上面建立授权账号3306：mysql> grant replication slave on *.* to "backup"@"192.168.24.129" identified by "backup";3307：mysql> grant replication slave on *.* to "backup"@"192.168.24.129" identified by "backup";从库操作修改从库存储方式修改3308的master-info和relay-info方式，从文件存储改为表存储。编辑配置文件[root@localhost 3308]# vim my.cnf在[mysqld]模块下添加如下两行master_info_repository=TABLErelay_log_info_repository=TABLE重启3308数据库：[root@localhost 3308]# /data/3308/mysqld restart重启之后我们可以登录数据库查看；[root@localhost 3308]# mysql -uroot -poldboy123 -S /tmp/mysql3308.sockmysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 3Server version: 5.7.16 MySQL Community Server （GPL）Copyright （c） 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type "help;" or "h" for help. Type "c" to clear the current input statement. mysql> show variables like "relay_log_info_repository";+---------------------------+-------+| Variable_name | Value |+---------------------------+-------+| relay_log_info_repository | TABLE |+---------------------------+-------+1 row in set （0.01 sec）mysql> show variables like "master_info_repository";+------------------------+-------+| Variable_name | Value |+------------------------+-------+| master_info_repository | TABLE |+------------------------+-------+1 row in set （0.01 sec）导入数据导入3306的数据：[root@localhost 3308]# gzip -d /tmp/dockerwy.sql.gz[root@localhost 3308]# mysql -uroot -poldboy123 -S /tmp/mysql3308.sock < /tmp/dockerwy.sql.导入3307的数据：[root@localhost 3308]# gzip -d /tmp/dockerwy_2.sql.gz[root@localhost 3308]# mysql -uroot -poldboy123 -S /tmp/mysql3308.sock < /tmp/dockerwy_2.sql执行change master to登录slave进行同步操作，分别change master两台服务器，后面以for channel ‘channel_name’区分mysql> change master to master_host="192.168.24.129",master_user="backup",master_port=3306,master_password="backup",master_log_file="mysql-bin.000006",master_log_pos=154 for channel "master_1";Query OK, 0 rows affected, 2 warnings （0.07 sec）mysql> change master to master_host="192.168.24.129",master_user="backup",master_port=3307,master_password="backup",master_log_file="mysql-bin.000007",master_log_pos=154 for channel "master_2";Query OK, 0 rows affected, 2 warnings （0.04 sec）启动slave操作可以通过start slave的方式去启动所有的复制，也可以通过单个复制源的方式，下面介绍单个复制的的启动演示mysql> start slave for channel "master_1";Query OK, 0 rows affected （0.01 sec）mysql> start slave for channel "master_2";Query OK, 0 rows affected （0.02 sec）查看同步状态正常启动后，可以查看同步的状态，执行show slave status for channel ‘channel_nameG’查看复制源master_1的同步状态；mysql> show slave status for channel "master_1"G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send event Master_Host: 192.168.24.129Master_User: backupMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000008Read_Master_Log_Pos: 154Relay_Log_File: localhost-relay-bin-master_1.000006Relay_Log_Pos: 367Relay_Master_Log_File: mysql-bin.000008Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB: Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 154Relay_Log_Space: 634Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 129Master_UUID: df233252-afd5-11e6-8070-000c2962d708Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 0Replicate_Rewrite_DB:Channel_Name: master_1Master_TLS_Version:1 row in set （0.00 sec）查看master_2的同步状态mysql> mysql> show slave status for channel "master_2"G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.24.129Master_User: backupMaster_Port: 3307Connect_Retry: 60Master_Log_File: mysql-bin.000008Read_Master_Log_Pos: 154Relay_Log_File: localhost-relay-bin-master_2.000004Relay_Log_Pos: 367Relay_Master_Log_File: mysql-bin.000008Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 154Relay_Log_Space: 634Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 130Master_UUID: 49bf20e1-afe2-11e6-aef5-000c2962d708Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 0Replicate_Rewrite_DB:Channel_Name: master_2Master_TLS_Version:1 row in set （0.00 sec）本文永久更新链接地址