Оптимизация MySQL сервера. Часть 6. Нюансы настройки репликации MySQL

Цикл статей по диагностике и оптимизации производительности сервера MySQL продолжается. В этой статье я приведу мой конспект, который я подготовил, когда читал про нюансы настройки репликации MySQL.

Эта публикация сугубо мой конспект прочитанной литературы. Информация может быть не очень структурирована и удобочитаема. Материал публикации предоставляется без каких-либо гарантий со стороны автора.

Список первоисточников

  1. O’Reilly High Performance MySQL 3rd Edition.
  2. 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 поддерживается несколько типов репликации:

  1. Репликация на основе команд (Statement-based replication).
  2. Репликация на основе строк (Row-based replication).
  3. Комбинированная репликация на основе двух методов выше.

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

По умолчанию используется репликация на основе строк (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.

Часть 5. Полезные запросы для MySQL.

Часть 7. Резервное копирование MySQL.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *