MySQL 5.6 -- dump-slave parameter usage
In MySQL 5.5 and later versions, a parameter "dump-slave" is added. With this parameter, we can export data on the slave node to create a new slave to avoid pressure on the master database.
The method is as follows:
Run the command on slave;
1. view the current synchronization status
# Show slave status \ G focuses on the following:
Master_Log_File: mysql-bin.000095
Read_Master_Log_Pos: 50607116
Relay_Master_Log_File: mysql-bin.000095
Exec_Master_Log_Pos: 50607116
2. Export data on slave as follows:
# Mysqldump-uroot-proot -- single-transaction -- dump-slave = 2-A> all. SQL
3. View Exported Files
# Head-30 all. SQL can see fields similar to the following:
-- Change master to MASTER_LOG_FILE = 'mysql-bin.000095 ', MASTER_LOG_POS = 50607116;
In this case, we will take the exported data and restore it to a new slave. Execute:
> CHANGE MASTER
MASTER_HOST = 'master _ ip ',
MASTER_USER = 'replace user ',
MASTER_PASSWORD = '000000 ',
MASTER_PORT = 3306,
MASTER_LOG_FILE = 'mysql-bin.000095 ',
MASTER_LOG_POS = 50607116,
MASTER_CONNECT_RETRY = 10;
> Start slave; you can start replication on the new slave.
This article permanently updates the link address: