MySQL Replication
Standard asynchronous replication is not a synchronous cluster. Keep in mind that stand and semi synchronous replication do not guarantee that the environments replication data coherence data integrity
- Statement-Based
- Row-Based
- Mixed replication
First fact you absolutely need to remember is MySQL Replication is single threaded, which means if you have any long running write query it clogs replication stream and small and fast updates which go after it in MySQL binary log can’t proceed.
Master:
- Dump thread
Slave:
- IO thread
- SQL thread
In /etc/my.cnf:
slave_parallel_workers = N
Worker threads are visible with SHOW PROCESSLIST;. On master:
mysql> SHOW PROCESSLIST;
+-------+------+-----------------+-------+-------------+--------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------+-----------------+-------+-------------+--------+---------------------------------------------------------------+------------------+
| 5923 | repl | victor:39884 | NULL | Binlog Dump | 145429 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 51098 | root | localhost:48238 | bampi | Sleep | 206 | | NULL |
| 89478 | root | localhost | bampi | Query | 0 | starting | SHOW PROCESSLIST |
| 91879 | root | localhost:38758 | bampi | Sleep | 6 | | NULL |
+-------+------+-----------------+-------+-------------+--------+---------------------------------------------------------------+------------------+
4 rows in set (0.00 sec)
On slave:
mysql> SHOW PROCESSLIST;
+--------+-------------+-----------+-------+---------+--------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+-------------+-----------+-------+---------+--------+--------------------------------------------------------+------------------+
| 2060 | system user | | NULL | Connect | 145420 | Waiting for master to send event | NULL |
| 2061 | system user | | NULL | Connect | 129 | Slave has read all relay log; waiting for more updates | NULL |
| 270102 | root | localhost | bampi | Query | 0 | starting | SHOW PROCESSLIST |
+--------+-------------+-----------+-------+---------+--------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
Reset MySQL Slave
Sometimes when something went wrong with MySQL slave, for example the slave cannot be started by Pacemaker resource agent, could be a very serious problem. Entries being inserted/updated/deleted on master cannot synchronize to slave. A brutal but simple solution is to reset the slave, forcing it to re-synchronize from a specified binlog position.
First, check MySQL master’s status. We’re going to use these information on the slave.
mysql> SHOW MASTER STATUS\\G
*************************** 1. row ***************************
File: binlog.000038
Position: 569781
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.05 sec)
Then bring up MySQL service on the slave node. Reset the slave with the binlog file and position we got from the above command.
mysql> RESET SLAVE;
mysql> CHANGE MASTER TO
-> MASTER_HOST='bampi-2',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='reppass',
-> MASTER_LOG_FILE='binlog.000038',
-> MASTER_LOG_POS=569781;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: bampi-1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000038
Read_Master_Log_Pos: 569781
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 424226
Relay_Master_Log_File: binlog.000038
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: 569781
Relay_Log_Space: 424434
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: 3b63306e-7e96-11e8-b7bd-ea448e98a451
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql> STOP SLAVE;