Master MySQL Backups: Commands, Scripts, and Best Practices

MySQL Database Backup

Backup a single database

# mysqldump format for a single database
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

# mysqldump format for multiple databases
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 -B db_mystorage db_myblogs db_myOA > mysqldump_db_mystorage_db_myblogs_db_myOA_`date +%Y%m%d-%H%M`.sql

Backup all databases

# mysqldump format for all databases
mysqldump -hHOST -PPORT -uUSER -p"PASSWORD" --all-databases > filename.sql

# Example
mysqldump -h10.*.*.9 -P3306 -uroot -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

# mysqldump format for specific tables
mysqldump -hHOST -PPORT -uUSER -p"PASSWORD" DB_NAME TABLE1 > filename.sql

# Example (backup tb_doc_permission from db_plus_core)
mysqldump -h10.*.*.9 -P3306 -uroot -p"PASSWORD" --single-transaction --master-data=2 --set-gtid-purged=OFF db_plus_core tb_doc_permission > mysqldump_tb_doc_permission_`date +%Y%m%d-%H%M`.sql

Backup specific multiple tables

# mysqldump format for multiple tables
mysqldump -hHOST -PPORT -uUSER -p"PASSWORD" DB_NAME TABLE1 TABLE2 > filename.sql

# Example
mysqldump -h10.*.*.9 -P3306 -uroot -p"PASSWORD" --single-transaction --master-data=2 --set-gtid-purged=OFF db_plus_core tb_1 tb_2 > mysqldump_tb_1_tb_2_`date +%Y%m%d-%H%M`.sql

Understanding the -B Parameter

Adding -B to mysqldump includes CREATE DATABASE and USE statements in the dump, so during restoration you do not need to create the target database manually.

Compressing Backups with gzip

# Compress backup on the fly
mysqldump -hHOST -PPORT -uUSER -p"PASSWORD" --database DB_NAME | gzip > filename.sql.gz

# Example: compress a single database
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

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

Including Drop Statements

Use --add-drop-database and --add-drop-table to generate DROP statements for databases and tables.

# Example with drop statements
mysqldump -hHOST -PPORT -uUSER -p"PASSWORD" --add-drop-table --add-drop-database DB_NAME > filename.sql

Exporting Only Schema (No Data)

Append --no-data to dump only the structure.

# Schema‑only dump for multiple databases
mysqldump -hHOST -PPORT -uUSER -p"PASSWORD" --no-data DB1 DB2 > filename.sql

Restoring Data

Typical restore using the dump file:

# Restore a specific 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

You can also execute the script directly from the MySQL client with the source command or use the non‑interactive -e option.

Partial Table Backup Technique

Copy the table structure with CREATE TABLE ... LIKE, insert the required rows, and later restore them.

# Copy table structure
CREATE TABLE tb_staff_bak LIKE tb_staff;

# Insert selected rows into backup table
INSERT INTO tb_staff_bak SELECT * FROM tb_staff WHERE name IN ('xiong','zhang');

# Delete rows from original table
DELETE FROM tb_staff WHERE name IN ('xiong','zhang');

# Rollback / restore
INSERT INTO tb_staff SELECT * FROM tb_staff_bak;
DROP TABLE tb_staff_bak;

Automated Backup Script Example

#!/bin/bash
backupdir=/data/mysqlbak   # backup directory
time=`date +%Y%m%d-%H%M`  # timestamp for filename

mysqldump -h10.*.*.9 -P3306 -uroot -p"PASSWORD" --all-databases --single-transaction --default-character-set=utf8 | gzip > $backupdir/mysql$time.sql.gz

# Delete backups older than 7 days
find $backupdir -name "mysql*.sql.gz" -type f -mtime +7 -exec rm {} \; > /dev/null 2>&1

Create the backup directory, make the script executable, and add a cron job (e.g., daily at 03:00) to run it automatically.

To restore, decompress the file and pipe it to mysql, or use the uncompressed .sql file directly.