Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Is there a way to backup an MySQL database so that if something wrong occurs, it will be easy to recreate the tables with the same data?

share|improve this question

migrated from stackoverflow.com Apr 1 '10 at 0:53

This question came from our site for professional and enthusiast programmers.

    
Use mysqldump. But in its current form should go to serverfault.com –  ChristopheD Mar 31 '10 at 22:47
add comment

3 Answers

Yes, there are lots of ways. There is a page in the documentation which summarizes many of the available methods.

For example, you could take a look at:

mysqldump db_name > dump_file
share|improve this answer
add comment

I have to agree with the

mysqldump db_name > dump_file

option. Just be warned, if you automate it, you will need to add the user name and password to the script for it run properly.

share|improve this answer
1  
You can avoid passing the username and password on the command line by setting $HOME to a directory containing an appropriately-arranged .my.cnf or --defaults-extra-file if it's available. –  Gerald Combs Oct 27 '11 at 22:48
add comment

mysqldump for quick and easy dumps of the database. But if you manage a few database servers and have a regular backup schedule you might try mysql-zrm which is a more sophisticated flexible approach.

http://www.zmanda.com/backup-mysql.html

It's a pain in the arse to setup but once you do you can easily search for and find individual backups based on date and other criteria. you can restore entire databases or just individual tables. you'll thank yourself if you take the time to do this and you have more than a simple need for an occasional backup to perform.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.