MySQL Replication

This morning I setup MySQL replication for two of our user / spam databases that we use internally. This is about the 10th time I have done this, so I don't even have to look up the instructions anymore, but I have to admit that the MySQL team has made the replication process ever so easy. We setup our databases in a master-master configuration with multiple slaves. Slaves can span geographic locations, for instance, from Dulles, Va to Blacksburg, Va, some 300+ miles apart. It's a very nice setup and I always have so much fun setting it up, I don't know why, but for some reason that type of work is fun for me, especially when it works the very first time I try it (and the last two times it has worked right off the bat). Thinking back to the first time I setup MySQL replication it took a few tries to get it right, and so if you are having trouble getting it to work, here are a few suggestions:

1. Read High Performance MySQL by Jeremy Zawodny
2. Turn on logging (vi /etc/my.cnf, log=/var/lib/mysql/log.txt)
3. Make sure every server has a unique id (server-id = 1)
4. I had a pound symbol (#) as part of the password, for some reason this didn't work.
5. Make sure you can telnet to the mysql port on each machine.
6. Make sure the username/passwords are correct.
7. The "show slave status" and "show master status" commands are very helpful.
8. Only replicate the databases you need, make sure there is not a lot of unnecessary traffic.
9. Testing is easy to do, so do it.
10. The /etc/my.cnf file is not used after the first reload of mysql. You need to run the 'change master to' command from that point forward.

Jeremy's book was very helpful, so I highly recommend it for the first-timer.

The best tip I can give is to setup a slave server that does nothing but replicate from the master. This is important because some day you will want to setup another slave without bringing down a live master. This is nearly impossible if you are using all of your machines for queries. To setup a new slave without any downtime, do the following:

On the slave that is doing nothing but replication:

1. Run 'stop slave' from mysql prompt
2. Run 'show slave status\G' from the mysql prompt, record the following two lines:
Master_Log_File: rss-master1-bin.000082
Read_Master_Log_Pos: 993448778
3. Run a "mysqldump" to get all of the data to a text file.

On the brand new slave, do the following:
1. Setup the databases, users, and import the exported data.
2. Run the following command from mysql

CHANGE MASTER TO
MASTER_HOST='master-server',
MASTER_USER='repl',
MASTER_PASSWORD='xxxxxxxx',
MASTER_LOG_FILE='master-server-bin.000082',
MASTER_LOG_POS=934159359;

3. Run 'start slave' from mysql (on both slaves).
4. If you have logging turned on, you will see all of the queries coming through since you stopped the slave process on the first slave machine.

That's it, it should work fine, but testing is encouraged. :)