How to backup MySQL database?

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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.