Greetings!

Yesterday I got the opportunity to meet Eric Sandall and have a brief conversation as we walked to and from the LUG room to reconnect the server(s) there (which I had dropped off the network a couple days ago. Heh. Heh.) Anyway, we got to geeking pretty quickly, and among other things, the subject of high-availability clustering and MySQL replication under Linux came up. I mentioned that I was using keepalived in a two-node cluster that among other things was running MySQL in a dual-master set-up. He said he didn't know how to set up MySQL this way, so I offered to make an addition to the LUG site with the procedure I followed to set it up.

I keep notes on everything I do with regard to systems administration in my job here as the Information Systems Coordinator for the College of Engineering and Architecture here at WSU. (And as a side note to all you sysadmin types-- if you aren't writing down everything you do when setting up or configuring a system, why aren't you? Having worked many sysadmin jobs, I can tell you the thing that most drives me nuts about a new environment is discovering a bunch of machines nobody knows what to do with because my predecessor couldn't be bothered to write anything down. If you want to keep your job for a long time and add more value to the company you're working for, write everything down. Yes, it takes a little more time when doing installs or maintenance, but you'll save yourself days of work when you are either forced to troubleshoot a nasty problem with your network, or required to do a 'fresh install' of some component of your network. I currently use a Subversion repository to document my systems, and am slowly moving a lot of the "higher level documentation" over to a wiki.)

Anyway, rant aside, here are the notes I took last year when setting up the MySQL dual-master relationship I use right now. Of course, you'll want to change your usernames / hostnames / passwords / etc. when setting this up for your own environment.

My environment is two machines running Fedora Core 5, one of which is a Pentium IV box, the other is an Athlon-64.

# $Id: mysql-replication 335 2005-10-13 19:46:20Z sbalukoff $
# To set up bi-directional mysql replication:

# For the purposes of this document, we have two servers which will in the
# end be filling a co-master type relationship.  However, since when you set up
# this replication there will probably be one machine with all the data on it
# that needs to be brought into sync with the other machine, the machine
# with all the data will be considered the 'master', and the one being brought
# into the relationship will be considered the 'slave'.  Again, don't let
# the names fool you:  We're only using 'master' and 'slave' here to
# differentiate the machines.  In the end they will be in a true co-master
# relationship.


# Before you begin:
#
# Make sure that /etc/my.cnf is set to have binary and relay logging turned on
# on both the slave and the master.  (Verify the log files actually exist if
# they weren't set up with this, you will need to restart the server process once
# the config file has been changed.)  Pertinent config lines should look like:
#
# # This stuff is needed for replication purposes
#   server-id=2
#   log-bin=/var/lib/mysql/master-bin.log
#   log-bin-index = /var/lib/mysql/master-log-bin.index
#   log-error = /var/lib/mysql/master-error.log
#
#   relay-log = /var/lib/mysql/slave-relay.log
#   relay-log-info-file = /var/lib/mysql/slave-relay-log.info
#   relay-log-index = /var/lib/mysql/slave-relay-log.index
#
# In the above, the master and slave must have different server-id's.

# Make sure these machines' firewalls are set up to allow them talk to each
# other on tcp port 3306, or wherever your mysql daemon is listening.

# Set up the replication user on the master:
#
sudo mysql -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%.cea.wsu.edu' IDENTIFIED BY 'replication_password';
mysql> GRANT REPLICATION CLIENT ON *.* TO 'replicator'@'%.cea.wsu.edu';
mysql> GRANT SUPER ON *.* TO 'replicator'@'%.cea.wsu.edu';
mysql> GRANT RELOAD ON *.* TO 'replicator'@'%.cea.wsu.edu';
mysql> GRANT SELECT ON *.* TO 'replicator'@'%.cea.wsu.edu';

# Get a clean snapshot of the master's data:
mysql> FLUSH TABLES WITH READ LOCK;
# On the shell:
sudo tar cvf /tmp/mysql.bak /var/lib/mysql
# on my mysql prompt:
mysql> SHOW MASTER STATUS;
# Take note of what is said!  You'll need to enter the log file name and position later on.
mysql> UNLOCK TABLES;


# Copy the snapshot to the slave, and slurp it into the database:
scp /tmp/mysql.bak user@slave-server:/tmp
# on the slave
sudo /etc/init.d/mysqld stop
cd /
sudo tar xvf /tmp/mysql.bak
sudo /etc/init.d/mysqld start


# Start replication from master to slave:
# on the slave's mysql command line as root:
mysql> CHANGE MASTER TO MASTER_HOST='master_host_name',
->     MASTER_USER='replicator',
->     MASTER_PASSWORD='replication_password',
->     MASTER_LOG_FILE='recorded_log_file_name',
->     MASTER_LOG_POS=recorded_log_position;

mysql> START SLAVE;


# Test the replication by creating a dummy table on the master...  it should be
# created on the slave

# On the master:
mysql> USE test;
mysql> CREATE TABLE foo (id INT);

# On the slave;
mysql> USE test;
mysql> SHOW TABLES;
# you should see the 'foo' table, eh.


# Start replication from slave to master:
# First, ensure that no updates are being made to the slave, by ensuring
# that no clients are connected to it, eh.  Do this however you see fit.
# Shutting down any local clients and adding firewall rules to prevent anyone but
# the master from connecting should do it.  Updates to the master can continue while
# you do this.

# Get the 'master' status from the slave:
# on the slave's mysql console:
mysql> SHOW MASTER STATUS;
# Make note of what you see!  Again, gonna need that logfile and position in the next step...

# Add the replication configuration to the master.
# On the master's mysql command line as root:
mysql> CHANGE MASTER TO MASTER_HOST='slave_host_name',
->     MASTER_USER='replicator',
->     MASTER_PASSWORD='replication_password',
->     MASTER_LOG_FILE='recorded_log_file_name',
->     MASTER_LOG_POS=recorded_log_position;

# Start replication on the master:
# on the master:
mysql> START SLAVE;

# Test bi-directional data flow by creating something on one server, and
# deleting it on the other, eh.  (Or just delete that 'foo' table we made
# earlier.)

# Slave:
mysql> use test;
mysql> drop table foo;

# Master:
mysql> use test;
mysql> SHOW TABLES;

# Test restarting each server individually, and then together
# to ensure there are no errors that show up in the logs when you do this.

# Be sure the check out the output of the following, on both master and slave:
mysql> SHOW SLAVE STATUS \G
# If you don't see the following in the list on both systems, you've got problems:
#   Slave_IO_Running: Yes
#   Slave_SQL_Running: Yes

# Enjoy the glow!

Other things to note:

• As far as I've been able to tell, this relationship works across architectures: One of my masters is a 32-bit Intel Pentium IV box, the other is an Athlon-64. (I don't know, though, if say, a PPC could dual-master with an Intel chip. LSB/MSB architectural differences might break things.)
• When upgrading your MySQL server version, be sure to read any release notes and be very careful especially when doing major version upgrades. This spring I managed to kill replication when I upgraded my machines from MySQL 4.x to MySQL 5.x. (This upgrade requires certain changes to the mysql meta-database-- which the replication system kind of choked on.) Always do a backup before you do this kind of thing. ;)
• Be sure to periodically check out the content of the SHOW SLAVE STATUS \G command to ensure that the machines keep in synch. Reboots and long outages of one machine or the other shouldn't screw things up, but it's good to monitor this, just in case.

And... that's it. If y'all found this helpful, please let me know; I'd be happy to share some of my other notes from stuff I've done in my position here at WSU or at my former jobs. Some of the things I could talk about include:

• Systems Administration documentation best practices
• Linux high-availability using keepalived
• L2TP/IPSec and PPTP VPN server set up with authentication against M$ Active Directory using open-source tools.
• Network-to-network VPN using Linux "2.6" IPSec.
• Network trend monitoring using Cacti (including advanced features like aggregate graphs, showing 10+ data sources on a single graph, and custom polls)
• E-mail spam and virus filtering using amavisd-new, spamassassin and clamav (which will, sadly enough, often beat the quality of filtering WSU's getting from the Barracuda systems central IT uses.)
• Configuring DNS, DHCP, Apache, Radius and other Linux "server" services and applications.

Anyway, feedback is appreciated, guys and gals!