Xtrabackup как пользоваться

MySQL — сверх-популярный сервер баз данных. Его используют (или использовали) практически все. Одна из самых популярных задач в системном администрировании — бэкап (и восстановление). Или, как подвид — миграция данных (бэкап + последующее восстановление). Это делают практически все. И практически все используют для mysql_dump, что категорически неправильно и часто просто опасно. В этой статье я расскажу, почему mysql_dump — это плохое решение и что с ним можно сделать.

Проблема

У mysql_dump есть три основных проблемы — скорость, неконсистентность и блокировки. Все три проблемы проистекают из его архитектуры, так что вылечить их невозможно. Чтобы понять проблемы, опишу алгоритм работы mysql_dump:

Самая серьезная проблема, которая лежит на поверхности — блокировки. В момент, когда mysql_dump блокирует таблицу — записать в нее что-либо становится невозможно. В лучшем случае изменения в таблице будут накапливаться (если приложение умеет копить изменения и работать с базой асинхронно, как gorm). В худшем случае приложение упадет с ошибкой записи, а пользователь получит фигу в виде ошибки 500. Проблема консистентности лежит глубже, а потому она много опаснее, так как сходу эти грабли неочевидны, а по лбу бьют больно. Рассмотрим пример. У нас есть база, в которой лежит 3 таблицы: “affilates”, “logs” и “organisations”. Таблица logs очень большая. Таблицы affilates и organisations связаны через внешний ключ. Приложение вставляет запись в “affilates”, а затем — пачку записей в “organisations” с ключом, указывающим на запись в “affilates”. По логике работы mysql_dump, первой будет блокирована и сдамплена affilates. Записи в organisations можно вставлять только для уже существующих affilates, целосность данных не нарушается. После того, как affilates закончится и разблокируется, будет заблокирована logs. Как я уже писал, по условиям задачи она у нас большая и копируется, скажем, час (вполне реальный срок для большой таблицы). При этом никто не мешает создать запись в affilates, а потом из organisations сослаться на эту новую запись. Так, как упаковка affilates уже завершена — в дамп новая запись не попадет. После того, как мы упакуем organisations — в organisations образуются записи, ссылающиеся на не существовавшие в момент дампа ключи. И восстановить такой дамп без определенных магических движений будет невозможно. Ну и в качестве бонуса — мы потеряем данные, которые были созданы в процессе дампа, то есть целосность дампа будет на момент начала операции, а не ее окончания. Чтобы бороться со всеми этими проблемами и был придуман xtrabackup.

В статье рассматривается создание резервных копий баз данных MySQL, MariaDB (без
остановки работы БД) с помощью бесплатной утилиты Percona XtraBackup.

Для установки предварительно понадобится добавить репозиторий Percona:

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

Добавление репозитория Percona

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

yum update
yum install percona-xtrabackup

Установка Percona Xtrabackup

Percona XtraBackup
представляет собой набор следующих инструментов:

Блокировки таблиц в процессе дампа  не будет происходить при использовании системы хранения InnoDB или XrtraDB. При использовании MyISAM блокировка таблиц будет производиться.

Предположим, СУБД MySQL/MariaDB уже установлена.
Непосредственно для выполнения резервных копий создадим отдельного
пользователя.

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

Предоставление привилегий пользователю:

Изменение привилегий пользователя

Для создания бэкапа в директории /root/backups
запустить команду

В папке /root/backups будет создана
поддиректория с текущей датой и временем.

Создание горячего бэкапа базы данных

С помощью опции —no-timestamp можно запретить
создание поддиректории с временной меткой.

В случае успешного завершения в консоли появится надпись «Completed OK!»

Завершение процесса создания резервной копии

Для создания инкрементальных бэкапов вначале необходимо сделать полный
бэкап.

innobackupex backups/
innobackupex —incremental=/root/inc_backups/ —incremental-basedir=/root/backups
—incremental — директория для бэкапа
—incremental-basedir – прошлый бэкап

В случае возникновения ошибки «Failed
to connect to MySQL server: Can’t connect to local MySQL server through socket
‘/tmp/mysql.sock’»

Можно перезапустить сервис

systemctl restart mysql

Либо завершить (kill)
процесс mysql и заново
запустить его

killall -9 mysqld
/etc/init.d/mysql start

Далее проверить использование файла:

lsof /tmp /mysql.sock

Если файл был ранее кем-то занят – завершаем приложение, если файл не
найден – создаем сокет и назначаем права:

mkfifo /tmp/mysql.sock
chown -R mysql /tmp/mysql.sock

Проверка занятости сокета

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

Подготовим созданную копию:

innobackupex —apply-log /root/backups

Подготовка резервной копии

После этого необходимо убедиться в отсутствии ошибок.

Если БД имеет большой объем и на сервере достаточно памяти, можно
использовать опцию —use-memory = memorynumber для задания innobackupex объема разрешенной для
использования памяти

innobackupex —apply-log —use-memory=4G /root/backups

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

systemctl stop mariadb (в случае mysql — systemctl stop mysql)

И, также, переместите прежнее содержимое каталога MySQL

mkdir /root/backups/mysql_old/
mv /var/lib/mysql/* /root/backups/mysql_old/

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

innobackupex —copy-back /root/backups/ 2019-12-02_17-42-54/

После появления в консоли надписи «Completed OK!» можно удостовериться, что данные
успешно восстановлены.

После этого можно изменить владельца каталога MySQL на пользователя MySQL и заново запустить службу:

chown -R mysql:mysql /var/lib/mysql
systemctl start mariadb

Изменение прав, запуск сервиса

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

Процесс восстановления  закончен.
Данные MySQL успешно
восстановлены с помощью утилиты percona-xtrabackup.

16 декабря, 2015 12:06 пп

MariaDB, mySQL, Ubuntu

Для активных систем баз данных горячее резервное копирование (то есть, создание резервных копий без остановки сервиса или изменения прав доступа) является процессом довольно проблемным. Простое копирование файлов данных в таком случае часто приводит ко внутренним противоречиям и несоответствиям данных (например, транзакции, которые произошли во время копирования, будут отсутствовать). С другой стороны, при остановке работы базы данных на время резервного копирования часть приложения, зависящая от БД, будет недоступна. Percona XtraBackup – это открытая утилита, позволяющая обойти эту проблему и создать последовательную полную или инкрементную резервную копию баз данных на запущенном сервере MySQL, MariaDB и Percona (что и называется горячим резервным копированием, или hot backup).

В отличие от логического резервного копирования (для которого предназначена утилита mysqldump), XtraBackup создает физические резервные копии файлов базы данных. Полученные резервные копии БД можно скопировать в удалённую систему при помощи rsync или системы резервного копирования Bacula.

Это руководство поможет создать полную резервную копию БД MySQL или MariaDB при помощи Percona XtraBackup на Ubuntu 14.04. Также оно охватывает процесс восстановления базы данных из резервной копии.

Примечание: Аналогичное руководство для CentOS можно найти здесь.

Требования

Примечание: XtraBackup можно использовать и с MyISAM, но в таком случае БД будет ненадолго заблокирована к концу резервного копирования.

Проверка движка базы данных

Если вы не знаете точно, какой движок использует БД, вы можете узнать это несколькими способами. Во-первых, это можно сделать через консоль MySQL; запросите статус каждой таблицы необходимой базы данных. Чтобы открыть консоль:

mysql -u root -p

Введите root-пароль MySQL.

В командной строке MySQL выберите базу данных, которую нужно проверить.

Запросите статус таблиц:

SHOW TABLE STATUSG;

Движок будет указан в выводе:

Установка Percona XtraBackup

Проще всего для установки Percona XtraBackup использовать пакетный менеджер apt-get.

Добавьте ключ репозитория Percona:

sudo apt-key adv —keyserver keys.gnupg.net —recv-keys 1C4CBDCDCD2EFD2A

Добавьте репозиторий Percona в apt source:

Запустите команду, чтобы обновить список пакетов apt:

sudo apt-get update

Чтобы установить XtraBackup, запустите команду:

sudo apt-get install percona-xtrabackup

Пакет XtraBackup состоит из собственно программы XtraBackup и Perl-скрипта innobackupex, который используется для создания бэкапа базы данных.

Подготовка системы

Прежде чем впервые запустить XtraBackup, нужно подготовить пользователей системы и MySQL.

Пользователь системы

Если вы не планируете использовать root-пользователя системы, то нужно подготовить систему к работе XtraBackup.

Примечание: В данном разделе предполагается, что вы работаете в сессии пользователя XtraBackup, у которого есть права суперпользователя.

Добавьте пользователя в группу mysql:

Создайте каталог для хранения резервных копий XtraBackup:

Команда chown даёт пользователю право на запись в каталоге backups.

Пользователь MySQL

Для корректной работы XtraBackup необходим отдельный пользователь MySQL, при помощи которого будет выполняться резервное копирование. Создайте такого пользователя.

Откройте консоль MySQL:

Затем передайте новому пользователю права на перезагрузку, блокировку и репликацию всех баз данных.

Это минимальные права, необходимые для создания полного бэкапа БД при помощи XtraBackup.

Закройте консоль MySQL:

Теперь всё готово к созданию полной резервной копии базы данных.

Полная горячая резервная копия БД

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

Права на каталог данных

В системе Ubuntu 14.04 файлы данных хранятся в каталоге /var/lib/mysql (который также называется datadir). По умолчанию доступ к этому каталогу заблокирован для всех, кроме пользователя mysql. XtraBackup требует доступа к этому каталогу для создания бэкапа, потому нужно передать созданному для XtraBackup пользователю соответствующие права.

Эти команды открывают группе mysql доступ ко всем каталогам datadir.

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

Итак, теперь можно запустить резервное копирование. Чтобы скопировать работающую БД MySQL, используйте утилиту innobackupex.

Примечание: Замените условные данные в команде данными своего пользователя MySQL.

Это создаст резервную копию БД в /data/backups/new_backup:

innobackupex output
innobackupex: Backup created in directory ‘/data/backups/new_backup’
150420 13:50:10  innobackupex: Connection to database server closed
150420 13:50:10  innobackupex: completed OK!

Также можно пропустить флаг –no-timestamp, чтобы программа XtraBackup создала каталог резервного копирования с текущей временной меткой:

Это создаст резервную копию БД в автоматически сгенерированном подкаталоге:

innobackupex output — no timestamp
innobackupex: Backup created in directory ‘/data/backups/2015-04-20_13-50-07’
150420 13:50:10  innobackupex: Connection to database server closed
150420 13:50:10  innobackupex: completed OK!

С флагом или без него, программа должна вернуть «innobackupex: completed OK!» в последней строке результата.

Подготовка резервной копии

Последнее, что нужно сделать, – это подготовить горячую копию БД к использованию; для этого нужно воспроизвести лог транзакций и внести все несовершённые транзакции в резервную копию. Подготовка резервной копии позволяет согласовать и уточнить данные.

Чтобы подготовить полученную копию (/data/backups/new_backup), нужно запустить команду:

innobackupex —apply-log /data/backups/new_backup

В случае успешного выполнения команды программа вернёт «innobackupex: completed OK!».

Теперь резервную копию можно восстановить. Кроме того, если у вас есть система резервного копирования файлов (например, Bacula), эта резервная копия базы данных должна быть добавлена в backup selection.

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

Для восстановления резервной копии XtraBackup нужно остановить БД и очистить datadir.

Остановите сервис MySQL.

sudo service mysql stop

Переместите или удалите содержимое каталога данных (/var/lib/mysql). В этом примере данные будут просто временно перемещены:

mkdir /tmp/mysql
mv /var/lib/mysql/* /tmp/mysql/

Теперь можно восстановить резервную копию:

innobackupex —copy-back /data/backups/new_backup

Восстановленные файлы будут принадлежать пользователю, запустившему резервное копирование. Передайте права mysql, чтобы система MySQL могла читать и писать в файлах.

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

Теперь можно запустить MySQL:

sudo service mysql start

Резервная копия БД полностью восстановлена.

Заключение

Теперь можно без проблем создавать горячие копии БД MySQL с помощью Percona XtraBackup; однако нужно иметь в виду несколько моментов.

Во-первых, рекомендуется автоматизировать процесс бэкапа, чтобы всегда иметь под рукой актуальную версию данных. Во-вторых, хранить резервные копии лучше удалённо на случай, если с сервером базы данных возникнут проблемы (для этого можно использовать Rsync, систему резервного копирования Bacula и т.п.). Также нужно настроить ротацию резервных копий (т.е. процесс замены устаревших копий более актуальными) и ознакомиться с созданием инкрементальных резервных копий (с помощью XtraBackup), чтобы сэкономить дисковое пространство.

11 декабря, 2015 11:54 дп

Centos, MariaDB, mySQL

Данное руководство поможет создать полную резервную копию БД MySQL или MariaDB при помощи Percona XtraBackup на CentOS 7. Также оно охватывает процесс восстановления базы данных из резервной копии.

Примечание: Аналогичное руководство для Ubuntu можно найти здесь.

В выводе будет указан движок:

После этого закройте консоль:

Проще всего установить Percona XtraBackup можно при помощи пакетного менеджера yum, поскольку репозиторий Percona предоставляет пакет RPM.

sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

После этого запустите установку XtraBackup:

sudo yum install percona-xtrabackup

Примите все запросы подтверждения, чтобы завершить установку.

Права на каталог даных

В системе CentOS 7 файлы данных MySQL хранятся в каталоге /var/lib/mysql (который также называется datadir). По умолчанию доступ к этому каталогу заблокирован для всех, кроме пользователя mysql. XtraBackup требует доступа к этому каталогу для создания бэкапа, потому нужно передать созданному для XtraBackup пользователю соответствующие права.

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

Подготовка БД

Остановите сервис MySQL:

sudo systemctl stop mariadb

Переместите или удалите содержимое каталога данных (/var/lib/mysql). В данном примере данные будут просто временно перемещены:

sudo systemctl start mariadb

Примерение

innobackupex —rsync —defaults-file/root/.percona —no-timestamp /var/tmp/backup
innobackupex —rsync —defaults-file/root/.percona —no-timestamp —apply-log /var/tmp/backup

Восстановление выглядит еще проще. Нужно просто остановить процесс mysql и положить файлы в datadir (обычно это /var/lib/mysql/), а затем — запустить mysql обратно. Перед запуском — обязательно сменить права доступа на файлы, чтобы файлами владел владелец процесса mysql:

service mysql stop
rm -rf /var/lib/mysql/*
mv /var/tmp/backup/* /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/
service mysql start

Усложненный вариант — создаем mysql slave от имеющегося мастера

innobackupex —rsync —defaults-file/root/.percona —no-timestamp /var/tmp/backup
innobackupex —rsync —defaults-file/root/.percona —no-timestamp —apply-log /var/tmp/backup
rsync -rahP /var/tmp/backup NEW. SERVER. IP:/var/tmp/

#выдадим права для slave, чтобы он мог читать данные из master-сервера:
mysql —defaults-file/root/.percona -Bse

innobackupex создает специальный файл с информацией о текущем бинлоге и позиции в этом бинлоге. Эта информация необходима для подключения slave — чтобы slave мог знать, с какого точно момента в прошлом была сделана эта копия базы (и начал синхронизацию именно с нужного момента). Файл называется xtrabackup_binlog_info. Содержимое у него выглядит примерно вот так:

Подключим slave-сервер к master-серверу. Для этого остановим mysql и подложим в datadir файлы, которые мы скопировали с master:

service mysql stop
rm -rf /var/lib/mysql/*
mv /var/tmp/backup/* /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
service mysql start

Теперь подключим slave к master. Для этого в консоли mysql:

Сложный вариант — переносим отдельную таблицу

Искушающе простой способ (остановить mysql, подсунуть файлы с таблицей и запустить обратно) — не сработает, техника немного сложнее. Чтобы восстановить таблицу, нужно подготовить таблицу внутри резеврной копии к экспорту:

xtrabackup —prepare —export —target-dir/var/tmp/backup

Теперь нужно создать таблицу с именем экспортируемой таблицы. Структура и содержимое не важны, важно только имя таблицы (имя базы тоже можно не учитывать):

mysql ineedtorestore (a int() ) ENGINEInnoDB CHARSETlatin1;
mysql ineedtorestore DISCARD TABLESPACE;

Кстати, если при вводе второй таблицы выскочит ошибка ERROR 1030 (HY000): Got error -1 from storage engine, то нужно включить в my.cnf настройку innodb_file_per_table.

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

mysql ineedtorestore IMPORT TABLESPACE;

После этого мы получим новую таблицу из бэкапа.

Установка и настройка

xtrabackup — отдельный инструмент от компании percona. Из-за этого в большинстве штатных системных репозиториев он отсутствует — нужно ставить его из репозитория percona. Установка для debian и ubuntu:

wget https://repo.percona.com/apt/percona-release_0.1-3.lsb_release -sc_all.deb
dpkg -i percona-release_0.1-3.lsb_release -sc_all.deb
apt-get update
apt-get install percona-xtrabackup

То же самое для RHEL-based (CentOS, RedHat, Fedora, ScientificLinux)

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
yum clean all
yum makecache
yum install -y percona-xtrabackup