UPDATE

I've got a script that pretty much does this for you, assuming you want a replica of our database. Right now the latest one is on ephesus; to use it, grab the /home/adler/database directory and execute the script database_setup. It assumes you already have a mysql server installed, but that you don't have anything of interest on it; if people would rather the script install mysql as well, I can do that, although it's probably a little less secure.

The Old Article

The point of this article is to describe the steps I took in synchronizing the databases, currently set up with Opteron2 (132.192.47.22) as the master and Olympia (132.192.47.28) as the slave. The process is fairly well documented in the MySQL online manual, but I'll duplicate a lot of that here for the convenience of seeing it all on one page. (Both machines were running 64-bit Centos 5).

Settings

First you'll have to modify the /etc/my.cnf file on both machines. Each machine needs a unique "server-id". Set this with the line "server-id = x" in the [mysqld] section of each machine, where x is typically a small natural number. The master will also need binary logging enabled; do so with the line "log-bin = x", where x is whatever prefix you want for the binary log files, i.e. "log-bin = mysql-bin".

The slave will need a bit more configuration than the master. First open a mysql terminal on the master and set up a user account to do the replication. In my case, I used something like

grant all privileges on *.* to 'root'@'132.192.47.22' identified by 'password';

In the [mysqld] section of its my.cnf file, add the following lines:

• master_host = 'host ip'
• master_user = 'user'
• master_password = 'password'

These aren't entirely necessary, since you can accomplish the same thing with judicious use of the "change master to ..." statement, but it'll save you time. In particular, if you're setting up a replica of db_webqtl, we have a special account 'replica' designed for that purpose. It's probably not a good idea to put passwords on this site, so I won't. Contact us.

There are a few more lines you'll need to add if you want the slave to replicate specific databases or tables.

• replicate-do-db = some database - replicate only this database, in my case db_webqtl (if you use this line multiple times, each specified database will be replicated)
• replicate-ignore-db = ... - do not replicate the specified database
• replicated-ignore-table = some database.a table in the database - do not replicated the specified table (I used this for the AccessLog? and MachineAccessLog? tables)

For good measure, restart MySQL on both machines (on CentOS, "service mysqld restart").

Data Transfer

Ok, now the slave is set up. The next step is retrieving all the data from the master and putting it on the slave. There are several ways to do this. In the olden days, "load data from master;" on the slave would accomplish this, but it's deprecated now and doesn't really work. The alternatives are using the mysqldump script to create a dump file on the master, which can be passed to the slave and loaded, or copying the database files directly. From my experience, the former takes a lot longer than the latter, so I'll be describing the copy approach.

• Step 1 - Lock tables on the master. This is done with "flush tables with read lock;".
• Step 2 - Write down the master's position in its binary logs. Run "show master status;" and record "File" and "Position".
• Step 3 - Turn off MySQL on the slave. (may not be necessary, but it can't hurt; "service mysqld stop" should work)
• Step 4 - Copy the files in the directory pertaining to your database(s) from the master to the slave. In my case, I was interested only in db_webqtl, so I executed something like

  scp -r /var/lib/mysql/db_webqtl root@olympia:/var/lib/mysql

  This can take a lot of time, depending on how big your database is. Make sure the database folders end up in the mysql directory on the slave.

Final Steps

The file transfer business is the longest part; after that there's not much left. Start up MySQL on the slave and open a terminal. Execute a "change master to ..." to specify where to start replicating, i.e.:

change master to master_log_file = 'File', master_log_pos = Position;

Now do "start slave;" (or "slave start;", a syntax which is technically deprecated but still works fine). If all is well, you'll get no errors. Do "show slave status;" to see a few things. There should be a line like "waiting for master to send event". If that's there, it's probably working. Go back to the master and do "unlock tables;".

If things screw up (and trust me, they often do), you can use "reset slave;" and do another "change master to ..." to get it to start replicating from an earlier binary log (likely the one you first used).

-- KevAdler - 26 Feb 2008