MySQL replication is relatively simple to set up, there are still a few points not to be overlooked and to check before you start to avoid any surprises.
If I have often worked with replicated MySQL servers, I had never had to set one up before. This operation is rather simple, but it still requires a number of checks beforehand to be sure that everything is going well. We will describe in this article the description of setting up a master/slave replication.
Preparing servers for replication
To be able to set up a replication, you need at least two MySQL servers, one which will act as master (master), one as slave (slave).
For replication to be launched, the databases must already be in an identical state on both machines. This will pass either by a duplication of the virtual machine if you are in this configuration, or by dumps of the master to load on the slave.
Basic settings for MySQL replication
Master setting
To set up your master, you must modify lines in my.cnf. It will be necessary to specify a server id (which must be different between the master and the slave(s), a binary log, an error log and the perimeter of the databases to be taken into account. This scope can be defined in “replicate all databases except…” mode or in “replicate only these databases…” mode. You can define these lines yourself, or if you have installed it, get help from phpMyAdmin which, from a form, will give you the lines to insert. For more details on replicating with phpMyAdmin, you can follow this link .
In the end, you will have lines to insert (in the [mysqld] section) that look like this:
server-id=4218408 log_bin=mysql-bin log_error=mysql-bin.err binlog_ignore_db=nom_db_a_ignore
If you are in “only replicate these bases…. », you will have in last line « binlog_do_db » instead of « binlog_ignore_db ». You will need to restart the MySQL service.
On the master, you will also have to create an account dedicated to replication (it is recommended) which will be reachable by the slave(s).
Basic slave settings for MySQL replication
On the Slavic side, there is little to do. You must define a server id in the my.cnf which must be different from the master. Then, you have to bind to the master via the MySQL account used for replication. You can again on the link given above to be assisted if necessary by phpMyAdmin. You will need to restart the MySQL service.
Points to check
Once this basic setting has been made, you will have to take a closer look at certain points to be sure that the replication will not have any problems, or even that your master will not be impacted by new constraints.
MySQL functions developed
If you have developed your own MySQL functions, you will have to check if they are “deterministic”, non-deterministic functions being considered unsafe (because they can potentially trigger a different result on the master and the slave and therefore worry about your replication). If your functions are deterministic, you should ideally have already indicated this in its declaration:
CREATE FUNCTION ..... DETERMINISTIC
If you have non-deterministic functions, if you have a history of deterministic functions but not indicated as such, you will have errors when executing these functions once the binary logs for replication have been activated. This can therefore cause big problems, because your production will be impacted.
If you believe that your functions are trustworthy, you can add this line in the my.cnf:
log_bin_trust_function_creators=On
At the system function level, some non-deterministic functions are still considered “safe” for replication, for example “LAST_INSERT_ID()”.
If you want more information on the subject, you can go to this page on the MySQL site .
Binary file format
You can define three formats for the binlog:
- statement: based on statements, default format before MySQL 5.7.7
- row: row-based, default format from 5.7.7
- mixed: mixed mode
If you encounter an error like this:
“Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED”
Your format is not suitable. If you have heterogeneous databases and tables, you will have to go through the mixed mode via this declaration in the my.cnf:
binlog-format=mixed
Disk space and purging binary logs
The exchange between your master and your slave(s) will be done through the binlog, a binary log where the instructions are written by the master, retrieved and played by the slave. But these log files can represent a significant volume, it is therefore necessary to provide disk space to accommodate this data.
You can then schedule a sliding purge of the binaries. Provide enough history to be able to restart the replication without having to go through the dump box.
For example if you want to purge your binary log with a three-day history, you can run the following command:
PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);
For this instruction to be played regularly, you can call it via a cron script, or even better via a MySQL event.
Monitoring and error management
Monitoring
You now have all the elements to launch your replication. Once launched, it will have to be monitored, just to be able to intervene quickly if an event brings down this replication.
To do this, you must monitor the result of the following query on the slave:
SHOW SLAVE STATUS;
This will give you a number of elements to know the good health of your replication. The information to be monitored in particular is:
- Slave_IO_Running: lets you know if the IO thread is running
- Slave_SQL_Running: lets you know if the SQL execution thread is running
- Seconds_behind_master: the offset in seconds between the master and the slave
Both threads must be running, and usually the delay between master and slave is 0s.
If you use Nagios, you can also find plugins like this to monitor your replication.
Error management
Restart the slave:
START SLAVE;
Restart the IO wire:
START SLAVE IO_THREAD;
Restart the SQL thread:
START SLAVE SQL_THREAD;
To unblock an error (usually a single error blocks replication, ignoring it generally unblocks the situation):
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
With the number (here 1) of the number of requests to skip. A start slave followed by a show slave status will show if the problem is resolved and if the replication has started again.
If some of the errors are recurring and not a concern for your replication, you can also choose to ignore them with an entry in my.cnf:
slave-skip-errors=1062,1053
