Updating AUTO_INCREMENT value of all tables in a MySQL database

In the below instructions you will need to replace everything that is in [brackets] with your correct value. BACKUP BEFORE ATTEMPTING.

If you can login to mysql as root through the command line then you could do the following to reset the auto_increment on all tables, first we will construct our queries which we want to run:

Make a database backup:

mysqldump -u [uname] -p [dbname] | gzip -9 > [backupfile.sql.gz]

Login:

mysql -u root -p

Set the group_concat_max_length to a higher value so our list of queries doesn't get truncated:

SET group_concat_max_len=100000;

Create our list of queries by using the following:

SELECT GROUP_CONCAT(CONCAT("ALTER TABLE ", table_name, " AUTO_INCREMENT = 0") SEPARATOR ";") FROM information_schema.tables WHERE table_schema = "[DATABASENAME]";

Then you will receive a long string of mysql queries followed by a bunch of dashes. Copy the string of queries to your clipboard, it will look something similar to:

ALTER table1 AUTO_INCREMENT = 0;ALTER table2 AUTO_INCREMENT = 0;...continued...

Change to the database you would like to run the command on:

USE [DATABASENAME];

Then paste the string that is on your clipboard and hit enter to run it. This should run the alter on every table in your database.

Messed up? Restore from your backup, be sure to logout of mysql before running the following (just type exit; to do so)

gzip -d < [backupfile.sql.gz] | mysql -u [uname] -p [dbname]

I will not take responsibility for any damage cause by your use of any of these commands, use at your own risk.

set @db = 'your_db_name';

SELECT concat('ALTER TABLE ', @db, '.', TABLE_NAME, ' AUTO_INCREMENT = 0;') 
FROM information_schema.TABLES WHERE TABLE_SCHEMA = @db AND TABLE_TYPE = 'BASE TABLE'

Then copy-paste and run the output you get.

Using:

ALTER TABLE some_table AUTO_INCREMENT = 0

...will reset the auto_increment value to be the next value based on the highest existing value in the auto_increment column.

To run this over all the tables, you'll need to use MySQL's dynamic SQL syntax called PreparedStatements because you can't supply the table name for an ALTER TABLE statement as a variable. You'll have to loop over the output from:

SELECT t.table_name
  FROM INFORMATION_SCHEMA.TABLES t
 WHERE t.table_schema = 'your_database_name'

...running the ALTER TABLE statement above for each table.