MySQL import LOCK doesn’t prevent PRIMARY key issues

constraintinnodbMySQLmysqldump

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 > my_database_data.sql

..and restore it like so;

imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysql -u root -p my_database < 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

  1. Store the PRIMARY key counters

When importing my_database_data.sql

  1. LOCK the tables fully (even ignoring PRIMARY key counter checks)
  2. INSERT all data (incl. primary keys)
  3. Set the PRIMARY key counter back to what was saved
  4. 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.

Best Answer

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=<some-number> ...

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 > 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 > my_database_data.sql
imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysqldump -u root -p my_database --no-data > 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="SELECT CONCAT('TRUNCATE TABLE ',db,'.',tb,'; ALTER TABLE ',db,'.',tb,'AUTO_INCREMENT=',autoinc,';')"
SQLSTMT="${SQLSTMT} FROM (SELECT table_schema db,table_name tb ,auto_increment autoinc"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE table_schema='my_database') A;"
mysql -uroot -p -Ane"${SQLSTMT}" > autoinc.sql

Simply run autoinc.sql before my_database_data.sql

Give it a Try !!!

Related Question