Цикл статей по диагностике и оптимизации производительности сервера MySQL продолжается. В этой статье я приведу мой конспект, который я подготовил, когда читал про нюансы настройки репликации MySQL.
Эта публикация сугубо мой конспект прочитанной литературы. Информация может быть не очень структурирована и удобочитаема. Материал публикации предоставляется без каких-либо гарантий со стороны автора.
Список первоисточников
- O’Reilly High Performance MySQL 3rd Edition.
- O’Reilly High Performance MySQL 4rd Edition.
Пример настройки репликации
В качестве практического примера я настроил репликацию, которая соответствует следующей схеме:

Подготовка исходного сервера (MySQL01)
Подробная информация по настройке репликации есть в соответствующем разделе в документации. Я только кратко задокументирую те изменения, которые я вносил на исходном сервере. Расширенные комментарии по конфигурационным параметрам я приведу в последующим разделах.
Основные изменения на мастер сервере:
1. В конфигурационный файл я добавил следующие параметры:
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
relay_log_info_repository = TABLE
relay_log_recovery = ON
server-id = 1
2. Перезапущу сервер mysql:
sudo systemctl restart mysql
3. Теперь необходимо подготовить учетную запись пользователя mysql, от имени которой реплика будет подключать к мастеру:
CREATE USER 'replica'@'192.168.56.112' IDENTIFIED WITH mysql_native_password BY 'Qwerty123';

4. Делегируем пользователю реплики необходимый минимум разрешений:
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.56.112';
FLUSH PRIVILEGES;

Подготовка сервера реплики (MySQL02)
Основные изменения на мастер сервере:
1. В конфигурационный файл я добавил следующие параметры:
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
relay_log_info_repository = TABLE
relay_log_recovery = ON
server-id = 2
super_read_only
2. Перезапущу сервер mysql:
sudo systemctl restart mysql
3. Изменим параметры сервер источника на сервере реплики:
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.56.106',
SOURCE_USER='replica',
SOURCE_PASSWORD='Qwerty123';

4. Запускаем реплику:
START REPLICA;
Нюансы настройки
Для сервера мастера и сервера реплики добавляется не так уж и много настроек к стандартному конфигурационному файла:
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
relay_log_info_repository = TABLE
relay_log_recovery = ON
server-id = <number>
Если нужно указать какую-то конкретную базу для репликации, то нужно добавить параметр binlog_do_db и указать имя базы. Например, binlog_do_db = tst_db1. Данный параметр можно указывать несколько раз, чтобы включить в репликацию несколько баз данных.
Дополнительно на сервере реплики рекомендуется добавить параметр, которые запрещает внесение изменений на сервере реплики:
super_read_only
При настройке репликации основной упор в настройках делается на надежность и сохранения всех транзакций. Жертвую, соответственно, производительностью в некоторых местах.
В таблице ниже я кратко опишу основное назначение настроек, которые я привел выше.
Параметр | Значение |
innodb_flush_log_at_trx_commit = 1 | Этот параметр нужен для того, чтобы при каждой транзакции информация о ней записывалась на в журналы и на диск. Это увеличивает интенсивность записи на диск, но обеспечивает максимально возможную надежность доставки транзакций на реплику. |
sync_binlog = 1 | Этот параметр указывает как сервер MySQL синхронизует данные из бинароного лога на диск. Параметр 1 указывает, что синхронизацию нужно выполнять каждый раз перед завершением транзакции. Также, как и параметр выше увеличивает интенсивность записи на диск в качестве платы за надежность. |
relay_log_info_repository = TABLE | Параметр указывает место, где необходимо хранить информацию о статусе репликации. Параметр TABLE указывает серверу, что информацию нужно хранить в таблицах InndoDB. Это позволяет фиксировать информацию и завершении транзакции и информацию и статусе репликации в одной транзакции, что повышает надежность процесса репликации. |
relay_log_recovery = ON | В случае аварии все данный о необходимых новых транзакциях для воспроизведения будут забираться с сервера источника. Это позволяет исключить ситуации с повреждением локальных файлов при аваррии. |
super_read_only | Применяется на сервере реплики для того, чтобы никто не мог вносить изменений в базу и не получилась ситуации рассинхронизации данных между мастер сервером и репликой. Есть еще параметр “read_only” – он позволяет вносить изменения на сервере реплики пользователю root. |
Для того, чтобы репликация на версиях ранее 8.0.26 работала в несколько потоков необходимо для параметра slave_parallel_workers
задать значение отличное от 0. В версия 8.0.27 и новее этот параметр заменен параметром replica_parallel_workers
, который имеет значение 4 по умолчанию.
Типы репликации (statement-based,
row-based, and mixed.)
В MySQL поддерживается несколько типов репликации:
- Репликация на основе команд (Statement-based replication).
- Репликация на основе строк (Row-based replication).
- Комбинированная репликация на основе двух методов выше.
Каждый из этих методом имеет свои особенности, достоинства и недостатки и я не ставлю целью этой статью дать подробный анализ работы каждого из метода.
По умолчанию используется репликация на основе строк (row-based).
Просмотр статуса репликации
Посмотреть текущий статус репликации можно следующей командой в консоле mysql на сервере реплики:
SHOW REPLICA STATUS\G;
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 192.168.56.106
Source_User: replica
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: binlog.000125
Read_Source_Log_Pos: 859
Relay_Log_File: mysql02-relay-bin.000270
Relay_Log_Pos: 323
Relay_Source_Log_File: binlog.000125
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: 859
Relay_Log_Space: 535
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: 23a1466e-983e-11ee-a470-ea5176d0d326
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: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
Пример сообщения о том, что реплика применила все изменения с мастер сервера и репликация находится в рабочем состоянии:

Примеры ошибок
Пример просмотра статуса с ошибкой репликации:
SHOW REPLICA STATUS\G;
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 192.168.56.106
Source_User: replica
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: binlog.000125
Read_Source_Log_Pos: 859
Relay_Log_File: mysql02-relay-bin.000034
Relay_Log_Pos: 367
Relay_Source_Log_File: binlog.000025
Replica_IO_Running: Yes
Replica_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1396
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log binlog.000025, end_log_pos 471. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

В моем случае на сервере в процессе репликации с мастер сервера должен был быть создать пользователь, но этот пользователь уже был создан ранее на сервере реплики. После удаления пользователя репликация продолжилась без ошибок.
Ссылки на другие статьи этого цикла
Часть 1. Тестирование производительности MySQL.
Часть 2. Профилирование нагрузки сервера MySQL.
Часть 3. Базовая конфигурация и оптимизация настроек MySQL.
Часть 4. Признаки проблем с производительностью MySQL.