LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - General
User Name
Password
Linux - General This Linux forum is for general Linux questions and discussion.
If it is Linux Related and doesn't seem to fit in any other forum then this is the place.

Notices

Reply
 
Search this Thread
Old 01-14-2008, 10:14 PM   #1
Swakoo
Member
 
Registered: Apr 2005
Distribution: Red Hat / Fedora / CentOS
Posts: 508

Rep: Reputation: 30
Best practice for backing up MySQL?


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
 
Old 01-14-2008, 10:22 PM   #2
trickykid
Guru
 
Registered: Jan 2001
Posts: 24,133

Rep: Reputation: 197Reputation: 197
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.
 
Old 01-15-2008, 08:21 AM   #3
choogendyk
Senior Member
 
Registered: Aug 2007
Location: Massachusetts, USA
Distribution: Solaris 9 & 10, Mac OS X, Ubuntu Server
Posts: 1,189

Rep: Reputation: 105Reputation: 105
"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
 
Old 01-15-2008, 10:22 AM   #4
trickykid
Guru
 
Registered: Jan 2001
Posts: 24,133

Rep: Reputation: 197Reputation: 197
For anyone who has to deal with backups, I recommend the Backup and Recovery from O'Reilly.
 
Old 01-16-2008, 12:54 PM   #5
popowich
Member
 
Registered: Nov 2004
Location: Rochester, New York
Distribution: RHEL5
Posts: 53

Rep: Reputation: 15
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
 
Old 01-16-2008, 08:08 PM   #6
choogendyk
Senior Member
 
Registered: Aug 2007
Location: Massachusetts, USA
Distribution: Solaris 9 & 10, Mac OS X, Ubuntu Server
Posts: 1,189

Rep: Reputation: 105Reputation: 105
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.
 
Old 01-17-2008, 03:45 AM   #7
Swakoo
Member
 
Registered: Apr 2005
Distribution: Red Hat / Fedora / CentOS
Posts: 508

Original Poster
Rep: Reputation: 30
hi guys,

thanks for the wonderful insights!
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Rsync not backing up MySQL cabron Linux - Server 3 12-26-2007 07:56 AM
Backing up MySQL without the dumping jme Linux - Software 8 05-27-2006 12:01 AM
Backing up mysql without it actually being on c00l Linux - General 1 03-12-2005 01:03 PM
Backing up MySql bifur Linux - Software 8 01-05-2005 07:30 PM
backing up MySQL dbase DropHit Linux - Software 5 05-04-2004 03:19 PM


All times are GMT -5. The time now is 08:17 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration