9

Replication Issues and Binlog Compressor

 1 year ago
source link: https://www.percona.com/blog/replication-issues-and-binlog-compressor/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Replication Issues and Binlog Compressor

March 24, 2023

Denis Subbota

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.

Shell
$ 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.

Shell
#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:

Shell
#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.

Shell
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?

  1. 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.

  1. 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.

Shell
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:

Shell
$ 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*.

Shell
$ 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.

Shell
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.

Shell
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!

Try Percona Distribution for MySQL today!

Share This Post!

Subscribe
Connect with
guest
Label
0 Comments

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK