How to Master-Slave replication in mysql
First you have to configure /etc/my.cnf in both master and slave server,
1.Master server configuration minimum needed
/myarch should have mysql:mysql ownership
2.Slave server minimum configuration needed
server_id=20 (should be different from master)
sync_binlog=1
log_bin_index=/myarch/slave_bin_log.index
log-bin=/myarch/slave_bin_log
relay-log-info-file=/myreplog/slave_rep.info
relay-log=/myreplog/slave_rep.log
relay-log-index=/myreplog/slave_rep.index
/myreplog also have mysql.mysql ownership
3. Procedure for creating master-slave replication
1. Stop any application that points to the production db that we are going to sync.
2. login as root and create replication user on master server
Leave the terminal open, otherwise, the database will be unlocked. Write down theFile and Position values, we will need them later.
Then copy this sql dump backup to slave server
7.Determine the current binary log file name and position and note down this,
Which we need later.
mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 3303 | | |
+------------------+----------+--------------+------------------+
The File column shows the name of the log file and Position shows the position within the file. In this example, the binary log file is mysql-bin.000003 and the position is 3303. Record these values. You need them later when you are setting up the slave.
4. Restoring and syncing the database at DR
1.restore the full backup or particular database backup in DR
(here we are going sync all database in the master server )
$ mysql -v -u root -p </thepathofthedumpfile.bkp
6.At last revoke the lock hold on tables from master server , go to live server and execute
First you have to configure /etc/my.cnf in both master and slave server,
1.Master server configuration minimum needed
server_id=10 (it may any number and may unique)
sync_binlog=1
then you have to specify the bin log location
log_bin_index=/myarch/master_bin_log.index
log-bin=/myarch/master_bin_log
sync_binlog=1
then you have to specify the bin log location
log_bin_index=/myarch/master_bin_log.index
log-bin=/myarch/master_bin_log
/myarch should have mysql:mysql ownership
2.Slave server minimum configuration needed
server_id=20 (should be different from master)
sync_binlog=1
log_bin_index=/myarch/slave_bin_log.index
log-bin=/myarch/slave_bin_log
relay-log-info-file=/myreplog/slave_rep.info
relay-log=/myreplog/slave_rep.log
relay-log-index=/myreplog/slave_rep.index
/myreplog also have mysql.mysql ownership
3. Procedure for creating master-slave replication
1. Stop any application that points to the production db that we are going to sync.
2. login as root and create replication user on master server
$mysql -u root -p
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slaveuser'@'%' IDENTIFIED BY 'slave';
mysql>FLUSH PRIVILEGES;
3. check all the open connections to the server
mysql> show processlist;
kill the process if any;
mysql> KILL processid;
4. take database full backup
$mysqldump -v -u root -p --all-databases >/root/dumpfilename.bkp
mysql>FLUSH PRIVILEGES;
3. check all the open connections to the server
mysql> show processlist;
kill the process if any;
mysql> KILL processid;
4. take database full backup
$mysqldump -v -u root -p --all-databases >/root/dumpfilename.bkp
5. Closes all open tables and locks all tables for all databases with a global read lock.
mysql>FLUSH TABLES WITH READ LOCK;
Leave the terminal open, otherwise, the database will be unlocked. Write down theFile and Position values, we will need them later.
Then copy this sql dump backup to slave server
7.Determine the current binary log file name and position and note down this,
Which we need later.
mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 3303 | | |
+------------------+----------+--------------+------------------+
The File column shows the name of the log file and Position shows the position within the file. In this example, the binary log file is mysql-bin.000003 and the position is 3303. Record these values. You need them later when you are setting up the slave.
4. Restoring and syncing the database at DR
1.restore the full backup or particular database backup in DR
(here we are going sync all database in the master server )
$ mysql -v -u root -p </thepathofthedumpfile.bkp
2. stop the Slave I/O threads
mysql > stop slave;
To use CHANGE MASTER TO, the slave replication threads must be stopped (use STOP SLAVE if necessary).
mysql > stop slave;
To use CHANGE MASTER TO, the slave replication threads must be stopped (use STOP SLAVE if necessary).
3. Now issue the following to mysql using the right parameters taken from SHOW MASTER STATUS above:
CHANGE MASTER TO MASTER_HOST='10.10.40.212',
MASTER_USER='slaveuser', MASTER_PASSWORD='slave',
MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=3303;
4.then start slave i/o threads.
mysql>slave start;
5.check slave status by
mysql> show slave status \G;
CHANGE MASTER TO MASTER_HOST='10.10.40.212',
MASTER_USER='slaveuser', MASTER_PASSWORD='slave',
MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=3303;
4.then start slave i/o threads.
mysql>slave start;
5.check slave status by
mysql> show slave status \G;
6.At last revoke the lock hold on tables from master server , go to live server and execute
Mysql > unlock tables;
UNLOCK TABLES explicitly releases any table locks held by the current session.also it release the global read lock acquired with the FLUSH TABLES WITH READ LOCK statement
