Резервое копирование БД MySQL на Debian (Linux) и Windows

Резервное копирование базы данных MySQL — критически важный процесс для обеспечения целостности и восстановления данных в случае сбоев.

⚠️ Только для случаев, когда можно остановить MySQL на несколько минут.

Физическое резервное копирование

— это копирование файлов данных MySQL (.frm, .ibd, .MYD, .MYI, ibdata1, ib_logfile*, mysql-bin.* и др.) напрямую с диска. Это быстрый и эффективный способ, но требует остановки MySQL, чтобы избежать повреждения данных.

✅ На Debian (Linux)

Шаг 1: Остановка MySQL

sudo systemctl stop mysql

# или

sudo service mysql stop

❗ Важно: Убедитесь, что никто не использует БД. Заранее предупредите пользователей.

Шаг 2: Копирование файлов данных

По умолчанию данные MySQL находятся в /var/lib/mysql/. Уточните путь:

sudo mysql -e "SHOW VARIABLES LIKE 'datadir';"

Пример:

sudo cp -a /var/lib/mysql /backup/mysql_backup_$(date +%F)

Шаг 3: Запуск MySQL

sudo systemctl start mysql

Шаг 4: Проверка работоспособности

sudo systemctl status mysqlb

mysql -e "SHOW DATABASES;"

✅ На Windows

Шаг 1: Остановка службы MySQL

Откройте командную строку от имени администратора:

net stop mysql

Если используется нестандартное имя службы (например, MySQL80), уточните:

sc query | findstr mysql

Затем:

net stop MySQL80

Шаг 2: Копирование файлов данных

Путь по умолчанию:
C:\ProgramData\MySQL\MySQL Server X.Y\Data\
(или в установочной папке, например C:\Program Files\MySQL\MySQL Server X.Y\data)

Скопируйте папку data:

xcopy "C:\ProgramData\MySQL\MySQL Server 8.0\Data" "D:\backup\mysql_backup_%date:~-4,4%%date:~-7,2%%date:~-10,2%" /E /H /K /Y

/E — копировать подкаталоги, включая пустые
/H — копировать скрытые и системные файлы
/K — сохранить атрибуты
/Y — не запрашивать подтверждение

Шаг 3: Запуск службы

net start mysql

Шаг 4: Проверка

Запустите MySQL Workbench или:

mysql -u root -p -e "SHOW DATABASES;"

Логическое резервное копирование «на ходу» (hot backup)

📌 Что такое логическое резервное копирование?

Используется утилита mysqldump для экспорта структуры и данных в SQL-скрипт. Можно выполнять без остановки MySQL, но нагрузка на сервер возрастает.

✅ На Debian (Linux)

Шаг 1: Выполнение резервного копирования

mysqldump -u root -p --all-databases --single-transaction --routines --triggers --events > /backup/full_backup_$(date +%F).sql

• --all-databases — все базы
• --single-transaction — гарантирует согласованность без блокировки (для InnoDB)
• --routines — сохраняет хранимые процедуры
• --triggers — триггеры
• --events — события (планировщик)

Шаг 2: Сжатие (опционально)

gzip /backup/full_backup_*.sql

Шаг : Расписание через cron

crontab -e

Добавьте в cron:

0 2 * * * /usr/bin/mysqldump -u root -p'password' --all-databases --single-transaction --routines --triggers --events | gzip > /backup/mysql_$(date +\%F).sql.gz

⚠️ Пароль в команде — небезопасно. Лучше использовать .my.cnf:

[client]

user=root

password=ваш_пароль

С правами: chmod 600 ~/.my.cnf

✅ На Windows

Шаг 1: Выполнение резервного копирования

Откройте командную строку:

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump.exe" -u root -p --all-databases --single-transaction --routines --triggers --events > D:\backup\full_backup_%date:~-4,4%%date:~-7,2%%date:~-10,2%.sql

Убедитесь, что путь к mysqldump.exe правильный.

Шаг 2: Сжатие (опционально)

Используйте 7-Zip или PowerShell:

Compress-Archive -Path "D:\backup\full_backup_*.sql" -DestinationPath "D:\backup\backup_$(Get-Date -Format 'yyyy-MM-dd').zip"

Шаг 3: Автоматизация через Планировщик заданий (Task Scheduler)

1. Откройте "Планировщик заданий"
2. Создайте задачу
3. В действии укажите:

• Программа: cmd.exe
• Аргумент: /c "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump.exe" -u root -pPassword --all-databases --single-transaction > D:\backup\backup_%date:~-4,4%%date:~-7,2%%date:~-10,2%.sql
  

⚠️ Пароль в открытом виде — риск. Используйте файл конфигурации (см. выше).

🔹 Восстановление из резервной копии

Физическое восстановление

1. Остановите MySQL
2. Удалите или переместите текущий datadir
3. Скопируйте резервную папку data на место

4. Убедитесь в правах (Linux):

chown -R mysql:mysql /var/lib/mysql

5. Запустите MySQL

⚠️ Не рекомендуется копировать файлы между разными версиями MySQL.

Логическое восстановление

mysql -u root -p < backup.sql

или

source /path/to/backup.sql;

Например:

C:\> mysql -u root -p

mysql> create database mydb;

mysql> use mydb;

mysql> source db_backup.dump;

⚠️ Возможные ошибки и проблемы (физическое копирование)

❗ Проблема:

Файлы повреждены после копирования

⚠️Причина:

Копирование во время работы MySQ

✅Решение:

Всегда останавливайте MySQL перед копированием

❗ Проблема:

Недостаточно места на диске

⚠️Причина:

Объём данных превышает свободное место

✅Решение:

Проверяйте место: df -h" (Linux), "dir или fsutil volume diskfree C: (Windows)

❗ Проблема:

Потеря прав доступа (Linux)

⚠️Причина:

Копирование без сохранения владельца

✅Решение:

Используйте cp -a или rsync -a

❗ Проблема:

Ошибка доступа к файлам (Windows)

⚠️Причина:

Файлы заблокированы системой или антивирусом

✅Решение:

Отключите антивирус временно, проверьте, что служба остановлена

❗ Проблема:

Несовместимость версий при восстановлении

⚠️Причина:

Резервная копия с MySQL 8.0 на MySQL 5.7

✅Решение:

Всегда проверяйте версии MySQL при восстановлении

Рекомендации

1. Регулярность: делайте резервные копии по расписанию (ежедневно, еженедельно).
2. Хранение: храните копии на отдельном диске или в облаке.
3. Тестирование: периодически проверяйте восстановление.
4. Шифрование: для чувствительных данных — шифруйте резервные копии.
5. Мониторинг: логируйте успешность/ошибки бэкапов.

Заключение

• Физическое копирование — быстрее, но требует остановки MySQL. Подходит для полного кластера или аварийного восстановления.
• Логическое копирование — гибче, можно делать «на ходу», но медленнее и требует больше ресурсов.

Выбор метода зависит от требований к доступности, объёму данных и времени простоя.

Регулярное тестирование резервных копий — залог успешного восстановления в кризисной ситуации