2009-04-09

MySQL Master-Master-Slave(s) Database Replication

I have watched the technology sessions and slideshows explaining how many large websites, such as Google or Facebook, setup their MySQL Databases. But none have ever gone into the details of how to configure MySQL in such a way. So I had to tackle that myself. I am documenting the process here so its hopefully useful to others (and myself when I forget in 6 months).

Overview of the resources involved
Data Integrity Concerns
The very first thing you need to consider is that MySQL technically does not support multiple masters. This is significant because it means that there is no conflict resolution if you write data to both masters. If you happen to change the same record on both masters they wipe each other's changes out. MySQL replication works simply by replaying queries from the master on the slave server.

The safest solution is to simply never write data to both masters. Although, there are strategies that allow you to safely write to multiple masters. For example, limit writes to specific tables on each master server. In my case I use the secondary master as a replication source and hot-spare in case I need to take down the primary master for maintenance. Using the secondary master as a replication source takes the burden of slave replication off your primary database server.

Master Configuration
We have two masters. They are identified as 1 and 2. (Slaves are 11, 12, etc.)

The very first thing you should do is to run /usr/bin/mysql_secure_installation on each of your masters to set MySQL's root password and other security settings.

Master 1
Add the following to the [mysqld] section of my.cnf making the appropriate changes and restart mysqld:
# primary master server id
server-id=1
auto_increment_offset=1
# total number of master servers
auto_increment_increment=2
# local slave replication options
log-bin=master1-bin
log-slave-updates
# remote master replication options
master-host=master2.yourdomain.com
master-port=3306
master-user=replica
master-password=replic8
master-connect-retry=10

Master 2
Add the following to the [mysqld] section of my.cnf making the appropriate changes and restart mysqld:
# secondary master server id
server-id=2
auto_increment_offset=2
# total number of master servers
auto_increment_increment=2
# local slave replication options
log-bin=master2-bin
log-slave-updates
# remote master replication options
master-host=master1.yourdomain.com
master-port=3306
master-user=replica
master-password=replic8
master-connect-retry=10

Create Replication Accounts
On both master servers run the following query as root:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%' IDENTIFIED BY 'replic8';

Dump/Load Existing Data and start Replication

On Master 1
Prevent writing to the database.
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: master1-bin.000001
Position: 254
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.20 sec)

Make note of the position and file name. You must have these later. From a terminal you now need to dump the database for loading onto the slaves.
bash> mysqldump -A -u root -p > master1.sql

On Master 2
Load the data from Master 1 onto Master 2.
bash> mysql -h master2.yourdomain.com -u root -p < master1.sql
Enable Master 2 as a slave to Master 1 (refer to master 1's show master status above for MASTER_LOG* values)
mysql> CHANGE MASTER TO
MASTER_HOST='master1.yourdomain.com',
MASTER_USER='replica',
MASTER_PASSWORD='replic8',
MASTER_LOG_FILE='master1-bin.000001',
MASTER_LOG_POS=254;
mysql> START SLAVE;

Get the Log Info for Master 2
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: master2-bin.000005
Position: 12314580
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

On Master 1
Enable Master 1 as a slave to Master 2 (refer to master 2's show master status above for MASTER_LOG* values)
mysql> CHANGE MASTER TO
MASTER_HOST='master2.yourdomain.com',
MASTER_USER='replica',
MASTER_PASSWORD='replic8',
MASTER_LOG_FILE='master2-bin.000005',
MASTER_LOG_POS=12314580;
mysql> START SLAVE;

Master-Master Setup is complete
If all you wanted was a master-master setup then you are finished. Any query executed on master 1 will also be executed on master 2.

Adding Slaves to your Master-Master

On Each Slave Follow this Procedure
Add the following to the [mysqld] section of my.cnf changing the server-id and master settings as appropriate and restart mysqld:
# this slave's server-id
server-id=11
# replicate from master 2
master-host=master2.yourdomain.com
master-port=3306
master-user=replica
master-password=replic8
master-connect-retry=10

Load the data dump from Master 1, if needed:
bash> mysql -h slave11.yourdomain.com -u root -p < master1.sql
Start Replication from Master 2 to each slave (refer to master 2's show master status above for MASTER_LOG* values)
mysql> CHANGE MASTER TO
MASTER_HOST='master2.yourdomain.com',
MASTER_USER='replica',
MASTER_PASSWORD='replic8',
MASTER_LOG_FILE='master2-bin.000005',
MASTER_LOG_POS=12314580;
mysql> START SLAVE;

Master-Master-Slave Setup is Complete
Congratulations, you are done with the server configuration! You may verify this by inserting or updating records on Master 1 and then verifying that the change is made on Master 1 and all of your slaves.

Client Access Considerations
To take full advantage of this configuration your client applications will need to be written in such a way that they perform all critical writes (that is, writing data that you want to keep) on Master 1. Also consider that some or all reads immediately following a write may need to come from the master. This is because it can take a few seconds for the slaves to synchronize with Master 1. This is what we call a "dirty" read. Clients performing only reads or creating temporary tables for use with those reads can access your pool of slaves and never touch the master.

Examples
  • Generate query-intense reports on dedicated slaves.
  • A portal page that doesn't need up-to-the-second data.
  • Statistics calculations can be performed on the slaves.
Enforcing Safe Practices
You would be well advised to GRANT only SELECT privileges to clients accessing your tables via a slave. This way a rogue client application cannot manipulate upstream data on the slave. Any changes made on the slave would not be known to any other slave nor the masters. This could be a devastatingly bad situation to find yourself in. Please think through these issues before you implement your clients.
You almost certainly will want to allow clients to create temporary tables, though.


Advanced Stuff
MySQL doesn't require slave tables to have the same schema as masters, either. This allows you to do interesting things like create your master database tables as InnoDB tables with full referential integrity and foreign keys while creating your slave tables as high performance MyISAM tables. You can create different indexing strategies on the slaves to fine-tune specific slaves to specific tasks such as report generation.

Labels: , ,

- posted by David L Norris @ 11:04 [Permanent link]
2009-03-20

Red Hat Enterprise Linux Cluster Suite 5.2 DHCP Problems

Well, I learned another valuable clustering lesson today. Manually assign all of your IP addresses and do not rely on DHCP. I'd still suggest you have the IPs statically assigned on the DHCP server but don't rely on it at all.

Yesterday I rebooted all of the machines in the cluster simultaneously after making some extensive modifications to the iSCSI configuration on them all. (I'm using cluster-ssh to manage them all simultaneously.) All of the machines went down at exactly the same time and came up at exactly the same time. (They are identical hardware with identical software, afterall.)

Today, for whatever reason, when all of the leases expired at roughly the same time they didn't renew quickly enough. I walked out of a meeting this afternoon to all of the cluster nodes reporting "Quorum dissolved" and all services on the cluster were failed.

Labels: , , ,

- posted by David L Norris @ 16:06 [Permanent link]
2009-03-11

Red Hat Enterprise Linux Cluster Suite 5.2 Relocate Problems

Gist of it all
So, I've been fighting mind bending failover problems on our new compute cluster at work for the past week. I can summarize the solution to service failover problems in one sentence:
Don't use Red Hat's cluster service scripts, evar.

How it actually works
Apache's script (apache.sh) in RHEL 5.2 sends a TERM signal to Apache, waits N seconds (0 by default for Luci/Ricci, 20 for sys-config-cluster) and then proclaims that Apache has failed to shutdown if its still running. This type of failure is fatal for the cluster service.

How it should work
The standard apache init scripts (/etc/rc.d/init.d/httpd) do the right thing, more or less. The parent process is sent the TERM signal, waits 10 seconds and if its still running sends the KILL signal. That may seem harsh but this is a web server for crying out loud. There's no real chance of data corruption if you KILL the web server. If something goes horribly wrong and Apache needs to move to another machine I'm quite happy with Billy Bob having to retype a form or resume his download. That affects only Billy Bob. Bringing down the service for the entire world, in comparison, seems like a very bad solution.

Rant
Red Hat has spent years developing this clustering software. Its fairly good and seems reasonably well written. Its a shame they only spent 5 minutes writing the service scripts. The stock init scripts are infinitely better written but won't work in a clustered environment.

Solution
So, my advice, spend 20 minutes and write your own userscript for each service. If a service is important enough to be on a cluster you can afford to spend the time to write your own script to start and stop it. If you don't know how to write shell scripts and you're managing a cluster then do everyone a favor and seek employment at McDonalds.

Howto
User scripts for the cluster are simply LSB compliant init scripts. I advise you to not waste much time trying trying to hack the /etc/rc.d/init.d scripts to work within the cluster. You can do so but you'll end up stripping out most of it. You may wish to source the /etc/rc.d/init.d/functions file, though. But you will need to pass the pid filename to every single function you call. (Read the functions script to see which options to pass to each function. daemon, killproc, and status functions are most useful for starting, stopping and checking status respectively.)

Labels: , , ,

- posted by David L Norris @ 16:58 [Permanent link]