How to backup MySQL database? - using mysqldump -

MySQL is one of the most used Databases in Linux, (if not the most).

It is a great Database engine, but anyway it could have problems, or your server, if it goes out of power for instance, you could loose a lot of data, so backing up is always a good idea.

A good command for doing this is mysqldump, this is a Linux shell mysql command, so you may need ssh access to the server to run it. (Later I will publish a php version of this)

What mysqldump really does is to create a script with the structure and the data to create and populate a "restored" Database with the data of the "backed up" Database

So let's go to the How To.

The most simple way is to issue this command:

mysqldump -u [user] -p [database_name] > [backupfile].dump

This command is going to ask you for the [user] password and then will create a script which later can be used to retore the data.

Another way is to use the optimized way.

mysqldump --opt -u [user_name] -p [database_name] > [backup_file].dump

This command will use an optimized method, and will include in the script MySQL commands that will erase (drop) tables that already exists and create them again before populate the data inside.

Maybe the best way to run this command is to use the option of gzip the output file. (for obvious reasons)

mysqldump --opt -u [user_name] -p [database_name] | gzip > [backup_file].dump.gz

Once you have your backup file, you may want to restore it someday, this is the way to do it. (remember tu unzip your file, if zipped, before). Run the following command from normal command promt:

mysql [database_name] < [backup_file].dump
Remeber that you can run

man mysqldump
for more help.


Source:
http://www.go2linux.org/mysql-backup-mysqldump