Назад в прошлое: как быстро восстановить MySQL на точку во времени15.05.2023 10:01

Intro

Point in Time Recovery (PiTR) — это восстановление базы данных на какой-то конкретный момент времени (с точностью до секунд или до конкретной транзакции).

PiTR невероятно полезен для восстановления базы данных после того, как «случилось непоправимое». Если достаточно точно выбрать точку на которую восстанавливать базу, то можно восстановить базу данных практически без потери данных.

Fun fact: в MySQL client есть настройка --i-am-a-dummy [doc], которая предотвращает ошибку как на картинке — UPDATE и DELETE запросы без WHERE / LIMIT не будут выполнены и будет возвращена ошибка

Теория говорит нам, что для того, чтобы восстановить базу на точку во времени нам нужно взять снепшот состояния и применить к нему лог изменений (так работает Replicated State Machine). Если все применяемые изменения детерминированные (зависят только от состояния базы данных, и применяемых изменений и никак не зависят от внешних обстоятельств — времени, имени хоста и т.п.), то полученная база данных не будет отличаться от базы данных которую мы видели в прошлом. К счастью, тулинг вокруг MySQL позволят создавать снепшоты (бекапы), а бинлог как раз содержит лог применяемых изменений.

PiTR как завещали деды

Существует годами проверенный PiTR, описанный в официальной документации [link]:

1. Разворачиваем новый инстанс MySQL из бекапа — не важно, физического (xtrabackup) или логического (mysqldump)

2. Запускаем mysqld

3. Берём бинлоги и с помощью команды mysqlbinlog --stop-datetime "1985-10-26 01:21:00" | mysql применяем бинлоги к MySQL (при желании, можно указать --stop-position, если хотим позиционировать восстановление с точностью до транзакции). Здесь происходит следующее:

   1. mysqlbinlog превращает записи из binlog в plain SQL запросы

   2. далее SQL запросы через unix pipe передаются в mysql-client

   3. mysql-client по сети (обычно через localhost) отправляет SQL запросы в MySQL сервер

   4. MySQL сервер будет выполнять все транзакции одну за другой.

Этот метод проверен годами — работает достаточно надежно. Но как видно из описания всей процедуры — происходит много преобразований и пересылок данных между процессами. К тому же при преобразовании бинлога в SQL теряется информация о binlog grop commit [link] и поэтому транзакции выполняются последовательно, одна за другой.

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

PITR со стилем

Для начала возьмём швейцарский нож бекапов баз данных — wal-g. Этот тул забёрет на себя бОльшую часть головняка — с помощью него можно организовать хранение бекапов и бинлогов на внешнем хранилище (S3), сжатие и шифрование.

Подготовка стенда

# Set unique server_id
server_id=1

# Enable GTIDs
gtid_mode=ON
enforce_gtid_consistency=ON

# Enable Group Commit
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=8

# Typical MySQL config
WALG_MYSQL_DATASOURCE_NAME=root:Qwerty12345@tcp(localhost:3306)/habr
WALG_STREAM_CREATE_COMMAND=/usr/bin/xtrabackup --backup --stream=xbstream --datadir=/var/lib/mysql
WALG_STREAM_RESTORE_COMMAND=xbstream -x -C /var/lib/mysql
WALG_MYSQL_BACKUP_PREPARE_COMMAND=xtrabackup --prepare --target-dir=/var/lib/mysql
WALG_MYSQL_BINLOG_REPLAY_COMMAND=mysqlbinlog --stop-datetime="$WALG_MYSQL_BINLOG_END_TS" "$WALG_MYSQL_CURRENT_BINLOG" | mysql
WALG_MYSQL_BINLOG_DST=/var/lib/mysql/my-binlogs

# (De)compress backup in 6 threads:
WALG_STREAM_SPLITTER_PARTITIONS=6
WALG_COMPRESSION_METHOD=zstd

# Storage
WALG_S3_PREFIX=s3://backet-name/mysql/
AWS_ENDPOINT=storage.yandexcloud.net
AWS_ACCESS_KEY_ID=
AWS_SECRET_ACCESS_KEY=

wal-g-mysql --turbo --config .walg.properties backup-push

sysbench --db-driver=mysql --mysql-user= --mysql-password= --mysql-db=habr --range_size=100 --table_size=10000000 --tables=16 --threads=32 --time=3000 /usr/share/sysbench/oltp_read_write.lua preapre
sysbench --db-driver=mysql --mysql-user= --mysql-password= --mysql-db=habr --range_size=100 --table_size=10000000 --tables=16 --threads=32 --time=3000 /usr/share/sysbench/oltp_read_write.lua run

mysql > FLUSH LOGS;
bash  > wal-g-mysql --config .walg.properties  binlog-push

Непоправимо «испортим» базу данных… И восстановим ее по инструкции с сайта документации wal-g [link].

service mysql stop
rm -rf /var/lib/mysql/*  # Oops!

(1) wal-g-mysql --turbo --config .walg.properties backup-fetch LATEST
⏱️ real    1m44.531s

(2) service mysql start
(3) gtids=$(tr -d '\n' < /var/lib/mysql/xtrabackup_binlog_info | awk '{print $3}')
(4) mysql -e "RESET MASTER; SET @@GLOBAL.GTID_PURGED='$gtids';"

time wal-g-mysql --turbo --config .walg.properties binlog-replay --since LATEST --until "2030-01-02T15:04:05Z
⏱️ real    118m3.330s

Восстановление бекапа заняло 2 минуты, а скачивание и применение бинлогов 118 минут(!). Можно отметить, что это значительно дольше чем время работы sysbench (50 минут), нагрузка от которого и создала все эти бинлоги.

База восстановлена, PROD спасен! Но можно ли восстановить базу быстрее?

PiTR like a rock-star

Этот подход я впервые увидел в блоге [link][slides][video] Frédéric (Lefred) Descamps. Идея заключается в том, что можно «подсунуть» серверу MySQL бинлоги как будто сервер их только что скачал с мастера, но еще не успел применить. В MySQL такие логи называются relay logs. MySQL, увидев relay logs, начнёт их применять так быстро, как сможет!

В my.cnf отключаем slave-threads, чтобы не случился фальстарт, и указываем как мы назовём relay log-и:

skip-slave-start
relay_log=mysql1

Восстанавливаем базу из бекапа, как и в прошлый раз (шаги 1–4). Далее, скачиваем бинлоги:

time wal-g-mysql --turbo --config .walg.properties binlog-fetch --since LATEST --until "2030-01-02T15:04:05Z"
⏱️ real    1m39.685s

Переименуем наши бинлоги в relay логи так, чтобы их название совпадало с ожиданием MySQL:

cd /var/lib/mysql

for i in $(ls my-binlogs/*.0*) 
do  
  ext=$(echo $i | cut -d'.' -f2); 
  mv $i mysql1-relay-bin.$ext; 
done

Создадим индекс:

cd /var/lib/mysql
ls ./mysql1-relay-bin.0* > mysql1-relay-bin.index

Применяем бинлоги:

mysql> SET GLOBAL SERVER_ID = 99;
mysql> CHANGE REPLICATION SOURCE  TO RELAY_LOG_FILE='mysql1-relay-bin.000057', RELAY_LOG_POS=1, SOURCE_HOST='dummy';
mysql> START REPLICA SQL_THREAD;

Ю-ху! Бекап развернули за 2 минуты, за 2 минуты скачали бинлоги и за 45 минут примененили бинлоги! Более чем в два раза быстрее!

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

mysql> SHOW PROCESSLIST
+----+-----------------+-----------+------+---------+-------+---------------------------------------------+------------------+---------+-----------+---------------+
| Id | User            | Host      | db   | Command | Time  | State                                       | Info             | Time_ms | Rows_sent | Rows_examined |
+----+-----------------+-----------+------+---------+-------+---------------------------------------------+------------------+---------+-----------+---------------+
|  5 | event_scheduler | localhost | NULL | Daemon  |   190 | Waiting on empty queue                      | NULL             |  190001 |         0 |             0 |
| 10 | root            | localhost | NULL | Query   |     0 | init                                        | show processlist |       0 |         0 |             0 |
| 35 | system user     |           | NULL | Query   |     0 | Waiting for dependent transaction to commit | NULL             |       0 |         0 |             0 |
| 36 | system user     |           | habr | Query   | 19129 | Applying batch of row changes (update)      | NULL             |       0 |         0 |             0 |
| 37 | system user     |           | habr | Query   | 19129 | Applying batch of row changes (update)      | NULL             |       0 |         0 |             0 |
| 38 | system user     |           | habr | Query   | 19129 | Applying batch of row changes (update)      | NULL             |       0 |         0 |             0 |
| 39 | system user     |           | habr | Query   | 19129 | Applying batch of row changes (update)      | NULL             |       0 |         0 |             0 |
| 40 | system user     |           | habr | Query   | 19129 | Applying batch of row changes (update)      | NULL             |       0 |         0 |             0 |
| 41 | system user     |           | habr | Query   | 19129 | Applying batch of row changes (update)      | NULL             |       0 |         0 |             0 |
| 42 | system user     |           | habr | Query   | 19129 | Applying batch of row changes (update)      | NULL             |       0 |         0 |             0 |
| 43 | system user     |           | habr | Query   | 19129 | Applying batch of row changes (update)      | NULL             |       0 |         0 |             0 |
+----+-----------------+-----------+------+---------+-------+---------------------------------------------+------------------+---------+-----------+---------------+

Supersonic PiTR

Можно ли восстановить и быстро, и не испачкавшись в bash? Для этого воспользуемся wal-g binlog-server. Он будет прикидываться MySQL master-ом и отдавать бинлоги с S3 в бинарном виде сразу в наш MySQL. При этом он возьмёт на себя заботу о декомпрессии и дешифровке файлов.

Донастроим mysql:

relay_log_space_limit=8589934592
skip-slave-start

и wal-g

WALG_MYSQL_BINLOG_SERVER_HOST=localhost
WALG_MYSQL_BINLOG_SERVER_PORT=9306
WALG_MYSQL_BINLOG_SERVER_USER=walg
WALG_MYSQL_BINLOG_SERVER_PASSWORD=walgpwd
WALG_MYSQL_BINLOG_SERVER_ID=99

WALG_MYSQL_BINLOG_SERVER_REPLICA_SOURCE=root:Qwerty12345@127.0.0.1:3306/habr

Теперь восстановить MySQL можно будет следующим образом:

• восстанавливаемся из бекапа с помощью wal-g backup-fetch (уже знакомые нам шаги 1–4 из первой части)

• Запускаем wal-g binlog-server

• Запускаем MySQL

• Настраиваем репликацию с wal-g:

SET GLOBAL SERVER_ID=999;
CHANGE MASTER TO MASTER_HOST="127.0.0.1", MASTER_PORT=9306, MASTER_USER="walg", MASTER_PASSWORD="walgpwd", MASTER_AUTO_POSITION=1;
SHOW REPLICA STATUS \G
START REPLICA;

На моём стенде этот вариант уложился в 48 минут, что очень близко к подходу «like a rock-star», если учесть 2 минуты на скачивание и распаковку бинлогов. В реальной жизни я бы ожидал, что этот метод отработает быстрее за счет одновременности скачивания бинлогов и их применения. Дополнительным плюсом этого подхода — необходимо меньше дополнительного места на диске и с ним сложнее ошибиться =)

Итого

Мы трижды восстановили базу MySQL на точку в прошлом. PiTR like a rock-star чуть менее гибок — он не позволяет указать точку во времени, можно восстановиться только на конкретную транзакцию: