Master MySQL Backups: Commands for Single, Multiple, and All Databases

MySQL Backup Database

Backup a single database

# Format
mysqldump -h<host> -P<port> -u<user> -p"<password>" --database <database_name> > <filename>.sql

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

Backup multiple databases

# Format
mysqldump -h<host> -P<port> -u<user> -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

# Format
mysqldump -h<host> -P<port> -u<user> -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

# Format
mysqldump -h<host> -P<port> -u<user> -p"<password>" <database> <table> > <filename>.sql

# Example
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

# Format
mysqldump -h<host> -P<port> -u<user> -p"<password>" <database> <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

The -B parameter

Adding -B makes the dump file include CREATE DATABASE and USE statements, so you don't need to create the target database manually when restoring.

Using the pipe with gzip for compressed backups

# Format
mysqldump -h<host> -P<port> -u<user> -p"<password>" --database <db> | gzip > <filename>.sql.gz

# Example (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 (all databases)
mysqldump -h10.*.*.9 -P3306 -uroot -p"<password>" --single-transaction --master-data=2 --all-databases | gzip > mysqldump_alldatabases_`%Y%m%d-%H%M`.sql.gz

Adding drop statements

Use --add-drop-database to include DROP DATABASE and --add-drop-table to include DROP TABLE in the dump.

# Format
mysqldump -h<host> -P<port> -u<user> -p"<password>" --add-drop-table --add-drop-database <database> > <filename>.sql

# Example
mysqldump -h10.*.*.9 -P3306 -uroot -p"<password>" --add-drop-table --add-drop-database cmdb > /data/backup/mysqldump_cmdb_`%Y%m%d-%H%M`.sql

Exporting only the schema (no data)

# Format
mysqldump -h<host> -P<port> -u<user> -p"<password>" --no-data <db1> <db2> > <filename>.sql

# Example
mysqldump -h10.*.*.9 -P3306 -uroot -p"<password>" --no-data db1 db2 > mysqldump_no_data_db1_db2_`%Y%m%d-%H%M`.sql

Restoring a dump

# Using source inside mysql client
mysql -uroot -p
mysql> use <database>;
mysql> source <file>.sql;

# Non‑interactive restore
mysql -uroot -p -e "use <database>; source <file>.sql;"

# Direct restore of a dump file
mysql -u<user> -p < <dumpfile>.sql

# Restore from a compressed file
gzip -d <dumpfile>.sql.gz
mysql -u<user> -p < <dumpfile>.sql

# Or pipe directly
gzip < <dumpfile>.sql.gz | mysql -u<user> -p

Sample automated backup script

#!/bin/bash
backupdir=/data/mysqlbak
# Timestamp for the backup file name
time=`date +%Y%m%d-%H%M`

# Backup all databases and compress
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., 00 3 * * * /data/mysqlbak/mysqlbak.sh) to run it daily.