HomeMySQL Page 2 - Backing Up and Restoring Your MySQL Database
What about Multiple Databases? - MySQL
Do you need to change your web host or switch your database server? This is probably the only time when you really think of backing up your MySQL data. If you've got a website with a database or your custom database running for your applications, it is imperative that you make regular backups of the database. In this article, I will outline two easy ways of backing up and restoring databases in MySQL.
If you are a database administrator who has to look after multiple databases, you'll need to back up more than one database at a time. Here's how you can backup multiple databases in one shot.
If you want to specify the databases to backup, you can use the --databases parameter followed by the list of databases you would like to backup. Each database name has to be separated by at least one space when you type in the command. So if you have to backup 3 databases, let say Customers, Orders and Comments, you can issue the following command to back them up. Make sure the username you specify has permissions to access the databases you would like to backup.
This is okay if you have a small set of databases you want to backup. Now how about backing up all the databases in the server? That's an easy one, just use the --all-databases parameter to backup all the databases in the server in one step.
mysqldump --all-databases> alldatabases.sql
Backing up only the Database Structure
Most developers need to backup only the database structure to while they are developing their applications. You can backup only the database structure by telling mysqldump not to back up the data. You can do this by using the --no-data parameter when you call mysqldump.
Backups of databases take up a lot of space. You can compress the output of mysqldump to save valuable space while you're backing up your databases. Since mysqldump sends its output to the console, we can pipe the output through gzip or bzip2 and send the compressed dump to the backup file. Here's how you would do that with bzip2 and gzip respectively.
You can automate the backup process by making a small shell script which will create a daily backup file. How do you get cron to back up your database without overwriting the older backup? You can use a tiny shell script to add the date to your backup file. An example of a shell script you could use is shown below.