How to Set Up MySQL Replication

MySQL Replication basically allows the contents of the master database server in MySQL to be replicated to other database servers. In this tutorial we will show you how to set up MySQL replication through phpMyAdmin.

Step#1: Getting Started with Replication

To get started, first of all, log in to your php my admin panel, and then click on the Replication tab on the top.

locate Replication tab

Step#2: Replication Plan

From there, click on the Configure option, under the Master Replication section.

With that done, the master configuration section will expand, having a list of databases and a replication plan as well.

expand configuration options in Master Replication

Step#3: Create new Database

Here, you can either Replicate All databases while ignoring the ones selected, or the other way round, that is, ignore all the databases and replicate the ones which would be selected.

Let’s choose the second plan and then select the database named “test” over here. With that done, copy the lines shown below the list. These have to be added in the MySQL “my.ini” file.

name the database to replicate to

Step#4: MY.INI File

Since we are using the XAMPP server, the “my.ini” file can be fetched by clicking on the Config button and choosing the “my.ini” option against the MySQL Module.

locate my.ini file

Step#5: Setting up MY.INI File

Once the file opens up, navigate to the “log_error” parameter, and just paste the copied lines beneath that. With that done, change the “max allowed packet” size to 16 MB.

Once you are done with that, scroll down the file and comment out the “server-id” parameter with a hash sign.

When you are done with that, save the file and then restart the services of both Mysql and Apache.

draw rectangle along page

Step#6: Master Replication Setup

After that, switch back to your phpMyAdmin page, and hit the F5 key to refresh the panel. With that done, move over to the Replication tab again. There you will find that the Master Replication has been set up successfully. You can click on the Show Master Status option to see its status. Now, click on the Add Slave Replication user option to add a Replication user

updating status of Master in replication

Step#7: Creating Replication User

Specify the username and the host, and then click on the Go button. A replication user would be added as a result.

adding replication user

Step#8: Slave Replication Setup

Now open up the Replication tab again, and click on the Configure option under the Slave Replication section. Another page will open up, and from here, copy the Server id which has to be pasted in the “my.ini” file.

So let’s open up the “my.ini” file again and locate the “server-id ” parameter which we had commented earlier.

Over here, simply paste the Server id copied from the Slave configuration, save the file and restart the MySQL services.

configuring slave replication

Step#9: Updating Slave Configuration

Now switch back to the Slave Replication page again, and specify the user details which were created before.

With that done, click on the Go button. A message will appear that the Master server had changed successfully.

add user to slave replication

Step#10: Warning Messages

Now, if you open the Replication tab once more, you will notice two warning messages appearing in the Slave Replication portion. One would be regarding the Slave SQL Thread and the other would be regarding the Slave IO Thread, both of which won’t be running. To resolve these errors, simply click on the Control Slave option, and Start these threads one after another. You will notice that the warning messages would disappear.

taking care of warning messages

Step#11: Verifying Replicated database

Now click on the Databases tab and here you can see that the test database has been replicated successfully. This means that we have setup and configured MySQL replication.

status of replicated database

And this is how to set up MySQL replication using phpMyAdmin.