Setting up RAID-Z for use in a MySQL Master-Master Replicator Pattern

Notes: I wrote this documentation around 2013 as internal documentation at Weever Apps for setting up RAID-Z as a method of making very quick backups of MySQL. Since we no longer use this setup and the documentation will never be used again, I felt it might be a good idea to post it out there for those who might find some use in it. Using RAID-Z for MySQL was amazing, and never failed us, and allowed hourly snapshot-based backups with no downtime. We've since moved to AWS RDS instances though.

This will be part of multiple posts that combined will allow one to build a RAID-Z setup for MySQL. Or, at least it used to be able to! While this is geared towards Azure, I'm sure it would work anywhere.

As I add more parts over the next few days I'll add links here.

Article series

1. Installing ZFS on CentOS
2. Creating a RAID-Z Drive for MySQL with ZFS on CentOS
3. Setting up RAID-Z for use in a MySQL Master-Master Replicator Pattern
4. Creating a ZFS Image of a MySQL Data Directory

Additional note

This documentation is a few years old and it uses the problematic "master/slave" terminology. Better terms have come up recently (my favourite is "master/minion"), but as this is legacy documentation I'm not sure if one can just drop in different terms without causing technical issues in the setup. Please leave a comment if you can confirm dropping the better terms in will work correctly!

Instructions

For MySQL to work on RAID-Z, we must be mounted on /var/lib/mysql. Also, this mount point needs to be owned by mysql user so we need to do the following:

chown mysql.mysql /var/lib/mysql

Then we'll want to create filesystem containers with this, which act like a directory but can be independently tracked by ZFS.

zfs create sqlstorage/data
zfs create sqlstorage/log

Two more chowns to do now..

chown mysql.mysql /var/lib/mysql/data
chown mysql.mysql /var/lib/mysql/log

You must then edit /etc/my.cnf to the following (change server-id depending on whether this is server 1 or 2 though, and auto_increment_offset):

[mysqld]
max_connections=1000
datadir=/var/lib/mysql/data
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_allowed_packet=1024M
log_bin = /var/lib/mysql/log/mysql-bin.log
server-id = 1
# this will prevent master-master auto_increment collisions, set offset to server-id
auto_increment_increment = 10
auto_increment_offset = 1
[mysqld_safe]
log-error=/var/lib/mysql/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Set the server-id to the correct ID.

You will then start up mysql for the first time, using

service mysqld start

Next we'll make sure this boots up every time the machine reboots:

/sbin/chkconfig mysqld --list
sudo /sbin/chkconfig mysqld on

Then follow the instructions provided upon this first boot-up to assign a root password, typically:

/usr/bin/mysqladmin -u root password 'new-password'

Then we'll be logging into mysql's terminal.

mysql -u root -p 

Enter the password.

Then we'll create our replicator user, using this mysql command:

create user 'replicator'@'%' identified by 'password'; 

Generate and replace 'password' with a password.

Now give it permission to replicate:

grant replication slave on *.* to 'replicator'@'%';

Now run this and make note of what information it displays:

show master status; 

You will need the 'position' and 'file name' provided, and use this on Server #2.

At this point, log into server #2, and repeat everything in this document up until this point.

On Server 2 only...

Time to set up the replicator.

slave stop; 
CHANGE MASTER TO MASTER_HOST = '1.1.1.1', MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000003', MASTER_LOG_POS = 106; 
slave start; 

Where 1.1.1.1 is the internal IP address of server #1. Replace it with the correct Azure Internal IP. Also use the password and user created for server #1 'replicator', be sure not to mix it up with the replicator password for server #2. The master log file name and log position you should have noted before, replace it here with those values. Position is likely to be 106 though the filename can vary.

Now once again we'll do:

show master status;

And now on to server 1...

On Server 1 only...

Go back into mysql as root user. Enter this:

slave stop; 
CHANGE MASTER TO MASTER_HOST = '2.2.2.2', MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000004', MASTER_LOG_POS = 107; 
slave start; 

Now just create the database you put in database_name_here in the my.cnf and replication should be working.

Is something badly out of date? Did this work for you? Please post a comment to help others who might use this for guidance.