The Database Design Resource Center



MySQL Replication

MySQL replication provides the ability to automatically and continuously replicate databases between two servers over a network. The benefits of this capability are fairly obvious; if there is a failure of some sort on one server, a ready-made copy of the database is already running on a connected server.

Replication is accomplished through a master-slave relationship between two servers.
The database that is being accessed and modified by clients is the master.
The slave server requests copies of data inserted or updated on the master.

A master server can have more than one slave, and a slave can serve as a master to its own slave, thus forming a master-slave chain. This article deals with the process of setting up MySQL replication over a network using TCP/IP between a single master-slave pair.

Understanding MySQL Replication

MySQL Replication works via two threads on the slave, the I/O thread and the SQL thread. The I/O thread receives events – updates that occur on the master – from the binary logs of the master server. The I/O thread writes these updates to a relay log. The SQL thread then reads the relay log and executes the events as updates to the slave database. The two threads work independently of one another so that one can be disabled without effecting the other. One advantage of this is that the SQL thread can be stopped while a backup is made of the slave database. New events from the master will still be written to the relay log, and will be executed when the SQL thread is resumed.

In order to establish the required connection and the transfer of data described above, each slave must have an account on its master. The slave periodically connects to the master and locates the position in the master's binary log where it left off the last time it connected. The exchange of data resumes at this point.

It is advisable to set up replication only on servers using the same versions of MySQL. MySQL replication has been an ongoing and changing area of development, and there are some compatibility issues between different versions of MySQL.

Setting up MySQL Replication

Here is an outline of the steps needed to establish MySQL replication on a master and slave over a TCP/IP network.
  • Set up an account on the master. This is the account that the slave will use to connect with. You must be logged into MySQL with grant privileges to do this:

    MySQL Replication

  • It is necessary to have an exact copy of the master database on the slave before replication begins.

    This is can be accomplished by copying the data files over the network, or by using one of the many backup methods available. The tables on the master server should be locked so that no updates can be made until after replication is in place. If binary logging has been used on the master server prior to this point, make a backup of the logs. The original log will be flushed and restarted later.

  • If the master server is running, shut it down.
  • 1.If binary logging has not been in use up to this point on the master, enable it now by editing the my.cnf file. Also each server must be assigned a unique id so that connections can be established. Server id's must be a positive integer from 1 to 4294967296. Enter one for the master server now:

    MySQL Replication

  • Start up the master server. If you had binary logging enabled prior to shutting the server down in step 4, issue the RESET MASTER statement from the MySQL command line. This will flush the binary log so your master server and slave server do not get out of sync. As an aside, it is possible to sync the two servers without clearing the binary log. The SHOW MASTER STATUS statement will show the current in the offset where the slave should begin processing new updates, and the slave can be configured to find this offset in the binary file. However, the procedure described here accomplishes the same end and is somewhat simpler.
  • Stop the slave server if it is running. It needs to be configured to connect to the master and to know its id number. That is done by adding the following to an option file on the slave:

    MySQL Replication

    The slave_server_id is the unique id number of the slave server. master-host is the host name or IP address of the master server. The slave_user and slave_pass must be the same as the ones you used when you created the account in step 1.

    It is possible, by the way, to set up MySQL replication using two MySQL servers in the same machine. They must be two separate servers installed in two different locations. If you choose to do this, be sure to use 127.0.0.1 rather than localhost to identify the server. This will ensure that the connection is made using TCP/IP.

    Also, the option file described in this section should not be placed in /etc/my.cnf. It should placed in secure directory, as it contains a plain text user name and password.

  • Restart the slave. If all is well, the slave will connect and begin accepting updates from the master server. Once it is running, it will create a file named master.info, located in the data directory. If this file exists, the slave will get its startup configuration options from this file, rather than the ones you specified in the options file in step 6. If you ever need to change these options, you will need to use the CHANGE MASTER statement rather than edit the options file.

You may choose not to replicate all databases from one server to another. To exclude a database, set the following in the master server's option file:

MySQL Replication

The MySQL database is one you may not want to replicate, because it would place all the user information from the master to the slave.

Return to MySQL DBA


Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant


Free eBook

Subscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:


What visitors say...

"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.

I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."

Mike, USA

Read more Testimonials



Database Normalization eBook:


Database Normalization eBook




Copyright © www.databasedesign-resource.com / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein.

Return to top

Copyright acknowledgement note:

The name Oracle is a trademark of Oracle Corporation.
The names MS Access/MS SQL Server are trademarks of Microsoft Corporation.
Any other names used on this website may be trademarks of their respective owners, which I fully respect.