MySQL table auto-increment id overflow fault review solution

Problem: The overflow of the auto-increment ID in a MySQL table caused a business block

background:

A large table tb1 of the tokudb engine stores business machine audit logs. A large number of data are written every day. Due to historical reasons, this table is of int signed type and can store a maximum of 2147483647 rows.

Processing process:

Add a DBLE middleware agent, perform range partitioning, and write new data to a newly added shard. At the same time, the business modified the connection and changed the connection mode of this table tb1 to DBLE. However, after the business code was modified, it was found that some remaining insert into tb1 write requests were forwarded to the old table, and some tables were incorrectly routed to DBLE. This complicates things further. Finally, the entire business returned to normal after the code that wrote tb1 was taken offline.

After reviewing the situation, I thought that in this case, for problems with log tables, the DBA should take quick and decisive measures to restore business as soon as possible, and then consider other issues. If we think in this way, the above problem can be easily solved. Just a few steps:

use logdb;

select max(id) from tb1; -- Record the current maximum id as xxxx
create table tb2 LIKE tb1; -- Create a shadow table alter table tb2 modify column id bigint unsigned not null auto_increment; -- Modify the new table to bigint unsigned type, which can store 18446744073709551615 rows of data.
alter table tb2 auto_increment=xxxx+1; -- Change the starting value of the auto-increment primary key of the new table rename table tb1 to tb_archive , tb2 to tb1; -- Switch the table name

After this operation, data can be written to tb1 and the business can be temporarily restored. The remaining work is to migrate the data in the tb_archive table to tb1 (you can use the pt-archiver tool to migrate data and run it slowly in the background).

After some calculations, it takes only about 5 minutes at most to cut the table and resume the business write operation, and the impact on the remaining migrated data will be relatively small.

Subsequent optimization measures:

Add monitoring of auto-increment id, see here https://www.jb51.net/article/184935.htm

Sort out some unexpected problems that may be encountered in production, and formulate relevant emergency plans accordingly

This is the end of this article about the troubleshooting and solution of MySQL table auto-increment id overflow. For more related MySQL auto-increment id overflow content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!