{"id":212,"date":"2011-07-05T05:59:13","date_gmt":"2011-07-05T05:59:13","guid":{"rendered":"http:\/\/tech.avant.net\/q\/?p=212"},"modified":"2012-12-25T22:39:51","modified_gmt":"2012-12-25T22:39:51","slug":"mysqldump","status":"publish","type":"post","link":"https:\/\/tech.avant.net\/q\/mysqldump\/","title":{"rendered":"mysqldump, tips and tricks"},"content":{"rendered":"

I want to backup a mysql database. The easiest approach is using mysqldump<\/em> with its default options, i.e.,<\/p>\n

\r\n# mysqldump -u user<\/em> -h host<\/em> -ppass<\/em> db<\/em> > backup.sql\r\n<\/pre>\n
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<\/em> option so that the entire backup will be contained in one transaction (the default option locks each table prior to backing up).<\/p>\n
You can also backup specific tables, i.e.,<\/p>\n
\r\n# mysqldump -u user<\/em> -h host<\/em> -ppass<\/em> db<\/em> table<\/em> > backup.sql\r\n<\/pre>\n
Since mysqldump<\/em> outputs to stdout, you can pipe the output to a separate mysql server, in this case a dedicated backup database:<\/p>\n
\r\n# mysqldump -u user<\/em> -h host<\/em> -ppass<\/em> db<\/em> | mysql -u user<\/em> -h host<\/em> -ppass<\/em> -C backup-db<\/em>\r\n<\/pre>\n
If you have a very large database you can use gzip to compress, i.e.,<\/p>\n
\r\n# mysqldump -u user<\/em> -h host<\/em> -ppass<\/em> db<\/em> | gzip -9 -c > backup.sql.gz\r\n<\/pre>\n
Export<\/h2>\n
I would like to export data from mysql into a spreadsheet application. Most spreadsheet applications can read CSV (comma-separated-value) files and mysqldump<\/em> can be used to create CSV output of your tables.<\/p>\n
\r\n# mysqldump -u user<\/em> -ppass<\/em> \\\r\n  -T \/tmp --fields-enclosed-by=\\\" --fields-terminated-by=, db<\/em>\r\n<\/pre>\n
This approach assumes you are running mysqldump<\/em> on the database server, it leverages the OUTFILE functionality, and (for each table) is equivalent to the following sql:<\/p>\n
\r\nmysql> SELECT *\r\n    -> INTO OUTFILE '\/tmp\/foobar_users.csv'\r\n    -> FIELDS TERMINATED BY ','\r\n    -> ENCLOSED BY '\"'\r\n    -> ESCAPED BY '\\\\'\r\n    -> LINES TERMINATED BY '\\n'\r\n    -> FROM foobar_users;\r\n<\/pre>\n
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.,<\/p>\n
\r\n# mysql -u user<\/em> -h host<\/em> -ppass<\/em> -ss \\\r\n  -e \"SELECT * FROM foobar_users\" db<\/em> \\\r\n  | sed 's\/\\t\/\",\"\/g;s\/^\/\"\/;s\/$\/\"\/' > foobar_users.csv\r\n<\/pre>\n
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.<\/p>\n
Restore<\/h2>\n
I would like to restore a mysql database from a previous export.<\/p>\n
The output from mysqldump<\/em> with default options can be sent directly into mysql as follows:<\/p>\n
\r\n# mysql -u user<\/em> -h host<\/em> -ppass<\/em> -C db<\/em> < backup.sql\r\n<\/pre>\n
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.,<\/p>\n
\r\n# zcat backup.sql.gz | mysql -u user<\/em> -h host<\/em> -ppass<\/em> -C db<\/em>\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[15,14],"tags":[],"_links":{"self":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/212"}],"collection":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/comments?post=212"}],"version-history":[{"count":10,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/212\/revisions"}],"predecessor-version":[{"id":724,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/212\/revisions\/724"}],"wp:attachment":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/media?parent=212"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/categories?post=212"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/tags?post=212"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}