基于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)
可以看到同步過來了。