В прошлом уроке мы снимали резервную копию. У неё два слабых места. Восстановление идёт долго, а всё, что продали после снятия копии, пропадает. Кинотеатр с сотней касс так рисковать не может. Сегодня разбираем, как держать рядом второй сервер, который в реальном времени повторяет каждое изменение первого. А в конце посмотрим, куда двигаться после этого раздела.
Диктант
Учитель читает текст вслух, ученик записывает. Учитель не ждёт, пока ученик допишет: он читает дальше, а ученик догоняет. Если ученик отвлёкся, он отстанет на пару предложений, но потом наверстает. Если ученик заболел и пропустил урок, он попросит тетрадь и спишет с того места, где остановился.
Репликация устроена так же. Источник читает вслух: записывает каждое изменение в журнал. Реплика записывает под диктовку: забирает журнал и выполняет те же изменения у себя.
Зачем это нужно:
- Запасной сервер. Источник сгорел, реплика становится новым источником за минуты, а не за часы восстановления из дампа.
- Чтение отдельно. Тяжёлые отчёты аналитика идут на реплику и не мешают кассам продавать билеты.
- Копия на другой площадке. Реплика в другом дата-центре переживёт пожар в первом.
Бинарный журнал
Тетрадь учителя в MySQL называется бинарный журнал. Каждая зафиксированная транзакция, которая что-то изменила, попадает туда. В MySQL 8.4 журнал включён по умолчанию, и в песочнице тоже:
SELECT @@server_id, @@log_bin;+-------------+-----------+
| @@server_id | @@log_bin |
+-------------+-----------+
| 1 | 1 |
+-------------+-----------+
1 row in set (0.00 sec)SHOW VARIABLES LIKE 'log_bin';+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)Это тот самый журнал, из-за которого в уроке про процедуры функция без DETERMINISTIC не создавалась с ошибкой 1418. Реплика повторяет изменения у себя, и серверу важно, чтобы функция дала там тот же результат.
server_id это номер сервера. У каждого участника репликации он свой: по нему реплика отличает чужие изменения от своих.
GTID: номер каждой транзакции
Как ученику понять, с какого места продолжать? Можно запомнить «страница 2, строка 730», то есть имя файла журнала и позицию в нём. Так MySQL и работал много лет, и при смене источника позиции приходилось пересчитывать руками.
Современный способ GTID: каждая транзакция получает номер вида uuid-сервера:порядковый-номер. Реплика помнит набор номеров, которые уже выполнила, и при подключении говорит источнику: «у меня есть 1-7, присылай остальное».
Как это настраивают
Дальше реальная настройка двух серверов MySQL 8.4 в Docker: источник bs-mysql-source и реплика. В песочнице один сервер, поэтому здесь только выводы настоящих серверов. Пароли в примерах учебные, придумай свои.
Шаг 1. На источнике проверяем номер сервера, GTID и журнал:
SELECT @@server_id, @@gtid_mode, @@log_bin;+-------------+-------------+-----------+
| @@server_id | @@gtid_mode | @@log_bin |
+-------------+-------------+-----------+
| 1 | ON | 1 |
+-------------+-------------+-----------+
1 row in set (0.00 sec)gtid_mode по умолчанию выключен, его включают при запуске сервера параметрами --gtid-mode=ON --enforce-gtid-consistency=ON.
Шаг 2. Заводим пользователя, под которым реплика будет забирать журнал. Ему нужно одно право, и по принципу наименьших привилегий больше не даём:
CREATE USER 'repl'@'%' IDENTIFIED BY 'fake-repl-pass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.01 sec)Право до сих пор называется по-старому, REPLICATION SLAVE, хотя команды уже переименовали.
Шаг 3. Смотрим, где сейчас журнал:
SHOW BINARY LOG STATUS\G*************************** 1. row ***************************
File: binlog.000002
Position: 730
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 567b7be2-cfa0-11f1-8831-a6f99e24b4bb:1-7
1 row in set (0.00 sec)Файл binlog.000002, позиция 730, на источнике выполнено семь транзакций с номерами 1-7.
Шаг 4. На реплике у сервера свой номер, и он открыт только на чтение:
SELECT @@server_id, @@read_only;+-------------+-------------+
| @@server_id | @@read_only |
+-------------+-------------+
| 2 | 1 |
+-------------+-------------+
1 row in set (0.00 sec)Шаг 5. Говорим реплике, откуда брать журнал, и запускаем её:
CHANGE REPLICATION SOURCE TO SOURCE_HOST = 'bs-mysql-source', SOURCE_USER = 'repl', SOURCE_PASSWORD = 'fake-repl-pass', SOURCE_AUTO_POSITION = 1, GET_SOURCE_PUBLIC_KEY = 1;
START REPLICA;Query OK, 0 rows affected, 2 warnings (0.03 sec)
Query OK, 0 rows affected (0.03 sec)SOURCE_AUTO_POSITION = 1 значит «найди место по GTID», файл и позицию указывать не нужно. Два предупреждения про безопасность: пароль ушёл без шифрования TLS (заметка 1759), и он сохранён в служебной таблице реплики (заметка 1760). В учебном стенде с этим можно жить, в рабочей сети включают TLS.
Шаг 6. На источнике создаём базу и таблицу:
CREATE DATABASE kino;
CREATE TABLE kino.halls (id INT PRIMARY KEY, name VARCHAR(30) NOT NULL, seats SMALLINT UNSIGNED NOT NULL);
INSERT INTO kino.halls VALUES (1, 'Большой', 120), (2, 'Малый', 40), (3, 'VIP', 12);Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0Шаг 7. На реплике смотрим состояние и данные:
SHOW REPLICA STATUS\G*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: bs-mysql-source
Source_User: repl
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: binlog.000002
Read_Source_Log_Pos: 1510
Relay_Log_File: 52997f6a8393-relay-bin.000003
Relay_Log_Pos: 1721
Relay_Source_Log_File: binlog.000002
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Source_Log_Pos: 1510
Relay_Log_Space: 2983470
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 1
Source_UUID: 567b7be2-cfa0-11f1-8831-a6f99e24b4bb
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
Source_Retry_Count: 10
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set: 567b7be2-cfa0-11f1-8831-a6f99e24b4bb:1-10
Executed_Gtid_Set: 567b7be2-cfa0-11f1-8831-a6f99e24b4bb:1-10,
56a052f4-cfa0-11f1-a43a-3ad2b020cdc2:1-5
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 1
Network_Namespace:
1 row in set (0.00 sec)Полей много, смотри на пять:
Replica_IO_Running: Yesпоток, который забирает журнал с источника, работает.Replica_SQL_Running: Yesпоток, который выполняет полученное, тоже работает. Если любой из двухNo, причина вLast_IO_ErrorилиLast_SQL_Error.Seconds_Behind_Source: 0реплика не отстаёт.Retrieved_Gtid_Setтранзакции 1-10 источника получены. Семь было до запуска, три добавил шаг 6.Executed_Gtid_Setони же выполнены. Вторая строка с другим uuid это транзакции, которые случились на самой реплике.
И строки на месте:
SELECT * FROM kino.halls;+----+----------------+-------+
| id | name | seats |
+----+----------------+-------+
| 1 | Большой | 120 |
| 2 | Малый | 40 |
| 3 | VIP | 12 |
+----+----------------+-------+
3 rows in set (0.00 sec)Асинхронно, значит с отставанием
По умолчанию репликация асинхронная, как в диктанте: источник фиксирует транзакцию и сразу отвечает кассе, не дожидаясь реплики. Отсюда два следствия.
Первое: реплика отстаёт. Обычно на доли секунды, но под тяжёлой нагрузкой или при долгом ALTER TABLE отставание вырастает до минут. Его показывает Seconds_Behind_Source. Если зритель купил билет, а приложение тут же читает список его билетов с реплики, свежего билета там может ещё не быть. Поэтому то, что пользователь только что записал, читают с источника.
Второе: если источник сгорит, последние транзакции, которые не успели уйти, потеряются. Для кинотеатра это пара билетов за последнюю секунду. Для банка неприемлемо, там включают полусинхронную репликацию, где источник ждёт подтверждения хотя бы от одной реплики.
read_only и super_read_only
Реплику нельзя менять руками: строка, добавленная только на реплике, разойдётся с источником, и следующая транзакция с тем же ключом сломает репликацию. Для этого на реплике был read_only = 1. Проверим, как он держит удар. Под root на реплике:
INSERT INTO kino.halls VALUES (4, 'Новый', 10);Query OK, 1 row affected (0.01 sec)Прошло. read_only не останавливает пользователей с административными правами, а root именно такой. Чтобы запретить запись всем, включают super_read_only:
SET GLOBAL super_read_only = ON;
INSERT INTO kino.halls VALUES (5, 'Ещё', 10);Query OK, 0 rows affected (0.00 sec)
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statementТеперь отказ. Сама репликация при этом работает: свои потоки реплика пускает. Зал номер 4 остался лишним на реплике, и в настоящей системе его пришлось бы убирать.
Реплика не резервная копия
Последнее и самое важное. Реплика повторяет всё, включая ошибки. Если администратор выполнит DROP TABLE tickets на источнике, через долю секунды таблицы не станет и на реплике. Поэтому резервные копии нужны и при живой репликации: реплика спасает от сгоревшего диска, копия от неверной команды.
Попробуй сам
Песочница не умеет запускать второй сервер, зато в ней можно посмотреть на параметры, с которых начинается любая настройка репликации.
Найди в выводе sql_mode режимы STRICT_TRANS_TABLES и ONLY_FULL_GROUP_BY, которые встречались в разделе. Потом подними у себя два контейнера MySQL по шагам из урока и посмотри SHOW REPLICA STATUS\G после вставки на источнике.
Проверь себя
Задание
Что дальше
Раздел закончен. Ты умеешь поднять MySQL, спроектировать таблицы с ключами и ограничениями, писать запросы с JOIN и группировкой, менять строки в транзакциях, ускорять запросы индексами, раздавать права и снимать копии. Куда двигаться дальше:
- PostgreSQL. Основная база этого курса. Начни с первого урока раздела и сравни на каждом шаге. Репликация там устроена по-другому, это отдельный урок.
- Теория баз данных. Почему уровни изоляции именно такие, разобрано в уроке про изоляцию. Как оптимизатор выбирает план, рассказано в уроке про планы запросов. Чем приходится платить за несколько серверов, объясняет урок про теорему CAP.
- SQL для собеседования. Задачи на
JOIN, группировку и подзапросы собраны в разделе теории, начни с порядка выполнения запроса. - Другие базы. Встроенная база в одном файле: SQLite. Документы вместо таблиц: MongoDB. Хранилище в памяти: Redis. Аналитика на миллиардах строк: ClickHouse. Связи как главное: Neo4j. Полнотекстовый поиск: Elasticsearch.
Возьми любой свой проект на MySQL и пройдись по нему глазами этого раздела: где не хватает индекса, где две команды стоило склеить транзакцией, у кого лишние права и когда последний раз проверяли восстановление из копии.
домашка