Searching around for instructions on how to properly backup a MySQL database will often lead you down a dark path filled with outdated, incomplete, or just plain incorrect examples. The official mysqldump documentation contains a wide variety of options and switches to tweak the final dump output, but most often you just want a complete backup of one or more databases for point-in-time snapshot. This post covers two example mysqldump usages that will work well for typical MySQL setups or as a baseline for more complex configurations.<\/p>\n
The following command performs a backup of a database named 'db1' on a given MySQL\/MariaDB instance.<\/p>\n
Beyond the obvious benefit of targeting the specific database(s) to backup, a key aspect of this approach is to avoid dumping the built-in 'mysql' database which includes database users and permissions that you may not want to overwrite in the database instance you restore the backup to.<\/p>\n
mysqldump \\\n -u root \\\n -p \\\n --routines \\\n --triggers \\\n --events \\\n --hex-blob \\\n --skip-extended-insert \\\n --complete-insert \\\n --single-transaction \\\n --databases db1 \\\n --result-file=db1-bkup.sql\n<\/pre>\nThe following options are used:<\/p>\n
Alternatively, you can use a similar mysqldump usage to dump all the databases in a given instance. This can be handy if for example you DO want to restore everything including instance-wide users and permissions on restore (for example a test database instance that is wiped out and re-initialized).<\/p>\n
mysqldump \\\n -u root \\\n -p \\\n --routines \\\n --triggers \\\n --events \\\n --hex-blob \\\n --skip-extended-insert \\\n --complete-insert \\\n --single-transaction \\\n --all-databases \\\n --result-file=all-dbs-bkup.sql\n<\/pre>\nAs you can see there are only two changes from the specific-database backup:<\/p>\n
Spin up docker testbed:<\/p>\n
docker run --name mysqltest -e MYSQL_ROOT_PASSWORD=password -d mysql:8\n\ndocker container exec -it mysqltest \/bin\/bash\n\nmysql -u root -p\n<\/pre>\nInitialize test table:<\/p>\n
CREATE DATABASE db1;\n\nUSE db1;\n\nCREATE TABLE testtbl (\n myInt INT PRIMARY KEY,\n myBit BIT,\n myDouble DOUBLE,\n myDate DATE,\n myText TEXT,\n myBlob BLOB\n);\n\nINSERT INTO testtbl (myInt, myBit, myDouble, myDate, myText, myBlob)\n VALUES (1, 0, 12.3, '20201231', 'hey', x'FF');\n\nINSERT INTO testtbl (myInt, myBit, myDouble, myDate, myText, myBlob)\n VALUES (2, 1, 45.6, '20220227', 'joe', x'8F');\n\n<\/pre>\nDump database:<\/p>\n
mysqldump \\\n -u root \\\n -p \\\n --routines \\\n --triggers \\\n --events \\\n --hex-blob \\\n --skip-extended-insert \\\n --complete-insert \\\n --single-transaction \\\n --databases db1 \\\n --result-file=db1-bkup.sql\n<\/pre>\nConfirm row format:<\/p>\n
grep INSERT db1-bkup.sql\nINSERT INTO `testtbl` (`myInt`, `myBit`, `myDouble`, `myDate`, `myText`, `myBlob`) VALUES (1,0x00,12.3,'2020-12-31','hey',0xFF);\nINSERT INTO `testtbl` (`myInt`, `myBit`, `myDouble`, `myDate`, `myText`, `myBlob`) VALUES (2,0x01,45.6,'2022-02-27','joe',0x8F);\n\n<\/pre>\nYou'll notice:<\/p>\n
\n
- The column names are explicitly listed<\/li>\n
- Each insert statement is independently complete (ie not extended format)<\/li>\n
- the BIT and BLOB types are represented as hex since they are binary column types<\/li>\n<\/ol>\n
Restrictions<\/h3>\n
As indicated on the official documentation<\/a> mysqldump does not dump the following by default:<\/p>\n
\n
- The performance_schema or sys schema by default<\/li>\n
- The INFORMATION_SCHEMA schema<\/li>\n
- The InnoDB CREATE TABLESPACE statemets<\/li>\n
- The NDB Cluster ndbinfo information database<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
Searching around for instructions on how to properly backup a MySQL database will often lead you down a dark path filled with outdated, incomplete, or just plain incorrect examples. The official mysqldump documentation contains a wide variety of options and switches to tweak the final dump output, but most often you just want a complete backup of one or more databases for point-in-time snapshot. This post covers two example mysqldump usages that will work well for typical MySQL setups or as a baseline for more complex configurations. [Continue reading...] Complete MySQL Backups With Mysqldump<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":835,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[59],"tags":[61,60,62],"_links":{"self":[{"href":"https:\/\/bhoey.com\/blog\/wp-json\/wp\/v2\/posts\/492"}],"collection":[{"href":"https:\/\/bhoey.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bhoey.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bhoey.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bhoey.com\/blog\/wp-json\/wp\/v2\/comments?post=492"}],"version-history":[{"count":52,"href":"https:\/\/bhoey.com\/blog\/wp-json\/wp\/v2\/posts\/492\/revisions"}],"predecessor-version":[{"id":550,"href":"https:\/\/bhoey.com\/blog\/wp-json\/wp\/v2\/posts\/492\/revisions\/550"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bhoey.com\/blog\/wp-json\/wp\/v2\/media\/835"}],"wp:attachment":[{"href":"https:\/\/bhoey.com\/blog\/wp-json\/wp\/v2\/media?parent=492"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bhoey.com\/blog\/wp-json\/wp\/v2\/categories?post=492"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bhoey.com\/blog\/wp-json\/wp\/v2\/tags?post=492"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}