Database Backups and Identifying Invalid Auto Increment Values

Recently I have been working with a third party service provider to create a disaster recovery process in the worse case scenario of our server completely failing. One key part of that process is to determine the best approach to backup our site's files and databases. Setting up the file backup is fairly straight forward and just requires a decision of how frequently we would like the backups to be created. Creating the database backup is a bit more complicated due to possibility of the mysql service interacting with the database files while we are creating the backup.

There are two primary ways of creating backups for a mysql database, either by creating a database export that is a "logical" backup using mysqldump or by creating a "directory" backup of the directory that houses all of the mysql files. The logical backup is quite stable and allows you to import that database into any environment with very little issues. The downsides are that it locks tables as it is exporting them and the import process is fairly slow as mysql needs to run through all of the commands to get the database into its final state. The directory backup can be significantly faster as you are simply swapping out files but requires either the mysql service to be shut down when creating the backup or some alternative means to handle changes that are done while the backup is being created.

With our current setup, a very basic directory backup that does not properly handle those concurrent changes which we are still working to improve, we have ran into issues recovering from our backup to a separate environment. The primary issues we have come across have been indexing issues that can be identified with mysqlcheck (ie the CHECK TABLE <table_name>; query) or auto_increment issues, where the current auto increment value already exists within the table. For the later I have created a fairly complex query (see below) that will search all tables containing an id column and determine if the auto_increment value is valid for the ids that already exist within the table. This will hopefully allow us to identify tables quickly that have this problem, so we can take the appropriate steps to resolve it and check for other issues.

-- If your database has a lot of tables, you will need to increase the maximum length that GROUP_CONCAT will be able to build.
SET GROUP_CONCAT_MAX_LEN=65536;

SET @database = '<database_name>';

SET @query = NULL;

SELECT GROUP_CONCAT(query SEPARATOR " UNION ") INTO @query FROM (SELECT CONCAT("SELECT '", information_schema.TABLES.TABLE_NAME, "' AS 'TABLE', t.* FROM (SELECT AUTO_INCREMENT, (SELECT MAX(id) FROM ", @database, ".", information_schema.TABLES.TABLE_NAME, ") AS MAX FROM information_schema.TABLES WHERE TABLE_SCHEMA = '", @database, "' AND TABLE_NAME = '", information_schema.TABLES.TABLE_NAME, "') AS t WHERE MAX >= AUTO_INCREMENT") AS query FROM information_schema.TABLES JOIN information_schema.COLUMNS ON information_schema.COLUMNS.TABLE_SCHEMA = information_schema.TABLES.TABLE_SCHEMA AND information_schema.COLUMNS.TABLE_NAME = information_schema.TABLES.TABLE_NAME AND information_schema.COLUMNS.COLUMN_NAME = 'id' WHERE information_schema.TABLES.TABLE_SCHEMA = @database) AS t;

PREPARE stmt1 FROM @query;

EXECUTE stmt1;

The results of this query includes only the tables that have an id value that matches or exceeds the current auto_increment value for the table.