Master MySQL Backups: Commands, Scripts, and Best Practices

MySQL Backup Database

Backup a single database

Use mysqldump with host, port, user, password and the --database option to export one database to a .sql file.

# Format:
mysqldump -hHOST -PPORT -uUSER -p"PASSWORD" --database DB_NAME > filename.sql

# Example:
mysqldump -h10.*.*.9 -P3306 -uroot -p"PASSWORD" --single-transaction --master-data=2 --set-gtid-purged=OFF --database -B db_mystorage > mysqldump_db_mystorage_`date +%Y%m%d-%H%M`.sql

Backup multiple databases

List several database names after --database to back them up together.

# Format:
mysqldump -hHOST -PPORT -uUSER -p"PASSWORD" --database DB1 DB2 DB3 > filename.sql

# Example:
mysqldump -h10.*.*.9 -P3306 -uroot -p"PASSWORD" --single-transaction --master-data=2 --set-gtid-purged=OFF --database -B db_mystorage db_myblogs db_myOA > mysqldump_db_mystorage_db_myblogs_db_myOA_`date +%Y%m%d-%H%M`.sql

Backup all databases

The --all-databases flag exports every database owned by the user.

# Format:
mysqldump -hHOST -PPORT -uUSER -p"PASSWORD" --all-databases > filename.sql

# Example:
mysqldump -h10.*.*.9 -P3306 -uUSERNAME -p"PASSWORD" --single-transaction --master-data=2 --set-gtid-purged=OFF --all-databases > mysqldump_all_databases_`date +%Y%m%d-%H%M`.sql

Backup specific tables

Specify the database name followed by one or more table names.

# Single table:
mysqldump -hHOST -PPORT -uUSER -p"PASSWORD" DB_NAME TABLE_NAME > filename.sql

# Multiple tables:
mysqldump -hHOST -PPORT -uUSER -p"PASSWORD" DB_NAME TABLE1 TABLE2 > filename.sql

The -B option

Adding -B includes CREATE DATABASE and USE statements in the dump, so restoration does not require manually creating the target database.

Compressing backups with | gzip

Pipe the dump to gzip to produce a compressed .sql.gz file, which is useful for large exports.

# Example:
mysqldump -h10.*.*.9 -P3306 -uroot -p"PASSWORD" --single-transaction --master-data=2 --database db_plus_core | gzip > mysqldump_db_plus_core_`date +%Y%m%d-%H%M`.sql.gz

Including drop statements

Use --add-drop-database and --add-drop-table to prepend DROP statements, making restores idempotent.

# Format:
mysqldump -hHOST -PPORT -uUSER -p"PASSWORD" --add-drop-table --add-drop-database DB_NAME > filename.sql

Backing up only schema (no data)

The --no-data flag exports only table definitions.

# Format:
mysqldump -hHOST -PPORT -uUSER -p"PASSWORD" --no-data DB1 DB2 > filename.sql

Restoring a dump

Use the mysql client to import a .sql file. If the dump contains CREATE DATABASE statements, no database needs to be specified.

# Restore a single table backup:
mysql -uroot -p"PASSWORD" db_plus_core < mysqldump_20180114_tb_doc_permission.sql

# Restore all databases:
mysql -uroot -p"PASSWORD" < mysqldump_20240114_all_databases.sql

Restoring via the MySQL console

After logging into the MySQL console, use source to run a script file.

mysql> source /path/to/file.sql

Using -e for non‑interactive execution

The -e option lets you run SQL statements directly from the command line without entering the interactive console.

mysql -uroot -p3306 -e "use crm2; show tables;"

Automated backup script example

A Bash script that backs up all databases daily, compresses the output, and removes files older than seven days.

#!/bin/bash
backupdir=/data/mysqlbak
time=`date +%Y%m%d-%H%M`
mysqldump -h10.*.*.9 -P3306 -uroot -p"PASSWORD" --all-databases --single-transaction --default-character-set=utf8 | gzip > $backupdir/mysql$time.sql.gz
find $backupdir -name "mysql*.sql.gz" -type f -mtime +7 -exec rm {} \; > /dev/null 2>&1

Creating the backup environment

Make the backup directory, give execution permission to the script, and schedule it with crontab (e.g., 03:00 daily).

Restoring from compressed files

Decompress with gzip -d then import, or pipe directly to mysql.

# Decompress then restore:
gzip -d mysql2020703.sql.gz
mysql -uUSER -p < mysql2020703.sql

# Direct pipe:
gzip < mysql2020703.sql.gz | mysql -uUSER -p