MySQL ODBC Connector - Backup / Restore database with foreign keys

Author

Backup / Restore database with foreign keys

Daniel Kasak

2005-09-29, 8:23 pm

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: dkasak@nusconsulting
.com.au
website: http://www.nusconsulting.com.au

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Michael Stassen

2005-09-29, 8:23 pm

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/i...onstraints.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/mysql? unsub...sie.nctu.edu.tw

Matthew Lenz

2005-09-29, 8:23 pm

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" < dkasak@nusconsulting
.com.au>
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: dkasak@nusconsulting
.com.au
> website: http://www.nusconsulting.com.au
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql? unsub...r
nal.org
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Daniel Kasak

2005-09-30, 3:23 am

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: dkasak@nusconsulting
.com.au
website: http://www.nusconsulting.com.au

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links