Backup / Restore database with foreign keys
Greetings. I've just hit an interesting problem. Luckily I don't actually *need* to restore from a backup right now - I'm just trying to create a database dump to submit an unrelated bug report. Anyway ... I'm using the command: mysqldump -K DATABASE_NAME db.sql -p However when I create a new database and try to load the dump file: mysql NEW_DATABASE db.sql -p I get an error when I hit an InnoDB table that has a relationship set up with a table that hasn't yet been created. How do I get around this? -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup / Restore database with foreign keys
Daniel Kasak wrote: Greetings. I've just hit an interesting problem. Luckily I don't actually *need* to restore from a backup right now - I'm just trying to create a database dump to submit an unrelated bug report. Anyway ... I'm using the command: mysqldump -K DATABASE_NAME db.sql -p However when I create a new database and try to load the dump file: mysql NEW_DATABASE db.sql -p I get an error when I hit an InnoDB table that has a relationship set up with a table that hasn't yet been created. How do I get around this? Before loading the file, SET FOREIGN_KEY_CHECKS = 0; after loading the file, SET FOREIGN_KEY_CHECKS = 1; Better yet, edit the dump file to place those as the first line and last lines, respectively. Even better, upgrade to a newer mysql (4.1.1+), where they are automatically added to the dump file for you. See the manual for more http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html (way down at the end). Michael Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup / Restore database with foreign keys
i think you can use -K on your mysqldump and it'll put the hints in there for the mysql command to use as well - Original Message - From: Daniel Kasak [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, September 29, 2005 7:45 PM Subject: Backup / Restore database with foreign keys Greetings. I've just hit an interesting problem. Luckily I don't actually *need* to restore from a backup right now - I'm just trying to create a database dump to submit an unrelated bug report. Anyway ... I'm using the command: mysqldump -K DATABASE_NAME db.sql -p However when I create a new database and try to load the dump file: mysql NEW_DATABASE db.sql -p I get an error when I hit an InnoDB table that has a relationship set up with a table that hasn't yet been created. How do I get around this? -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup / Restore database with foreign keys
Michael Stassen wrote: Before loading the file, SET FOREIGN_KEY_CHECKS = 0; after loading the file, SET FOREIGN_KEY_CHECKS = 1; That's it! Thanks :) Even better, upgrade to a newer mysql (4.1.1+), where they are automatically added to the dump file for you. Not until the client libraries are ready. I don't feel 'right' about hacking up the place with --old-password options and such. Also, my Gentoo server ( stable branch ) insists that 4.0.x is the latest that I can expect to install without breaking things. After doing some testing on my workstation, I tend to agree - getting everything compiled against 4.1.x is a major pain, and certainly not something I'm about to do at the moment with no real advantages on offer - I'm just likely to break something and be very sorry. Once all the questions about: Client does not support authentication protocol requested by server; consider upgrading MySQL client have died down, *then* it's time to upgrade the server. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
