Hi guys,

currently I am doing mysql backup via 2 ways

1) copying every MYI, MYD, FRM files from the respective database directory in /var/lib/mysql/...

the 2 downside of this I can think of is
- might be version dependent if i need to restore
- would not be accurate if write is allowed concurrently

2) using mysqldump

- one issue is that the restore is way much slower. but i do believe this is a cleaner way of backing up.

Are there any things I should note of using either ways? Is there any preferred or better ways you guys would recommend? I'm just looking into better ways of doing this

Actually just doing a raw file backup of everything in /var/lib/mysql is only accurate if you shutdown mysql first, then back up the files. Backing these up while the database is still active can lead to inconsistent data and or just result in a corrupted database.

mysqldump is the preferred way. Depending on the size and what type of engine for the tables you're using, it can be fast or slow. Also take note this can also lead to inconsistent data dumps unless you lock the tables for writing while dumping. One tool that can be used to make administering your mysql backups is mysql-zrm from the amanda folks at zmanda.com. Either find a 3rd party tool or write yourself a custom script using mysqldump.

"Best Practices" is a term that cracks me up. Too often it is a marketing term with a "white paper" by someone who has something to sell. I don't use the term.

Anyway, what trickykid says is right. It also depends some on your database type (storage engine) -- MyISAM, InnoDB, etc. And then there is transaction logging. Which can get you incrementals. All of that is incorporated into Zmanda Recovery Manager (ZRM) for MySQL which is free open source software. You can get it at http://mysqlbackup.zmanda.com/ . Corporate clients can also get installation and support contracts from Zmanda. That's why the top level of their website looks commercial. They are a business, but they are also based on and support the open source community around Amanda.

MySQL backups are also covered on the mysql website, e.g. http://dev.mysql.com/doc/refman/5.0/en/backup.html , and in the O'Reilly book "Backup & Recovery" which has a companion website at http://www.backupcentral.com

For anyone who has to deal with backups, I recommend the Backup and Recovery from O'Reilly.

On my server I have dumps of the databases written to a simple /usr/local/mysql-backups directory.

If my vBulletin explodes I can get last nights MySQL from there.

Those dumps get picked up by tape, so I can get from tapes if the server has an issue.

-Raymond

Yup, "Backup & Recovery" is a real resource. I have a few gripes with it, but mostly it's a great reference I still go to regularly.

One thing you have to watch with any printed book is it's date. This one is January 2007, which is relatively new, but already a year old. Add to that the processing time at the publisher, and you're probably back a year and a half, to, say July 2006. Some things are stable over that span, but the open source community doesn't stand still, and that's why the author set up the companion website I pointed to in my earlier message.

Assuming the book was finished and at the publishers in July, 2006, Zmanda was not quite 2 years old. They were mentioned in the book in a paragraph that started, "Amanda is the only open-source backup software with enterprise support, available as a subscription from Zmanda, Inc." However, in the year and a half since then, it has become more clear that Zmanda has energized the ongoing development of Amanda. Also, in August of 2006, presumably after the book was at the publishers, Zmanda released version 1.0 of ZRM for MySQL. So, the book totally missed that, and you now see references even on other backup software lists that ZRM is really cool. It manages the backup and recovery of MySQL to disk, and any bakcup software you choose can then cycle that to tape.

So, that's sort of a lengthy way of saying, yup, the book is a really good reference, but . . .

It will give you most of the background you want on backing up a MySQL database, but it will miss the one open source tool that integrates it all for you. That tool just wasn't out when the book was sent to the publisher.

hi guys,

thanks for the wonderful insights!