MySQL Replication for Backups and more

You might be fortunate enough to allow yourself some downtime, it is dependent on your application and business model. During this window it's possible for you to stop your MySQL daemon or lock your tables to give yourself a consistent backup of your data. Quite often this is a luxury that you cannot afford. If you are tied to a strict uptime that doesn't permit any interruption to your data availability then MySQL Replication could be the answer you need to grab that essential backup file. Once you've enabled Replication to a slave then you have the chance to backup by stopping the replication thread and  mitigate the risk of corruption whilst securing your latest dataset. Using the slave will also negate any overhead a backup like mysqldump would have on your active Master server.

Although in this case we are deploying Replication to take consistent backups of our data, there are many uses for the mechanism such as scaling out you solution, analytics and reporting, data distribution over geographically disperse locations and high availability.

Replication at a high level overview works like so...

For the sake of the guide we will use Server1 and Server2, Master and Slave respectively.

• Changes are made to data on the Master (Server1). These changes are logged in the Binary Log of the Master and are referred to as binary log events. 
•  The Slave (Server2) will then copy the Master's binary log events into it's Relay Log.
•  The Slave will then replay the events that it has copied into the Relay Log to it's own data. The result is an identical dataset.  

There are many guides online to setting up replication but it's essentially an easy feat to complete. On your journey into replication you will encounter all sorts of extra options such as type of replication (statement, row, mixed), exclusion of certain tables or even whole databases.

Setting Up Replication (quick guide)

Before you read on, I assume you have MySQL installed on both Server and the servers are on the same network, communicating. This guide takes you down the basic route of replicating to a single slave.

To ensure that Replication will work you need to ensure a few configuration settings are set. Check your my.cnf for the following attributes:

Master;

[mysqld]
server_id=1
log_bin = {filename} e.g. mysql-bin

Slave;

[mysqld]
server_id=10
log_bin = {filename}
relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 1

The server-id attribute needs to be a unique integer. You may incur problems if you have duplicates on your network so set this to something unused. Best practice is to ensure you set the servier-id in my.cnf on all your servers as the MySQL will default to 1 and think it's a master. I frequently see the use of the last quartet of the server's IP address. Ensure that you're using binary logging on your master too, Replication will not work without it. Make sure that the lines skip-networking and bind-address are either commented out or deleted as these will make it impossible to connect to your network. Once your my.cnf complies with Replication's needs, restart the mysql daemon;

OS command

shell$ mysqladmin -uroot -p shutdown
shell$ service mysql start

The previous OS commands will ensure graceful cycle of your daemon. Run them on your command line.

1. Authentication
At the beginning we need to create a user on the Master so that our slave's thread can access our data. Login to your master MySQL node and then add your replication user

MySQL master command

mysql> GRANT REPLICATION SLAVE ON *.* TO `slave`@`Server2` IDENTIFIED BY 'password';

This command will add a user to your Master that allows the Slave to connect.

**It's best practice that this is account is used for no other purpose then replication.**

2. Load Data
This step is optional. If you've got data in your tables already then this is a sensible step to undertake. Dump your data into your slave using the mysqldump tool;

OS master command

shell$ mysqldump -u{user} -p{password} {--single-transaction|--lock-all-tables}--all-databases --master-data=1 --host=server1 | mysql -u{user}-p{password} --host=server2

This mysqldump command will dump your data from Server1 and the pipe the output into Server2. You can also dump to a file using the *nix redirect;

OS master command

shell$ mysqldump -u{user} -p{password} {--single-transaction|--lock-all-tables} --all-databases --master-data=1 > masterdata_dump.sql

and then copy the output file to your slave and import it.

3. Configure Slave
Start a MySQL session on your slave and enter the following command. This coordinates your replication. It tells the slave how to connect to your master server.

MySQL slave command

mysql> CHANGE MASTER TO
MASTER_HOST='ServerIP/FQDN',
MASTER_USER='ReplClient',
MASTER_PASSWORD='ClientPassword',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=98;

4. Start Slave
MySQL slave command

mysql> START SLAVE;

All going well you should be up and running. Check your replication using command

MySQL slave command

mysql> SHOW SLAVE STATUS;

Backing Up
Now you have an operational Replication Setup you can explore the different tools on the market for backing up your data. A robust and thorough disaster recovery policy is a gem in the crown of the DBA. Without it one will need to keep an up-to-date CV handy at all times because disasters do happen! Plan a full backup and restore strategy, consider what backup window is available to you, how much data you can afford to lose, if you need 'point in time' restoration. Check out ProductionDBA.com's MySQL Blog for a great summary on MySQL Backup/Restore tools.

MySQLDump from A to B with pipes

Moving your data and tables around comes in many different flavours. The use of mysqldump is common practice to dump your data and schema out to a file. It is also possible to pipe your mysqldump into a 2nd server. Try the code below (adapting the users and passwords!) in a test environment;

$ mysqldump -u UserA -p p455w0rd --single-transaction --all-databases --host=Server1 | mysql -u UserA -p p455w0rd --host=Server2

As you can see from the command we are taking all the databases in a single transaction into Server2 from Server1. If you're not using transactional tables substitute the --single-transaction for --lock-all-tables to ensure you get a consistent copy.

Remember; You must be able to see the 'other' server over the network and there must be permissions set for remote access from your feeding Server. For large databases this technique may not be suitable because of the performance restrictions surrounding mysqldump. For smaller databases this can be an ideal tool for transporting data to a test machine for some manipulation.