MySQL Master-Slave Replication in linux

MySQL Master-Slave Replication replication is used for taking backup of MYSQL data from Master Server to Slave Server in real time.

Assuming that mysql is already install in both server i.e Master server and Slave Server.

IP address of both server

Master Server: 192.168.1.10

Slave Server: 192.168.1.20

Database Name: kencorner

Database Password : pwdkencorner

First run This command in both master and slave server in MYSQL shell

GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY 'pwdkencorner' WITH GRANT OPTION;

Step 1 Configuring the Master Server

Taking backup of Master Server

mysqldump -uroot -ppwdkencorner kencorner > dumpfilename.sql

And then Transfer dumpfilename.sql in Slave server later we will restore it in Slave server

Then login to MySQL shell in Master Server and run below command

GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.1.20' IDENTIFIED BY 'pwdkencorner';

Then open up the mysql configuration file on the master server. (Path for my.cnf may be differ)

sudo vi /etc/mysql/my.cnf

add the following line in [mysqld] group

log-bin=mysql-bin 
binlog-do-db=kencorner 
server-id=1 
innodb_flush_log_at_trx_commit=1 
sync_binlog=1

save and exit out of the configuration file.

Here we can see server-id=1 this is unique id and 1 stand for master server.

Re-staring MySQL.

 sudo service mysql restart

Then

Go to the MySQL shell.

mysql -uroot -pkencorner

SHOW MASTER STATUS;

It will give us output like this :

Note : Here it gives us starting position of slave database.keep note of this.

Step 2 Configuring Slave Server

Restore Database in slave server

Login to MYSQL shell

 mysql -uroot -ppwdkencorner

create database kencorner; 
exit;

Then run the below command which will restore master server database to slave server database

mysql -u root -ppwdkencorner kencorner << dumpfilename.sql

Then open up the mysql configuration file on the slave server. (Path for my.cnf may be differ)

 sudo vi /etc/mysql/my.cnf

add the following line in [mysqld] group

server-id=2 
replicate-do-db=kencorner 
slave-skip-errors=1062

save and exit out of the configuration file.

Here server-id=2, which is different from master’s server-id.

Restarting MySQL.

sudo service mysql restart

Go to the MySQL shell.

 mysql -uroot -pkencorner

 CHANGE MASTER TO MASTER_HOST='192.168.1.10',MASTER_USER='root', MASTER_PASSWORD='pwdkencorner',MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=107;

SLAVE START;

show slave status \G

It will display the status of replication. The \G is used to display the content in more readable form.

All done.

Understanding Linux

Understanding Linux