Back Up and Test a MySQL Database from the Command Line

A MySQL database backup saves database structure and data, not your website’s files, email or hosting configuration. Keep those components separately. For a browser-based export, use the phpMyAdmin backup guide. The command-line workflow below is for authorized SSH users with the required MySQL client tools and database privileges.

Prepare private storage and credentials

Confirm the actual database name and user from your application configuration; cPanel names commonly include an account prefix. Use a client compatible with your database server. MariaDB installations may provide mariadb-dump and mariadb instead of the MySQL tools shown here; do not assume every version accepts the same options.

On the database host, create a private client option file with a text editor at $HOME/.mysql-backup.cnf. Use the format below, replacing the placeholders with your own local database credentials. Do not include the password in a shell command, public directory or support ticket. MySQL option-file quoting matters for special characters; consult the option-file documentation.

[client]
user=account_dbuser
password=REPLACE_WITH_LOCAL_SECRET

chmod 600 "$HOME/.mysql-backup.cnf"
umask 077
mkdir -p "$HOME/private-backups"
backup=$(mktemp "$HOME/private-backups/mysql.XXXXXX.sql")
if mysqldump --defaults-extra-file="$HOME/.mysql-backup.cnf" \
  --single-transaction --quick --routines --events --triggers \
  --no-tablespaces account_database > "$backup"; then
  printf 'Backup created: %s\n' "$backup"
else
  printf 'Dump failed; do not use the incomplete file: %s\n' "$backup" >&2
  exit 1
fi

Replace account_database before running. These examples assume a local database connection; for a remote server, obtain its supported TLS and certificate settings rather than sending database credentials over an unverified connection.

Understand the consistency boundary

--single-transaction supplies a consistent snapshot for InnoDB tables, not a universal guarantee for MyISAM or other nontransactional engines. Avoid schema changes during the dump. Mixed engines need a planned maintenance/locking strategy. Routines, events and triggers also require appropriate export and restore privileges; permission errors are failures, not warnings to ignore.

Restore into a disposable test database

Create an empty, separately named test database with an authorized tool only after inspecting the dump’s scope. Dumps can contain explicit source-database references in views or routines; a different destination name does not automatically remap them. An isolated test server using the original database name is another recovery-test option. Give the test user access only where needed, and keep the live database/application unchanged. SQL can contain DROP or other destructive statements; never use an import as a merge into unrelated live data.

mysql --defaults-extra-file="$HOME/.mysql-backup.cnf" \
  account_restore_test < "$backup"

The restore example uses the backup variable from the same shell session. In a new session, set it to the actual private archive path first. Check the import exit status and compare tables, representative rows, character encoding, views and any required routines/events/triggers. Application users, grants and external files need their own recovery plan.

Copy the verified export to access-controlled backup storage and protect or remove the temporary credential file according to your security policy. A nonempty SQL file or matching checksum proves neither completeness nor successful recovery.

Reference: MySQL mysqldump documentation. Reviewed October 14, 2026. The lab record covers synthetic local MySQL data, not your server, MariaDB compatibility, remote TLS or a production recovery.