List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 18 2003 3:27am
Subject:Re: mysqldump with innoDB
View as plain text  
At 10:33 AM +1000 9/18/03, Daniel Kasak wrote:
>Paul DuBois wrote:
>
>>If you have problems reloading the table due to the order
>>in which the InnoDB tables appear in the dump files, add
>>
>>SET FOREIGN_KEY_CHECKS = 0;
>>
>>to the beginning of the file before reloading it.
>>
>Our backups are quick large - over 500 MB. Opening the file and 
>adding the above line at the top takes a lot of CPU time and memory 
>- and when I'm restoring, I don't have a lot of time...
>Is there an easier way to get it there - can I 'cat' to the 
>beginning of a file, or should I make my backup scripts cat the 
>output of mysqldump to the end of a file with 'set 
>foreign_key_check=0;' at the top? Maybe we could have a switch for 
>mysqldump that does this for us?

You can use SOURCE:

shell> mysql [plus any other options you need here]
mysql> SET FOREIGN_KEY_CHECK = 0;
mysql> SOURCE your_dump_file;

Or a cat trick:

shell> echo "SET FOREIGN_KEY_CHECK = 0;" | cat - your_dump_file | mysql

mysqldump will add the SET statement to its output in MySQL 4.1.1,
so this messing around won't be necessary.


-- 
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

Are you MySQL certified?  http://www.mysql.com/certification/

Thread
mysqldump with innoDBIrwin Boutboul17 Sep
  • Re: mysqldump with innoDBPaul DuBois17 Sep
    • Re: mysqldump with innoDBDaniel Kasak18 Sep
      • Re: mysqldump with innoDBPaul DuBois18 Sep
      • Re: mysqldump with innoDBFranky18 Sep
  • Re: mysqldump with innoDBHarald Fuchs18 Sep
  • Large number of DatabasesRichard18 Sep
  • Re: Large number of DatabasesHarald Fuchs18 Sep
    • Re: Large number of DatabasesGustavo A. Baratto18 Sep
      • Re: Large number of DatabasesJeremy Zawodny18 Sep
        • Re: Large number of DatabasesGustavo A. Baratto18 Sep
          • Re: Large number of DatabasesDan Nelson18 Sep
          • Re: Large number of DatabasesJeremy Zawodny18 Sep