Назад в прошлое: как быстро восстановить MySQL на точку во времени / forpes.ru

15.05.2023 04:00

ostinru 0 2200 Источник

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]:

Разворачиваем новый инстанс MySQL из бекапа — не важно, физического (xtrabackup) или логического (mysqldump)
Запускаем mysqld
Берём бинлоги и с помощью команды mysqlbinlog --stop-datetime "1985-10-26 01:21:00" <binlog_name> | 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 со стилем

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

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

Установим percona-server и явно выставим:

# 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

Установим [link] (в примерах используются ночные сборки) и настроим wal-g :

# 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=<key_id>
AWS_SECRET_ACCESS_KEY=<access_key>

Снимем бекап:

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

После чего создадим тестовую нагрузку:

sysbench --db-driver=mysql --mysql-user=<user> --mysql-password=<pwd> --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=<user> --mysql-password=<pwd> --mysql-db=habr --range_size=100 --table_size=10000000 --tables=16 --threads=32 --time=3000 /usr/share/sysbench/oltp_read_write.lua run

Сохраним бинлоги (лучше делать cron-ом в компании с flock):

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;

попиваем смузи, пока wal-g делает свою работу

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

Итого

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

Метод	PiTR to time	PiTR to position	PiTR to GTID
PiTR с mysqlbinlog	✅ [1]	✅ [1]	✅ [1]
PiTR like a rock-star	????	✅ [2]	✅ [3]
PiTR с binlog-server	✅	✅ [2]	✅ [3]

[1] с помощью различных аргументов командной строки для mysqlbinlog

[2] с помощью CHANGE MASTER TO … MASTER_LOG_FILE=xxx MASTER_LOG_POS=yyy

[3] с помощью START REPLICA … SQL_BEFORE_GTIDS/SQL_AFTER_GTIDS=xxx

Если же мы сравним время выполнения — то новые методы работают раза в 2–3 быстрее! И при этом могут занимать не сильно больше места на диске! Все мои замеры и способы ещё разогнать recovery в таблице ниже:

Метод	PiTR	PiTR + хаки	дополнительное место
PiTR с mysqlbinlog	118min	65min [3][4]	пара бинлогов[6]
PiTR like a rock-star	45min	17min [1][2][3]	все применяемые бинлоги [5]
PiTR с binlog-server	48min	18min [2][3][4]	2 x relay_log_space_limit [6]

[1] log_replica_updates=0 - опция позволяет не копировать redo log в новые бинлоги: (Note: после применения redo-лога придётся убрать настройку из файла конфигурации и перезапустить mysqld) [MySQL 8.0.26+]

[2] binlog_transaction_dependency_tracking=WRITESET - увеличить параллелизм применения транзакций

[3] отключить redo log: ALTER INSTANCE DISABLE INNODB REDO_LOG;

[4] sync_binlog=3000 - реже вызываем fsync бинлогов

[5] x2 по месту в случае если не выставлена настройка log_replica_updates=0

[6] Вы же не полагаетесь на binlog_expire_logs_seconds, а удаляете бинлоги, которые уже сохранил wal-g?

Kudos

https://commitstrip.com/ за идею комиксов
https://github.com/Fizic за имплементацию binlog-server

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