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.
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
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
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
shell$ service mysql start
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';
**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
OS master command
shell$ mysqldump -u{user} -p{password} {--single-transaction|--lock-all-tables} --all-databases --master-data=1 > masterdata_dump.sql
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;
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;
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.