Set up MySQL database replication

Jack Wallen
Apr 9, 2009
Updated • Nov 28, 2012

If you use databases then you know how important it is to back them up. But backing up a database and being able to use that backup depends upon the back up being always up to date. There is a method that ensures you will always have an up to date backup. That method is database replication.Replication is a way to configure a MySQL database to update on a remote server whenever the local client is updated.

Database replication will require the use of two machines. The reason you want to use two machines is to ensure you will always have a working copy of your database (should one machine die). When using replication you think of your machines as Master and Slave. The Master is the machine that holds the original database. The Slave is the machine that holds the replicated database (the backup per say.) Both machines must have a working MySQL installation and must be networked together. You will need root access as well as access to the database administrator on both machines.

Setting up the Master

For purpose of example our database to replicate will be called "sample_database".? The first thing you need to do is open up the /etc/mysql/my.conf file for editing. You need to search for the lines:

#bind-address =

Uncomment out these lines (remove the "#" character). If these lines do not exist, add them. Now we have to inform mysql which database we plan on replicating. For this you will add the following lines:
log-bin = /var/log/mysql/mysql-bin.log

The above lines do the following:

Line 1: Instruct mysql what log file to use.

Line 2: Instruct mysql which database to replicate

Line 3: Instruct mysql that this machine is the master.

Restart mysql on this machine with the following command:

/etc/init.d/mysql restart

The next step is to log into the mysql terminal and create a user that has replication privileges. To do this issue the command:

mysql -u root -p

You will prompted for the mysql admin password. Now we have to make some mysql magic. From the mysql prompt issue the following:


Where NEW_PASSWORD is the actual password and USER is the actual username that will have replication privileges.

Now issue the command:


You're not done with mysql yet. Now issue the command:

USE sample_database;

And now the command:


And now you need to make sure mysql is seeing the Master and can show the status of the master with the command:


When you issue the above command you should see a listing printed out for your sample_database. Write this information down (you will see a Position number that you will need later).
Now you need to get tables and data from the sample_database. The method I will show you requires that the database on the Master be locked momentarily. To that end the database will be unavailable until the database is unlocked. Keep this in mind when setting this up.

Now you're ready to set up the Slave. Move over to the machine that will serve as the slave.

The first thing to do on the Slave is to create the database the Master will write to. On the slave open up the mysql shell with:

mysql -u root -p

You will be prompted for the password. Now enter the following command to create the database:

CREATE DATABASE sample_database;

And quit mysql with the "quit" command.

Now to configure MySQL to know it is the slave and open up the /etc/mysql/my.conf file for editing. Add the following lines:

Where IP_ADDRESS_OF_MASTER is the actual IP address of the master server, USER is the user created on the master for replication, and USER_PASSWORD is the password given to the replication user on the Master.

Save that file and restart mysql on the slave with the command:

/etc/init.d/mysql restart

Now we have to load the data from the Master into the Slave. This is when the database will be locked. Here are the commands to load the data:

mysql -u root -p

Enter the mysql admin password. Now from the mysql prompt enter the following command:


Now exit the mysql prompt with the command quit.

The next step is to stop the slave so you can finish up the Slave configuration. Issue the following:

mysql -u root -p

You will prompted for the root password.

now enter the command:


Now the next command is a bit lengthy:


IP_ADDRESS_OF_MASTER is the actual IP address of the Master server.
USER is the actual user you created on the Master.
USER_PASSWORD is the actual password you gave the user on the Master.
mysql-bin.007 is the File name from the output of the SHOW MASTER STATUS command from above.
MASTER_LOG_POS is the Position given in the output of the SHOW MASTER STATUS command from above.

Finally issue the command:


and then quit the mysql prompt with the command "quit".

You're done. If you have phpmyadmin installed you can check the status of the databases as they updated. You now have database replication up and running. Congratulations.


Previous Post: «
Next Post: «


  1. Josiah said on May 9, 2017 at 8:41 am

    This things works with localhost, but with remote database (online website database), there is no way to connect and edit the config files.

  2. vysakh said on July 24, 2014 at 11:18 am

    I am using xampp.I changed Xampp/mysql/bin/my.ini. But after that when I am trying to run phpmyadmin using local /phpmyadmin/
    this shows an error message

  3. guest said on August 30, 2012 at 1:56 pm

    @Pier-Olivier P. C̫t̩ Рgreat comment.

  4. pascal said on March 17, 2011 at 12:31 pm

    How to edit the /etc/mysql/my.conf file in sharedhost?

    I am having hostgator shared hosts phpmyadmin.

    How to do this ?

  5. Pier-Olivier P. Côté said on April 10, 2009 at 11:58 pm

    DATABASE REPLICATION IS NOT A BACKUP, you should always have a backup policy although replication is usefull it doesn’t cover everything and I think its article mislead people at the beginning:

    “But backing up a database and being able to use that backup depends upon the back up being always up to date. There is a method that ensures you will always have an up to date backup.”

    Replication is not a backup policy. A mistyped DELETE statement will be replicated on the slave too, and you could end up with two, perfectly synchronized, empty databases. Replication can help protect against hardware failure though. (

    You’ve been warned.

Leave a Reply

Check the box to consent to your data being stored in line with the guidelines set out in our privacy policy

We love comments and welcome thoughtful and civilized discussion. Rudeness and personal attacks will not be tolerated. Please stay on-topic.
Please note that your comment may not appear immediately after you post it.