mysqldump: резервное копирование и восстановление баз данных MySQL

Основные команды mysqldump

mysqldump — стандартная утилита для создания логических резервных копий MySQL и MariaDB. Она создаёт SQL-файл с командами для воссоздания структуры и данных базы.

Бэкап одной базы данных

# Базовый бэкап
mysqldump -u root -p mydb > mydb_backup.sql

# С сжатием (рекомендуется)
mysqldump -u root -p mydb | gzip > mydb_$(date +%Y%m%d_%H%M%S).sql.gz

# Без блокировки таблиц (для InnoDB)
mysqldump -u root -p --single-transaction --quick mydb | gzip > mydb.sql.gz

Полезные флаги mysqldump

Бэкап всех баз данных

mysqldump -u root -p --all-databases --single-transaction \
    --routines --triggers | gzip > all_databases_$(date +%Y%m%d).sql.gz

Восстановление из дампа

# Восстановление обычного дампа
mysql -u root -p mydb < mydb_backup.sql

# Восстановление сжатого дампа
gunzip < mydb_backup.sql.gz | mysql -u root -p mydb

# Или через zcat
zcat mydb_backup.sql.gz | mysql -u root -p mydb

Автоматизация через cron

sudo nano /etc/cron.d/mysql-backup

# Бэкап каждый день в 2:00, хранить 7 дней
0 2 * * * root mysqldump -u backup_user -pPassword --single-transaction \
    --all-databases | gzip > /backups/mysql/all_$(date +\%Y\%m\%d).sql.gz && \
    find /backups/mysql/ -name "*.sql.gz" -mtime +7 -delete

Создание пользователя только для бэкапов

mysql -u root -p -e "
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongBackupPass';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;"

Пароль в ~/.my.cnf (без ввода пароля)

cat ~/.my.cnf
[mysqldump]
user=backup_user
password=StrongBackupPass

chmod 600 ~/.my.cnf
# Теперь можно без -p:
mysqldump mydb > backup.sql