MySQL Backup

[Garry-UK]

Hi,

I would like to backup some MySQL databases on my VPS every hour or 2, then rsync it to a backup server, how would I go about doing this. I think I read somewhere you shouldn't do a direct rsync as the tables would not be locked or something like that. So I have done some looking around and come up with:

mysqlhotcopy - with this I noticed it creates: frm , MYI and MYD
How would I restore these. Would I stop mysql and copy them into username_database and start mysql, would I need to do any repair as the backup be either hourly or every 2 hours

mysqldump - with this I noticed it create an sql file with all the info in it, the only problem I see with this, if I want to restore just one table I would need to remove it from the database then replace it.

What is the best way ?

phpMyAdmin says: MyISAM is the default storage engine on this MySQL server.

The databases are small at the moment but hopefully the site will go well, which mean the database will get bigger.

[jayh38]

MysqlHotCopy,
Yes you would restore the files just as they are. I would suggest stopping mysql first so you retain the current backup integrity while restoring.

MysqlDump,
This is probably the most preferred method. But for dumping tables and restoring tables, this could be a daunting task if you have many files. So here is a very nice script which will dump and restore and works like a dream.

Dump script
Restore script

Of course you may want to add some option such as
--databases --lock-tables

Hope this gets you started.

[Garry-UK]

Hi,

Thanks for those links
Doesn't mysqldump use lock tables or lock database as default ?

[jayh38]

Hmm.. I don't think it uses --lock-tables as default but regardless, I like to be sure of it. My common dump line would be something like this:

mysqldump --opt --lock-tables --databases database_name > dumpfile.sql

Without --databases you would need to create the empty database first if it doesn't exist.

For database table dumps, I simply use the script.

[Garry-UK]

Hi,

Thanks for the info.
One more questions should I be using: --add-drop-table

o --add-drop-table
Add a DROP TABLE statement before each CREATE TABLE statement.

As these backups I am taking, should only be used in an emergency.

[jayh38]

No, theres no need for that. If you use --databases the entire database is dropped and recreated if it exists.

I just confirmed that these statements:
DROP TABLE IF EXISTS and
LOCK TABLES statements are by default included in the mysqldump with no options.

Now the LOCK TABLES is for restoring, not for dumping. So to keep your data in sync, you would want to cut off apache and cronjobs during your restore since it would lock only the current table that its restoring.

Or if you have many accounts that you don't want to interrupt, I simply rename their virtual container until the restore of their database is complete (if restoring manually) otherwise a cpanel restore would take care of its self.

[cpanelkenneth]

One should always LOCK InnoDB tables when dumping and restoring. For such, the use of --single-transaction is better than --lock-tables. The Locks applied are READ locks, which allow data to be SELECTed but not modified (e.g. INSERT, DELETE).

There is some excellent information on the subject in the MySQL manual:

http://dev.mysql.com/doc/refman/5.1/en/backup.html