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

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

• 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:

  

• 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:

  

• 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:

  

  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:

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