There are many components of a database system that a database administrator will need to consider for maintaining high availability. We considered server equipment (e.g., memory) in a previous introductory article. Now let's look at using multiple servers for your MySQL and MariaDB databases—let's look at replication.
Replication Overview
One common and effective way to structure a highly available database system is through some form of database replication. There are a few reasons for using replication. One reason is for load balancing: you can split user traffic between servers, sending write traffic (e.g., UPDATE statements) to the master and read traffic (e.g., SELECT statements) to a slave—or distributed to multiple slaves. If this is new to you, it may seem complicated. So let's keep it simple to start. We'll look at how to set up a second server to replicate an existing database server: we'll set up a slave. We'll cover more complex replication systems in another article.
Basically, you designate one server as the master and another server as a slave. All changes that are made to the databases on the master are also made automatically on the slave. As for load balancing, we'll cover it in another article. To start, you can set up replication to have an extra machine that you can use as a spare in case your main server fails, and for making back-ups.
Preparations on the Master
There are only a few steps to configuring replication. First, there's no software to purchase or install—replication is built into MySQL and MariaDB. On the master, you will need to enable binary logging. To do this, add log-bin on a separate line to the database configuration file (e.g., my.cnf). Also, add a line with server-id to give the server a unique identifier—the number 1 is fine. Below is an excerpt from the configuration file showing these two variables. Be sure to restart MySQL when finished adding them.
[mysqld] log-bin server-id=1
Next, you will need to create a user account for the slave to use when communicating with the master. The slave doesn't query the databases on the master. Instead, it requests new entries to the master's binary log. This log records all changes to the server (e.g., SET statements), database structure (e.g., ALTER TABLE), and data (e.g., INSERT). The changes to the server, schema and data are all that's needed for replication. The binary log doesn't include SELECT statements. The slave doesn't need them.
So, the replication user needs only the REPLICATION SLAVE privilege, which allows it to get updates to the master's binary log. You can execute this SQL statement on the master to create such a user:
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'12.345.0.2' IDENTIFIED BY 'its_pwd';
You would replace the name replicator with a username you prefer. Use the IP address of the slave server—not 12.345.0.2, as shown here. And provide a better password.
Now you need to make a complete back-up of the master's databases to transfer to the slave. You can use the mysqldump utility to do this:
mysqldump -p --user backup_user \
--master-data --flush-logs \
--all-databases > full-backup.sql
You will have to change the username from backup_user to whatever user has the privileges needed to make a full back-up of all databases. The --master-data option tells mysqldump to include information on the master in the dump file. The --flush-log option tells mysqldump to flush the binary logs so you have a fresh start.
Configuring the Slave
On the server which is to be the slave, install MySQL or MariaDB. You should use the same software and version and release as you're using on the master. In its configuration file, set the server-id equal to 2 or some other unique number. Also add the option, read-only so that no one will change the data directly on the slave. You'll need to restart MySQL on the slave for these options to take effect.
[mysqld] server-id=2 read-only
If you haven't already, copy the back-up file from the master to the slave. You could use FTP or a similar method. Here's how you might do this with scp from your home directory:
scp -i ./.ssh/my_key.pem \ ec2-user@12.345.0.1:/home/ec2-user/full-backup.sql .
This line is set for copying between two AWS instances, which is fast. For other systems, you may have to authenticate differently, without a key. Once you have the database copied, you can use the mysql client to load the databases contained in the back-up file:
mysql -p -u root < full-backup.sql
When that's done, you need to provide the slave with the information it needs to authenticate with the master. To do this, log into MySQL on the slave as root and execute the following SQL statement:
CHANGE MASTER TO MASTER_HOST='12.345.0.1', MASTER_PORT=3306, MASTER_USER='replicator', MASTER_PASSWORD='its_pwd';
The host here should be set to the master's IP address, not the slave's IP address. When you've done this, you're ready to start the slave. Just execute the following on the slave, from within MySQL:
START SLAVE;
At this point, the slave should be replicating the master. Try changing or adding some data on the master to see if it's immediately replicated on the slave. You can create a table in the test database or insert a row into a table on the master. It should immediately reproduce whatever you do on the slave—but not SELECT statements. If it didn't work, you'll have to check the server's status to troubleshoot the problem.
Initial Troubleshooting
MySQL and MariaDB provide a SHOW statement for monitoring and troubleshooting replication. Execute the following statement on the slave to check its status:
SHOW SLAVE STATUS \G Slave_IO_State: Waiting for master to send event ... Slave_IO_State: Waiting for master to send event Slave_IO_Running: Yes Slave_SQL_Running: Yes ... Last_Errno: 0 Last_Error: ...
The results will tell you plenty, but we've listed just a few variables here for you to consider initially. In particular, look to see if the fields Slave_IO_Running and Slave_SQL_Running each say, Yes. If they do, that means it's replicating. If the IO and SQL threads on the slave aren't running, check for error messages (i.e., Last_Errno and Last_Error). This may give you a clue as to what is the problem.
If you're still having problems, execute SHOW MASTER STATUS on the master. It will show you the name of the current binary log file in use by the master, and the position number of the last entry in that binary log. Compare this to the results from SHOW SLAVE STATUS on the slave. The values for the master should be the same as on the slave. Also, check that the user name for the slave, and the host address for the master are correct, as well as the server identification number.
Setting up replication the first time should go smoothly, if you did all of the steps given above, set the user names and hosts correctly, and started with a fresh install on the slave. If troubleshooting becomes too difficult, though, you can start over: you can uninstall MySQL and delete the data directory for MySQL on the slave and then try again. Be care you delete the data directory on the slave, and not on the master.
Monitoring Replication
Once you have replication installed and it's been running for a while, you'll discover two things: it works well and it will stop unexpectedly—without notifying you. This means you will have to execute regularly the SHOW SLAVE STATUS statement on the slave to see if replication is running. Besides checking the IO and SQL threads mentioned above, you should also check the Seconds_Behind_Master field. If it's too far behind the master, there may be a problem with your network.
Besides what's provided with SHOW SLAVE STATUS, you might want to check SHOW STATUS for slave related information:
SHOW STATUS LIKE 'slave_%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | Slave_connections | 0 | | Slave_heartbeat_period | 1800.000 | | Slave_open_temp_tables | 0 | | Slave_received_heartbeats | 0 | | Slave_retried_transactions | 0 | | Slave_running | ON | | Slave_skipped_errors | 0 | | Slaves_connected | 0 | | Slaves_running | 1 | +----------------------------+----------+
If everything is going well, this won't show much. But you may want to check this information, regularly. For instance, if Slave_retried_transactions shows a high number, that might indicate a problem. In which case, check the error logs for more information.
Monyog – Add a Slave Server
If you have Monyog installed and monitoring your server, you can add a second server, the slave easily".
Monitoring a slave and replication is much easier if you have Monyog installed already and are monitoring the master—if not, download it and try it. You can use Monyog to monitor both the master and the slave. It will provide you with information on all of the variables from the SHOW statements mentioned. You can also create alerts to notify you when the slave stops replicating or has other problems. Let's go through how to add a slave to Monyog.
First, there a few things to do to prepare the slave. Create a user for Monyog on the slave. This user will need only REPLICATION CLIENT and SUPER privileges to monitor replication:
GRANT REPLICATION CLIENT, SUPER ON *.* TO 'monyog'@'12.345.0.1' IDENTIFIED BY 'its_pwd';
Now open Monyog in your web browser and click on Servers in the left margin. You'll see the one server you already added—assuming you're already using Monyog. You probably called it Localhost. For clarity, you might want to rename it. Just click on the ellipses for the box for the server and you'll see a list of choices. Choose Edit Server. The first box is labeled, Name. Change the name from Localhost to Master, then Save.
Back at the Servers page, click on ADD NEW SERVER at the top. A panel will open on the right (see screenshot). Name the new server Slave or whatever seems appropriate for you. Enter the IP address of the slave in the MySQL Host box. Enter the Username and Password for Monyog on the slave. Everything else is fine for now with the default settings. On Amazon's AWS, you may have to edit the Inbound Rules for the to allow MySQL traffic from the master. Once you're done, click the blue button, Test MySQL Connection.
When the new server tests without a problem, click on the Advanced tab. Look for Replication in the list of monitor groups. Click its plus-sign to add it. You'll see a line that reads, Is this replication slave? This is necessary to gather information about replication on the slave. Enable it. By the way, on that same screen you may notice that there is an option to Auto-register All Slaves. If you enable this on the master, you won't need to add the slave‐it will add automatically a new slave when it detects it interacting with the master.
Monyog – Monitoring Replication
Monyog is now monitoring the slave. Let's see how that looks. Click on Monitors in the left margin, the select Replication from the list of monitor groups (see screenshot). Here you'll see the results of SHOW SLAVE STATUS and the replication related fields from SHOW STATUS. Besides being a nicer display, if you click on the flag icon for a monitor, you can have Monyog send you an email or an SNMP trap to alert you when it exceeds parameters you set. At a minimum, Monyog will be monitoring Slave_IO_Running and Slave_SQL_Running and will alert you if those values switch from Yes to No, indicating that replication has stopped. You may just need to edit the server again and make sure you have the email address you want in the Notification section.
Conclusion
Replication can be useful for many things related to high-availability. At a minimum, as we said, you can use it as a hot spare or for making back-ups of the databases. We'll look at back-ups related to high-availability, as well as other methods related to replication to improve high availability of databases, in upcoming articles in this series on high-availability.
You can download a 14-day free trial of Monyog MySQL monitor here.