Binary logs and replication improvements¶

Due to continuous development, Percona Server for MySQL incorporated a number of improvements related to replication and binary logs handling. This resulted in replication specifics, which distinguishes it from MySQL.

Safety of statements with a `LIMIT` clause¶

Summary of the fix¶

MySQL considers all UPDATE/DELETE/INSERT ... SELECT statements with LIMIT clause to be unsafe, no matter wether they are really producing non-deterministic result or not, and switches from statement-based logging to row-based one. Percona Server for MySQL is more accurate, it acknowledges such instructions as safe when they include ORDER BY PK or WHERE condition. This fix has been ported from the upstream bug report #42415 (#44).

Performance improvement on relay log position update¶

Relay log position fix¶

MySQL always updated relay log position in multi-source replications setups regardless of whether the committed transaction has already been executed or not. Percona Server omits relay log position updates for the already logged GTIDs.

Relay log position details¶

Particularly, such unconditional relay log position updates caused additional fsync operations in case of relay-log-info-repository=TABLE, and with the higher number of channels transmitting such duplicate (already executed) transactions the situation became proportionally worse. Bug fixed #1786 (upstream #85141).

Performance improvement on source and connection status updates¶

Source and connection status update fix¶

Replica nodes configured to update source status and connection information only on log file rotation did not experience the expected reduction in load. MySQL was additionally updating this information in case of multi-source replication when replica had to skip the already executed GTID event.

Source and connection status details¶

The configuration with master_info_repository=TABLE and sync_master_info=0 makes replica to update source status and connection information in this table on log file rotation and not after each sync_master_info event, but it didn’t work on multi-source replication setups. Heartbeats sent to the replica to skip GTID events which it had already executed previously, were evaluated as relay log rotation events and reacted with mysql.slave_master_info table sync. This inaccuracy could produce huge (up to 5 times on some setups) increase in write load on the replica, before this problem was fixed in Percona Server for MySQL. Bug fixed #1812 (upstream #85158).

Write `FLUSH` commands to the binary log¶

FLUSH commands, such as FLUSH SLOW LOGS, are not written to the binary log if the system variable binlog_skip_flush_commands is set to ON.

In addition, the following changes were implemented in the behavior of read_only and super_read_only modes:

When read_only is set to ON, any FLUSH ... command executed by a normal user (without the SUPER privilege) are not written to the binary log regardless of the value of the binlog_skip_flush_command variable.
When super_read_only is set to ON, any FLUSH ... command executed by any user (even by those with the SUPER privilege) are not written to the binary log regardless of the value of the binlog_skip_flush_commands variable.

An attempt to run a FLUSH command without either SUPER or RELOAD privileges results in the ER_SPECIFIC_ACCESS_DENIED_ERROR exception regardless of the value of the binlog_skip_flush_commands variable.

binlog_skip_flush_commands¶

Option	Description
Command-line	Yes
Config file	Yes
Scope	Global
Dynamic	Yes
Default	OFF

This variable was introduced in Percona Server for MySQL 8.0.15-5.

When binlog_skip_flush_commands is set to ON, FLUSH ... commands are not written to the binary log. See Writing FLUSH Commands to the Binary Log for more information about what else affects the writing of FLUSH commands to the binary log.

Note

FLUSH LOGS, FLUSH BINARY LOGS, FLUSH TABLES WITH READ LOCK, and FLUSH TABLES ... FOR EXPORT are not written to the binary log no matter what value the binlog_skip_flush_commands variable contains. The FLUSH command is not recorded to the binary log and the value of binlog_skip_flush_commands is ignored if the FLUSH command is run with the NO_WRITE_TO_BINLOG keyword (or its alias LOCAL).

Maintaining comments with DROP TABLE¶

When you issue a DROP TABLE command, the binary log stores the command but removes comments and encloses the table name in quotation marks. If you require the binary log to maintain the comments and not add quotation marks, enable binlog_ddl_skip_rewrite.

binlog_ddl_skip_rewrite¶

Option	Description
Command-line	Yes
Config file	Yes
Scope	Global
Dynamic	Yes
Default	OFF

This variable was introduced in Percona Server for MySQL 8.0.26-16.

If the variable is enabled, single table DROP TABLE DDL statements are logged in the binary log with comments. Multi-table DROP TABLE DDL statements are not supported and return an error.

SET binlog_ddl_skip_rewrite = ON;
/*comment at start*/DROP TABLE t /*comment at end*/;

Binary log user-defined functions¶

To implement Point in Time recovery, we have added the binlog_utils_udf plugin. These functions help you locate specific transactions in binary logs and determine which binary log files contain particular GTIDs, which is essential for precise point-in-time recovery operations.

Prerequisites¶

Before using these functions, ensure that:

Binary logging is enabled on your MySQL server
You have the SYSTEM_VARIABLES_ADMIN and SERVICE_CONNECTION_ADMIN privileges to install plugins
You have read access to the binary log directory
GTID-based replication is configured (for GTID-related functions)

Installation¶

Before using the user-defined functions, you must install the plugin:

mysql> INSTALL PLUGIN binlog_utils_udf SONAME 'binlog_utils_udf.so';

After installation, you can verify the plugin is loaded by checking the INFORMATION_SCHEMA.PLUGINS table:

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'binlog_utils_udf';

Understanding GTIDs¶

A Global Transaction Identifier (GTID) is a unique identifier for each transaction in a MySQL replication setup. GTIDs help ensure data consistency and enable precise point-in-time recovery. GTIDs follow the format source_id:transaction_id, where source_id is the server’s UUID and transaction_id is a sequence number.

When using these user-defined functions, you must use CAST to return a result. For example:

mysql> SELECT CAST(get_last_gtid_from_binlog("binlog.0001") AS CHAR) as result;

Available functions¶

The following user-defined functions are included:

Name	Returns	Description	Use Case
get_binlog_by_gtid()	Binlog file name as STRING	Returns the binlog file name that contains the specified GTID	Find which binary log file contains a specific transaction
get_last_gtid_from_binlog()	GTID as STRING	Returns the last GTID found in the specified binlog	Determine the most recent transaction in a binary log file
get_gtid_set_by_binlog()	GTID set as STRING	Returns all GTIDs found in the specified binlog	Get a complete list of transactions in a binary log file
get_binlog_by_gtid_set()	Binlog file name as STRING	Returns the file name of the binlog which contains at least one GTID from the specified set	Find binary log files containing any of the specified transactions
get_first_record_timestamp_by_binlog()	Timestamp as INTEGER	Returns the timestamp of the first event in the specified binlog	Determine when a binary log file was first created
get_last_record_timestamp_by_binlog()	Timestamp as INTEGER	Returns the timestamp of the last event in the specified binlog	Determine when the last transaction was written to a binary log file

Note

All functions returning timestamps return their values as microsecond precision UNIX time. In other words, they represent the number of microseconds since 1-JAN-1970.

All functions accepting a binlog name as a parameter accept only short names, without a path component. If the path separator (‘/’) is found in the input, an error is returned. This restriction serves the purpose of limiting the locations from which binlogs can be read. They are always read from the current binlog directory (@@log_bin_basename system variable).

All functions returning binlog file names return the name in short form, without a path component.

get_binlog_by_gtid¶

The basic syntax for get_binlog_by_gtid() is the following:

* get_binlog_by_gtid(string) [AS] alias

Usage: SELECT get_binlog_by_gtid(string) [AS] alias

Example:

mysql> CREATE FUNCTION get_binlog_by_gtid RETURNS STRING SONAME 'binlog_utils_udf.so';
mysql> SELECT CAST(get_binlog_by_gtid("F6F54186-8495-47B3-8D9F-011DDB1B65B3:1") AS CHAR) AS result;

Expected output

+--------------+
| result       |
+==============+
| binlog.00001 |
+--------------+

mysql> DROP FUNCTION get_binlog_by_gtid;

get_last_gtid_from_binlog¶

The basic syntax for get_last_gtid_from_binlog() is the following:

* get_last_gtid_from_binlog(string) [AS] alias

Usage: SELECT get_last_gtid_from_binlog(string) [AS] alias

For example:

mysql> CREATE FUNCTION get_last_gtid_from_binlog RETURNS STRING SONAME 'binlog_utils_udf.so';
mysql> SELECT CAST(get_last_gtid_from_binlog("binlog.00001") AS CHAR) AS result;

Expected output

+-----------------------------------------+
| result                                  |
+=========================================+
| F6F54186-8495-47B3-8D9F-011DDB1B65B3:10 |
+-----------------------------------------+

mysql> DROP FUNCTION get_last_gtid_from_binlog;

get_gtid_set_by_binlog¶

The basic syntax for get_gtid_set_by_binlog() is the following:

* get_gtid_set_by_binlog(string) [AS] alias

Usage: SELECT get_gtid_set_by_binlog(string) [AS] alias

For example:

mysql> CREATE FUNCTION get_gtid_set_by_binlog RETURNS STRING SONAME 'binlog_utils_udf.so';
mysql> SELECT CAST(get_gtid_set_by_binlog("binlog.00001") AS CHAR) AS result;

Expected output

+-------------------------+
| result                  |
+=========================+
| 11ea-b9a7:7,11ea-b9a7:8 |
+-------------------------+

mysql> DROP FUNCTION get_gtid_set_by_binlog;

get_binlog_by_gtid_set¶

The basic syntax for get_binlog_by_gtid_set() is the following:

get_binlog_by_gtid_set(string) [AS] alias

Usage: SELECT get_binlog_by_gtid_set(string) [AS] alias

Example:

mysql> CREATE FUNCTION get_binlog_by_gtid_set RETURNS STRING SONAME 'binlog_utils_udf.so';
mysql> SELECT CAST(get_binlog_by_gtid_set("11ea-b9a7:7,11ea-b9a7:8") AS CHAR) AS result;

Expected output

+---------------------------------------------------------------+
| result                                                        |
+===============================================================+
| bin.000003                                                    |
+---------------------------------------------------------------+

mysql> DROP FUNCTION get_binlog_by_gtid_set;

get_first_record_timestamp_by_binlog¶

The basic syntax for get_first_record_timestamp_by_binlog() is the following:

* get_first_record_timestamp_by_binlog(TIMESTAMP) [AS] alias

Usage: SELECT get_first_record_timestamp_by_binlog(TIMESTAMP) [AS] alias

For example:

mysql> CREATE FUNCTION get_first_record_timestamp_by_binlog RETURNS INTEGER SONAME 'binlog_utils_udf.so';
mysql> SELECT FROM_UNIXTIME(CAST(get_first_record_timestamp_by_binlog("bin.00003") AS UNSIGNED) DIV 1000000) AS result;

Expected output

+---------------------+
| result              |
+=====================+
| 2024-12-03 09:10:40 |
+---------------------+

mysql> DROP FUNCTION get_first_record_timestamp_by_binlog;

get_last_record_timestamp_by_binlog¶

The basic syntax for get_last_record_timestamp_by_binlog() is the following:

* get_last_record_timestamp_by_binlog(TIMESTAMP) [AS] alias

Usage: SELECT get_last_record_timestamp_by_binlog(TIMESTAMP) [AS] alias

For example:

mysql> CREATE FUNCTION get_last_record_timestamp_by_binlog RETURNS INTEGER SONAME 'binlog_utils_udf.so';
mysql> SELECT FROM_UNIXTIME(CAST(get_last_record_timestamp_by_binlog("bin.00003") AS UNSIGNED) DIV 1000000) AS result;

Expected output

+---------------------+
| result              |
+=====================+
| 2024-12-04 04:18:56 |
+---------------------+

mysql> DROP FUNCTION get_last_record_timestamp_by_binlog;

Troubleshooting¶

Common issues and solutions:

Function returns NULL or error:

Ensure the binary log file exists in the current binlog directory
Verify you have read permissions on the binary log files
Check that the binary log file name is correct and does not include a path

Plugin installation fails:

Verify you have the SYSTEM_VARIABLES_ADMIN and SERVICE_CONNECTION_ADMIN privileges
Ensure the binlog_utils_udf.so file exists in the plugin directory
Check that the plugin is compatible with your MySQL version

GTID format errors:

Ensure GTIDs follow the correct format: source_id:transaction_id
Verify that GTID-based replication is enabled on your server

Timestamp conversion issues:

Remember that timestamps are returned in microseconds since Unix epoch
Use FROM_UNIXTIME() with division by 1000000 to convert to readable format

Character set and display issues:

UDFs developed before MySQL’s UDF API character set enhancements used to not care about character set of STRING return values and it was always “binary”
Oracle introduced the --binary-as-hex command line option and made it TRUE by default in interactive mode
If you see unexpected hexadecimal output from UDF functions, start the mysql client with --binary-as-hex=FALSE or add this parameter to the client section of your MySQL config file
The --binary-as-hex=FALSE option only affects how binary data is displayed in the output. You still need to use CAST operations (for example, CAST(function_name() AS CHAR)) for the UDF functions to work properly, regardless of this setting

Uninstalling the plugin¶

To uninstall the binlog_utils_udf plugin, use the following command:

mysql> UNINSTALL PLUGIN binlog_utils_udf;

The plugin cannot be disabled without uninstalling. When uninstalled, all user-defined functions provided by the plugin are automatically removed and become unavailable.

Limitations¶

For the following variables, do not define values with one or more dot (.) characters:

A value defined with these characters is handled differently in MySQL and Percona XtraBackup and can cause unpredictable behavior.

Binary logs and replication improvements¶

Safety of statements with a LIMIT clause¶

Summary of the fix¶

Performance improvement on relay log position update¶

Relay log position fix¶

Relay log position details¶

Performance improvement on source and connection status updates¶

Source and connection status update fix¶

Source and connection status details¶

Write FLUSH commands to the binary log¶

binlog_skip_flush_commands¶

Maintaining comments with DROP TABLE¶

binlog_ddl_skip_rewrite¶

Binary log user-defined functions¶

Prerequisites¶

Installation¶

Understanding GTIDs¶

Available functions¶

get_binlog_by_gtid¶

get_last_gtid_from_binlog¶

get_gtid_set_by_binlog¶

get_binlog_by_gtid_set¶

get_first_record_timestamp_by_binlog¶

get_last_record_timestamp_by_binlog¶

Troubleshooting¶

Uninstalling the plugin¶

Limitations¶

Safety of statements with a `LIMIT` clause¶

Write `FLUSH` commands to the binary log¶