Multi-Source Replication with MySQL 5.7 – example

Send to Kindle

Complementing this previous post, today we are going to configure 1 slave receiving updates from 2 master with Multi-source replication.
As mention before, this feature is only available on labs.mysql.com.

To configure it, is very simple, we are going to nees 2 masters with GTID enabled (see this post to know how to configure) and a slave with crash save options enabled.

Master 1 e 2:

gtid-mode=on
enforce-gtid-consistency

Slave

master_info_repository=TABLE
relay_log_info_repository=TABLE
gtid-mode=on
enforce-gtid-consistency

Let’s first create our replication user:

master1 [localhost] {msandbox} ((none)) > GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'127.0.0.1' IDENTIFIED BY '123';
master2 [localhost] {msandbox} ((none)) > GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'127.0.0.1' IDENTIFIED BY '123';

Now we are going to configure our slave normally, there is a new option named FOR CHANEL, that will identify which master are we connecting to:

slave [localhost] {msandbox} ((none)) > CHANGE MASTER TO MASTER_HOST="127.0.0.1", MASTER_USER="slave_user", MASTER_PASSWORD="123", MASTER_PORT=7773 FOR CHANNEL="channel1";
slave [localhost] {msandbox} ((none)) > CHANGE MASTER TO MASTER_HOST="127.0.0.1", MASTER_USER="slave_user", MASTER_PASSWORD="123", MASTER_PORT=7774 FOR CHANNEL="channel2";

Now, we just need to star the replication:

slave [localhost] {msandbox} ((none)) > START SLAVE FOR ALL CHANNELS;

Let’s insert some data in both master to see if we can retrieve it on our slave:

master1 [localhost] {msandbox} ((none)) > CREATE TABLE test.tst1 (ID INT);
Query OK, 0 rows affected (0.12 sec)

master1 [localhost] {msandbox} ((none)) > INSERT INTO test.tst1 SET ID=1;
Query OK, 1 row affected (0.01 sec)

master2 [localhost] {msandbox} ((none)) >  CREATE TABLE test.tst2 (ID INT);
Query OK, 0 rows affected (0.06 sec)

master2 [localhost] {msandbox} ((none)) > INSERT INTO test.tst2 SET ID=2;
Query OK, 1 row affected (0.01 sec)

slave [localhost] {msandbox} ((none)) > USE test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
slave [localhost] {msandbox} (test) > SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| tst1           |
| tst2           |
+----------------+
2 rows in set (0.00 sec)

slave [localhost] {msandbox} (test) > SELECT * FROM tst1;
+------+
| ID   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

slave [localhost] {msandbox} (test) > SELECT * FROM tst2;
+------+
| ID   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

Some useful commands:

START | STOP SLAVE [SQL_THREAD | IO_THREAD] FOR CHANNEL=”channel name";
START | STOP SLAVE [SQL_THREAD | IO_THREAD] FOR ALL CHANNELS;
SHOW SLAVE STATUS FOR CHANNEL='channel name'\G
FLUSH RELAY LOGS FOR CHANNEL="channel name";
RESET SLAVE FOR CHANNEL="channel name";

That’s it, we now have our slave receiving updates from multiple masters with Multi-source replication.

Send to Kindle
This entry was posted in replication. Bookmark the permalink.

5 Responses to Multi-Source Replication with MySQL 5.7 – example

  1. Ronaldo Ottoni says:

    Boa tarde Marcelo, tentei executar esse teste mas quando faço o CHANGE MASTER TO ele apresenta um erro no FOR CHANNEL, não aceita o FOR CHANNEL como parte do comando, sabe o que pode ser?

  2. Samy says:

    Bom artigo,

    Parece que o recurso não está presente na beta corrente – versão “5.7.3-m13″.
    Olhei na documentação do “help CHANGE MASTER TO” e não encontrei os parâmetros “FOR CHANNEL|FOR ALL CHANNELS”.

    mysql> SHOW VARIABLES LIKE “%version%”;
    +————————-+——————————+
    | Variable_name | Value |
    +————————-+——————————+
    | innodb_version | 5.7.3 |
    | protocol_version | 10 |
    | slave_type_conversions | |
    | version | 5.7.3-m13-log |
    | version_comment | MySQL Community Server (GPL) |
    | version_compile_machine | x86_64 |
    | version_compile_os | Linux |
    +————————-+——————————+
    7 rows in set (0.00 sec)

    mysql> START SLAVE FOR ALL CHANNELS;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘FOR ALL CHANNELS’ at line 1

    • marceloaltmann says:

      Ola Samy,
      Você pode baixar o mysql com suporte a Multi-Source Replication em labs.mysql.com, na parte inferior tem um drop-down, basta selecionar MySQL Multi-source Replication.

      abs

      • Samy says:

        Olá Marcelo,

        Eu testei a versão 5.7.2, meu comentário foi com a intenção de discutir se a feature pode\poderia ter saído do backlog do produto, pois não está presente na versão beta atual.

        Abraços

Leave a Reply

Your email address will not be published. Required fields are marked *


7 − one =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>