MySQL Labs: Multi Source Replication - Examples

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

1. 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

            Slave:

            master_info_repository=TABLE

            relay_log_info_repository=TABLE
            gtid-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> begin;commit;

         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.  

             mysql> change master to master_host='localhost', master_user='rpl',
                          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";

            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.

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);

14.  Notice the error in performance schema workers table.

 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.