MySQL Master-Slave Replication

MySQL Master-Slave Replication in linux

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 :

SHOW MASTER STATUS; +——————+———-+————–+——————+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +——————+———-+————–+——————+ | mysql-bin.000002 | 107 | kencorner | | +——————+———-+————–+——————+ 1 row in set (0.00 sec)

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 restaore 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.


You May Also Enjoy Reading This …

One comment

Leave a Reply

Your email address will not be published. Required fields are marked *