In this post I will try to explain the new MySQL binlog_error_action server option. This new option is available from MySQL 5.6.22 and later.
Background:
——————–
As part of MySQL replication all data changes that happen on the master server are recorded into a binary log so that they can be sent to slave and replayed there. If an error occurs that prevents mysqld from writing to the binary log (disk full, readonly file system, etc.) then the logs are simply disabled and operations continue on the master. This error mainly occurs during rotation of the binary log or while opening a binary log file.
This problem creates a serious potential for data loss within a replication group. When a master hits this failure in production, all downstream replication clients stop receiving replication events. The master will not store binlog events for committed transactions to its binary log and consequently there will be no new events in the binary log to send to replication clients. If that master then fails, then all the transactions the master received while binlogging was turned off are lost forever. This can lead to out of sync slaves and improper backups.
Error message when file system becomes readonly:
As part of the bug fix for Bug#51014 the binlog_error_action server option was introduced. Using this option a user can choose to either ignore the error (still the default so as to avoid behavior changes in GA releases) or to abort the server. The IGNORE_ERROR option value refers to the default behavior (as described above) where binlogging will simply be disabled and the master will continue with its normal operations.
|
1
2
3
4
5
6
7
|
mysql> SHOW GLOBAL VARIABLES LIKE 'binlog_error_action'; +---------------------+--------------+ | Variable_name | Value | +---------------------+--------------+ | binlog_error_action | IGNORE_ERROR | +---------------------+--------------+ 1 row in set (0.00 sec) |
However, the ABORT_SERVER option value will cause the server to exit when binlogging operations fail. At the time of the resulting server exit a fatal error is pushed to clients and the server will shut down. The error details being:
|
1
2
|
Error code: ER_BINLOG_LOGGING_IMPOSSIBLE Error message: Binary logging not possible. Either disk is full or file system is read only while rotating the binlog. Aborting the server |
Specifying the option:
———————————–
This option can be specified as a startup option (either on the command-line or in a config file) or dynamically in the running server using the SET command:
mysql> SET GLOBAL binlog_error_action=ABORT_SERVER;
Demonstration of the new option in the case of a read-only file system:
——————————————————————————————————————
Step 1: SET GLOBAL binlog_error_action= ABORT_SERVER;
Step 2: Make your file system readonly
Step 3: flush logs
Summary:
——————
If an error occurs that prevents mysqld from writing to the binary log the existing behaviour is: binary logging is disabled and the server continues with its normal operations. Now with the new binlog_error_action server option the user can choose to either ignore the error (IGNORE_ERROR) or to abort the server (ABORT_SERVER) when binary logging failures occur. This optional behavior was first introduced in MySQL 5.6.20 using the binlogging_impossible_mode server option. That option name is now deprecated in MySQL 5.6.22 and the option is instead now referred to as binlog_error_action.
We look forward to your feedback on this new feature! If you have any questions or encounter any bugs, please do let us know by opening a support ticket or filing a bug. As always, THANK YOU for using MySQL!