Beginner's Guide to MySQL Replication

Introduction

Although the "Idiot's Guide to MySQL Replication" below will probably cause a few security nightmares, I hope it's a start in getting proper mirroring off the ground. I'm assuming that readers are familiar with the basic concepts of MySQL Replication.

The description below shows a "chained" replication setup, where changes on Server A are mirrored on Slave B. Slave B in turn has additional/different databases, and combined with those from Server A, this machine serves as Master B where changes to its own databases, as well as those received from Master A, ultimately filter through to Slave C, which will function as a Backup Server. Make sense?

Getting Started

Ports

For starters, we need to open up port 3306 to allow Slaves to connect to a Master. Scary thought for the Adam Richardson's of this world, but there you go.

User Account

We set up a special account for the Slave on the Master server:

  mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'slaveDomain'
    IDENTIFIED BY 'slavePassword';

Note: the MySQL manual states that this statement doesn't work in version prior to 4.0.2 and you should use FILE instead, but I found it works fine in 4.0.17.

Tip: Don't use "replication" as a username - I found that you can't connect when you use that, unless you give that user ALL PRIVILEGES - possibly because it's a mysql reserved word?

Binary logging, server-id and the my.cnf file

Each MySQL server involved in the replication chain needs a certain configuration. Masters need to have Binary Logging turned on, and both Masters and Slaves need to have a server-id. The easiest way to set this up is by editing or creating the my.cnf file.

On startup, MySQL will attempt to read all sorts of settings from /etc/my.cnf - if that file doesn't exist on the servers, you'll need to create a new one. On the Master - including Slaves in the replication chain who function as Masters to other Slaves - this my.cnf file should contain the following:

  [mysqld]
    log-bin
    log-slave-updates
    server-id=123123123123

the "log-bin" option turns on Binary Logging when the MySQL Server starts up. "log-slaves-updates" is needed if the Master is somewhere in the middle of the replication chain and you want changes to server(s) from the beginning of the chain to filter through to subsequent slaves. "server-id" is an integer to uniquely identify the server in the replication chain - it could simply be a number 1, 2, 3 etc. but I prefer to use the machine's IP address without the dots - 'cause I'm gonna set up a worlwide replication chain ;-)

The final Slave in the chain will only need the "server-id" option.

For the security-paranoid among us, it is good to know that the my.cnf file can also be used to set startup option to use replication over ssl, for version over 4.1.1.

When you have set up the my.cnf file, restart the mysql daemon and take a look in the data directory of the Master - typically in /usr/local/mysql/data. If all went according to plan, you should now have a Binary Log, which takes the name of the machine followed by "-bin.001" - assuming binary logging has been enabled for the first time, since this 001 value will change, I presume depending on log rotation settings :-|

  /usr/local/mysql/data ->
    myserver-bin.001

Taking a Snapshot of the Master

Next, time to take a snapshot of the Master. This involves copying the Master's data file as well as recording the status of the Master's binary log. I'm assuming we're dealing with MyISAM tables only - the routine for InnoDB tables is described in the MySQL manual but didn't apply to my setup :-)

I have so far used two terminal windows to do this quickly and keep downtime to a minimum.

First of all, you need to lock the tables on the Master and keep them locked until you've finished copying the data and recorded the binlog status. Start by issuing these commands in mysql:

mysql> FLUSH TABLES WITH READ LOCK;
  mysql> SHOW MASTER STATUS;

  +------------------+----------+--------------+------------------+
  | File             | Position | Binlog_do_db | Binlog_ignore_db |
  +------------------+----------+--------------+------------------+
  | myserver-bin.001 | 1046     |              |                  |
  +------------------+----------+--------------+------------------+

 

Record "File" and "Position" from this result ("myserver-bin.001" and "1046" in this example).

In a new terminal window (since you need to keep the Read Lock in place), proceed by creating an archive of the Master's data directory. Although the MySQL Manual recommends that you simply tar the archive to the /tmp directory, I prefer to first copy the directory so that I can then weed out the bits I don't need. I'm sure there is a way to tar leaving out certain files, but I simply don't know how :-(

  mastershell> sudo cp -R -p /usr/local/mysql/data ~/Desktop

Time to release the Read Lock, so back to my MySQL client in the first Terminal:

  mysql> UNLOCK TABLES;

Moving Data to the Slave

Switch to the second Terminal, and change location into the newly copied data directory on your desktop. Keeping in mind that this is the data you will be putting on the Slave, weed out any bits you don't need. For starters, I would remove the data directory of the "mysql" database, since that is more than likely different on Master and Slave - and you wouldn't want to replicate these. Remove any other database directories that you do not want to replicate, as well as log files (including the - copy of - the binary log, which will be useless on the Slave.

Once you've finished weeding, create an archive of the data directory in the Master's tmp directory, then copy the archive file to the /tmp directory on the slave server host:

  mastershell> cd ~/Desktop/data
    mastershell> tar -cvf /tmp/mysql-snapshot.tar .
    mastershell> scp /tmp/mysql-snapshot.tar user@myslave.domain.com:/tmp/

Log onto the Slave machine, and first of all stop the MySQL server. Then unpack the archive into the mysql data directory:

slaveshell> cd /usr/local/mysql/data/
slaveshell> tar -xvf /tmp/mysql-snapshot.tar

Of course, you have already set up the my.cnf file on the Slave as described above. So, with the new data from the Master added to its own set of data, you can now restart mysql.

Starting the Slave

To get the Slave up and running, you first need to issue a statement to configure mysql:

slavemysql> CHANGE MASTER TO
  -> MASTER_HOST='masterHostName',
  -> MASTER_USER='replicationUserName',
  -> MASTER_PASSWORD='replicationPassword',
  -> MASTER_LOG_FILE='recordedLogFileName',
  -> MASTER_LOG_POS=recordedLogPosition;

  slavemysql> START SLAVE;

 

The first statement will create a file "master.info" in the mysql data directory, where the relevant data is stored. Remember that this is one of the files you do not want to copy to a subsequent Slave if your setting this Slave machine up as a Master in a chain.

Finding Out What's Going On

On both the Master and the Slave, you can monitor the status of your replication setup by issuing the mysql SHOW PROCESSLIST command - the manual has plenty of info on this, so I won't go into it here.

Summary

Obviously, replication has huge benefits for failsafe mechanisms. It also means that you can run backup from the Slave without interfering with the Master.

In addition, I see the benefit of sharing useful databases with other developers. For example, a New Zealand developer who shall remain anonymous uses a database to look up country names based on IP address. Using replication, other developers could tap into his database without having to manually update their own version - just a thought.

I have found that database updates from Master to Slave happen almost instantly - the master-connect-retry startup option, the default of which can be identified as "60" in the master.info file (in the mysql data directory) refers to retries in case a connection fails - it does not mean the interval at which updates are performed.

Author: Pier Kuipers
Created: 7 Feb 2008
Last Modified: 2 Mar 2011