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.

Assumptions

• MySQL 8.0 or MariaDB 10.4
• Only InnoDB tables
• MySQL/MariaDB 'root' user access
• Database server is not part of a replication set

Backup Specific Database(s)

The following command performs a backup of a database named 'db1' on a given MySQL/MariaDB instance.

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.

mysqldump \
    -u root \
    -p \
    --routines \
    --triggers \
    --events \
    --hex-blob \
    --skip-extended-insert \
    --complete-insert \
    --single-transaction \
    --databases db1 \
    --result-file=db1-bkup.sql

The following options are used:

• -u root - Use root mysql user
• -p - Prompt for password
• --routines - Include stored procedures and functions
• --triggers - Include triggers
• --events - Include events
• --hex-blob - Dump binary columns as hex. If you don't specify this switch, raw binary is written to the result file which is problematic and error-prone
• --skip-extended-insert - Disable extended insert. This trade-off allows you to cherry-pick specific inserts out of the output file later at the expense of increased dump file size
• --complete-insert - Include column names in insert statement. Similar trade-off to --skip-extended-insert above
• --single-transaction - Perform dump within a transaction to prevent reading stale data that has changed in other concurrent sessions
• --databases db1 - Indicates the database(s) to backup
• --result-file - Specifies the output file, it is common to see file redirection used instead (ie '> bkup.sql'), however simple redirection may cause issues depending on the encoding, particularly on Windows systems

Backup All Databases

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

mysqldump \
    -u root \
    -p \
    --routines \
    --triggers \
    --events \
    --hex-blob \
    --skip-extended-insert \
    --complete-insert \
    --single-transaction \
    --all-databases \
    --result-file=all-dbs-bkup.sql

As you can see there are only two changes from the specific-database backup:

• --all-databases - Include all databases, notably even system-specific ones like the 'mysql' database
• --result-file=all-dbs-bkup.sql - Changed dump filename to reflect that it contains all databases

Testing

Spin up docker testbed:

docker run --name mysqltest -e MYSQL_ROOT_PASSWORD=password -d mysql:8

docker container exec -it mysqltest /bin/bash

mysql -u root -p

Initialize test table:

CREATE DATABASE db1;

USE db1;

CREATE TABLE testtbl (
        myInt INT PRIMARY KEY,
        myBit BIT,
     myDouble DOUBLE,
       myDate DATE,
       myText TEXT,
       myBlob BLOB
);

INSERT INTO testtbl (myInt, myBit, myDouble,     myDate, myText, myBlob)
             VALUES (1,         0,     12.3, '20201231',  'hey',  x'FF');

INSERT INTO testtbl (myInt, myBit, myDouble,     myDate, myText, myBlob)
             VALUES (2,         1,     45.6, '20220227',  'joe',  x'8F');

Dump database:

mysqldump \
    -u root \
    -p \
    --routines \
    --triggers \
    --events \
    --hex-blob \
    --skip-extended-insert \
    --complete-insert \
    --single-transaction \
    --databases db1 \
    --result-file=db1-bkup.sql

Confirm row format:

grep INSERT db1-bkup.sql
INSERT INTO `testtbl` (`myInt`, `myBit`, `myDouble`, `myDate`, `myText`, `myBlob`) VALUES (1,0x00,12.3,'2020-12-31','hey',0xFF);
INSERT INTO `testtbl` (`myInt`, `myBit`, `myDouble`, `myDate`, `myText`, `myBlob`) VALUES (2,0x01,45.6,'2022-02-27','joe',0x8F);

You'll notice:

1. The column names are explicitly listed
2. Each insert statement is independently complete (ie not extended format)
3. the BIT and BLOB types are represented as hex since they are binary column types

Restrictions

As indicated on the official documentation mysqldump does not dump the following by default:

• The performance_schema or sys schema by default
• The INFORMATION_SCHEMA schema
• The InnoDB CREATE TABLESPACE statemets
• The NDB Cluster ndbinfo information database