这里使用两主一从的架构，基于GTID多源复制。
两主是安装在一台机器上的多实例
|
数据库版本 |
5.7.22-log |
|
MasterA |
192.168.0.20:3306 |
|
MasterB |
192.168.0.20:3307 |
|
Slave |
192.168.0.21:3306 |
搭建中的注意事项：
1.MasterA和MasterB不能拥有相同的数据库名，否则就会在从库出现数据覆盖的现象
2.MasterA-->slave和MasterB-->slave要拥有不同的复制账号
SLAVE上需要额外添加的参数
master_info_repository=table
relay_log_info_repository=table
主从间的复制信息需要记录到表中
1.分别在MasterA MasterB上创建复制账号
MasterA：
mysql> create user 'u1'@'192.168.0.%' identified by 'u1';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'u1'@'192.168.0.%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MasterB:
mysql> create user 'u2'@'192.168.0.%' identified by 'u2';
Query OK, 0 rows affected (0.15 sec)
mysql> grant replication slave on *.* to 'u2'@'192.168.0.%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
2.备份MasterA ,MasterB的数据库
[root@dhr bk]# /usr/local/mysql/bin/mysqldump -uroot -pdhr -S/tmp/mysql3306.sock --single-transaction --master-data=2 db1 > db1.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@dhr bk]#
[root@dhr bk]# /usr/local/mysql/bin/mysqldump -uroot -pdhr2 -S/tmp/mysql3307.sock --single-transaction --master-data=2 db2 > db2.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
3.在从库上添加参数，并重启
master_info_repository=table
relay_log_info_repository=table
[root@node2 bk]# service mysqld restart
Shutting down MySQL..[ OK ]
4.在从库把要导入的库db1,db2创建上
root@localhost [(none)]>create database db1;
Query OK, 1 row affected (0.00 sec)
root@localhost [(none)]>create database db2;
Query OK, 1 row affected (0.00 sec)
5.在从库上恢复数据
[root@node2 bk]# mysql -uroot -pdhr db1 < db1.sql
[root@node2 bk]# mysql -uroot -pdhr db2 < db1.sql
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
导入MasterB的备份时报错，因为已经导入了MasterA，所以gtid_purged不为空了，所以报错。
进入从数据库，记录下当前的gtid_purged中的值 ，然后执行reset master，清空gtid_purged的值
root@localhost [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000001 | 154 | | | 0526f365-913f-11e8-9359-005056389d91:1-10 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
root@localhost [(none)]>reset master;
Query OK, 0 rows affected (0.01 sec)
root@localhost [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
再导入MasterB的数据，正常导入
[root@node2 bk]# mysql -uroot -pdhr db2 < db2.sql
进入数据库，记录下gtid_purged的值，然后重置GTID-PURGED的值
root@localhost [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000001 | 154 | | | 7aba56d2-9140-11e8-ac21-005056389d91:1-10 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
root@localhost [(none)]>reset master;
Query OK, 0 rows affected (0.01 sec)
root@localhost [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
root@localhost [(none)]>set global gtid_purged='0526f365-913f-11e8-9359-005056389d91:1-10,7aba56d2-9140-11e8-ac21-005056389d91:1-10';
Query OK, 0 rows affected (0.00 sec)
6.在从库上分别配置MasterA->slave ,MasterB->slave的同步过程
change master to master_host='192.168.0.20',
master_port=3306,
master_user='u1',
master_password='u1',
master_auto_position=1 for channel 'master3306';
change master to master_host='192.168.0.20',
master_port=3307,
master_user='u2',
master_password='u2',
master_auto_position=1 for channel 'master3307';
7.开启主从复制，可以通过START SLAVE命令开启所有复制，也可以通过START SLAVE FOR CHANNEL分别开启
root@localhost [(none)]>start slave for channel 'master3306';
Query OK, 0 rows affected (0.01 sec)
root@localhost [(none)]>start slave for channel 'master3307';
Query OK, 0 rows affected (0.00 sec)
通过show slave status for channel 'master3306' \G,可以单独查看某个复制源的同步状态
root@localhost [(none)]>show slave status for channel 'master3306'\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.20
Master_User: u1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 1775
Relay_Log_File: relay-bin-master3306.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000004
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: 1775
Relay_Log_Space: 626
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: 203306
Master_UUID: 0526f365-913f-11e8-9359-005056389d91
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:
Executed_Gtid_Set: 0526f365-913f-11e8-9359-005056389d91:1-10,
7aba56d2-9140-11e8-ac21-005056389d91:1-10
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: master3306
Master_TLS_Version:
1 row in set (0.00 sec)
也可通过performance_schema.replication_connection_status视图来查看
root@localhost [(none)]>select * from performance_schema.replication_connection_status\G;
*************************** 1. row ***************************
CHANNEL_NAME: master3306
GROUP_NAME:
SOURCE_UUID: 0526f365-913f-11e8-9359-005056389d91
THREAD_ID: 40
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 21
LAST_HEARTBEAT_TIMESTAMP: 2018-07-27 10:58:38
RECEIVED_TRANSACTION_SET:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 2. row ***************************
CHANNEL_NAME: master3307
GROUP_NAME:
SOURCE_UUID: 7aba56d2-9140-11e8-ac21-005056389d91
THREAD_ID: 43
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 19
LAST_HEARTBEAT_TIMESTAMP: 2018-07-27 10:58:21
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)
8.验证同步。
在MasterA,MasterB上插入数据，看是否同步到从库
mysql> select * from t1;
+------+
| id |
+------+
| 10 |
+------+
1 row in set (0.00 sec)
mysql> insert into t1 values(20);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| t2 |
+---------------+
1 row in set (0.00 sec)
mysql> insert into t2 values(30);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t2 values(50);
Query OK, 1 row affected (0.00 sec)
从库确认
root@localhost [(none)]>use db1;
Database changed
root@localhost [db1]>select * from t1;
+------+
| id |
+------+
| 10 |
| 20 |
+------+
2 rows in set (0.00 sec)
root@localhost [db1]>use db2;
Database changed
root@localhost [db2]>select * from t2;
+------+
| id |
+------+
| 30 |
| 50 |
+------+
2 rows in set (0.00 sec)
