MySQL Replication: Create a Slave with No Downtime21st August 2014
Highly available systems and ensuring proper backups is a must. You do not have the luxury of downtime nor do you have the luxury to take down production for a snapshot.
Best of all, if you are in the situtation where you have a single database in MySQL and do not have replication, failover, master-master there is a way forward that does not cause impact to your user base.
I recently encountered a system where we had a Windows server running MySQL. While we had backup we did not have replication. This can cause serious issues such as unexpected downtime, long recovery times and ultimately data loss. While we were lucky to have built a system that can withstand failure, being highly available is a necessity in the current day.
This article is about my experience in moving from Windows to Linux for MySQL and setting up a replicant with no downtime.
There is a small amount of tools and an even worse amount of documentation for building out your first slave. Most documentation out there will recommend mysqldump or will assume that you are already prepared to setup replication. We make no assumptions here.
One thing to mention is the tools that are available to you. If like me you are running a Windows server or you do not have the luxury of having your MySQL instance on Linux then you have a single tool vs. multiple.
MySQL Backup (Enterprise) - This tool is built by Oracle and you can download a trial to generate out your backups. If you are using Windows this is your only choice. If you are to continue using Windows then you will need to purchase a license. However, as I stated you can utilize a trial.
Percona XtraBackup - This tool is free and you can download and utilize at your will. It also has additional documentation and has a community to assist you.
We will be utilizing the MySQL Backup or Hot Backup so to speak for this article. You can easily utilize either or and I will attempt to state the differences.
Preparing the Master
Generally, when people install MySQL they accept the defaults. The defaults, however, will not assist you in creating a master / slave setup. There is a small amount of configuration necessary to state a master database as a master.
In the /etc/mysql/my.cnf OR C:\Program Files\MySQL\MySQL Server *\my.ini file you will need to make particular changes. These changes involve telling how the master should operate. Add the following to the configuration under the [mysqld] area:
server-id=1 binlog-format=mixed log-bin=mysql-bin
What this means:
server-id - the ID of the server. With replication these cannot be duplicated. binlog-format - there is statement, row and mixed. Mixed is the safest as statements cannot always be run while row is heavier. It's best to simply do mixed. log-bin - enables binary logging.
Now restart the server; this may incur a small bit of downtime but we are talking less than a minute. In my case it took approximately 15 seconds for a 175GB database.
Create a Replication User
In order for replication to work, a replicant needs to be able to login to the server and have permissions to operate as a slave.
CREATE USER replicant@'10.1.0.%'; GRANT REPLICATION SLAVE ON *.* TO replicant@'10.1.0.%' IDENTIFIED BY 'somePassword';
In the case above I am binding it to my private network and giving it a password.
Creating a Backup
In order to create your slave, you cannot just simply tell it to create a slave from the master. Even if that did exist, it would be far more damaging than beneficial for load, locking and more. This is because you would not have a binary log position as of yet which is how MySQL knows what is left to backup.
Utilizing MySQL Backup
To backup the database there are several things to think about. First is the available memory, CPU and overall threads that you could utilize. In my situtation we configured MySQL to run with peak memory and CPU. Executing the following allowed the database to still perform extremely well at the cost of a longer backup:
mysqlbackup --user=mysqlbackup --backup-dir=/var/backups/mysql --read-threads=2 --write-threads=2 --process-threads=2 --limit-memory=64 --no-locking --sleep=500 backup
If you are using XtraBackup your command would look similar to the following (linux only):
innobackupex --user=root --no-lock --no-timestamp --throttle=16 --use-memory=64MB /var/backups/mysql
This will backup InnoDB without locking, if you are running other storage engines you will lock and could be an issue. So InnoDB is a must here. The flags that I am passing is limiting the amount of memory, threads and allowing our production database to continue. Remember this is only for InnoDB tables, MyISAM will still lock!
Once the backup is complete, we now need to replay the logs between when the backup started and finished. The applicable commands are:
You will need to ensure that you write down the values when this completes for the master log position and file. It will show something like the following:
mysqlbackup: INFO: Last MySQL binlog file position 0 281658185, file name .\mysql-bin.000006
The second number of the position and the file name are the most important and we will use them later.
Or for XtraBackup:
innobackupex --apply-log --use-memory=64MB /var/backups/mysql
XtraBackup exports a file that you can see the position and file in.
Once this has completed, you have a copy ready for your first slave.
Creating a Slave
First off install the MySQL Server. Then we can start configuration. Remember, your slave should be alike to production so that you can utilize it within a case of emergency. I do assume you know how to install MySQL server.
In the my.cnf or my.ini file, you will want to ensure you have the following configuration:
server-id=2 binlog-format=mixed log-bin=mysql-bin read-only=true relay-log=mysql-relay-bin
Remove MySQL Data Files
rm -rf /var/lib/mysql/*
Transfer over the files via rsync or some other utility to your data files directory.
rsync email@example.com:/var/backups/mysql/* /var/lib/mysql/
Now that we have a clone of our master and we have all of the data imported, we can now setup replication.
Login to the MySQL Slave (it will have your same users as the master)
Register with the master
CHANGE MASTER TO MASTER_HOST='10.0.1.200', MASTER_USER='replicant', MASTER_PASSWORD='somePassword', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=281658185; START SLAVE;
Watch it get up to date and check if you have any errors
Checking Slave Status
SHOW SLAVE STATUS;
Force Solving Errors - Warning: doing this will cause you to miss queries or otherwise. So be careful when you do this. This will skip the query and start executing the next one. Repeat as necessary (but really - don't).
STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;
So now you have read this article you have no excuse not to have a slave. In addition, you can now backup your database from your slave. While this process can be time consuming on a large database it is easily accomplished and you can start leveraging the vast benefits of a slave.