I want to backup a mysql database. The easiest approach is using mysqldump with its default options, i.e.,

# mysqldump -u user -h host -ppass db > backup.sql

This will dump the full DDL and DML needed to re-create the database. Table locking is enabled by default, although for InnoDB it is recommended to use the –single-transaction option so that the entire backup will be contained in one transaction (the default option locks each table prior to backing up).

You can also backup specific tables, i.e.,

# mysqldump -u user -h host -ppass db table > backup.sql

Since mysqldump outputs to stdout, you can pipe the output to a separate mysql server, in this case a dedicated backup database:

# mysqldump -u user -h host -ppass db | mysql -u user -h host -ppass -C backup-db

If you have a very large database you can use gzip to compress, i.e.,

# mysqldump -u user -h host -ppass db | gzip -9 -c > backup.sql.gz

Export

I would like to export data from mysql into a spreadsheet application. Most spreadsheet applications can read CSV (comma-separated-value) files and mysqldump can be used to create CSV output of your tables.

# mysqldump -u user -ppass \
  -T /tmp --fields-enclosed-by=\" --fields-terminated-by=, db

This approach assumes you are running mysqldump on the database server, it leverages the OUTFILE functionality, and (for each table) is equivalent to the following sql:

mysql> SELECT *
    -> INTO OUTFILE '/tmp/foobar_users.csv'
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '"'
    -> ESCAPED BY '\\'
    -> LINES TERMINATED BY '\n'
    -> FROM foobar_users;

If you do not have access to the database servers filesystem (or do not want to touch it), you can use some shell magic to convert mysql output into csv, e.g.,

# mysql -u user -h host -ppass -ss \
  -e "SELECT * FROM foobar_users" db \
  | sed 's/\t/","/g;s/^/"/;s/$/"/' > foobar_users.csv

The sed command is switching unescaped tabs to “,” (including the double quotes), and adding a double quote to the beginning and end of the line.

Restore

I would like to restore a mysql database from a previous export.

The output from mysqldump with default options can be sent directly into mysql as follows:

# mysql -u user -h host -ppass -C db < backup.sql

If you have a compressed output file and lack the disk space to uncompress, you can use zcat and pipe the uncompressed backup into mysql, e.g.,

# zcat backup.sql.gz | mysql -u user -h host -ppass -C db