Backup and Restore with MySQL

There's a lot of ways to backup a MySQL database. I've had to look this up from time to time. And I have not found anyone take these scenarios and boil them down into a short concise guide. Enjoy.

Backup

The simple way of backing up a MySQL database.

$ mysqldump -u [username] -p [password] [database_name] > [backup_filename.sql]

What if you want to restore an existing database? You can add 'drop table' statements to your backup like so...

$ mysqldump --add-drop-table -u [username] -p [password] [database_name] > [backup_filename.sql]

What if you just wanted a table and not the whole database? Yes, you can do that too...

$ mysqldump --add-drop-table -u [username] -p [password] [database_name] [table1 table2 ...] > [backup_filename.sql]

More than one database to backup?

$ mysqldump -u [username] -p [password] --databases [database1 database2 ...] > [backup_filename.sql]

Need to backup all databases in a MySQL instance?

$ mysqldump --all-databases > [backup_filename.sql]

How about the database, but no data please. Coming right up!

$ mysqldump --no-data --all-databases > [backup_filename.sql]

Automating Backups

Need to automate your MySQL backup? There's a script for that...

#!/bin/sh
date=`date -I`
mysqldump --all-databases | gzip > /some/path/backup-db-name-$date.sql.gz 

Restore

The way to restore a MySQL database.

$ mysql -u [username] -p [password] [database_name_to_restore] < [backup_filename.sql]