.png)
In MySQL, a Binary Log is a set of files that contains events. This event describes changes in databases such as changes to table data(inserts/updates/deletes Operations) and table structure(Create/Alter Operations). It plays an important role in Replication to send the data changes to the replicas and also helps to perform Point in Time Recovery(PITR) Operations. So, it is vital to have binary logs enabled on the database.
Since MySQL 8.0.14, Encryption is also possible over Binary and Relay logs which helps to protect these files from unauthorized viewing by users. Unlike other files in MySQL, the extension used in Binary Log is Sequence Number which usually starts from a 6 digit numer “000001".
mysql [localhost:8033] {root} ((none)) > show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 8331 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)Understanding MySQL Binary Logs
MySQL increments the Sequence Number By 1 which means it rotates the binary log for the following reasons:
- The server started or restarted
- The size of the binary log crosses the max_binlog_size defined. Exception for large transactions because a transaction never splits between multiple binlog files.
- When the server flushes the logs (Execution of flush logs)
Have you ever considered an issue where we could run out of sequence numbers in the binary log, potentially causing a production outage? Yes, we do have a possibility of running out of it.
Let's see now what is the maximum sequence number we can reach, how to proactively identify it and prevent outage. Also, we will look at how to recover the database if it reaches its maximum sequence number.
The Problem: Sequence Number Exhaustion
By default, MySQL starts its binlog extension with a six-digit number. Setting the maximum value in six digits(999999) to see what happens.
mysql [localhost:8033] {root} ((none)) > show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 8326 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)Setting to the maximum 6 digit sequence number
mysql [localhost:8033] {root} ((none)) > reset master to 999999;
Query OK, 0 rows affected (0.01 sec)
mysql [localhost:8033] {root} ((none)) > show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.999999 | 157 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)Executing flush logs to move to the next binlog file
mysql [localhost:8033] {root} ((none)) > flush logs;
Query OK, 0 rows affected (0.02 sec)Adjusting Sequence Number to 7 digits
mysql [localhost:8033] {root} ((none)) > show master status;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| binlog.1000000 | 157 | | | |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql [localhost:8033] {root} ((none)) >Nothing happened. It simply moved to the 7-digit sequence number. So, what is the maximum sequence value then?
Exploring the Maximum Possible Sequence Number
If we dig the source code, we can get the maximum possible sequence number for binlog.
mysql-server/sql/binlog.cc:
if (max_found >= MAX_LOG_UNIQUE_FN_EXT) {
LogErr(ERROR_LEVEL, ER_BINLOG_FILE_EXTENSION_NUMBER_EXHAUSTED, max_found);
error = 1;
goto end;
}Max Sequence Number value is defined as MAX_LOG_UNIQUE_FN_EXT in the header file.
mysql-server/sql/binlog.h:
#define MAX_LOG_UNIQUE_FN_EXT 0x7FFFFFFF
A hexadecimal notation for 2,147,483,647(Approximately 2 Billion) is 0x7FFFFFFFSo, we can have a maximum sequence number until 2.1 Billion. It is equivalent to 2k PetaBytes of Writes if max_binlog_size is set to 1GB(default) or 200 PetaBytes if max_binlog_size is set to 100MB.
Handling Sequence Number Limits
Let's explore a scenario where we approach the maximum number and observe the outcome.
mysql [localhost:8033] {root} ((none)) > reset master to 2147483647;
ERROR 3567 (HY000): The requested value '2147483647' for the next binary log index is out of range. Please use a value between '1' and '2000000000'.
mysql [localhost:8033] {root} ((none)) >Tried to set the sequence number to the maximum value, but got an error with a limit of '2000000000'. So, using the reset master command, we are only allowed to set it until '2000000000'.
Setting it to the maximum possible value using the reset master command
mysql [localhost:8033] {root} ((none)) > reset master to 2000000000;
Query OK, 0 rows affected (0.01 sec)mysql [localhost:8033] {root} ((none)) > flush logs;
Query OK, 0 rows affected (0.03 sec)
mysql [localhost:8033] {root} ((none)) > show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| binlog.2000000001 | 157 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)It exceeds the CLI's allowed value of '2000000000'. Consider editing the binlog index file to set the sequence number to the maximum. Please note, refrain from attempting this on a production server.
Stop the MySQL service
root@mydbopslabs:~/sandboxes/msb_8_0_33/data# cat binlog.index
./binlog.2000000000
./binlog.2000000001Configure it to 2 numbers below the maximum, specifically setting it to 2147483644 and 2147483645
root@mydbopslabs:~/sandboxes/msb_8_0_33/data# cat binlog.index
./binlog.2147483644
./binlog.2147483645Rename the binlog files
root@mydbopslabs:~/sandboxes/msb_8_0_33/data# mv binlog.2000000000 binlog.2147483644
root@mydbopslabs:~/sandboxes/msb_8_0_33/data# mv binlog.2000000001 binlog.2147483645
root@mydbopslabs:~/sandboxes/msb_8_0_33/data# ls -lrth binlog.*
-rw-r----- 1 root root 205 Jan 30 13:13 binlog.2147483644
-rw-r----- 1 root root 157 Jan 30 13:13 binlog.2147483645Start the MySQL service
The sequence number is currently just one value below its maximum.
mysql [localhost:8033] {root} ((none)) > show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| binlog.2147483646 | 157 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)Execute flush log to reach its maximum number
mysql [localhost:8033] {root} ((none)) > show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| binlog.2147483646 | 157 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql [localhost:8033] {root} ((none)) > flush logs;
Query OK, 0 rows affected (0.02 sec)mysql [localhost:8033] {root} ((none)) > flush logs;
ERROR 1598 (HY000): Binary logging not possible. Message: Can't generate a unique log-filename binlog.(1-999), while rotating the binlog. Aborting the serverMySQL crashed once we reached the maximum value of 2147483647 as it could not accept any more writes.
Server Logs during the crash
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (85cd1a0): flush logs
Connection ID (thread ID): 8
Status: NOT_KILLEDThe manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2024-01-30T13:22:46.129781Z 0 [System] [MY-013951] [Server] 2024-01-30T13:22:46Z UTC - mysqld got signal 6 ;
2024-01-30T13:22:46.129782Z 0 [System] [MY-013951] [Server] Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
2024-01-30T13:22:46.129783Z 0 [System] [MY-013951] [Server] BuildID[sha1]=e0d4895db012310b67f6c40df56188f44a982ce2
2024-01-30T13:22:46.129784Z 0 [System] [MY-013951] [Server] Thread pointer: 0x85b7d60
2024-01-30T13:22:46.129785Z 0 [System] [MY-013951] [Server] Attempting backtrace. You can use the following information to find out
2024-01-30T13:22:46.129786Z 0 [System] [MY-013951] [Server] where mysqld died. If you see no messages after this, something went
2024-01-30T13:22:46.129787Z 0 [System] [MY-013951] [Server] terribly wrong...
2024-01-30T13:22:46.129788Z 0 [System] [MY-013951] [Server] stack_bottom = 7efc503f1c20 thread_stack 0x100000
2024-01-30T13:22:46.129789Z 0 [System] [MY-013951] [Server] /root/opt/mysql/8.0.33/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x206ba4e]
2024-01-30T13:22:46.129790Z 0 [System] [MY-013951] [Server] /root/opt/mysql/8.0.33/bin/mysqld(print_fatal_signal(int)+0x35f) [0xfb8adf]
2024-01-30T13:22:46.129791Z 0 [System] [MY-013951] [Server] /root/opt/mysql/8.0.33/bin/mysqld(my_server_abort()+0x6e) [0xfb8c1e]
2024-01-30T13:22:46.129792Z 0 [System] [MY-013951] [Server] /root/opt/mysql/8.0.33/bin/mysqld(my_abort()+0xa) [0x206613a]
2024-01-30T13:22:46.129793Z 0 [System] [MY-013951] [Server] /root/opt/mysql/8.0.33/bin/mysqld() [0x1c9abea]
2024-01-30T13:22:46.129794Z 0 [System] [MY-013951] [Server] /root/opt/mysql/8.0.33/bin/mysqld(MYSQL_BIN_LOG::new_file_impl(bool, Format_description_log_event*)+0x65c) [0x1cab41c]
2024-01-30T13:22:46.129795Z 0 [System] [MY-013951] [Server] /root/opt/mysql/8.0.33/bin/mysqld(MYSQL_BIN_LOG::rotate(bool, bool*)+0x35) [0x1cac115]
2024-01-30T13:22:46.129796Z 0 [System] [MY-013951] [Server] /root/opt/mysql/8.0.33/bin/mysqld(MYSQL_BIN_LOG::rotate_and_purge(THD*, bool)+0x52) [0x1cb3932]
2024-01-30T13:22:46.129797Z 0 [System] [MY-013951] [Server] /root/opt/mysql/8.0.33/bin/mysqld(handle_reload_request(THD*, unsigned long, Table_ref*, int*)+0x4df) [0xe8a9cf]
2024-01-30T13:22:46.129798Z 0 [System] [MY-013951] [Server] /root/opt/mysql/8.0.33/bin/mysqld(mysql_execute_command(THD*, bool)+0x302e) [0xe588ee]
2024-01-30T13:22:46.129799Z 0 [System] [MY-013951] [Server] /root/opt/mysql/8.0.33/bin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x4f4) [0xe5a024]
2024-01-30T13:22:46.129800Z 0 [System] [MY-013951] [Server] /root/opt/mysql/8.0.33/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0xd14) [0xe5b2d4]
2024-01-30T13:22:46.129801Z 0 [System] [MY-013951] [Server] /root/opt/mysql/8.0.33/bin/mysqld(do_command(THD*)+0x1df) [0xe5d65f]
2024-01-30T13:22:46.129802Z 0 [System] [MY-013951] [Server] /root/opt/mysql/8.0.33/bin/mysqld() [0xfa95a0]
2024-01-30T13:22:46.129803Z 0 [System] [MY-013951] [Server] /root/opt/mysql/8.0.33/bin/mysqld() [0x26d2805]
2024-01-30T13:22:46.129804Z 0 [System] [MY-013951] [Server] /lib/x86_64-linux-gnu/libpthread.so.0(+0x8609) [0x7efc67f68609]
2024-01-30T13:22:46.129805Z 0 [System] [MY-013951] [Server] /lib/x86_64-linux-gnu/libc.so.6(clone+0x43) [0x7efc6731d353]Proactive Monitoring Strategies
At this point, we know we may facean outage because of binlog sequence numbers too. So, it is very important to have monitoring in place to avoid it. We can achieve this by using two methods
Monitoring MySQL Error Logs
MySQL starts to print the Warning message like below once the sequence number crosses 2000000000. At that moment, we will be left with 147483646 sequences.
[Warning] [MY-010815] [Server] Next log extension: 2000000001. Remaining log filename extensions: 147483646. Please consider archiving some logs.Frequent Master Status Checks
By querying the master status also helps to see the growth rate of sequence numbers.
Know about the System
Have the details of how long the MySQL instance is running and how old it is. Also, have the stats of per day writes volume. This helps to plan the sequence number growth.
What to Do When Exhausted
Recommended Actions if Exhausted
Once it has crashed or is about to exhaust, shut it down properly. Rename the binlog files to the lowest sequence number in order.
root@mydbopslabs:~/sandboxes/msb_8_0_33/data# mv binlog.2147483644 binlog.000001
root@mydbopslabs:~/sandboxes/msb_8_0_33/data# mv binlog.2147483645 binlog.000002
root@mydbopslabs:~/sandboxes/msb_8_0_33/data# mv binlog.2147483646 binlog.000003
root@mydbopslabs:~/sandboxes/msb_8_0_33/data# mv binlog.2147483647 binlog.000004Then edit the index files accordingly with these new binary log names.
root@mydbopslabs:~/sandboxes/msb_8_0_33/data# cat binlog.index
./binlog.000001
./binlog.000002
./binlog.000003
./binlog.000004Testing Changes in Non-Production Environments
For cases having replicas, test it once before doing it on a production instance.
Additional Resources
Securing MySQL Binary logs at Rest in MySQL 8.0
Read MySQL Binlogs better with rows query log events
Maintenance of MySQL Binary logs
Creating a Simple MySQL Binlog Server
MySQL Binlog server with Maxscale
Binlog expiry now in seconds (MySQL 8.0)
Managing MySQL binary logs and preventing sequence number exhaustion is critical for maintaining database stability and performance. By implementing proactive monitoring strategies and understanding how to respond when limits are approached, you can avoid potential outages.
As you navigate the complexities of managing MySQL binary logs and preventing sequence number exhaustion, consider partnering with Mydbops. Our expert team offers comprehensive MySQL Managed Services and Consulting to help you optimize your database performance, ensure data integrity, and implement effective monitoring strategies
Whether you're looking for ongoing support or need guidance on specific challenges, Mydbops is here to assist you. Contact us today to learn more about how we can help you manage your MySQL databases more effectively!
.png)
.svg)
