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.
MySQL-Med 
Pingback: MySQL en vrac (2) | L'Endormitoire
Pingback: MySQL 5.7.7 RC & Multi Source Replication 40 to 1. | MySQL-Med
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.