Backups and MySQL

Mr. Brightside · 29 Oct 2006 at 14:44

I've just setup a backup system for my server using rsync, that backs up the whole filesystem. However, I'm not sure about MySQL - my question is, if the files stored on disc are safe, can they be reloaded into a new MySQL setup if necessary, or should I be exporting them into some other format for backup?

This is on Debian Etch btw.

Thanks, null

Beansprout · 29 Oct 2006 at 14:53

I'd use a tool like mysqldump to get a .sql backup file

Mr. Brightside · 29 Oct 2006 at 15:50

Thanks for the reply Beansprout

I've had a play and came up with this:

mysqldump --all-databases --user root --password=password > /home/user/mysqlbackup.sql

I then made an entry in cron by running crontab -e, to run that command at 1am daily - just before the filesystem backup runs. Does this all look good or have I made mistakes? (Still very new to Linux).

Thanks, null

Beansprout · 29 Oct 2006 at 16:08

Looks good - try it and see

Daniel · 29 Oct 2006 at 16:36

Put this into a cron for the time you want. The gzip bit will give you a smaller backup file...100MB raw mysql will be something like 20MB as gzip

mysqldump -username -password databasename | gzip > /home/username/mysqlbackup.sql.gz

Dan

Mr. Brightside · 29 Oct 2006 at 18:35

Just edited my crontab to include the gzipping. I tried it out aswell and it did indeed make a big difference - 83mB uncompressed to 19mB compressed.

Thanks, null