mysqldump,
Syntax
mysqldump [options] database [tables] > backup.sql mysql database < backup.sql
Parameters
| Parameters | Description | Examples | Level |
|---|---|---|---|
--single-transaction |
InnoDB () | mysqldump --single-transaction ecommerce > backup.sql |
Common |
--all-databases |
mysqldump --all-databases > all_backup.sql |
Common | |
--routines |
and | mysqldump --routines ecommerce > backup.sql |
Advanced |
--triggers |
() | mysqldump --triggers ecommerce > backup.sql |
Advanced |
--where |
mysqldump ecommerce orders --where="created_at > '2026-01-01'" > orders_2026.sql |
Advanced | |
--no-data |
mysqldump --no-data ecommerce > schema.sql |
Common |
Examples
(Recommended)
mysqldump -u root -p \ --single-transaction \ --routines \ --triggers \ --set-gtid-purged=OFF \ ecommerce > ecommerce_$(date +%Y%m%d_%H%M%S).sql
--single-transaction InnoDB
# () mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS ecommerce;" # mysql -u root -p ecommerce < ecommerce_20260523_100000.sql
,
mysqldump -u root -p \ --single-transaction \ ecommerce users orders > core_tables.sql
, and
#!/bin/bash
# /opt/scripts/mysql_backup.sh
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR=/data/backup/mysql
DB_NAME=ecommerce mysqldump -u backup_user -p'BackupPass!' \ --single-transaction --routines \ $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz # 7 find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete # crontab: 0 2 * * * /opt/scripts/mysql_backup.shcrontab 2, 7
Common Errors
mysqldump: Got error: 1045: Access deniedSELECT, SHOW VIEW, TRIGGER, LOCK TABLES
ERROR 1049 (42000): Unknown database: CREATE DATABASE dbname;
Tips
- --single-transaction,
- gzip 70-80%
- ,