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.
- Installing ZFS on CentOS
- Creating a RAID-Z Drive for MySQL with ZFS on CentOS
- Setting up RAID-Z for use in a MySQL Master-Master Replicator Pattern
- Creating a ZFS Image of a MySQL Data Directory
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!
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
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.
Time to set up the replicator.
slave stop; CHANGE MASTER TO MASTER_HOST = '18.104.22.168', MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000003', MASTER_LOG_POS = 106; slave start;
22.214.171.124 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...
Go back into mysql as root user. Enter this:
slave stop; CHANGE MASTER TO MASTER_HOST = '126.96.36.199', 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.