Get a replication overview, see how to make preparations on the master, learn how to configure the slave, and see how to monitor replication.
Many organizations have MySQL or MariaDB databases at the core of their business for processing and storing product sales, collecting information related to services offered, or just providing essential information to customers. As a result, keeping these databases running continuously can be critical for the success of an organization.
There are many components of a database system that a database administrator will need to consider for maintaining high availability. We considered server equipment (for example, 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.
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 (like UPDATE statements) to the master and read traffic (like 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.
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 (for example, 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.
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 (ALTER TABLE) , and data (INSERT) . The changes to the server, schema and data are all that’s needed for replication. The binary log doesn’ t include SELECTstatements. 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:
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:
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.
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.
If you haven’ t already, copy the backup 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:
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 backup file:
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:
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:
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.
MySQL and MariaDB provide a SHOW statement for monitoring and troubleshooting replication. Execute the following statement on the slave to check its status:
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 username 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.
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.
