// Setup Standard Master-Slave repliation -master scripts/mysql_install_db --basedir=/data/ushastry/server/mysql-5.6.21 --datadir=/tmp/master --user=root bin/mysqld --log-bin --binlog-format=ROW --max_binlog_size=1G --server-id=1 --basedir=/data/ushastry/server/mysql-5.6.21 --datadir=/tmp/master --socket=/tmp/mysql_master.sock --port=3306 --log-error=/tmp/master/log.err --user=root 2>&1 & -slave scripts/mysql_install_db --basedir=/data/ushastry/server/mysql-5.6.21 --datadir=/tmp/slave --user=root bin/mysqld --log-bin --binlog-format=ROW --server-id=2 --basedir=/data/ushastry/server/mysql-5.6.21 --datadir=/tmp/slave --socket=/tmp/mysql_slave.sock --port=3307 --log-error=/tmp/slave/log.err --user=root 2>&1 & // Restart both servers with GTIDs enabled -master bin/mysqld --server-id=1 --gtid_mode=ON --log-bin --binlog-format=ROW --max_binlog_size=1G --log-slave-updates --enforce-gtid-consistency --basedir=/data/ushastry/server/mysql-5.6.21 --datadir=/tmp/master --socket=/tmp/mysql_master.sock --port=3306 --log-error=/tmp/master/log.err --user=root 2>&1 & -slave bin/mysqld --server-id=2 --gtid_mode=ON --log-bin --binlog-format=ROW --log-slave-updates --enforce-gtid-consistency --skip-slave-start --basedir=/data/ushastry/server/mysql-5.6.21 --datadir=/tmp/slave --socket=/tmp/mysql_slave.sock --port=3307 --log-error=/tmp/slave/log.err --user=root 2>&1 & // Direct the slave to use the master CHANGE MASTER TO MASTER_HOST = '127.0.0.1',MASTER_PORT = 3306,MASTER_USER = 'repl',MASTER_PASSWORD = 'slavepass',MASTER_AUTO_POSITION = 1; START SLAVE; // Start load on master ( test case - just using my way to generate bulk data, you may want to try your own method) // Master - Session 1 use test; drop table if exists A; CREATE TABLE `A` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(250), PRIMARY KEY (`id`) ) ENGINE=innodb; set @id:=0; insert into `A` values (@id:=@id+1,md5(rand()*1000000)),(@id:=@id+1,md5(rand()*1000000)),(@id:=@id+1,md5(rand()*1000000)),(@id:=@id+1,md5(rand()*1000000)); insert into `A`(`id`,`name`) select @id:=@id+1,md5(rand()*1000000) from `A` k1, `A` k2, `A` k3, `A` k4,`A` k5,`A` k6, `A` k7, `A` k8, `A` k9, `A` k0,`A` ka, `A` kb, `A` kc, `A` kd limit 10000000; // Master - Session 2 use test; drop table if exists B; CREATE TABLE `B` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(250), PRIMARY KEY (`id`) ) ENGINE=innodb; set @id:=0; insert into `B` values (@id:=@id+1,md5(rand()*1000000)),(@id:=@id+1,md5(rand()*1000000)),(@id:=@id+1,md5(rand()*1000000)),(@id:=@id+1,md5(rand()*1000000)); insert into `B`(`id`,`name`) select @id:=@id+1,md5(rand()*1000000) from `B` k1, `B` k2, `B` k3, `B` k4,`B` k5,`B` k6, `B` k7, `B` k8, `B` k9, `B` k0,`B` ka, `B` kb, `B` kc, `B` kd limit 10000000; // Master - Session 3 use test; drop table if exists C; CREATE TABLE `C` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(250), PRIMARY KEY (`id`) ) ENGINE=innodb; set @id:=0; insert into `C` values (@id:=@id+1,md5(rand()*1000000)),(@id:=@id+1,md5(rand()*1000000)),(@id:=@id+1,md5(rand()*1000000)),(@id:=@id+1,md5(rand()*1000000)); insert into `C`(`id`,`name`) select @id:=@id+1,md5(rand()*1000000) from `C` k1, `C` k2, `C` k3, `C` k4,`C` k5,`C` k6, `C` k7, `C` k8, `C` k9, `C` k0,`C` ka, `C` kb, `C` kc, `C` kd limit 10000000; // Slave stop slave; <-- this should be done when Master's session 1 and 2 bulk load is over and binlog file size is over >800M on master #set 1 second slave_net_timeout on slave set global slave_net_timeout=1; ^^ this warns mysql> set global slave_net_timeout=1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1704 | The requested value for the heartbeat period exceeds the value of `slave_net_timeout' seconds. A sensible value for the period should be less than the timeout. | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show variables like 'slave_net_timeout'; +------------------------------+-----------------------+ | Variable_name | Value | +------------------------------+-----------------------+ | slave_net_timeout | 1 | // Master - Session 4 free page caches on master so binlog file read is slow. You can use this bash script on linux while [ 1 ]; do echo 3 > /proc/sys/vm/drop_caches done // Slave start slave io_thread // Slave mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting to reconnect after a failed master event read Master_Host: 127.0.0.1 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: cluster-repo-bin.000004 Read_Master_Log_Pos: 843610649 Relay_Log_File: cluster-repo-relay-bin.000004 Relay_Log_Pos: 2187 Relay_Master_Log_File: cluster-repo-bin.000004 Slave_IO_Running: Connecting Slave_SQL_Running: No ^^ stuck on this state from long (monitored over 15 minutes) Slave_IO_Running status remaines in "Connecting", and for a split second to 'Yes' and again changes back to "Connecting". // Master mysql> show processlist; +----+------+-----------------+------+------------------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+------+------------------+------+-------+------------------+ | 1 | root | localhost | test | Sleep | 486 | | NULL | | 3 | root | localhost | test | Query | 0 | init | show processlist | | 4 | repl | localhost:51487 | NULL | Binlog Dump GTID | 113 | init | NULL | | 5 | repl | localhost:51488 | NULL | Binlog Dump GTID | 112 | init | NULL | | 6 | repl | localhost:51489 | NULL | Binlog Dump GTID | 51 | init | NULL | +----+------+-----------------+------+------------------+------+-------+------------------+ 5 rows in set (0.00 sec) .. .. To increase the zombie threads count.. Just run over stop slave; start slave io_thread; couple of times and then they never goes away and count icreases by +1 at an interval. mysql> show processlist; +--------+------+-----------------+------+------------------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +--------+------+-----------------+------+------------------+------+-------+------------------+ | 1 | root | localhost | test | Sleep | 3689 | | NULL | | 10 | root | localhost | test | Query | 0 | init | show processlist | | 652858 | repl | localhost:51836 | NULL | Binlog Dump GTID | 1027 | init | NULL | | 652859 | repl | localhost:51837 | NULL | Binlog Dump GTID | 966 | init | NULL | | 652860 | repl | localhost:36321 | NULL | Binlog Dump GTID | 905 | init | NULL | | 652861 | repl | localhost:36322 | NULL | Binlog Dump GTID | 844 | init | NULL | | 652862 | repl | localhost:36323 | NULL | Binlog Dump GTID | 783 | init | NULL | | 652863 | repl | localhost:36324 | NULL | Binlog Dump GTID | 727 | init | NULL | | 652864 | repl | localhost:36325 | NULL | Binlog Dump GTID | 726 | init | NULL | | 652865 | repl | localhost:36326 | NULL | Binlog Dump GTID | 726 | init | NULL | | 652866 | repl | localhost:36327 | NULL | Binlog Dump GTID | 725 | init | NULL | | 652867 | repl | localhost:36328 | NULL | Binlog Dump GTID | 725 | init | NULL | | 652868 | repl | localhost:36329 | NULL | Binlog Dump GTID | 724 | init | NULL | | 652869 | repl | localhost:36330 | NULL | Binlog Dump GTID | 724 | init | NULL | | 652870 | repl | localhost:36331 | NULL | Binlog Dump GTID | 724 | init | NULL | | 652871 | repl | localhost:36332 | NULL | Binlog Dump GTID | 723 | init | NULL | | 652872 | repl | localhost:36333 | NULL | Binlog Dump GTID | 723 | init | NULL | | 652873 | repl | localhost:36334 | NULL | Binlog Dump GTID | 722 | init | NULL | | 652874 | repl | localhost:36335 | NULL | Binlog Dump GTID | 722 | init | NULL | | 652875 | repl | localhost:36336 | NULL | Binlog Dump GTID | 721 | init | NULL | | 652876 | repl | localhost:36337 | NULL | Binlog Dump GTID | 720 | init | NULL | | 652877 | repl | localhost:36338 | NULL | Binlog Dump GTID | 659 | init | NULL | | 652878 | repl | localhost:35219 | NULL | Binlog Dump GTID | 598 | init | NULL | | 652879 | repl | localhost:35220 | NULL | Binlog Dump GTID | 537 | init | NULL | | 652880 | repl | localhost:35221 | NULL | Binlog Dump GTID | 476 | init | NULL | | 652881 | repl | localhost:35222 | NULL | Binlog Dump GTID | 415 | init | NULL | | 652882 | repl | localhost:35223 | NULL | Binlog Dump GTID | 354 | init | NULL | | 652883 | repl | localhost:38286 | NULL | Binlog Dump GTID | 293 | init | NULL | | 652884 | repl | localhost:38287 | NULL | Binlog Dump GTID | 232 | init | NULL | | 652885 | repl | localhost:38288 | NULL | Binlog Dump GTID | 171 | init | NULL | | 652886 | repl | localhost:38289 | NULL | Binlog Dump GTID | 110 | init | NULL | | 652887 | repl | localhost:56711 | NULL | Binlog Dump GTID | 49 | init | NULL | +--------+------+-----------------+------+------------------+------+-------+------------------+ 32 rows in set (0.00 sec) // Slave status stuck in the same position mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting to reconnect after a failed master event read Master_Host: 127.0.0.1 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: cluster-repo-bin.000004 Read_Master_Log_Pos: 843610649 Relay_Log_File: cluster-repo-relay-bin.000004 Relay_Log_Pos: 2187 Relay_Master_Log_File: cluster-repo-bin.000004 Slave_IO_Running: Connecting Slave_SQL_Running: No ####### OS, MySQL version used mysql> \! more docs/INFO_SRC revision-id: georgi.kodinov@oracle.com-20140911132720-endbsmmm8137eqef date: 2014-09-11 16:27:20 +0300 build-date: 2014-09-11 15:42:15 +0200 revno: 6130 branch-nick: mysql-5.6.21-release MySQL source 5.6.21 mysql> \! cat /etc/*release LSB_VERSION=base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch Oracle Linux Server release 6.5 Red Hat Enterprise Linux Server release 6.5 (Santiago) Oracle Linux Server release 6.5