You might want to use binlog compression with MySQL/Percona Server for MySQL, but it can come with drawbacks. This article discusses the need for using binlog compression and the potential issues it may cause.
Binlog compression is a technique used to reduce the size of binary log files, which can become quite large over time. This can be especially important in situations where disk space is limited. However, it’s important to be aware that using binlog compression can also cause issues with replication.
Consider the following scenario: you have restored a backup that was taken from a replica node using Percona XtraBackup. Once the restoration is complete, you want to set up replication using the information from the xtrabackup_slave_info file.
1 2 3 4 5 6 7 | $ cat xtrabackup_slave_info #GTID replication: SET GLOBAL gtid_purged=00022031-1111-1111-1111-111111111111:1-31781; CHANGE MASTER TO MASTER_AUTO_POSITION=1; #General replication with Binlog file and position: CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000053', MASTER_LOG_POS=274; |
You updated the command with details for your primary and started replication.
1 2 3 4 5 6 7 8 | #GTID replication SET GLOBAL gtid_purged=00022031-1111-1111-1111-111111111111:1-31781; CHANGE MASTER TO MASTER_HOST="10.0.0.2", MASTER_USER="replica", MASTER_PASSWORD="somestrongpas$", MASTER_AUTO_POSITION=1; START SLAVE; #General replication with Binlog file and position: CHANGE MASTER TO MASTER_HOST="10.0.0.2", MASTER_USER="replica", MASTER_PASSWORD="somestrongpas$", MASTER_LOG_FILE='mysql-bin.000053', MASTER_LOG_POS=274; START SLAVE; |
But you have an error:
1 2 3 4 5 | #GTID replication Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Cannot replicate because the master purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. The GTID set sent by the slave is '00022031-1111-1111-1111-111111111111:1-31781', and the missing transactions are '00022031-1111-1111-1111-111111111111:1-31781'' #General replication with Binlog file and position: Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not open log file' |
The first step is to uncompress the binlog files and start replication. If you are using general replication with binlog file and position, your replication should work well and there is no need to make any changes.
However, if you want to use GTID-based replication, the story doesn’t end there. We need to fix one more replication error.
1 | Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. The GTID sets and the missing purged transactions are too long to print in this message. For more information, please see the master's error log or the manual for GTID_SUBTRACT.' |
So, what can we do to fix this?
- The easiest way to fix this error is to restart the primary MySQL server to update the gtid_purged variable. To do this, make sure that binlog_gtid_simple_recovery=ON is enabled.
After restarting MySQL, it will check all binlogs and update gtid_purged. To start replication on the replica node, simply execute START SLAVE;
Of course, this method is not for everyone, as a primary restart is not the recommended approach since it causes downtime for the entire database cluster.
- Another way to achieve this is by reconfiguring the replica to use the binary log file and position.
Ensure that Master_Log_File and Exec_Master_Log_Pos are present in the output of SHOW SLAVE STATUSG.
1 2 3 | STOP SLAVE; CHANGE MASTER TO MASTER_AUTO_POSITION = 0; START SLAVE; |
But if you don’t have it, do not despair. You can find binlogfile/position from uncompressed binlog and files using the following commands.
NOTE! – As in the output of the xtrabackup_slave_info, we have gtid_purged=00022031-1111-1111-1111-111111111111:1-31781, so we need to find the next GTID, which is 00022031-1111-1111-1111-111111111111:31782.
Go to the binlog directory on the primary node:
1 | $ cd $(mysql -BNe"select @@log_bin_basename" | cut -d'/' -f1-$(mysql -BNe"select @@log_bin_basename"|grep / -o |wc -l)) |
To find the file containing this position, run the following command:
Note: To speed up this process, please provide a good range of binlog files, such as mysql-bin.0*.
1 2 3 4 5 6 | $ for file in $(ls mysql-bin.0*) ; do echo $file ;mysqlbinlog --base64-output=decode-rows -vv $file | grep "00022031-1111-1111-1111-111111111111:31782" -A15 ; done | grep "00022031-1111-1111-1111-111111111111:31782" -B1 -A15 mysql-bin.000053 SET @@SESSION.GTID_NEXT= '00022031-1111-1111-1111-111111111111:31782'/*!*/; # at 274 #230224 18:20:48 server id 22031 end_log_pos 386 CRC32 0x0280d048 Query thread_id=14 exec_time=0 error_code=0 Xid = 85 SET TIMESTAMP=1677244848/*!*/; SET @@session.pseudo_thread_id=14/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1168113696/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!C utf8mb4 *//*!*/; SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/; /*!80016 SET @@session.default_table_encryption=0*//*!*/; create database test2 /*!*/; |
As the output of the above script, we will have binlog filename and position.
Sure thing! To solve this situation, let’s use the file mysql-bin.000053 and position 274.
1 2 3 | STOP SLAVE; CHANGE MASTER TO MASTER_HOST="10.0.0.2", MASTER_USER="replica", MASTER_PASSWORD="somestrongpas$", MASTER_LOG_FILE='mysql-bin.000053', MASTER_LOG_POS=274, MASTER_AUTO_POSITION=0; START SLAVE; |
We should wait until replication is fully caught up before switching back to GTID-based replication.
1 | STOP SLAVE; CHANGE MASTER TO MASTER_AUTO_POSITION = 1; START SLAVE; |
Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
What binlog compression is talked about here? Is this about manually compressing binlog files using gzip? Then that’s normal that things don’t work as expected.
Use binlog transaction compression instead, then these problems do not happen (https://dev.mysql.com/doc/refman/8.0/en/binary-log-transaction-compression.html). This has been released 3 years ago.
Hello Kenny,
Thanks for checking blog.
Binlog compression, it’s when you are using script which is compressing for you binlary logs based on your time policy.
It might be a simple cron task in bundle find + gzip or you can create a script for same :
Example:
## Binlog compression for files older than day
15,45 * * * * root find /path/to/binlog -maxdepth 1 -type f -mtime +1 -name “mysql-binlog.??????” -exec gzip {} \;
Thanks for sharing about built-in binlog transaction compression. This is something that needs to be checked first. I am curious if enabled binlog trx compression will be causing CPU overwhelming for replicas.
Regards,
Denis Subbota.
Managed Services, Percona.