Backup Mysql Schema With Foreign Key Table Constraints

I created my MySQL schema that consists of multiple tables and I decided that I would add the foreign key constraints afterwards for each table, using the command: ALTER TABLE Orde

Solution 1:

mysqldump create the dump of foreign keys as well... it adds syntax like:

mysql> SET foreign_key_checks = 0;mysql> SOURCE dump_file_name;mysql> SET foreign_key_checks = 1;

You can read the manual at: http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html for mysqldump of foreign keys

Solution 2:

I come across this often. This is how I do it so I can use 'pv' from the command line to get a progress bar on the sql dump while it restores:

{ echo"SET foreign_key_checks=0;";pv sql.gz; echo"SET foreign_key_checks=1;"; } | mysql dbname

(I put the several commands in { } so that its treated like a single command when being piped to mysql while retaining the progress bar from 'pv')