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.
mysqldump --no-data --databases Customers Orders Comments> structurebackup.sql
Compressing your Backup file on the Fly
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.
mysqldump --all-databases | bzip2 -c>databasebackup.sql.bz2
mysqldump --all-databases | gzip>databasebackup.sql.gz
A Shell Script for Automating Backups?
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.
#!/bin/sh
date=`date -I`
mysqldump --all-databases | gzip> /var/backup/backup-$date.sql.gz
Now that you've got backups of your database, let's learn how to restore your backup in case your database goes down. Here's how you can restore your backed up database using the mysql command.
Restore using mysql
If you have to re-build your database from scratch, you can easily restore the mysqldump file by using the mysql command. This method is usually used to recreate or rebuild the database from scratch.
Here's how you would restore your custback.sql file to the Customers database.
mysql -u sadmin -p pass21 Customers < custback.sql Easy isn't it ? Here's the general format you would follow: mysql -u [username] -p [password] [database_to_restore] < [backupfile] Now how about those zipped files? You can restore your zipped backup files by first uncompressing its contents and then sending it to mysql. gunzip < custback.sql.sql.gz | mysql -u sadmin -p pass21 Customers You can also combine two or more backup files to restore at the same time, using the cat command. Here's how you can do that. cat backup1.sql backup.sql | mysql -u sadmin -p pass21