Crash course in setting up MySQL replication

The MySQL web site has a pretty decent HOWTO for setting up MySQL replication, but it's rather long and obtuse. Here's my much-condensed version.

First, you'll need two MySQL servers. :-) I'm also going to assume that one is already in use, and thus already contains data. The second one is brand-new and thus empty.

Let's call the existing server master.example.com, and the new server slave.example.com.

Make sure the master server is listening on TCP; the slave can't use UNIX sockets to communicate with it (duh.) And of course, make sure the slave server can see the master server via TCP port 3306.

Login to master.example.com and execute the following SQL statements:

If your DNS is horked you can just substitute % for slave.example.com in the above, although it rather reduces your security. Also, as 12345 is a sufficiently strong password for both luggage and planetary atmospheres, it should suffice for your MySQL server.

Next, edit or create the my.cnf file on master.example.com, and add the following section to it:

The log-bin line turns on binary logging, which is required for all this to work. The ID doesn't matter so long as the server has a different ID than the slave (MySQL defaults to an ID of 0 if none is specified.)

So while we're at it, edit or create the same file (my.cnf) on slave.example.com, and give it a different ID:

Of course, for the binary logging and ID changes to take effect, you'll need to bounce both MySQL servers:

Now comes the fun part. The next steps are to (a) halt processing of SQL statements on the master; (b) note the binary log position (you'll need this information later); and (c) take a dump of the SQL data from the master for restoration on the slave.

First, flush all changes and block further SQL write statements by running:

Next, determine the binary log position by running:

You'll get a table that looks something like the following:

Important! Keep your session that you used to run the FLUSH TABLES... statement open! If you close it, the read lock will be automatically released, and you'll need to start over.

Now make a dump of the master:

Unlock the tables on the master by running:

If your master database has a username and password assigned (meaning, the root user has a password) you'll need to use the -p option as per usual with the above command.

Copy the dump.sql file to your slave server, and restore it by running:

At this point, we now have a second MySQL server that exactly matches the first MySQL server as of binary log position 129 (from the above example.) The first server has since moved on, but changes subsequent to that binary log position are being captured. We now must tell the secondary server to become a slave, and to "replay" the binary log from that position onward:

If the master server is under considerable load, the binary log file may be different than mysql-bin.001; be sure to set it to match whatever filename you got from the SHOW MASTER STATUS command. And be sure to get the MASTER_LOG_POS value exactly right; if you don't, your slave will be corrupt.

Start the slave I/O and SQL threads so that it begins to read the master's binary log and apply the changes:

To make sure that everything is working as expected, run this command on the slave:

First, it should indicate that both the I/O and SQL threads are running:

Second, the binary log position should be incrementing (assuming your master is receiving any write traffic):

Third and most important, the slave should be at most only a few seconds behind the master:

If that number starts climbing, then something is seriously wrong.

Congratulations, you now have MySQL replication up and running! You can now do all kinds of nifty things without downtime. For example, you can now take clean, integral backups of the database without needing to shutdown the master. On the slave, run the following commands:

By selectively stopping the SQL thread you can take a snapshot of the tables without worrying about data changing mid-backup. The I/O thread will continue to read changes from the master during the backup, and upon restarting the SQL thread, those changes will be applied en mass.

Good luck!