What’s MySQL Replication

Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves).

So MySQL Master-Master Replication

In other words MySQL replication is the process by which data stored in a database, will be copied to a second server’s database. This is called MySQL master-slave replication. Today I’m going to focus on MySQL master-master replication where it allows data to be copied from either server. In other words perform reads or writes from either server.

 MySQL master-master replication, Image Courtesy : alexfilatov.com

Image Courtesy : alexfilatov.com

Below are the two server we are going to use, Server X: 192.168.X.28 Server Y: 192.168.Y.29

Step 1 – Install and Configure MySQL on Server X

We need to edit /etc/mysql/my.cnf on Server X and add below lines.

After you save and quit that file, you need to restart mysql:

Open up the MySQL shell.

We have to create a user where the user is used for the replication. User “replicator_user” & replace “password” with the password you wish.

Next, we need to give this user permissions to replicate the data:

The following command will output important information which will be used later.

lock the database to prevent any new changes

Let’s export the database using mysqldump

unlock the databases (making it writeable again).

Finish up by exiting the shell.

Step 2 – Install and Configure MySQL on Server Y

We need to repeat the same steps that we followed on Server X.

Let’s start by editing the /etc/mysql/my.cnf file.

After you save and quit the file, you need to restart MySQL:

It’s time to go into the MySQL shell.

Next, we need to create the database that we are going to replicate.

then exit

Import the database that you previously exported.

Let’s login the back to the shell. Just as on Server X, A user will be created which will be responsible for the replication.

permissions for replication

This will allow replication to begin. The following should be typed at the mysql shell

Now let’s make a note of the master log file and position to use to replicate in the other direction (from Server Y to Server X).

Step 3 – Completing Replication on Server X

Running this command will replicate all data from Server Y.

You be able to see the details of the replication by typing in this command. The \G rearranges the text to make it more readable.

As a verification that everything are working as expected examine the output of the above command and make sure that Slave_IO_Running and Slave_SQL_Running are both YES.

Tips & Tricks

Above mentioned configuration is the minimal configuration for a MySQL master-master replication. See the below for a full my.ini configurations.

Hope you got an idea how to do a MySQL master-master replication. If you have any questions on MySQL master-master replication let me know in the comments below. Your feedback is highly appreciated(happy-face).

Be Sociable, Share!