MySQL 5.7.7 RC & Multi Source Replication 40 to 1.

One of the cool new features in 5.7 Release Candidate is Multi Source Replication, as I previously looked into in 5.7.5 DMR.

I’ve had more and more people like the idea of this, so here’s a quick set-up as to what’s needed and how it could work.

1. Prepare master environments.
2. Prepare 40 masters for replication.
3. Create slave.
4. Insert data into the Masters.

* I originally tried running 50 mysql’s but I just ran out of resources on my old pc, so it’s at 40.

* The real key behind this scenario is that each of the masters has a unique centre_code that is implicitly inserted via the app & users at that site. i.e. no other site will want to modify any data that was entered from that site, e.g. call centre reclaims dept for specific areas / regions, CNC machine data input originating from specific factories and machinists, etc.

* You might to have a look at the Reference Manual entry to get used to the syntax, etc.

* FYI, instances 3001-3040 are the masters and instance id 3100 is the slave.

1. Prepare master environments.

:: Download MySQL 5.7.7 RC and install.
— 40x on a single master & 1x slave host.
:: create dir’s, permissions, mysqld_multi my.cnf without logging.
— my.cnf

[mysqld_multi]
#no-log
log                    =/usr/local/msr/mysqld_multi.log
mysqld                 =/usr/local/mysql/bin/mysqld_safe
mysqladmin             =/usr/local/mysql/bin/mysqladmin
user                   =root
password               =contraseña
..
..
[mysqld3100]
server-id              =3100
mysqld                 =/usr/local/mysql/bin/mysqld_safe
ledir                  =/usr/local/mysql/bin
port                   =3100
pid-file               =/usr/local/msr/3100/khollman-es_3100.pid
socket                 =/usr/local/msr/3100/mysql.sock
datadir                =/usr/local/msr/3100/data
log-error              =/usr/local/msr/3100/msr.err
innodb_buffer_pool_size=80M
innodb_file_per_table  =1
##log-bin              =3100
..
..
[mysqld3001]
server-id              =3001
mysqld                 =/usr/local/mysql/bin/mysqld_safe
ledir                  =/usr/local/mysql/bin
port                   =3001
pid-file               =/usr/local/msr/3001/khollman-es_3001.pid
socket                 =/usr/local/msr/3001/mysql.sock
datadir                =/usr/local/msr/3001/data
log-error              =/usr/local/msr/3001/msr.err
#log-bin               =3001
..
..
[mysqld3002]
server-id              =3002
mysqld                 =/usr/local/mysql/bin/mysqld
port                   =3002
socket                 =/usr/local/msr/3002/mysql.sock
datadir                =/usr/local/msr/3002/data
log-error              =/usr/local/msr/3002/msr.err
#log-bin               =3002
..
..
[mysqld3003]
..
..
..
[mysqld3039]
..
[mysqld3040]
..

:: create script to initialize (5.7 remember) mysqld, start and reset root passwd.
— myinstall.sh (in my unimportant safe env I used ‘ –initialize-insecure’. Don’t try this at home kids!)

vi myinstall.sh
#!/bin/bash

export PATH=$PATH:/usr/local/mysql/bin
mysqld --user=mysql --datadir=/opt/mysql/msr/$1/data --basedir=/usr/local/mysql --initialize-insecure
mysqld_multi --defaults-file=/usr/local/msr/my.cnf  start $1
mysqladmin -uroot -S/opt/mysql/msr/$1/mysql.sock password 'contraseña'

:: do all 40 in 1 go.
— myinstall_all.sh

myinstall.sh 3001 &
myinstall.sh 3002
myinstall.sh 3003 &
..
myinstall.sh 3039 &
myinstall.sh 3040

2. Prepare 40 masters for replication.

:: create replication user & table we’ll be replicating.
— cre_tab_recdAUTOINC.sql (PK on ‘id’ column)

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

 create database coredb;
 use coredb;
 drop table if exists recording_data_AUTOINC;
 create table recording_data_AUTOINC (
   centre_code SET('01','02','03','04','05','06','07','08','09','10',
           '11','12','13','14','15','16','17','18','19','20',
           '21','22','23','24','25','26','27','28','29','30',
           '31','32','33','34','35','36','37','38','39','40') NOT NULL,
   id INT AUTO_INCREMENT,
   user_email VARCHAR(55) NOT NULL,
   start_info DATETIME NOT NULL,
   end_info DATETIME NOT NULL,
   INDEX (user_email),
   PRIMARY KEY (id)
 )  COMMENT='Master table definition with ID column is PK' ;

:: create script for INSERT procedure for each master to generate data.
— cre_proc_msr_loader.sh

cd /usr/local/msr
mysqld_multi --defaults-file=/usr/local/msr/my.cnf start 30$1
sleep 5
mysql -uroot -pcontraseña -v -S/opt/mysql/msr/30$1/mysql.sock << EOF  tee cre_$1.log  source cre_tab_recdAUTOINC.sql  delimiter //  DROP PROCEDURE IF EXISTS msr_loader//  CREATE PROCEDURE msr_loader (p1 INT)   BEGIN    SET @x = 0;    REPEAT     INSERT INTO recording_data_AUTOINC SELECT '$1', NULL, 'centre_$1@testdata.com',date_format(sysdate(),'%Y-%m-%d %H:%i:%s'),date_add(sysdate(),INTERVAL 1 DAY) ;    SET @x = @x + 1;    UNTIL @x > p1 END REPEAT;
  END
 //
 delimiter ;
 exit
EOF
mysqld_multi --defaults-file=/usr/local/msr/my.cnf stop 30$1

:: do it x40
— cre_master_setup.sh

./cre_proc_msr_loader.sh 01 > cre_proc_msr_loader.sh.log &
./cre_proc_msr_loader.sh 02 >> cre_proc_msr_loader.sh.log
...
./cre_proc_msr_loader.sh 39 >> cre_proc_msr_loader.sh.log &
./cre_proc_msr_loader.sh 40 >> cre_proc_msr_loader.sh.log

:: check procedure and table has been created.
:: stop all 40 masters.
— mysqld_multi –defaults-file=/usr/local/msr/my.cnf  stop 3001-3040
:: Activate logging in all 40 masters
— vi multi_my.cnf  ->  :1,$ s/\#log-bin/log-bin/g
:: Start all 40 masters
— mysqld_multi –defaults-file=/usr/local/msr/my.cnf  start 3001-3040

3. Create slave.

Now to be fair, I recognize that this would be done in very few scenarios, as I’d expect you’d probably be restoring a logical dump of each of the 40x masters into the single slave table, or something along those lines. This scenario assumes that we’re starting off with Multi Source Replication and then populating the environment.

:: create single table that will store all data (no AUTO_INCREMENT, & PK on centre_code & id)
— cre_slave_table.sql

 create database coredb;
 use coredb;

 drop table if exists recording_data_AUTOINC;
 create table recording_data_AUTOINC (
   centre_code SET('01','02','03','04','05','06','07','08','09','10',
           '11','12','13','14','15','16','17','18','19','20',
           '21','22','23','24','25','26','27','28','29','30',
           '31','32','33','34','35','36','37','38','39','40') NOT NULL,
   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,id) 
 ) COMMENT='Slave Table WithOut PK on a non-AUTO_INCREMENT ID column but a multi-column ID+centre_code PK' ;

:: create slave replication script (implicit start)
— start_slave_rep.sh

mysql -uroot -pcontraseña -S/usr/local/msr/3100/mysql.sock << EOF
change master to master_host='192.168.12.39', master_user='rpl', master_port=30$1, master_auto_position=1 for channel "CHANNEL_$1";
start slave for channel "CHANNEL_$1";
exit
EOF

:: script for x40 masters.
— start_all_slave.sh

./start_slave_rep.sh 01
./start_slave_rep.sh 02
...
./start_slave_rep.sh 39
./start_slave_rep.sh 40

:: check them all:

select * 
from performance_schema.replication_connection_status,
 performance_schema.replication_applier_status_by_coordinator
where replication_connection_status.channel_name=replication_applier_status_by_coordinator.channel_name ;

4. Insert data into the Masters

:: create “call msr_loader(10000);” script for inserting data.
— insert_msr_loader.sh

mysql -uroot -pcontraseña -S/opt/mysql/msr/30$1/mysql.sock << EOF
 use coredb; 
 call msr_loader (10000);
 exit
EOF

:: Script for automating 40x 10000 inserts.
— insert_msr_loader_all.sh

cd /usr/local/msr
./insert_msr_loader.sh 01 > insert_msr_loader.sh.log &
./insert_msr_loader.sh 02 >> insert_msr_loader.sh.log &
...
./insert_msr_loader.sh 40 >> insert_msr_loader.sh.log &

:: Observe slave:
— select count(*), centre_code from …. group by centre_code;
— Use MySQL Enterprise Monitor to observe performance increase on slave as the logs are applied from each master.

That’s how it’s done.

My next steps are to look at performance:

5. Sysbench tests

:: install sysbench
— sudo apt-get install sysbench
:: prepare sysbench schema & table for testing
— sysbench_prep.sh
:: create sysbench template script
— sysbench_tests.sh
:: run tests for different values for NoOfThreads, MaxRequests: 1,1  10,1000  40,100
— sysbench_tests.sh  1    1 01 .. sysbench_tests.sh  1    1 40
— sysbench_tests.sh 10 1000 01 .. sysbench_tests.sh 10 1000 40
— sysbench_tests.sh 50  100 01 .. sysbench_tests.sh 50  100 40

Hope it gives some ideas to someone out there.

One thought on “MySQL 5.7.7 RC & Multi Source Replication 40 to 1.

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