// conf used [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.26: cat master.cnf slave.cnf #Master [mysqld] pid-file = /export/umesh/server/binaries/mysql-advanced-5.6.26/run/master.pid socket = /export/umesh/server/binaries/mysql-advanced-5.6.26/run/master.sock log-error=/export/umesh/server/binaries/mysql-advanced-5.6.26/log/master.log slow_query_log_file=/export/umesh/server/binaries/mysql-advanced-5.6.26/log/slow.log general_log_file=/export/umesh/server/binaries/mysql-advanced-5.6.26/log/general.log port = 15000 server-id = 1 basedir = /export/umesh/server/binaries/mysql-advanced-5.6.26 datadir = /export/umesh/server/binaries/mysql-advanced-5.6.26/master tmpdir = /tmp log-bin = /export/umesh/server/binaries/mysql-advanced-5.6.26/log/master-bin log-bin-index = /export/umesh/server/binaries/mysql-advanced-5.6.26/log/master-bin.index binlog_format=STATEMENT #Slave [mysqld] pid-file = /export/umesh/server/binaries/mysql-advanced-5.6.26/run/slave.pid socket = /export/umesh/server/binaries/mysql-advanced-5.6.26/run/slave.sock log-error=/export/umesh/server/binaries/mysql-advanced-5.6.26/log/slave.log port = 15001 server-id = 2 basedir = /export/umesh/server/binaries/mysql-advanced-5.6.26 datadir = /export/umesh/server/binaries/mysql-advanced-5.6.26/slave tmpdir = /tmp relay-log-index = /export/umesh/server/binaries/mysql-advanced-5.6.26/log/slave-relay-bin.index relay-log = /export/umesh/server/binaries/mysql-advanced-5.6.26/log/slave-relay-bin log-bin = /export/umesh/server/binaries/mysql-advanced-5.6.26/log/slave-bin log-bin-index = /export/umesh/server/binaries/mysql-advanced-5.6.26/log/slave-bin.index binlog_format=STATEMENT log_slow_slave_statements=ON slow_query_log=ON long_query_time=0 // start up, setup simple replication scripts/mysql_install_db --defaults-file=./master.cnf bin/mysqld --defaults-file=./master.cnf 2>&1 & scripts/mysql_install_db --defaults-file=./slave.cnf bin/mysqld --defaults-file=./slave.cnf 2>&1 & // enable Replication Using GTIDs - https://dev.mysql.com/doc/refman/5.6/en/replication-gtids-howto.html scripts/mysql_install_db --defaults-file=./master.cnf bin/mysqld --defaults-file=./master.cnf --gtid_mode=ON --log-slave-updates --enforce-gtid-consistency 2>&1 & scripts/mysql_install_db --defaults-file=./slave.cnf bin/mysqld --defaults-file=./slave.cnf --gtid_mode=ON --log-slave-updates --enforce-gtid-consistency --skip-slave-start 2>&1 & // mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: repl Master_Port: 15000 Connect_Retry: 60 Master_Log_File: master-bin.000004 Read_Master_Log_Pos: 2258 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 2470 Relay_Master_Log_File: master-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 2258 Relay_Log_Space: 2674 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 3242b70a-4648-11e5-97d2-0010e05f3e06 Master_Info_File: /export/umesh/server/binaries/mysql-advanced-5.6.26/slave/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 3242b70a-4648-11e5-97d2-0010e05f3e06:1-5 Executed_Gtid_Set: 3242b70a-4648-11e5-97d2-0010e05f3e06:1-5 Auto_Position: 1 1 row in set (0.00 sec) --- To revert to the older file-based replication protocol after using GTIDs, you can issue a new CHANGE MASTER TO statement that specifies MASTER_AUTO_POSITION = 0, -- as well as at least one of MASTER_LOG_FILE or MASTER_LOG_POSITION. -- It seems that without one of MASTER* option it just mysql> stop slave; change master to master_auto_position=0; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Master_Host: 127.0.0.1 Master_User: repl Master_Port: 15000 Connect_Retry: 60 Master_Log_File: master-bin.000004 Read_Master_Log_Pos: 2258 Relay_Log_File: slave-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: master-bin.000004 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 2258 Relay_Log_Space: 151 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 3242b70a-4648-11e5-97d2-0010e05f3e06 Master_Info_File: /export/umesh/server/binaries/mysql-advanced-5.6.26/slave/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 3242b70a-4648-11e5-97d2-0010e05f3e06:1-5 Auto_Position: 0 1 row in set (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: repl Master_Port: 15000 Connect_Retry: 60 Master_Log_File: master-bin.000004 Read_Master_Log_Pos: 2258 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 315 Relay_Master_Log_File: master-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 2258 Relay_Log_Space: 519 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 3242b70a-4648-11e5-97d2-0010e05f3e06 Master_Info_File: /export/umesh/server/binaries/mysql-advanced-5.6.26/slave/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 3242b70a-4648-11e5-97d2-0010e05f3e06:1-5 Auto_Position: 0 1 row in set (0.00 sec) -- Master, provisioned some data mysql> drop table if exists keyvalue; CREATE TABLE `keyvalue` ( Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE `keyvalue` ( -> `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, -> `name1` varchar(250), -> `name2` varchar(250), -> `name3` varchar(250), -> `name4` varchar(250), -> PRIMARY KEY (`id`) -> ) ENGINE=innodb; Query OK, 0 rows affected (0.00 sec) mysql> mysql> set @id:=0; Query OK, 0 rows affected (0.00 sec) mysql> mysql> insert into `keyvalue` values -> (@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000),md5(rand()*1000000),md5(rand()*1000000)) -> , (@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000),md5(rand()*1000000),md5(rand()*1000000)) -> , (@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000),md5(rand()*1000000),md5(rand()*1000000)) -> , (@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000),md5(rand()*1000000),md5(rand()*1000000)); Query OK, 4 rows affected, 1 warning (0.00 sec) Records: 4 Duplicates: 0 Warnings: 1 mysql> mysql> insert into `keyvalue`(`id`,`name1`,`name2`,`name3`,`name4`) -> select @id:=@id+1,md5(rand()*1000000),md5(rand()*1000000),md5(rand()*1000000),md5(rand()*1000000) from -> `keyvalue` k1, `keyvalue` k2, `keyvalue` k3, `keyvalue` k4,`keyvalue` k5,`keyvalue` k6, `keyvalue` k7, `keyvalue` k8, `keyvalue` k9, -> `keyvalue` k0,`keyvalue` ka, `keyvalue` kb, `keyvalue` kc, `keyvalue` kd limit 50000; Query OK, 50000 rows affected, 3 warnings (0.79 sec) Records: 50000 Duplicates: 0 Warnings: 3 -- Slave mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: repl Master_Port: 15000 Connect_Retry: 60 Master_Log_File: master-bin.000004 Read_Master_Log_Pos: 4209 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 2266 Relay_Master_Log_File: master-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 4209 Relay_Log_Space: 2470 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 3242b70a-4648-11e5-97d2-0010e05f3e06 Master_Info_File: /export/umesh/server/binaries/mysql-advanced-5.6.26/slave/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 3242b70a-4648-11e5-97d2-0010e05f3e06:6-9 Executed_Gtid_Set: 3242b70a-4648-11e5-97d2-0010e05f3e06:1-9 Auto_Position: 0 1 row in set (0.00 sec)