Резервное копирование MySQL

Это руководство поможет вам выполнить полный резервный копирование базы данных MySQL без остановки системы. Оно было написано и протестировано командой devops Navixy, поэтому вы можете доверять ему как проверенному рабочему решению.

Эта инструкция действительна для MySQL версий 8.xx (меньше или равно 8.0.29) и 5.xx. Она написана для, протестирована и может быть применена на следующих ОС:

• Ubuntu 18.04, 20.04

• Debian 9, 10, 11

Для других ОС и версий программного обеспечения её можно применять и адаптировать на ваш собственный риск.

Что необходимо для выполнения полного резервного копирования базы данных MySQL без остановки системы:

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

• Установите дополнительное программное обеспечение - https://www.percona.com/software/mysql-database/percona-xtrabackup

• Получите учетные данные существующего пользователя MySQL или создайте пользователя специально для резервного копирования

• Все операции должны выполняться от имени пользователя с правами root или с использованием sudo.

Ниже мы подробнее рассмотрим шаги, которые необходимо выполнить.

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

В качестве примера мы создадим пользователя с именем bkpuser с минимальными правами, достаточными для создания полного резервного копирования, и паролем s3cret.

Сначала необходимо войти в консоль MySQL с административными правами, например:

mysql -uroot -p

Затем в консоли MySQL введите команды, специфичные для различных версий MySQL:

MySQL 5.xx

CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cret';
CREATE USER 'bkpuser'@'%' IDENTIFIED BY 's3cret';
GRANT SELECT, RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
GRANT SELECT, RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkpuser'@'%';
FLUSH PRIVILEGES;

MySQL 8.xx

CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cret';
CREATE USER 'bkpuser'@'%' IDENTIFIED BY 's3cret';
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'%';
GRANT SELECT ON performance_schema.log_status TO 'bkpuser'@'localhost';
GRANT SELECT ON performance_schema.log_status TO 'bkpuser'@'%';
GRANT SELECT ON performance_schema.keyring_component_status TO bkpuser@'localhost';
GRANT SELECT ON performance_schema.keyring_component_status TO bkpuser@'%';
FLUSH PRIVILEGES;

Том для резервного копирования

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

Чтобы определить размер тома, необходимого для резервного копирования базы данных, нужно узнать размер текущей базы данных и умножить его на 1.5.

Если ваша база данных находится в стандартном расположении, заданном при установке сервера MySQL, необходимо выполнить следующую команду с правами root:

du -sh /var/lib/mysql

Если у вас нестандартное расположение базы данных, укажите путь к базе данных в качестве аргумента команды location, например:

du -sh /your_path_to_database

Пример расчёта

На скриншоте видно, что текущий размер базы данных MySQL составляет 181 GB. Следовательно, размер тома для резервного копирования этой базы должен быть не менее 181 * 1.5 = 271.5 GB, округлённого вверх до 272 GB

Расчёт применим к одной копии базы данных. Если вы планируете хранить более одной резервной копии базы, не забудьте умножить результат на количество хранимых копий.

Программное обеспечение для резервного копирования

После подключения нового тома и создания на нём файловой системы необходимо установить программное обеспечение, требуемое для резервного копирования базы данных. Это программное обеспечение называется https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html. Рассмотрите возможность установки его с использованием готовых пакетов установки, доступных в репозиториях Percona XtraBackup.

Установка выполняется через пакет, содержащий утилиту percona-release для конфигурации.

Прежде всего, загрузите этот пакет из репозиториев Percona XtraBackup с помощью следующей команды:

wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb

Следующим шагом запустите установку загруженного пакета с помощью утилиты dpkg. Вы должны быть root или использовать sudo для выполнения этой команды:

sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb

Теперь настройте репозитории, необходимые для этой цели:

percona-release enable-only tools
apt-get update

---

Исключение для Debian 10

На Debian 10 указанный выше шаг возвращает ошибку:

Чтобы исправить эту ошибку, необходимо отредактировать файл /etc/apt/sources.list

Откройте этот файл в любом текстовом редакторе, например:

root@debian10:~# nano /etc/apt/sources.list

Добавьте следующую строку в список в начале файла:

deb http://ftp.de.debian.org/debian buster-backports main

Содержимое файла будет выглядеть примерно так:

#
# deb cdrom:[Debian GNU/Linux 10.4.0 _Buster_ - Official amd64 NETINST 20200509-10:25]/ buster main

# deb cdrom:[Debian GNU/Linux 10.4.0 _Buster_ - Official amd64 NETINST 20200509-10:25]/ buster main
deb http://ftp.de.debian.org/debian buster-backports main

deb http://mirror.corbina.net/debian/ buster main
deb-src http://mirror.corbina.net/debian/ buster main

deb http://security.debian.org/debian-security buster/updates main
deb-src http://security.debian.org/debian-security buster/updates main

# buster-updates, ранее известный как 'volatile'
deb http://mirror.corbina.net/debian/ buster-updates main
deb-src http://mirror.corbina.net/debian/ buster-updates main

# Эта система была установлена с использованием небольших переносных носителей
# (например, netinst, live или single CD). Соответствующие записи "deb cdrom"
# были отключены в конце процесса установки.
# Для получения информации о том, как настроить источники пакетов apt,
# смотрите руководство sources.list(5).

Сохраните изменения в файле, а затем выполните следующие команды:

apt update
apt install curl/buster-backports
apt --fix-broken install
percona-release enable-only tools
apt-get update

---

Теперь вы готовы установить утилиту резервного копирования. Для этого выполните следующую команду от root или с sudo:

для MySQL версии 5.xx:

sudo apt install percona-xtrabackup-24

для версий MySQL 8.xx, меньших или равных 8.0.29:

sudo apt install percona-xtrabackup-80

На этом процедура установки утилиты Percona XtraBackup завершена.

Создание резервной копии

Теперь вы создадите полный резерв базы данных с помощью Percona XtraBackup.

• команда резервного копирования выполняется на том же сервере, где хранится копируемая база данных

• в приведённом ниже примере дополнительный том для резервного копирования смонтирован в /mnt/backups папке

• используется заранее созданный пользователь bkpuser с паролем s3cret для подключения к базе данных

xtrabackup --user=bkpuser --password=s3cret --backup --target-dir=/mnt/backups/

После завершения резервного копирования необходимо подготовить скопированную базу данных к работе. Без этой операции база данных будет нерабочей.

xtrabackup --prepare --target-dir=/mnt/backups/

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

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