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.
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.
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.
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.
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.
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
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.
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.
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.
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.
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.
And this is how to set up MySQL replication using phpMyAdmin.
Home