基于5.7的多源复制
GTID复制的好处： 对dba来说是很happy的，在传统的方式里，你需要找到binlog和POS点，然后change master to指向，初学者不熟悉可能会找错，造成主从同步复制报错，在mysql5.6里，你不用再知道binlog和POS点， 你只需要知道master的IP、端口，账号密码即可，因为同步复制是自动的，mysql通过内部机制GTID自动找点同步。
多源复制：即多个主，复制到1个从库，所有db汇总到一个slave的实例 注意：多源数据库不能有同名库，否则会导致多源复制失败
环境：
master 两个实例， slave 一个实例
master master slave ip : 192.168.26.233 192.168.26.233 192.168.26.108 port : 3306 3307 3306
1.参数文件
3306参数文件
server-id = 3306100 port = 3306 log-bin = /data/mysql/mysql_3306/logs/mysql-bin #gtid gtid-mode = on enforce-gtid-consistency = 1
3307参数文件
server-id = 3307100 port = 3307 log-bin = /data/mysql/mysql_3307/logs/mysql-bin #gtid gtid-mode = on enforce-gtid-consistency = 1
2.把数据库保持在刚创建的状态 在3306 和 3307 master
show master status; reset master; reset slave all;
3.创建测试db
3306 创建 zw3306 3307 创建 zw3307
#3306 mysql> create database zw3306; Query OK, 1 row affected (0.00 sec)
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | zw3306 | +--------------------+ 5 rows in set (0.00 sec)
mysql> use zw3306 Database changed mysql> show tables; Empty set (0.00 sec)
mysql> create table t1(id int(5)); Query OK, 0 rows affected (0.25 sec)
mysql> insert into t1 values(1); Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(2); Query OK, 1 row affected (0.00 sec)
mysql> select * from t1; +------+ | id | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec)
#3307
mysql> create database zw3307; Query OK, 1 row affected (0.00 sec)
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | zw3307 | +--------------------+ 5 rows in set (0.00 sec)
mysql> use zw3307 Database changed mysql> show tables; Empty set (0.00 sec)
mysql> create table t2(id int(5)); Query OK, 0 rows affected (0.18 sec)
mysql> insert into t2 values(1); Query OK, 1 row affected (0.03 sec)
mysql> insert into t2 values(2); Query OK, 1 row affected (0.00 sec)
mysql> select * from t2; +------+ | id | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec)
4. master创建复制账号
mysql> create user 'repl'@'192.168.26.%' identified by 'repl'; Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'repl'@'192.168.26.%'; Query OK, 0 rows affected (0.00 sec)
mysql> create user 'repl3307'@'192.168.26.%' identified by 'repl3307'; Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'repl3307'@'192.168.26.%'; Query OK, 0 rows affected (0.00 sec)
5. slave端配置文件
server-id = 3306101 port = 3306 log-bin = /data/mysql/mysql_3306/logs/mysql-bin #gtid gtid-mode = on enforce-gtid-consistency = 1 #信息存在table里面 master_info_repository = table relay_log_info_repository = table
可动态修改 set global master_info_repository = 'table';
注意： 基于gtid有两种方法初始化同步
方法一： dump出数据
------------------------------------------------------------------------------------------------------------------------------- 1.将数据库导出来
[[email protected] ~]# mysqldump -S /tmp/mysql3306.sock -uroot -p --single-transaction --master-data=2 zw3306 > /tmp/backup_3306.sql [[email protected] ~]# mysqldump -S /tmp/mysql3307.sock -uroot -p --single-transaction --master-data=2 zw3307 > /tmp/backup_3307.sql 2. scp 到slave端
[[email protected] tmp]# scp *.sql [email protected]:/tmp reverse mapping checking getaddrinfo for bogon [192.168.26.108] failed - POSSIBLE BREAK-IN ATTEMPT! [email protected]'s password: backup_3306.sql 100% 2183 2.1KB/s 00:00 backup_3307.sql 100% 2183 2.1KB/s 00:00
SET @@GLOBAL.GTID_PURGED='7e354a2c-6f5f-11e6-997d-005056a36f08:1-4'; --3306 SET @@GLOBAL.GTID_PURGED='fed5c075-7009-11e6-954d-005056a36f08:1-6'; --3307 也可也用 show master status; 命令查看gtid
3.恢复到slave
4. 然后 change master ----------------------------------------------------------------------------------------------------------------------------
方法二：也可以直接change过来
基于GTID： change master to master_host='192.168.26.233', master_port=3306, master_user='repl',master_password='repl', master_auto_position=1 for channel 'master-3306100'; change master to master_host='192.168.26.233', master_port=3307, master_user='repl3307',master_password='repl3307', master_auto_position=1 for channel 'master-3307100';
for channel 多通道
基于binlog+postion
change master to master_host='192.168.26.233', master_port=3306, master_user='repl',master_password='repl',master_log_file='mysql-bin.000016',master_log_pos=154 for channel 'master-3306100'; change master to master_host='192.168.26.233', master_port=3307, master_user='repl3307',master_password='repl3307',master_log_file='mysql-bin.000016',master_log_pos=154 for channel 'master-3307100';
[email protected] [(none)]>show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.26.233 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: [email protected] Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 0 Relay_Log_Space: 154 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_UUID: Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: ba0d5587-74d6-11e6-ab5c-005056a3f46e:1 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: master-3306100 Master_TLS_Version: *************************** 2. row *************************** Slave_IO_State: Master_Host: 192.168.26.233 Master_User: repl Master_Port: 3307 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: [email protected] Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 0 Relay_Log_Space: 154 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_UUID: Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: ba0d5587-74d6-11e6-ab5c-005056a3f46e:1 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: master-3307100 Master_TLS_Version: 2 rows in set (0.00 sec)
可以看到有两个同步信息
6. 常用一些命令
可以用以下命令只看一个 show slave status for channel 'master-3306100'\G; show slave status for channel 'master-3307100'\G;
开关复制： start/stop slave; start/stop slave io_thread/sql_thread for channel;
查看slave
show slave io_thread/sql_thread for channel channel_name; 校验环境
master1 :3306 create databse zw3306
use performance_schema 下面的一张表
[email protected] [performance_schema]>select * from replication_connection_configuration \G; *************************** 1. row *************************** CHANNEL_NAME: master-3306100 HOST: 192.168.26.233 PORT: 3306 USER: repl NETWORK_INTERFACE: AUTO_POSITION: 1 SSL_ALLOWED: NO SSL_CA_FILE: SSL_CA_PATH: SSL_CERTIFICATE: SSL_CIPHER: SSL_KEY: SSL_VERIFY_SERVER_CERTIFICATE: NO SSL_CRL_FILE: SSL_CRL_PATH: CONNECTION_RETRY_INTERVAL: 60 CONNECTION_RETRY_COUNT: 86400 HEARTBEAT_INTERVAL: 30.000 TLS_VERSION: *************************** 2. row *************************** CHANNEL_NAME: master-3307100 HOST: 192.168.26.233 PORT: 3307 USER: repl NETWORK_INTERFACE: AUTO_POSITION: 1 SSL_ALLOWED: NO SSL_CA_FILE: SSL_CA_PATH: SSL_CERTIFICATE: SSL_CIPHER: SSL_KEY: SSL_VERIFY_SERVER_CERTIFICATE: NO SSL_CRL_FILE: SSL_CRL_PATH: CONNECTION_RETRY_INTERVAL: 60 CONNECTION_RETRY_COUNT: 86400 HEARTBEAT_INTERVAL: 30.000 TLS_VERSION: 2 rows in set (0.00 sec)
ERROR: No query specified
[email protected] [performance_schema]>select * from replication_connection_status\G; *************************** 1. row *************************** CHANNEL_NAME: master-3306100 GROUP_NAME: SOURCE_UUID: THREAD_ID: NULL SERVICE_STATE: OFF --还没开启 COUNT_RECEIVED_HEARTBEATS: 0 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00 RECEIVED_TRANSACTION_SET: LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 *************************** 2. row *************************** CHANNEL_NAME: master-3307100 GROUP_NAME: SOURCE_UUID: THREAD_ID: NULL SERVICE_STATE: OFF COUNT_RECEIVED_HEARTBEATS: 0 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00 RECEIVED_TRANSACTION_SET: LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 2 rows in set (0.00 sec)
ERROR: No query specified
还没开启服务！
7. 开启服务：
[email protected] [performance_schema]>start slave; Query OK, 0 rows affected (0.00 sec)
[email protected] [performance_schema]>select * from replication_connection_status\G; *************************** 1. row *************************** CHANNEL_NAME: master-3306100 GROUP_NAME: SOURCE_UUID: 7e354a2c-6f5f-11e6-997d-005056a36f08 THREAD_ID: 30 SERVICE_STATE: ON COUNT_RECEIVED_HEARTBEATS: 0 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00 RECEIVED_TRANSACTION_SET: 7e354a2c-6f5f-11e6-997d-005056a36f08:1-4 LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 *************************** 2. row *************************** CHANNEL_NAME: master-3307100 GROUP_NAME: SOURCE_UUID: fed5c075-7009-11e6-954d-005056a36f08 THREAD_ID: 32 SERVICE_STATE: ON COUNT_RECEIVED_HEARTBEATS: 0 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00 RECEIVED_TRANSACTION_SET: fed5c075-7009-11e6-954d-005056a36f08:1-6 LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 2 rows in set (0.00 sec)
ERROR: No query specified
有gtid的信息了！
[email protected] [performance_schema]>show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.26.233 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1002 Relay_Log_File: [email protected] Relay_Log_Pos: 1215 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1002 Relay_Log_Space: 1435 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 3306100 Master_UUID: 7e354a2c-6f5f-11e6-997d-005056a36f08 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 7e354a2c-6f5f-11e6-997d-005056a36f08:1-4 Executed_Gtid_Set: 7e354a2c-6f5f-11e6-997d-005056a36f08:1-4, ba0d5587-74d6-11e6-ab5c-005056a3f46e:1, fed5c075-7009-11e6-954d-005056a36f08:1 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: master-3306100 Master_TLS_Version: *************************** 2. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.26.233 Master_User: repl Master_Port: 3307 Connect_Retry: 60 Master_Log_File: mysql-bin.000011 Read_Master_Log_Pos: 194 Relay_Log_File: [email protected] Relay_Log_Pos: 532 Relay_Master_Log_File: mysql-bin.000010 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1396 Last_Error: Error 'Operation CREATE USER failed for 'repl'@'192.168.26.%'' on query. Default database: ''. Query: 'CREATE USER 'repl'@'192.168.26.%' IDENTIFIED WITH 'mysql_native_password' AS '*A424E797037BF97C19A2E88CF7891C5C2038C039'' Skip_Counter: 0 Exec_Master_Log_Pos: 319 Relay_Log_Space: 2678 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1396 Last_SQL_Error: Error 'Operation CREATE USER failed for 'repl'@'192.168.26.%'' on query. Default database: ''. Query: 'CREATE USER 'repl'@'192.168.26.%' IDENTIFIED WITH 'mysql_native_password' AS '*A424E797037BF97C19A2E88CF7891C5C2038C039'' Replicate_Ignore_Server_Ids: Master_Server_Id: 3307100 Master_UUID: fed5c075-7009-11e6-954d-005056a36f08 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 160914 15:04:09 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: fed5c075-7009-11e6-954d-005056a36f08:1-6 Executed_Gtid_Set: 7e354a2c-6f5f-11e6-997d-005056a36f08:1-4, ba0d5587-74d6-11e6-ab5c-005056a3f46e:1, fed5c075-7009-11e6-954d-005056a36f08:1 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: master-3307100 Master_TLS_Version: 2 rows in set (0.00 sec)
ERROR: No query specified
注意：上面这个蛋疼的问题折腾了我一天，原因是创建账号的时候master端和slave全部都创建了相同名字的账户。蛋疼的折腾了两天！
[email protected] [zw3307]>show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.26.233 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 2030 Relay_Log_File: [email protected] Relay_Log_Pos: 454 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 2030 Relay_Log_Space: 974 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 3306100 Master_UUID: 7e354a2c-6f5f-11e6-997d-005056a36f08 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 7e354a2c-6f5f-11e6-997d-005056a36f08:1-8 Executed_Gtid_Set: 7e354a2c-6f5f-11e6-997d-005056a36f08:1-8, ba0d5587-74d6-11e6-ab5c-005056a3f46e:1-4, fed5c075-7009-11e6-954d-005056a36f08:1-15 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: master-3306100 Master_TLS_Version: *************************** 2. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.26.233 Master_User: repl3307 Master_Port: 3307 Connect_Retry: 60 Master_Log_File: mysql-bin.000011 Read_Master_Log_Pos: 1931 Relay_Log_File: [email protected] Relay_Log_Pos: 454 Relay_Master_Log_File: mysql-bin.000011 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1931 Relay_Log_Space: 974 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 3307100 Master_UUID: fed5c075-7009-11e6-954d-005056a36f08 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: fed5c075-7009-11e6-954d-005056a36f08:1-15 Executed_Gtid_Set: 7e354a2c-6f5f-11e6-997d-005056a36f08:1-8, ba0d5587-74d6-11e6-ab5c-005056a3f46e:1-4, fed5c075-7009-11e6-954d-005056a36f08:1-15 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: master-3307100 Master_TLS_Version: 2 rows in set (0.00 sec)
ERROR: No query specified
删除多余的账户后，没有报错了！
8.测试同步
[email protected] [zw3307]>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | zw3306 | | zw3307 | +--------------------+ 6 rows in set (0.00 sec)
可以看到两个数据库已经过来了！
[email protected] [zw3306]>show tables; +------------------+ | Tables_in_zw3306 | +------------------+ | t1 | +------------------+ 1 row in set (0.00 sec)
[email protected] [zw3306]>select * from t1; +------+ | id | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec) 有两条数据
master 3306 插入两条数据
mysql> insert into t1 values(3); Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(4); Query OK, 1 row affected (0.00 sec)
查看slave 3306的库 [email protected] [zw3306]>select * from t1; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | +------+ 4 rows in set (0.00 sec)
可以看到同步过来了。
![MySQL半同步复制[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)