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

Step 1 Configuring the Master Server

Taking backup of Master Server

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

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

add the following line in [mysqld] group

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.

Then

Go to  the MySQL shell.

It will give us output like this :

 

+——————+———-+————–+——————+
| 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

 

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

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

add the following line in [mysqld] group

save and exit out of the configuration file.

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

Restarting MySQL.

Go to  the MySQL shell.

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 *