Резервное копирование — ключевой элемент стратегии обеспечения безопасности и надёжности данных. Для баз данных MySQL, которые широко используются в веб-разработке и других областях, регулярное создание резервных копий особенно важно. В случае сбоя, атаки или случайного удаления данных резервная копия позволяет быстро восстановить информацию и минимизировать простои.

В этой статье мы рассмотрим, как организовать резервное копирование баз данных MySQL в Linux, включая примеры скриптов с оповещениями, сохранением паролей, ротацией резервных копий и другими полезными функциями.

Основные методы резервного копирования MySQL

Существует несколько способов создания резервных копий баз данных MySQL:

• использование утилиты mysqldump — это стандартный инструмент для экспорта данных и структуры баз данных в формате SQL;
• применение сторонних инструментов и утилит, которые могут предоставлять дополнительные возможности, например, инкрементальное копирование или более гибкие настройки;
• использование встроенных механизмов репликации и снапшотов.

В этой статье мы сосредоточимся на использовании mysqldump, так как это наиболее распространённый и доступный метод для большинства пользователей.

Создание резервной копии с помощью mysqldump

Утилита mysqldump позволяет экспортировать данные и структуру одной или нескольких баз данных в файл SQL. Базовый синтаксис команды выглядит следующим образом:

# mysqldump -u [пользователь] -p[пароль] [база_данных] > [файл_резервной_копии].sql

Например, для создания резервной копии базы данных mydb с пользователем root команда будет выглядеть так:

# mysqldump -u root -p mydb > mydb_backup.sql

После ввода команды система запросит пароль пользователя.

Автоматизация резервного копирования с помощью скриптов

Для регулярного создания резервных копий удобно использовать скрипты, которые можно запускать по расписанию с помощью cron. Ниже приведён пример простого скрипта на Bash для резервного копирования всех баз данных MySQL который я использую для бэкапа текущего pet-проекта с кластером из MySQL:

#!/bin/bash

# Указываем пользователя и пароль для доступа к MySQL
USER="root"
PASSWORD=$ENV_PASSWORD_MYSQL
HOST="s-rain-03.shiskitech.ru"
DATE=`date +%F`
# Получаем список всех баз данных
DATABASES=$(mysql -h $HOST -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "|" | grep -v "Database")

# Создаём резервные копии каждой базы данных
for DB in $DATABASES; do
    mysqldump -h $HOST -u $USER -p$PASSWORD $DB > ./database/$DATE-$DB.sql
done

Важно помнить о безопасности при сохранении паролей в скриптах. Один из способов — использовать файл с настройками, который будет иметь строгие права доступа, или применять переменные окружения.

Оповещения о завершении резервного копирования

Чтобы получать уведомления о завершении процесса резервного копирования, можно добавить в скрипт команду отправки электронного письма или сообщения в мессенджер. Тут вам придется напрячься и настроить релэй для отправки почты, у меня есть статья на эту тему. Например, для отправки письма можно использовать утилиту mail:

# mail -s "Резервное копирование завершено" your_email@example.com <<< "Резервная копия создана успешно."

Если по чесному, то никто это проверять не будет и лучше сделать оповешения в мессенджер и настроить систему мониторинга и алертов.

Ротация резервных копий

Ротация резервных копий позволяет управлять объёмом хранимых данных и обеспечивать их актуальность. Можно использовать различные стратегии ротации, например, сохранять копии за последние несколько дней, недель или месяцев. Для реализации ротации можно добавить в скрипт команды для удаления старых файлов:

# find /backup/ -name "*.sql" -type f -mtime +7 -exec rm {} \;

Эта команда удалит все файлы с расширением .sql в директории /backup/, которые не изменялись более 7 дней.

Пользователи и пароли

При восстановлении базы данных MySQL после сбоя можно столкнуться с ситуацией, когда приложения не могут подключиться к ней. Причина может быть в отсутствии учётных записей пользователей или их паролей. Резервное копирование пользователей и паролей MySQL не менее важная задача, чем копирование самих данных.

Как MySQL управляет пользователями, паролями и привилегиями

Каждый пользователь, который подключается к MySQL, должен иметь учётную запись, определённую в системной базе данных mysql. Самая важная таблица в ней — user. В ней хранятся имена пользователей, хэши паролей (не сами пароли в текстовом виде), разрешённые для входа хосты (например, localhost или %) и глобальные привилегии, такие как SELECT, INSERT или GRANT OPTION.

Другие таблицы в базе данных mysql управляют более детальными разрешениями:

• db — привилегии на уровне базы данных;
• tables_priv — привилегии на уровне таблицы;
• columns_priv — привилегии на уровне столбца;
• procs_priv — привилегии для хранимых процедур и функций.

MySQL поддерживает различные плагины для хэширования паролей (например, mysql_native_password или caching_sha2_password). Это влияет на то, как хранятся хэши паролей, и может повлиять на совместимость при восстановлении между версиями.

Почему важно резервно копировать пользователей и пароли MySQL

Потеря учётных записей пользователей или их привилегий может быстро остановить бизнес-операции. Даже если вы успешно восстановите все данные приложения, но забудете об учётных записях или привилегиях, подключения не будут работать, пока вы вручную не воссоздадите эти учётные записи. Это огромная проблема! Если у вас не две-три учетки, а около 300 и непонятно кто за что отвечает и куда кому можно, то это превращается в изумительный цирк в котором я однажды участвовал и повторять не хочется.

Резервное копирование учётных записей и паролей позволяет:

• переносить серверы без ручного ввода каждой учётной записи;
• быстро восстанавливаться после случайного удаления данных или сбоя сервера;
• проводить аудит прав доступа для соответствия требованиям соответствия;
• экономить время при обновлении, избегая ручного воссоздания привилегий.

Способы резервного копирования пользователей и паролей MySQL

Метод 1: использование mysqldump

Утилита mysqldump часто используется для логического резервного копирования в средах MySQL. Чтобы экспортировать только таблицу user, которая содержит все имена учётных записей и хэши паролей, можно использовать следующую команду:

# mysqldump -u root -p mysql user > user_table_backup.sql

Для экспорта всех релевантных таблиц из системной базы данных mysql (включая db, tables_priv и другие) и сохранения детальных настроек разрешений вместе с именами пользователей, используйте:

# mysqldump -u root -p mysql > mysql_db_backup.sql

Метод 2: использование SQL-запросов

Иногда требуется больше гибкости, чем предлагает mysqldump. Например, при миграции только выбранных учётных записей между серверами или при аудите определённых привилегий без изменения других. В этом случае можно генерировать переносимые операторы GRANT, которые воссоздадут пользователей и их разрешения на другом сервере:

# mysql -u root -p -BNe "SELECT CONCAT('\'',user,'\'@\'',host,'\'') FROM mysql.user WHERE user NOT IN ('root','mysql.sys')" | \
while read userhost; do 
  mysql -u root -p -BNe "SHOW GRANTS FOR $userhost" | sed 's/$/;/; s/\\\\/\\/g'
done > grants.sql

Этот скрипт выполняет следующие действия:

1. Выводит список всех пользователей, кроме root и системных, в формате ‘username’@’hostname’.
2. Для каждой найденной записи извлекает точные операторы разрешений с помощью SHOW GRANTS FOR.
3. Очищает выходные строки и сохраняет их в файл grants.sql.

Восстановление пользователей и паролей MySQL

Шаги по восстановлению зависят от того, как был создан файл резервной копии. Если вы использовали mysqldump для экспорта таблицы user или всей базы данных mysql, используйте следующие команды для импорта данных:

# mysql -u root -p mysql < user_table_backup.sql

или

# mysql -u root -p mysql < mysql_db_backup.sql

После импорта данных в таблицы разрешений выполните команду:

FLUSH PRIVILEGES;

Это перезагрузит информацию о разрешениях, чтобы изменения вступили в силу немедленно без перезапуска служб.

Если вы экспортировали переносимые операторы GRANT с помощью скрипта, восстановите их так:

# mysql -u root -p < grants.sql

Снова выполните FLUSH PRIVILEGES, если эта команда не была включена в выходной файл скрипта.

Автоматизация резервного копирования с помощью cron и anacron: примеры настройки /etc/crontab и /etc/anacrontab

Запускать скрипты резервного копирования руками идея скажем не особо хорошая, запуск по расписанию идея отличная если сервер всегода доступен. Но есть еще наши любимые варианты с PET-проектами которые тоже хотелось бы бэкапить, но на ноутбук например и не особо принципиально и допустим я включил ноутбук, он полез фоном делать бэкап rsync-ом например.

Что такое cron и /etc/crontab?

Cron — это демон (сервис) в Unix-подобных операционных системах, который позволяет выполнять задачи по расписанию. /etc/crontab — это файл, в котором задаются задания для cron. Он содержит расписание запуска скриптов и команд. Каждая строка в файле представляет собой задание, которое состоит из нескольких полей: время выполнения, пользователь, который будет выполнять команду, и сама команда.

Что такое anacron и /etc/anacrontab?

Anacron — это утилита, которая позволяет выполнять задачи, запланированные с помощью cron, даже если компьютер был выключен в момент запланированного выполнения задачи. В отличие от cron, который работает в реальном времени, anacron запускает задания с определённым интервалом, когда система становится доступной. /etc/anacrontab — это файл конфигурации для anacron, где задаются задания и интервалы их выполнения.

Примеры настройки /etc/crontab и /etc/anacrontab для резервного копирования

1. Запуск резервного копирования при старте сервера.

Для запуска скрипта резервного копирования при старте сервера можно использовать не только cron, но и другие механизмы, например, систему инициализации systemd. Однако если необходимо использовать именно cron, можно добавить задание в файл /etc/crontab, которое будет запускаться сразу после загрузки системы. Для этого используется символ @reboot:

@reboot root /path/to/backup_script.sh

Здесь:

• @reboot — указание на запуск при загрузке системы;
• root — пользователь, от имени которого будет выполняться скрипт;
• /path/to/backup_script.sh — путь к скрипту резервного копирования.

Anacron не подходит для запуска задач при загрузке системы, так как он ориентирован на выполнение задач с определённым интервалом.

2. Запуск резервного копирования каждые шесть часов с использованием cron.

Чтобы скрипт резервного копирования запускался каждые шесть часов, нужно указать соответствующее расписание в /etc/crontab. Например:

0 */6 * * * root /path/to/backup_script.sh

Здесь:

• 0 — минута, в которую будет запускаться скрипт (в данном случае — в начале каждого часа, кратного шести);
• */6 — каждый шестой час;
• * * * — любые день, месяц и день недели;
• root — пользователь, от имени которого будет выполняться скрипт;
• /path/to/backup_script.sh — путь к скрипту.

Если сервер часто выключается и включается, можно использовать anacron для обеспечения выполнения задачи с интервалом в шесть часов. В /etc/anacrontab можно добавить запись следующего вида:

6H  6H  root  /path/to/backup_script.sh

Здесь:

• первое 6H — интервал между запусками (каждые шесть часов);
• второе 6H — задержка перед первым запуском после включения системы;
• root — пользователь, от имени которого будет выполняться скрипт;
• /path/to/backup_script.sh — путь к скрипту.

1. Гарантированный запуск скрипта раз в месяц для копирования бэкапов на другой сервер.

Чтобы обеспечить гарантированный запуск скрипта раз в месяц, можно использовать следующее расписание в /etc/crontab:

0 0 1 * * root /path/to/monthly_backup_script.sh

Здесь:

• 0 0 1 — запуск в 00:00 первого числа каждого месяца;
• * * — любой месяц и день недели;
• root — пользователь, от имени которого будет выполняться скрипт;
• /path/to/monthly_backup_script.sh — путь к скрипту, который будет копировать текущие бэкапы на другой сервер.

С помощью anacron можно настроить выполнение этой задачи, если система была выключена в первый день месяца. В /etc/anacrontab можно добавить запись:

1M  1M  root  /path/to/monthly_backup_script.sh

Здесь:

• первое 1M — интервал между запусками (раз в месяц);
• второе 1M — задержка перед первым запуском после включения системы;
• root — пользователь, от имени которого будет выполняться скрипт;
• /path/to/monthly_backup_script.sh — путь к скрипту.

Этот скрипт можно настроить так, чтобы он проверял, выполнялись ли предыдущие копии, и в случае необходимости выполнял дополнительные действия для обеспечения целостности резервных копий.

Использование cron и anacron для автоматизации резервного копирования — эффективный способ обеспечить регулярность и надёжность процесса. Правильно настроенные задания в /etc/crontab и /etc/anacrontab позволяют минимизировать риск потери данных и упростить процесс их восстановления, даже если система не всегда работает в режиме 24/7.

Корпоративное решение для резервного копирования: Vinchin Backup & Recovery (использую на крупном проекте) и т.п.

Для организаций, которым нужны более надёжные средства защиты, чем предоставляют встроенные инструменты, Vinchin Backup & Recovery предлагает корпоративное решение, поддерживающее различные инфраструктуры баз данных, включая MySQL, Oracle, SQL Server, MariaDB, PostgreSQL, PostgresPro и MongoDB. Среди возможностей — инкрементное резервное копирование, гибкие политики хранения данных, сжатие данных, запланированные резервные копии, защита от программ-вымогателей, интеграция с облачными и ленточными архивами, мгновенное восстановление на определённый момент времени и многое другое.

Рабочее решение, но стоит денег, причем хороших денег и для малого бизнеса или локальных пет-проектов я не стану их рекомендовать. Возможно чуть позже напишу обзорную инструкцию как это работает, у меня как раз на одном из проектов активно используется.

Итого.

Резервное копирование учётных записей и паролей MySQL обеспечивает бесперебойную миграцию и защищает от простоев, вызванных потерей учётных данных или нарушением цепочек разрешений. Независимо от того, используете ли вы встроенные инструменты, такие как mysqldump, или автоматизируете процессы с помощью Vinchin, вы сможете контролировать критически важный доступ в любое время.

Регулярное резервное копирование баз данных MySQL — необходимая мера для обеспечения безопасности данных. Используя mysqldump и автоматизируя процесс с помощью скриптов, можно создать надёжную систему резервного копирования, которая будет учитывать различные аспекты, такие как оповещения, сохранение паролей и ротация копий.