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.