Table dump order with mysqldump on InnoDB
Posted: Fri Apr 04, 2003 3:49 am
I use: If db_name has only InnoDB tables with Foreign keys like "cascade on delete" between them, the output will be broken, in case I want to restore the data.
mysqldump sends the table dumps sorted alphabetically, for example:Let's say that GroupUserConnect has a foreign key to Users.UserID. This means that a restore tries to insert data into GroupUserConnect with UserID's not yet present in Users.
ERROR!
I solve this by listing the tables in the shell script in the correct order:which will work just fine.
BUT, since development is ongoing, I might add some tables to the database which I might forget to add to that backup dump shell script... INTEGRITY VIOLATION!
Is there any way of getting mysqldump to take sorting actions for the dump based on foreign key information, so that complete database dumps will automatically always be correct?
Code: Select all
mysqldump db_namemysqldump sends the table dumps sorted alphabetically, for example:
Code: Select all
AccessLog
Groups
GroupUserConnect
UsersERROR!
I solve this by listing the tables in the shell script in the correct order:
Code: Select all
mysqldump db_name AccessLog Users Groups GroupUserConnectBUT, since development is ongoing, I might add some tables to the database which I might forget to add to that backup dump shell script... INTEGRITY VIOLATION!
Is there any way of getting mysqldump to take sorting actions for the dump based on foreign key information, so that complete database dumps will automatically always be correct?