Command-Line MySQL Backups

Using tools like phpMyAdmin (or better yet, Adminer) for MySQL backups can be convenient, but sometimes it doesn’t work so well, such as when you need to work with dozens of databases at the same time, or if you have a database that is too large. So it’s good to know how to do it from the command-line.

First, some basics. All the mysql tools accept a username and password using the following pattern.

# Leave off the "{" and "}"
# Also note there is no space after -p and before your password
mysql -u{username} -p{password}
mysqldump -u{username} -p{password}

If any parameters are missing, your defaults are used. You can find (or edit) those defaults in your ~/.my.cnf file. (Where ~/ is your home directory; e.g. /home/tylerl/.my.cnf).

# File /root/.my.cnf
[client]
user="username"
pass="password"

Now that we’ve got that taken care of, here’s the basic backup and restore syntax. Obviously, fill in databasename, databasefile, etc., with your real names. And remember you can also specify a username and password as above.

#Backup:
mysqldump databasename > databasefile.sql

#Restore:
mysql databasename < databasefile.sql

#If a database by that name doesn't already exist before restoring:
mysql -e "create database databasename"
mysql databasename < databasefile.sql

#Backup partial database:
mysqldump databasename table1 table2 table3 > partialdatabase.sql

How about compression? Easy — just gzip it on the way out:

# Backup
mysqldump databasename | gzip > databasefile.sql.gz

# Restore (just switch things around)
gunzip -c databasefile.sql.gz | mysql databasename

You can dump an entire database set (all DBs) into one big file by using -A. Like so:

mysqldump -A > entiredb.sql

Since the database names are embedded in the file, you don’t specify a DB name on the command line to restore:

mysql < entiredb.sql

Want to backup all the databases into separate files?

# skip information_schema, since it's not a real DB
while read DBNAME ; do
    [[ $DBNAME == "information_schema" ]] && continue
    mysqldump $DBNAME > $DBNAME.sql
done < <(mysql -B --skip-column-names -e "show databases")

And then to restore everything:

for DBFILE in *.sql ; do
    DBNAME=${DBFILE%.sql}
    mysql -e "create database if not exists $DBNAME"
    mysql $DBNAME < $DBFILE
done

Ah! Shell scripting in all its glory.