Master MySQL Backups: Commands for Single DB, Multiple DBs, Compression & Restoration

MySQL Backup Database

Backup a single database

mysqldump -hHOST -PPORT -uUSER -p"PASSWORD" --database DBNAME > filename.sql

Backup multiple databases

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

Backup all databases

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

Backup specific tables

mysqldump -hHOST -PPORT -uUSER -p"PASSWORD" DBNAME TABLE1 > filename.sql

Backup specific multiple tables

mysqldump -hHOST -PPORT -uUSER -p"PASSWORD" DBNAME TABLE1 TABLE2 > filename.sql

The -B parameter

Adding -B makes the dump file contain CREATE DATABASE and USE statements, so you do not need to create the target database manually before restoring.

Compressing backups with gzip

mysqldump -hHOST -PPORT -uUSER -p"PASSWORD" --database DBNAME | gzip > filename.sql.gz

Including drop statements

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

Exporting only schema

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

Restoring a dump

Interactive method: mysql -uUSER -p"PASSWORD" DBNAME < dumpfile.sql Using the source command inside the MySQL console: mysql> source dumpfile.sql Non‑interactive method with -e:

mysql -uUSER -p"PASSWORD" -e "use DBNAME; source dumpfile.sql"

Automatic backup script (bash)

#!/bin/bash
backupdir=/data/mysqlbak
time=$(date +%Y%m%d-%H%M)
mysqldump -hHOST -PPORT -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

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.