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
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.
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