Это руководство поможет вам сделать полную резервную копию базы данных MySQL без остановки работы системы. Оно было написано и протестировано devops-командой Navixy, поэтому вы можете доверять ему как заведомо рабочему решению.
Данная инструкция действительна для MySQL версий 8.xx (меньше или равной 8.0.29) и 5.xx.
Она написана для, протестирована и может быть применена на следующих ОС:
- Ubuntu 18.04, 20.04
- Debian 9, 10, 11
Для других версий ОС и программного обеспечения инструкция также может быть применена, но на ваш страх и риск.
Что вам потребуется для создания полной резервной копии базы данных MySQL без остановки работы системы:
- Подключить дополнительный том на отдельном диске, на который будет производиться резервное копирование.
- Установить дополнительное программное обеспечение - Percona XtraBackup
- Получить учетные данные существующего пользователя базы данных MySQL, либо создать пользователя специально для резервного копирования
- Все операции должны выполняться от имени пользователя с правами root или с использованием sudo.
Ниже мы более подробно рассмотрим шаги, которые вам необходимо предпринять.
Создание пользователя
В качестве примера мы создадим пользователя с именем bkpuser с минимальными правами, необходимыми для создания полной резервной копии, и паролем s3cret.
Сначала вам нужно войти в консоль MySQL с правами администратора, например:
mysql -uroot -p
Затем в консоли MySQL введите следующие команды (различные для разных версий MySQL):
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;
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
Если у вас нестандартное расположение базы данных, вам необходимо указать путь к базе данных в качестве аргумента к команде:
du -sh /путь/до/базы/данных
На скриншоте видно, что текущий размер базы данных MySQL составляет 181 ГБ.
Следовательно, размер тома для резервного копирования этой базы данных должен быть не менее 181 * 1.5 = 271.5 ГБ, округлим до 272 ГБ.
Пример расчетов приводится для одной копии базы данных. Если вы планируете хранить более одной резервной копии базы данных, необходимо умножить результат на количество хранимых резервных копий.
Программное обеспечение для резервного копирования
После подключения нового тома и создания на нем файловой системы необходимо установить программное обеспечение, необходимое для резервного копирования базы данных.
Это программное обеспечение называется Percona XtraBackup. Ниже мы рассмотрим его установку с помощью готовых установочных пакетов, доступных в репозиториях 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 этот шаг приводит к ошибке:
Разверните информационный блок ниже, чтобы узнать, как это исправить.
Чтобы исправить эту ошибку, необходимо отредактировать файл /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, previously known as 'volatile'
deb http://mirror.corbina.net/debian/ buster-updates main
deb-src http://mirror.corbina.net/debian/ buster-updates main
# This system was installed using small removable media
# (e.g. netinst, live or single CD). The matching "deb cdrom"
# entries were disabled at the end of the installation process.
# For information about how to configure apt package sources,
# see the sources.list(5) manual.
Сохраните изменения в файле, а затем выполните следующие команды:
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/
Теперь у вас имеется копия базы данных, актуальная на момент её создания, созданная без остановки сервера и без снятия с него рабочей нагрузки.
Резервная копия может быть использована для восстановления базы данных в чрезвычайной ситуации.