General discussion


Download: Backing up and restoring MySQL databases

By Mark W. Kaelin Editor ·

We all acknowledge the importance of backing up important data, but when was the last time you performed a backup? When you create your MySQL database backups, what steps do you take; can you add to the steps listed in the download?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

A completely different way

by Tony Hopkinson In reply to Download: Backing up and ...

About 45 minutes ago.
I don't use mysqldump though.
Straight copy of the files, zipped up and dumped to mirrored raid set and a non raid drive. Goes every day and to dvd once a week.
This is on a slave server in a replication set. Entire operation is perl scripted, logged and cron'd.
Another cron job, checks on the replication and reads in the log file and checks to see if it's up to date. Emails supervisor group on failure, and also on success for the dvd. Even reminds me to change it and knows when I haven't.
Going to have to play about a bit more when the dbs go over 4Gig zipped up.

Collapse -

Doesn't always work with InnoDB tables

by Island Bound In reply to Download: Backing up and ...

I've found the backup in the article, mysqldump, is fast and works well. However, when restoring InnoDB tables, it can be problematic. I've found that the backup creates the restore script with the tables in alphabetical order which will seldom work when foreign key constraints are involved. The restore script drops the table, rebuilds the table, and then loads the table with your backed up data. With constraints, you cannot drop a table that is involved in a foreign key constraint as the parent until the child is dropped or the constraint is dropped. A similar situation exists with the Create Table command: If you're trying to build a child table with a foreign key constraint before the parent table is build, the restore will fail.

To make the restore work, I've had to move the Drop Table commands to the top of the script, re-order the Drops to ensure children are dropped before parents, and re-order the Create Table commands so as to build parents before children. Obviously, this will take time that you don't always have when in a restore situation.

I would like to think that there is a better way somewhere out there on the market but I've not yet found it.

Collapse -

MS SQL Server 6.0

by Tony Hopkinson In reply to Doesn't always work with ...

did that trick as well. If you used it's scripting facilities it got the drops or the creates in the wrong order, can't remember which I'm still too annoyed about it.
Why is everybody mad keen one dumping the data and structure as SQL anyway ?

Related Discussions

Related Forums