MySQL import LOCK doesn't prevent PRIMARY key issues I am using Django and only want to backup mysql data (not it's structure; Django will do that, and it will otherwise mess up Django-South's migration checks).

Some info;


my tables are almost all intertwined with each other by Foreign Key bindings.
I'm currently having over 100 tables that hold all my data.


What I'm trying to achieve is some mechanism with mysqldump to dump my data, like so;

imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysqldump -u root -p my_database --no-create-info &gt; my_database_data.sql


..and restore it like so;

imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysql -u root -p my_database &lt; my_database_data.sql


..but then with the ability to store the following on the dump (and thus restore it that way on import);

When doing mysqldump


Store the PRIMARY key counters


When importing my_database_data.sql


LOCK the tables fully (even ignoring PRIMARY key counter checks)
INSERT all data (incl. primary keys)
Set the PRIMARY key counter back to what was saved
UNLOCK the tables again


Other issue

My first thought was to just ignore the PRIMARY keys somehow and just look-increment them again on insertion. The problem is that I can't do this due to their bindings with Foreign Keys, that would then thus break.
Whenever you dump a mysql table that has an AUTO_INCREMENT column, the next value is always attached to the definition of the table. You should see something like:
) ENGINE=InnoDB AUTO_INCREMENT=&lt;some-number&gt; ...

You may want to consider one of three(3) things
SUGGESTION #1 : mysqldump the database with table structure
imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysqldump -u root -p my_database &gt; my_database_data.sql

That way, the auto_increment is attached to the table definition
SUGGESTION #2 : mysqldump the data and table structure separately
imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysqldump -u root -p my_database --no-create-info &gt; my_database_data.sql
imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysqldump -u root -p my_database --no-data &gt; my_database_schema.sql

You would load my_database_schema.sql first, then my_database_data.sql
SUGGESTION #3 : Read the next auto_increment of every table into a script
You can use a query like this to make the SQL script:
SELECT CONCAT('TRUNCATE TABLE ',db,'.',tb,'; ALTER TABLE ',db,'.',tb,' AUTO_INCREMENT=',autoinc,';')
FROM (SELECT table_schema db,table_name tb ,auto_increment autoinc
FROM information_schema.tables WHERE table_schema='my_database') A;

In the OS, you can do it like this:
SQLSTMT=&quot;SELECT CONCAT('TRUNCATE TABLE ',db,'.',tb,'; ALTER TABLE ',db,'.',tb,'AUTO_INCREMENT=',autoinc,';')&quot;
SQLSTMT=&quot;${SQLSTMT} FROM (SELECT table_schema db,table_name tb ,auto_increment autoinc&quot;
SQLSTMT=&quot;${SQLSTMT} FROM information_schema.tables WHERE table_schema='my_database') A;&quot;
mysql -uroot -p -Ane&quot;${SQLSTMT}&quot; &gt; autoinc.sql

Simply run autoinc.sql before my_database_data.sql
Give it a Try !!!