-- Start up 2 nodes, setup replication rm -rf master scripts/mysql_install_db --defaults-file=./master.cnf --basedir=/export/umesh/server/binaries/mysql-advanced-5.6.30 --datadir=/export/umesh/server/binaries/mysql-advanced-5.6.30/master bin/mysqld --defaults-file=./master.cnf & rm -rf slave scripts/mysql_install_db --defaults-file=./slave.cnf --basedir=/export/umesh/server/binaries/mysql-advanced-5.6.30 --datadir=/export/umesh/server/binaries/mysql-advanced-5.6.30/slave bin/mysqld --defaults-file=./slave.cnf & #Master [mysqld] pid-file = /export/umesh/server/binaries/mysql-advanced-5.6.30/run/master.pid socket = /export/umesh/server/binaries/mysql-advanced-5.6.30/run/master.sock log-error=/export/umesh/server/binaries/mysql-advanced-5.6.30/log/master.log slow_query_log_file=/export/umesh/server/binaries/mysql-advanced-5.6.30/log/slow.log general_log_file=/export/umesh/server/binaries/mysql-advanced-5.6.30/log/general.log port = 15000 server-id = 1 basedir = /export/umesh/server/binaries/mysql-advanced-5.6.30 datadir = /export/umesh/server/binaries/mysql-advanced-5.6.30/master tmpdir = /tmp log-bin = /export/umesh/server/binaries/mysql-advanced-5.6.30/log/master-bin log-bin-index = /export/umesh/server/binaries/mysql-advanced-5.6.30/log/master-bin.index binlog_format=ROW #Slave [mysqld] pid-file = /export/umesh/server/binaries/mysql-advanced-5.6.30/run/slave.pid socket = /export/umesh/server/binaries/mysql-advanced-5.6.30/run/slave.sock log-error=/export/umesh/server/binaries/mysql-advanced-5.6.30/log/slave.log port = 15001 server-id = 2 basedir = /export/umesh/server/binaries/mysql-advanced-5.6.30 datadir = /export/umesh/server/binaries/mysql-advanced-5.6.30/slave tmpdir = /tmp relay-log-index = /export/umesh/server/binaries/mysql-advanced-5.6.30/log/slave-relay-bin.index relay-log = /export/umesh/server/binaries/mysql-advanced-5.6.30/log/slave-relay-bin binlog_format=ROW log_slow_slave_statements=ON slow_query_log=ON long_query_time=0 ### Master [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.30: bin/mysql -uroot -S run/master.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.30-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> CREATE USER 'repl'@'localhost' IDENTIFIED BY 'slavepass'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> mysql> CREATE USER 'repl'@'127.0.0.1' IDENTIFIED BY 'slavepass'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000003 | 813 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) ### Slave [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.30: bin/mysql -uroot -S run/slave.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.30-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CHANGE MASTER TO -> MASTER_HOST='localhost', -> MASTER_PORT=15000, -> MASTER_USER='repl', -> MASTER_PASSWORD='slavepass', -> MASTER_LOG_FILE='master-bin.000003', -> MASTER_LOG_POS=813; Query OK, 0 rows affected, 2 warnings (0.01 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: localhost Master_User: repl Master_Port: 15000 Connect_Retry: 60 Master_Log_File: master-bin.000003 Read_Master_Log_Pos: 813 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 284 Relay_Master_Log_File: master-bin.000003 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: 813 Relay_Log_Space: 457 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: ea8ef301-0786-11e6-83f1-0010e05f3e06 Master_Info_File: /export/umesh/server/binaries/mysql-advanced-5.6.30/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: Auto_Position: 0 1 row in set (0.00 sec) mysql> show processlist; +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ | 1 | root | localhost | NULL | Query | 0 | init | show processlist | | 2 | system user | | NULL | Connect | 11 | Waiting for master to send event | NULL | | 3 | system user | | NULL | Connect | 11 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ 3 rows in set (0.00 sec) ### Slave - STOP SLAVE, and monitor dump thread on master mysql> show processlist; +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ | 1 | root | localhost | NULL | Query | 0 | init | show processlist | | 2 | system user | | NULL | Connect | 11 | Waiting for master to send event | NULL | | 3 | system user | | NULL | Connect | 11 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ 3 rows in set (0.00 sec) mysql> SELECT NOW(); STOP SLAVE; +---------------------+ | NOW() | +---------------------+ | 2016-04-21 08:08:33 | +---------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> show processlist; +----+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+------------------+ | 1 | root | localhost | NULL | Query | 0 | init | show processlist | +----+------+-----------+------+---------+------+-------+------------------+ 1 row in set (0.00 sec) ## Master mysql> show global variables like 'net_retry_count'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | net_retry_count | 10 | +-----------------+-------+ 1 row in set (0.00 sec) mysql> show global variables like 'net_write_timeout'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | net_write_timeout | 60 | +-------------------+-------+ 1 row in set (0.00 sec) mysql> SELECT NOW();show processlist; +---------------------+ | NOW() | +---------------------+ | 2016-04-21 08:08:51 | +---------------------+ 1 row in set (0.00 sec) +----+------+-----------------+------+-------------+------+-----------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+------+-------------+------+-----------------------------------------------------------------------+------------------+ | 1 | root | localhost | NULL | Query | 0 | init | show processlist | | 2 | repl | localhost:42863 | NULL | Binlog Dump | 148 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | +----+------+-----------------+------+-------------+------+-----------------------------------------------------------------------+------------------+ 2 rows in set (0.00 sec) . . ## per documentation - https://dev.mysql.com/doc/refman/5.6/en/replication-implementation-details.html If sufficient time elapses on the master side without activity on the Binlog Dump thread, the master determines that the slave is no longer connected. As for any other client connection, the timeouts for this depend on the values of net_write_timeout and net_retry_count; But, confirmed that it doesn't honour this and binloig dump thread still exists after long time mysql> SELECT NOW();show processlist; +---------------------+ | NOW() | +---------------------+ | 2016-04-21 08:40:03 | +---------------------+ 1 row in set (0.00 sec) +----+------+-----------------+------+-------------+------+-----------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+------+-------------+------+-----------------------------------------------------------------------+------------------+ | 1 | root | localhost | NULL | Query | 0 | init | show processlist | | 2 | repl | localhost:42863 | NULL | Binlog Dump | 2020 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | +----+------+-----------------+------+-------------+------+-----------------------------------------------------------------------+------------------+ 2 rows in set (0.00 sec)