Create new MySql schema from Backup .SQL source file resets auto increment id columns?

Question

I am developing a large database schema that I desire to make available to clients.

I am new to MySql database administration. How do I restore a .sql file of the schema so that it also resets ALL auto incremental id columns back to 0 or Null again.

I actually restored to a new schema name and inserted a test record and found the auto id columns were incrementing from where they left-off in the last schema name.

How do I address this in MySql database modeling?

I want to always be able to 'spin-off' an exact copy of my current version data model for a paying customer.

Thank you.

why don't you use **alter table queries** for all tables which has **auto increment column** at the end of the **sql script**. — Dhinakar, Dec 24 '12 at 13:17
@DhinakaranPragasam very good idea, I need to learn how to do that correctly still. For now I just created a new dump file and reset all auto_increment= values back to auto_increment=1 — JoJo, Dec 24 '12 at 14:32
Future readers: Informational link found during research http://forums.mysql.com/read.php?160,376461,376461 — JoJo, Dec 24 '12 at 21:16

score 5 · Accepted Answer · answered Dec 24 '12 at 20:25

You can dump the schema only, without data this way:

$ mysqldump -d databasename > databasename-schema.sql

If you use InnoDB tables (which you should), the auto-increment value is always reset every time you restart the MySQL instance. The value it is reset to in each table is equal to the max value currently stored in the table, plus 1. So if your tables are empty, the value will be 1.

If you don't want to restart the MySQL instance after you restore, you can either strip the auto-increment clause in your CREATE TABLE statements before restoring:

$ mysqldump -d databasename | sed -e 's/AUTO_INCREMENT=[0-9][0-9]*//' 
  > databasename-schema.sql

Or else you can ALTER TABLE to reset the auto-increment value after restoring. This has to be done on a per-table basis, but you can query the list of tables that have auto-increment keys from the INFORMATION_SCHEMA:

$ mysql -N -B -e "SELECT CONCAT('ALTER TABLE \`', table_schema, '\`.\`', 
  table_name, '\` AUTO_INCREMENT=1;') AS ddl FROM INFORMATION_SCHEMA.TABLES 
  WHERE table_schema = 'test' AND AUTO_INCREMENT IS NOT NULL" | mysql -v

Such a help, Thanks! is that Data Definition Language or `DDL`? Can MySql Workbench execute these DDL statements? =) — JoJo, Dec 24 '12 at 20:49
Yes, you can save the output of that information schema query, and execute it as an SQL script in any tool that supports SQL scripts. — Bill Karwin, Dec 24 '12 at 20:52
I may have just found it. Workbench Scripting Shell - from the Scripting Menu in MySql Workbench. I will also download phpMyAdmin I have heard good things about it as well. — JoJo, Dec 24 '12 at 20:59

score 1 · Answer 2 · answered Dec 24 '12 at 19:13

You can use this to dump table structure, and you will find all your auto increment primary keys from the beginning as you had defined previously, it will not execute insert queries and will create all your stored procedure, views and tables :

cat backup.sql | grep -v ^INSERT | mysql -u $USER -p

score 1 · Answer 3 · edited May 23 '17 at 12:28

AUTO_INCREMENT is reset after

TRUNCATE `table`

and when you run batch INSERT query afterwards (from your data backup), the AUTO_INCREMENT value will be automaticaly updated to proper value (inserted records+1).

So you don't need to update AUTO_INCREMENT value manualy (which is rather a bad practice).

When you want to restore from backup more that one table that are constrained by foreign keys, you can disable FK checks before TRUNCATE+INSERT and enable it back when you're done. Details can be found here: Truncate all tables (most of which have constraints). How to temporarily drop them

Create new MySql schema from Backup .SQL source file resets auto increment id columns?

3 Answers3