In this article, I will let you know how to set up Master-Slave Replication in MySql.
For this you should have two IPs, one for Master server and another one for Slave server.
Consider we have the below two IPs for Master & Slave server.
10.10.19.20 – Master Server
10.10.19.22 – Slave Server
1. Install MySql in both the Master & Slave Server. You can visit the link given below and download the MySql as per your OS.
http://dev.mysql.com/doc/refman/5.7/en/installing.html
For this you should have two IPs, one for Master server and another one for Slave server.
Consider we have the below two IPs for Master & Slave server.
10.10.19.20 – Master Server
10.10.19.22 – Slave Server
1. Install MySql in both the Master & Slave Server. You can visit the link given below and download the MySql as per your OS.
http://dev.mysql.com/doc/refman/5.7/en/installing.html
Step 1.
Configure the Master Server :-
Open the file vim /etc/mysql/my.cnf
Change the bind address as
Configure the Master Server :-
Open the file vim /etc/mysql/my.cnf
Change the bind address as
bind-address = 10.10.19.20
Now change the Server-id, It should be unique and does not match with any server-id within the Replication group. I am giving the server-id 1, just for ease.
server-id=1
Now enable the logging of log_bin, our slave servers will copy the data from this bin file. Uncomment this line in /etc/mysql/my.cnf as given below.
log_bin = /var/log/mysql/mysql-bin.log
Finally, we have to decide which database we want to replicate to slave server, mention the database name.
binlog_do_db = db_name
Restart the MySql.
sudo service mysql restart
Now, we will log-in to mysql shell to give access to slave-user to replicate data from Master server.
mysql -uroot -p
To give access to the slave-user, we will grant permission.
GRANT REPLICATION SLAVE ON *.* TO 'slave-user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
Switch to database which you want to replicate.
USE db_name
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
Result will look this.
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 110 | db_name | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
Above given is the position from where the Slave server start replication from Master.
Take the database backup using mysqldump
Take the database backup using mysqldump
mysqldump -uroot -p db_name --master-data --single-transaction > db_name.sql
Now Unlock the database and exit.
UNLOCK TABLES;
QUIT;
We are done with Master-Server configuration.
Step 2.
Import the database, which we have previously taken at Master-Server to Slave-Server.
Import the database, which we have previously taken at Master-Server to Slave-Server.
mysql -u root -p db_name < /path/to/db_name.sql
Configure the Slave-Server( Ip- 10.10.19.22) :-
Open the file /etc/mysql/my.cnf
Open the file /etc/mysql/my.cnf
vi /etc/mysql/my.cnf
Change the server-id, log_bin, relay-log, binlog_do_db as given below.
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
bin_log_do_db = db_name
Restart the mysql.
sudo service mysql restart
Log-in to MySql shell in Slave-server and enable replication.
mysql -u root -p
CHANGE MASTER TO MASTER_HOST='10.10.19.20',MASTER_USER='slave-user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 117;
Above statement do following things.
1. It update the current server as slave of Master-Server.
2. It gives to server the correct login credentials.
3. It inform the Slave-Server replication should start from where; the master log file and log position come from the numbers we note down previously.
Start the slave server from the MySql shell.
1. It update the current server as slave of Master-Server.
2. It gives to server the correct login credentials.
3. It inform the Slave-Server replication should start from where; the master log file and log position come from the numbers we note down previously.
Start the slave server from the MySql shell.
START SLAVE;
You can see the Slave-Server status from MySql shell.
SHOW SLAVE STATUS;
If getting any issue while connecting start the slave with a command to skip over it.
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;
Your MySql Master-Slave Replication is ready to use.
