This document expains the step-by-step process to
a. Create Master-Slave Replication
b. Pause Replication
c. Resume Replication
d. Disable Replication
e. Restore Database
sudo vi /etc/mysql/my.cnf
a. Add Master Server's IP Address in place of <master_ip_address>:
bind-address = <master_ip_address>
b. Uncomment/Insert below lines:
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
c. Add the Master Database name in place of <database_name>:
binlog_do_db = <database_name>
sudo service mysql restart
mysql -u <username> -p
GRANT REPLICATION SLAVE ON *.* TO '<username>'@'%' IDENTIFIED BY '<password>';
FLUSH PRIVILEGES;
USE <database_name>;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS
* Note down the values for status response.
* If required, then take the backup of Master database for creating Slave using below command:
mysqldump -u <username> -p --opt <database_name> > /path/to/database.sql
UNLOCK TABLES;
QUIT;
mysql -u <username> -p
CREATE DATABASE <database_name>;
EXIT;
mysql -u <username> -p <database_name> < /path/to/database.sql;
sudo vi /etc/mysql/my.cnf
a. Uncomment/Insert below line as:
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = <database_name>
relay-log = /var/log/mysql/mysql-relay-bin.log
sudo service mysql restart
mysql -u <username> -p
CHANGE MASTER TO MASTER_HOST=<master_ip_address>, MASTER_USER='<username>', MASTER_PASSWORD='<password>', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
START SLAVE;
SHOW SLAVE STATUS\G
* If there is an issue in connecting, you can try starting slave with a command to skip over it by:
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
SLAVE START;
EXIT;
mysql -u <username> -p
STOP SLAVE SQL_THREAD;
START SLAVE SQL_THREAD;
EXIT;
mysql -u <username> -p
STOP SLAVE;
RESET SLAVE ALL;
EXIT;
* Edit the my.cnf file and remove any information (if present) which refers to "master-..." or "replicate-..." options. Sometimes you may not have anything in the my.cnf, since replication can be setup dynamically as well.
sudo service mysql restart
mysql -u <username> -p
STOP SLAVE;
EXIT;
scp <remote_user>@<remote_host_ip>:/path/to/remote/database.sql /path/to/local/file;
mysql -u <username> -p
DROP DATABASE <database_name>;
CREATE DATABASE <database_name>;
EXIT;
mysql -u root -f -p <database_name> < /path/to/database.sql
mysql -u <username> -p
DROP DATABASE <database_name>;
CREATE DATABASE <database_name>;
EXIT;
mysql -u root -f -p <database_name> < /path/to/database.sql
mysql -u <username> -p
USE <database_name>;
SHOW MASTER STATUS;
EXIT;
mysql -u <username> -p
USE <database_name>;
SHOW SLAVE STATUS;
EXIT;
CHANGE MASTER TO MASTER_HOST=<master_ip_address>, MASTER_USER='<username>', MASTER_PASSWORD='<password>', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
START SLAVE;
SHOW SLAVE STATUS\G
EXIT;
* Slave Status now should be similar to Master Server. Also test if Master-Slave Replication is working or not by doing some changes at any test table in Master Database; which should be reflected in Slave Database.
Copyright © Vikrant Kakad 2018