In this post, I present a few examples on “how to” for multisource replication using the MySQL Labs Linux packages for masters and the slave. We also use replication performance schema tables to find out status of replication per channel.
Multisource replication with GTIDs
- Let us set up two masters and a slave. The following are the important options to be set in the respective cnf files.Masters:
gtid-mode=on
enforce-gtid-consistency
log-slave-updates
master_info_repository=TABLE
relay_log_info_repository=TABLEgtid-mode=on
enforce-gtid-consistency
log-slave-updates
2. Start the two masters and the slave.
rith@endorphin:~/mysql-5.7.2-labs-multi-src-rep-linux-glibc2.5-x86_64$ ./bin/mysqld --defaults-file=support-files/master1.cnf
rith@endorphin:~/mysql-5.7.2-labs-multi-src-rep-linux-glibc2.5-x86_64$ ./bin/mysqld --defaults-file=support-files/master2.cnf
rith@endorphin:~/mysql-5.7.2-labs-multi-src-rep-linux-glibc2.5-x86_64$ ./bin/mysqld --defaults-file=support-files/slave.cnf
3. On the two masters, setup replication.
rith@endorphin:~/mysql-5.7.2-labs-multi-src-rep-linux-glibc2.5-x86_64$ ./bin/mysql -uroot -S/tmp/master1labs.sock
mysql> create user 'rpl'@'localhost' identified by '';
mysql> grant replication slave on *.* to 'rpl'@'localhost';
rith@endorphin:~/mysql-5.7.2-labs-multi-src-rep-linux-glibc2.5-x86_64$ ./bin/mysql -uroot -S/tmp/master2labs.sock
mysql> create user 'rpl'@'localhost' identified by '';
mysql> grant replication slave on *.* to 'rpl'@'localhost';
4. We want to avoid data conflicts at the slave due to same changes in the mysql/
database on both the masters (create user 'rpl' statement above). So, we find out the
GTID of conflicting transaction from 'show master status'.
mysql> show master status\G
*************************** 1. row ***************************
File: endorphin-bin.000003
Position: 572
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 90ed76fe-23ca-11e3-ac3f-5c260a83b12b:1-2
5. On the slave, set the GTID_NEXT corresponding to the transaction which created
the 'rpl' user.
rith@endorphin:~/mysql-5.7.2-labs-multi-src-rep-linux-glibc2.5-x86_64$ ./bin/mysql -uroot -S/tmp/slavelabs.sock
mysql> set gtid_next="90ed76fe-23ca-11e3-ac3f-5c260a83b12b:1";
mysql> set gtid_next="90ed76fe-23ca-11e3-ac3f-5c260a83b12b:1";
mysql> begin;commit;
We set GTID_NEXT to 'AUTOMATIC' for the incoming transactions.
We set GTID_NEXT to 'AUTOMATIC' for the incoming transactions.
mysql> set gtid_next="automatic";
6. On the slave, we are ready to create two channels. Create two channels
"CHANNEL1" and "CHANNEL2" respectively for the first master and the
second master. The 'change master' statement should use AUTO_POSITION protocol.
And then, we start the slave for two channels.
second master. The 'change master' statement should use AUTO_POSITION protocol.
And then, we start the slave for two channels.
mysql> change master to master_host='localhost', master_user='rpl',
master_port=3444, master_auto_position=1 for channel="CHANNEL1";
master_port=3444, master_auto_position=1 for channel="CHANNEL1";
mysql> change master to master_host='localhost', master_user='rpl',
master_port=3454, master_auto_position=1 for channel="CHANNEL2";
master_port=3454, master_auto_position=1 for channel="CHANNEL2";
mysql> start slave for channel="CHANNEL1";
mysql> start slave for channel="CHANNEL2";
7. Do updates on both the masters and see if these are replicated to the slave.
Master1:
mysql> use test; create table t1 (a int); insert into t1 values (10);
Master2:
mysql> use test; create table t2 (a int); insert into t2 values(42);
Result on the slave:
mysql> use test; show tables;
Database changed
+----------------+
| Tables_in_test |
+----------------+
| t1 |
| t2 |
+----------------+
2 rows in set (0.00 sec)
mysql> select * from t1;
+------+
| a |
+------+
| 10 |
+------+
1 row in set (0.00 sec)
mysql> select * from t2;
+------+
| a |
+------+
| 42 |
+------+
1 row in set (0.00 sec)
8. To look at the status of IO threads and SQL threads for these channels, use performance
schema replication tables.
schema replication tables.
mysql> select * from replication_connection_status, replication_execute_status_by_coordinator where replication_connection_status.channel_name=replication_execute_status_by_coordinator.channel_name\G
*************************** 1. row ***************************
CHANNEL_NAME: CHANNEL1
SOURCE_UUID: 7cff7406-23ca-11e3-ac3e-5c260a83b12b
THREAD_ID: 21
SERVICE_STATE: ON
RECEIVED_TRANSACTION_SET: 7cff7406-23ca-11e3-ac3e-5c260a83b12b:1-4
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
CHANNEL_NAME: CHANNEL1
THREAD_ID: 22
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 2. row ***************************
CHANNEL_NAME: CHANNEL2
SOURCE_UUID: 90ed76fe-23ca-11e3-ac3f-5c260a83b12b
THREAD_ID: 23
SERVICE_STATE: ON
RECEIVED_TRANSACTION_SET: 90ed76fe-23ca-11e3-ac3f-5c260a83b12b:2-4
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
CHANNEL_NAME: CHANNEL2
THREAD_ID: 24
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
2 rows in set (0.00 sec)
9. Stop replication for all channels.
mysql> stop slave for all channels;
10. Let us set the number of slave parallel workers =2
mysql> set global slave_parallel_workers=2;
11. Start slave for all channels.
mysql> start slave for all channels;
12. Let us look at performance schema worker table, to know the overall workers status.
mysql> select * from replication_execute_status_by_worker\G
*************************** 1. row ***************************
CHANNEL_NAME: CHANNEL1
WORKER_ID: 0
THREAD_ID: 27
SERVICE_STATE: ON
LAST_SEEN_TRANSACTION:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 2. row ***************************
CHANNEL_NAME: CHANNEL1
WORKER_ID: 1
THREAD_ID: 28
SERVICE_STATE: ON
LAST_SEEN_TRANSACTION:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 3. row ***************************
CHANNEL_NAME: CHANNEL2
WORKER_ID: 0
THREAD_ID: 31
SERVICE_STATE: ON
LAST_SEEN_TRANSACTION:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 4. row ***************************
CHANNEL_NAME: CHANNEL2
WORKER_ID: 1
THREAD_ID: 32
SERVICE_STATE: ON
LAST_SEEN_TRANSACTION:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
4 rows in set (0.00 sec)
13. Intentionally create a data conflict at the slave by creating table t1 on master2.
Master2:
mysql> create table t1 (a int);
Master2:
mysql> create table t1 (a int);
14. Notice the error in performance schema workers table.
mysql> select * from replication_execute_status_by_worker where channel_name="CHANNEL2"\G
mysql> select * from replication_execute_status_by_worker where channel_name="CHANNEL2"\G
*************************** 1. row ***************************
CHANNEL_NAME: CHANNEL2
WORKER_ID: 0
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 2. row ***************************
CHANNEL_NAME: CHANNEL2
WORKER_ID: 1
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION: 90ed76fe-23ca-11e3-ac3f-5c260a83b12b:5
LAST_ERROR_NUMBER: 1050
LAST_ERROR_MESSAGE: Worker 1 failed executing transaction '' at master log endorphin-bin.000003, end_log_pos 1146; Error 'Table 't1' already exists' on query. Default database: 'test'. Query: 'create table t1 (a int)'
LAST_ERROR_TIMESTAMP: 2013-09-23 03:30:36
Summary:
In this post it was shown, how to use MySQL Labs packages to setup Multisource replication with GTIDs. It was also shown how the new performance schema tables for replication are integrated with Multisource replication to monitor the replication configuration and status for all channels.
Summary:
In this post it was shown, how to use MySQL Labs packages to setup Multisource replication with GTIDs. It was also shown how the new performance schema tables for replication are integrated with Multisource replication to monitor the replication configuration and status for all channels.
No comments:
Post a Comment