a multisource replication scenario: 10 masters, 1 slave.

A customer asked whether we they could have 10 or more masters consolidate all the data into a single central slave. After getting a bit more information from them and seeing the application functionality, it was clear that MySQL Labs 5.7.5 Multi Source Replication could be a good candidate. Why?:
– Each master is independent from the rest of the masters.
– One-way traffic: there is only one way to update a row, and that’s from the master.
– All the masters use the same schema and table, but no single master will ever need to, nor be able to update a row from another master.
– PK determined via app & master env.

Multisource replication is still in http://labs.mysql.com, but here’s what I did to test it out.

First, I read:
http://on-mysql-replication.blogspot.se/2013/09/mysql-labs-multi-source-replication.html

Then, downloaded what I needed from labs.mysql.com:

mysql-5.7.5-labs-msr-preview-linux-el6-x86_64.tar.gz

And tried for myself:

cd /opt/mysql/msr
mkdir -p 3100/data 3001/data 3002/data 3003/data 3004/data 3005/data 3006/data 3007/data 3008/data 3009/data 3010/data

How many masters do I want to test? Well.. for now 10, but I’ll see later how far I can go.

My setup will look like the following, all on the same host:

datadirs, port, socket location, server-id (1-10, 100):

3100               the single slave

3001 – 3010   the 10 masters

and a my.cnf that will let me use multi_mysqld and make my life a whole lot easier, as all this is being done on a single machine:

[mysqld_safe]
log-error                       =/opt/mysql/msr/mysqld.log

[mysqld_multi]
#no-log
log                             =/usr/local/mysql-5.7.5-labs-msr-preview-linux-el6-x86_64/msr/mysqld_multi.log
mysqld                          =/usr/local/mysql-5.7.5-labs-msr-preview-linux-el6-x86_64/bin/mysqld_safe
mysqladmin                      =/usr/local/mysql-5.7.5-labs-msr-preview-linux-el6-x86_64/bin/mysqladmin
user                            =root
password                        =pass

[mysqld]
basedir                         =/usr/local/mysql-5.7.5-labs-msr-preview-linux-el6-x86_64
performance-schema-instrument   ='%=ON'

[mysqld3100]
server-id                       =3100
mysqld                          =/usr/local/mysql-5.7.5-labs-msr-preview-linux-el6-x86_64/bin/mysqld_safe
ledir                           =/usr/local/mysql-5.7.5-labs-msr-preview-linux-el6-x86_64/bin
port                            =3100
pid-file                        =/opt/mysql/msr/3100/pchost_3100.pid
socket                          =/opt/mysql/msr/3100/mysql.sock
datadir                         =/opt/mysql/msr/3100/data
log-error                       =/opt/mysql/msr/3100/msr.err
innodb_buffer_pool_size         =40M
innodb_file_per_table           =1
innodb_log_buffer_size          =2M
innodb_log_file_size            =12M
innodb_flush_log_at_trx_commit  =2
innodb_data_file_path           =ibdata1:12M;ibdata2:12M:autoextend
innodb_open_files               =50
language                        =/usr/local/mysql-5.7.5-labs-msr-preview-linux-el6-x86_64/share/english
table_open_cache                =80
open_files_limit                =100
query_cache_size                =0
max_connections                 =10
user                            =mysql
# Replication
#log-bin                        =3100
binlog-row-image                =minimal
binlog-rows-query-log-events    =1
log-slave-updates               =TRUE
gtid-mode                       =ON
enforce-gtid-consistency =TRUE
master-info-repository =TABLE
relay-log-info-repository =TABLE
sync_binlog =1
sync_master_info =1
slave-parallel-workers =2
slave_transaction_retries =0
binlog-checksum =CRC32
master-verify-checksum =1
slave-sql-verify-checksum =1
binlog-rows-query-log-events =1
binlog_format =ROW
report-host =pchost
report-port =3100
replicate-ignore-db =nexus

[mysqld3001]
server-id =3001
mysqld =/usr/local/mysql-5.7.5-labs-msr-preview-linux-el6-x86_64/bin/mysqld_safe
ledir =/usr/local/mysql-5.7.5-labs-msr-preview-linux-el6-x86_64/bin
port =3001
pid-file =/opt/mysql/msr/3001/pchost_3001.pid
...
[mysqld3002]
...
[mysqld3003]
...
..
[mysqld3010]
...

All entries were the same for each [mysqld300x] except for the obvious, i.e. port, datadir, socket, pid, etc.

And once that’s done, install each server’s datadir, from 3100, 3001 to 3010:

bin/mysql_install_db --user=mysql --datadir=/opt/mysql/msr/3100/data --basedir=/usr/local/mysql-5.7.5-m15-linux-glibc2.5-x86_64
bin/mysqld_multi --defaults-file=/usr/local/mysql/msr/my.cnf  start 3100
cat ~/.mysql_secret
bin/mysqladmin -uroot -p -S/opt/mysql/msr/3100/mysql.sock password 'pass'

Remember, this is 5.7.5 Labs, i.e. be aware of some changes occurring here (bin/mysql_install_db).

Let’s comment out the log-bin entry on all 10 masters first, as I don’t want some things I’m about to do to be logged and cause replication to break before I start it all up:

bin/mysqld_multi --defaults-file=/usr/local/mysql/msr/my.cnf  stop 3001-3010
bin/mysqld_multi --defaults-file=/usr/local/mysql/msr/my.cnf  start 3001-3010

Now, on all the masters, 3001-3010 and on the slave, 3100, create the table:

 create database coredb;
 use coredb;
 create table recording_data (
   centre_code SET('A','AB','AC','AL','B','BI','BU','C','CA','CE','CO','CU') NOT NULL,
   case_id INT NOT NULL,
   user_email VARCHAR(55) NOT NULL,
   start_info DATETIME NOT NULL,
   end_info DATETIME NOT NULL,
   INDEX (user_email),
   PRIMARY KEY (centre_code,case_id)
 );

The app at each master has programming logic and config that will force each user to use the default value for centre_code for that specific centre and not be able to adjust this in anyway.

Also, case_id could be autoincrement, but for simplistic sake I will force a common id just to prove my point.

These 2 factors mean that when multi source replication occurs on the slave, each row will be unique from each master, i.e. no need for conflict resolution or headaches along those lines.

Now, for the masters, uncomment log-bin again, as we’ll obviously need it, and restart masters 3001-3010.

Create the replication user on each master (from a single slave, so it will be same one for all the masters):

create user 'rpl'@'localhost' identified by '';
grant replication slave on *.*   to 'rpl'@'localhost';

Lets get replicating: go to the slave and create all the channels for each individual master:

change master to master_host='localhost', master_user='rpl', master_port=3001,  master_auto_position=1 for channel "CHANNEL1";
change master to master_host='localhost', master_user='rpl', master_port=3002,  master_auto_position=1 for channel "CHANNEL2";
change master to master_host='localhost', master_user='rpl', master_port=3003,  master_auto_position=1 for channel "CHANNEL3";
...
change master to master_host='localhost', master_user='rpl', master_port=3010,  master_auto_position=1 for channel "CHANNEL10";

And start each channel:

start slave for channel "CHANNEL1";
start slave for channel "CHANNEL2";
start slave for channel "CHANNEL3";
...
start slave for channel "CHANNEL10";

Right.. all’s well, but now for some data.

We insert some data, unique to each master, and see what happens:

On master 3001:
 use coredb
 insert into recording_data values
  ('A',1234567890,'prueba1@test.com',sysdate()-1,sysdate()+1),
  ('A',1234567891,'prueba1@test.com',sysdate()-1,sysdate()+1),
  ('A',1234567893,'prueba1@test.com',sysdate()-1,sysdate()+1)
 ;
On master 3002:
 use coredb
 insert into recording_data values
  ('AB',1234567890,'prueba1@test.com',sysdate()-1,sysdate()+1),
  ('AB',1234567891,'prueba1@test.com',sysdate()-1,sysdate()+1),
  ('AB',1234567893,'prueba1@test.com',sysdate()-1,sysdate()+1)
 ;
...
On master 3010:
 use coredb
 insert into recording_data values
  ('CE',1234567890,'prueba1@test.com',sysdate()-1,sysdate()+1),
  ('CE',1234567891,'prueba1@test.com',sysdate()-1,sysdate()+1),
  ('CE',1234567893,'prueba1@test.com',sysdate()-1,sysdate()+1)
 ;

And on the slave 3100, what do we see:

bin/mysql -uroot -p -S/opt/mysql/msr/3100/mysql.sock

mysql> use coredb;
Database changed
mysql> select * from recording_data ;
+-------------+------------+------------------+---------------------+---------------------+
| centre_code | case_id    | user_email       | start_info          | end_info            |
+-------------+------------+------------------+---------------------+---------------------+
| A           | 1234567890 | prueba1@test.com | 2015-01-26 19:02:08 | 2015-01-26 19:02:10 |
| A           | 1234567891 | prueba1@test.com | 2015-01-26 19:02:08 | 2015-01-26 19:02:10 |
| A           | 1234567893 | prueba1@test.com | 2015-01-26 19:02:08 | 2015-01-26 19:02:10 |
| AB          | 1234567890 | prueba1@test.com | 2015-01-26 19:02:25 | 2015-01-26 19:02:27 |
| AB          | 1234567891 | prueba1@test.com | 2015-01-26 19:02:25 | 2015-01-26 19:02:27 |
| AB          | 1234567893 | prueba1@test.com | 2015-01-26 19:02:25 | 2015-01-26 19:02:27 |
| AC          | 1234567890 | prueba1@test.com | 2015-01-27 12:59:48 | 2015-01-27 12:59:50 |
| AC          | 1234567891 | prueba1@test.com | 2015-01-27 12:59:48 | 2015-01-27 12:59:50 |
| AC          | 1234567893 | prueba1@test.com | 2015-01-27 12:59:48 | 2015-01-27 12:59:50 |
| AL          | 1234567890 | prueba1@test.com | 2015-01-27 13:13:35 | 2015-01-27 13:13:37 |
| AL          | 1234567891 | prueba1@test.com | 2015-01-27 13:13:35 | 2015-01-27 13:13:37 |
| AL          | 1234567893 | prueba1@test.com | 2015-01-27 13:13:35 | 2015-01-27 13:13:37 |
| B           | 1234567890 | prueba1@test.com | 2015-01-27 13:13:56 | 2015-01-27 13:13:58 |
| B           | 1234567891 | prueba1@test.com | 2015-01-27 13:13:56 | 2015-01-27 13:13:58 |
| B           | 1234567893 | prueba1@test.com | 2015-01-27 13:13:56 | 2015-01-27 13:13:58 |
| BI          | 1234567890 | prueba1@test.com | 2015-01-27 13:15:16 | 2015-01-27 13:15:18 |
| BI          | 1234567891 | prueba1@test.com | 2015-01-27 13:15:16 | 2015-01-27 13:15:18 |
| BI          | 1234567893 | prueba1@test.com | 2015-01-27 13:15:16 | 2015-01-27 13:15:18 |
| BU          | 1234567890 | prueba1@test.com | 2015-01-27 13:15:34 | 2015-01-27 13:15:36 |
| BU          | 1234567891 | prueba1@test.com | 2015-01-27 13:15:34 | 2015-01-27 13:15:36 |
| BU          | 1234567893 | prueba1@test.com | 2015-01-27 13:15:34 | 2015-01-27 13:15:36 |
| C           | 1234567890 | prueba1@test.com | 2015-01-27 13:15:52 | 2015-01-27 13:15:54 |
| C           | 1234567891 | prueba1@test.com | 2015-01-27 13:15:52 | 2015-01-27 13:15:54 |
| C           | 1234567893 | prueba1@test.com | 2015-01-27 13:15:52 | 2015-01-27 13:15:54 |
| CA          | 1234567890 | prueba1@test.com | 2015-01-27 13:16:11 | 2015-01-27 13:16:13 |
| CA          | 1234567891 | prueba1@test.com | 2015-01-27 13:16:11 | 2015-01-27 13:16:13 |
| CA          | 1234567893 | prueba1@test.com | 2015-01-27 13:16:11 | 2015-01-27 13:16:13 |
| CE          | 1234567890 | prueba1@test.com | 2015-01-27 13:16:30 | 2015-01-27 13:16:32 |
| CE          | 1234567891 | prueba1@test.com | 2015-01-27 13:16:30 | 2015-01-27 13:16:32 |
| CE          | 1234567893 | prueba1@test.com | 2015-01-27 13:16:30 | 2015-01-27 13:16:32 |
+-------------+------------+------------------+---------------------+---------------------+
30 rows in set (0,00 sec)

And as mentioned in MySQL Labs: Multi Source Replication – examples now to use Performance Schema to see all my masters:

mysql> select * from performance_schema.replication_execute_status_by_worker;
+--------------+-----------+-----------+---------------+----------------------------------------+-------------------+--------------------+----------------------+
| CHANNEL_NAME | WORKER_ID | THREAD_ID | SERVICE_STATE | LAST_SEEN_TRANSACTION                  | LAST_ERROR_NUMBER | LAST_ERROR_MESSAGE | LAST_ERROR_TIMESTAMP |
+--------------+-----------+-----------+---------------+----------------------------------------+-------------------+--------------------+----------------------+
| channel1     |         1 |        27 | ON            | c1b91daa-a55a-11e4-be70-b86b23917877:6 |                 0 |                    | 0000-00-00 00:00:00  |
| channel1     |         2 |        28 | ON            | c1b91daa-a55a-11e4-be70-b86b23917877:7 |                 0 |                    | 0000-00-00 00:00:00  |
| channel2     |         1 |        44 | ON            | fd35c073-a55e-11e4-be8c-b86b23917877:7 |                 0 |                    | 0000-00-00 00:00:00  |
| channel2     |         2 |        45 | ON            | fd35c073-a55e-11e4-be8c-b86b23917877:8 |                 0 |                    | 0000-00-00 00:00:00  |
| channel3     |         1 |        50 | ON            | 3183d919-a569-11e4-bece-b86b23917877:1 |                 0 |                    | 0000-00-00 00:00:00  |
| channel3     |         2 |        51 | ON            | 3183d919-a569-11e4-bece-b86b23917877:2 |                 0 |                    | 0000-00-00 00:00:00  |
| channel4     |         1 |        54 | ON            | 2205d704-a56c-11e4-bee1-b86b23917877:1 |                 0 |                    | 0000-00-00 00:00:00  |
| channel4     |         2 |        55 | ON            | 2205d704-a56c-11e4-bee1-b86b23917877:2 |                 0 |                    | 0000-00-00 00:00:00  |
| channel5     |         1 |        58 | ON            | 3e007b4e-a56c-11e4-bee2-b86b23917877:1 |                 0 |                    | 0000-00-00 00:00:00  |
| channel5     |         2 |        59 | ON            | 3e007b4e-a56c-11e4-bee2-b86b23917877:4 |                 0 |                    | 0000-00-00 00:00:00  |
| channel6     |         1 |        62 | ON            | fecf246b-a616-11e4-833c-7c7a91bc3176:1 |                 0 |                    | 0000-00-00 00:00:00  |
| channel6     |         2 |        63 | ON            | fecf246b-a616-11e4-833c-7c7a91bc3176:2 |                 0 |                    | 0000-00-00 00:00:00  |
| channel7     |         1 |        66 | ON            | 6ccfd319-a617-11e4-833f-7c7a91bc3176:1 |                 0 |                    | 0000-00-00 00:00:00  |
| channel7     |         2 |        67 | ON            | 6ccfd319-a617-11e4-833f-7c7a91bc3176:2 |                 0 |                    | 0000-00-00 00:00:00  |
| channel8     |         1 |        70 | ON            | a800f433-a617-11e4-8341-7c7a91bc3176:1 |                 0 |                    | 0000-00-00 00:00:00  |
| channel8     |         2 |        71 | ON            | a800f433-a617-11e4-8341-7c7a91bc3176:2 |                 0 |                    | 0000-00-00 00:00:00  |
| channel9     |         1 |        74 | ON            | bd6fac63-a617-11e4-8341-7c7a91bc3176:1 |                 0 |                    | 0000-00-00 00:00:00  |
| channel9     |         2 |        75 | ON            | bd6fac63-a617-11e4-8341-7c7a91bc3176:2 |                 0 |                    | 0000-00-00 00:00:00  |
| channel10    |         1 |        78 | ON            | d1d47dec-a618-11e4-8348-7c7a91bc3176:1 |                 0 |                    | 0000-00-00 00:00:00  |
| channel10    |         2 |        79 | ON            | d1d47dec-a618-11e4-8348-7c7a91bc3176:2 |                 0 |                    | 0000-00-00 00:00:00  |
+--------------+-----------+-----------+---------------+----------------------------------------+-------------------+--------------------+----------------------+
20 rows in set (0,00 sec)

So what’s next? In my “to do” list I’ve got:

– Evaluate performance impact on the slave as a new master is added. Today: 10, tomorrow: 50, next week: the www.

– See how the slave impact increases and hence, server resources. CPU will be key I expect, due to the number of channels, but then also will depend on how frequently each master sends it’s own load of bin logs and the size of them, network will be hard for me to look into here, as it’s all self-contained….

BRB.

5 thoughts on “a multisource replication scenario: 10 masters, 1 slave.

  1. Yogesh says:

    nice explanation.. i am doing same thing..i want to add master every day or as per my requirements. is it possible to do that, and second thing pls explain more details on my.cnf file, for every master should i use separate
    [mysqld3001]………. – what is 3001 here..
    [mysqld3002]………
    [mysqld3002]……….

    • Thank you for your comments. Yes, you can add a new master to the same slave as needed via:
      change master to master_host=”, master_user=’rpl’, master_port=, master_auto_position=1 for channel “”;
      start slave for channel “”;
      For the my.cnf on the other post on multi-source I did after this one, you can see an example of the mysqld_multi usage there, explaining what mysqld3001, mysqld3002 and so on are, and what changes between them all. Please remember that I used a single my.cnf with mysqld_multi as I was using a single server for all my masters (‘cos it was a test env of course). If you have masters spread about on different servers, there’s no need to use_mysqld_multi and an ordinary my.cnf can be fine. Or, if you have a small amount of mysql servers on the same box and you want them to be 100% independent to each other, create separate my.cnf files and keep it simple. Also a good approach. Apologies if I have confused things for you.

    • ah, and I used 3001, 3002 as the different port numbers, and therefore, made it my naming convention to help me understand which instance was which. You can call them what you want, and name accordingly.
      thx
      K.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s