Основы репликации MySQL

Введение

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

Лабораторное занятие начинается с настройки мастера с включенной бинарной журналировкой, которая необходима для записи всех изменений данных. Мы отредактируем файл конфигурации MySQL (/etc/mysql/mysql.conf.d/mysqld.cnf), чтобы установить log_bin = mysql-bin и server_id = 1. Затем мы настроим слейв - сервер для репликации данных с мастера, проверим статус репликации с помощью команды SHOW SLAVE STATUS и, наконец, смоделируем и исправим ошибку репликации, чтобы понять методы устранения неполадок.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("MySQL")) -.-> mysql/BasicKeywordsandStatementsGroup(["Basic Keywords and Statements"]) mysql(("MySQL")) -.-> mysql/TransactionManagementandSecurityGroup(["Transaction Management and Security"]) mysql(("MySQL")) -.-> mysql/SystemManagementToolsGroup(["System Management Tools"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_database("Database Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_table("Table Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("Data Retrieval") mysql/BasicKeywordsandStatementsGroup -.-> mysql/insert("Data Insertion") mysql/TransactionManagementandSecurityGroup -.-> mysql/grant_permission("Permission Granting") mysql/SystemManagementToolsGroup -.-> mysql/show_status("Status Overview") mysql/SystemManagementToolsGroup -.-> mysql/show_variables("Configuration Overview") subgraph Lab Skills mysql/create_database -.-> lab-550913{{"Основы репликации MySQL"}} mysql/create_table -.-> lab-550913{{"Основы репликации MySQL"}} mysql/select -.-> lab-550913{{"Основы репликации MySQL"}} mysql/insert -.-> lab-550913{{"Основы репликации MySQL"}} mysql/grant_permission -.-> lab-550913{{"Основы репликации MySQL"}} mysql/show_status -.-> lab-550913{{"Основы репликации MySQL"}} mysql/show_variables -.-> lab-550913{{"Основы репликации MySQL"}} end

Настройка мастера с бинарной журналировкой

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

Сначала разберемся, почему бинарная журналировка важна. Без бинарной журналировки слейв - сервер не сможет узнать, какие изменения нужно реплицировать с мастера. Бинарные логи действуют как журнал транзакций, обеспечивая синхронизацию слейв - сервера с мастером.

Теперь настроим мастер - сервер.

Редактирование файла конфигурации MySQL:

Файл конфигурации MySQL обычно находится по пути /etc/mysql/mysql.conf.d/mysqld.cnf. Мы будем использовать nano для редактирования этого файла.
```
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
```
Включение бинарной журналировки:

Добавьте следующие строки в раздел mysqld файла конфигурации. Если раздел mysqld не существует, найдите строку [mysqld] и добавьте следующие настройки ниже нее.
```
log_bin = mysql-bin
server_id = 1
```
- log_bin = mysql-bin: Это включает бинарную журналировку и устанавливает базовое имя для файлов журнала в mysql-bin. MySQL автоматически добавит порядковый номер к этому базовому имени (например, mysql-bin.000001, mysql-bin.000002 и т.д.).
- server_id = 1: Это устанавливает уникальный идентификатор для мастера. Каждый сервер в схеме репликации должен иметь уникальный server_id.
Раздел mysqld вашего файла конфигурации должен теперь выглядеть примерно так:
```
[mysqld]
#
## * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
log_bin = mysql-bin
server_id = 1
## ... other configurations ...
```
Нажмите Ctrl+X, затем Y, а затем Enter, чтобы сохранить изменения и выйти из nano.
Перезапуск сервера MySQL:

Чтобы применить изменения, перезапустите сервер MySQL. Поскольку мы находимся в Docker - контейнере, мы не можем использовать systemctl. Вместо этого мы будем использовать скрипт mysql.server.
```
sudo /etc/init.d/mysql restart
```
Вы должны увидеть вывод, похожий на следующий:
```
[ ok ] Restarting mysql (via systemctl): mysql.service.
```
Проверка включения бинарной журналировки:

Войдите в сервер MySQL как пользователь root. Возможно, вам придется использовать sudo для доступа к MySQL, если вы еще не установили пароль для пользователя root.
```
sudo mysql -u root
```
Выполните следующий SQL - запрос, чтобы проверить статус бинарной журналировки:
```
SHOW VARIABLES LIKE 'log_bin';
```
Вывод должен быть таким:
```
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)
```
Это подтверждает, что бинарная журналировка включена.

Вы также можете проверить идентификатор сервера:
```
SHOW VARIABLES LIKE 'server_id';
```
Вывод должен быть таким:
```
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)
```
Выйдите из монитора MySQL:
```
exit
```

Теперь вы успешно настроили мастер - сервер с бинарной журналировкой. Следующим шагом будет настройка слейв - сервера для репликации данных с этого мастера.

Настройка слейв - сервера для репликации данных

На этом этапе мы настроим сервер MySQL в качестве слейва для репликации данных с мастера, настроенного на предыдущем шаге. Это включает настройку параметров слейв - сервера и установку соединения с мастером.

Настройка слейв - сервера:

Сначала нам нужно настроить слейв - сервер с уникальным server_id и включить реплицирующую журналировку (relay logging). Реплицирующие логи используются слейв - сервером для хранения событий бинарного журнала, полученных от мастера, перед их применением к своей собственной базе данных.

Отредактируйте файл конфигурации MySQL на слейв - сервере (обычно он находится по пути /etc/mysql/mysql.conf.d/mysqld.cnf):
```
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
```
Добавьте или измените следующие строки в разделе [mysqld]:
```
server_id = 2
relay_log = mysql-relay-bin
```
- server_id = 2: Это устанавливает уникальный идентификатор для слейв - сервера. Он должен отличаться от server_id мастера.
- relay_log = mysql-relay-bin: Это включает реплицирующую журналировку и устанавливает базовое имя для файлов реплицирующего журнала.
Раздел mysqld вашего файла конфигурации должен теперь выглядеть примерно так:
```
[mysqld]
#
## * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
server_id = 2
relay_log = mysql-relay-bin
## ... other configurations ...
```
Нажмите Ctrl+X, затем Y, а затем Enter, чтобы сохранить изменения и выйти из nano.
Перезапуск сервера MySQL на слейве:

Перезапустите сервер MySQL, чтобы применить изменения:
```
sudo /etc/init.d/mysql restart
```
Вы должны увидеть вывод, похожий на следующий:
```
[ ok ] Restarting mysql (via systemctl): mysql.service.
```
Создание пользователя для репликации на мастере:

На мастере войдите в сервер MySQL как пользователь root:
```
sudo mysql -u root
```
Создайте специального пользователя для репликации. Замените 'slave_user' на желаемое имя пользователя и 'password' на надежный пароль. Также замените '%' на IP - адрес вашего слейв - сервера для большей безопасности. Если вы не знаете IP - адрес слейв - сервера, вы можете использовать '%', чтобы разрешить подключения с любого хоста (не рекомендуется для производственных окружений).
```
CREATE USER 'slave_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%';
FLUSH PRIVILEGES;
```
- CREATE USER 'slave_user'@'%' IDENTIFIED BY 'password': Создает нового пользователя MySQL с именем slave_user, который может подключаться с любого хоста ('%').
- GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%': Предоставляет привилегию REPLICATION SLAVE пользователю slave_user для всех баз данных и таблиц. Эта привилегия необходима для того, чтобы слейв - сервер мог запрашивать обновления бинарного журнала у мастера.
- FLUSH PRIVILEGES: Перезагружает таблицы привилегий, чтобы применить новые привилегии.
Выйдите из монитора MySQL на мастере:
```
exit
```
Настройка слейва для подключения к мастеру:

На слейве войдите в сервер MySQL как пользователь root:
```
sudo mysql -u root
```
Укажите слейв - серверу, как подключиться к мастеру. Замените 'master_host' на IP - адрес или имя хоста вашего мастера, 'slave_user' на имя пользователя для репликации, которое вы создали, и 'password' на пароль для репликации.
```
CHANGE MASTER TO
    MASTER_HOST='master_host',
    MASTER_USER='slave_user',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=4;
```
- MASTER_HOST: Имя хоста или IP - адрес мастера.
- MASTER_USER: Имя пользователя, которое слейв будет использовать для подключения к мастеру.
- MASTER_PASSWORD: Пароль для пользователя репликации.
- MASTER_LOG_FILE: Имя файла бинарного журнала мастера, с которого нужно начать репликацию. Вы можете узнать это, выполнив запрос SHOW MASTER STATUS; на мастере. Для новой настройки это обычно mysql-bin.000001.
- MASTER_LOG_POS: Позиция в файле бинарного журнала, с которой нужно начать репликацию. Вы можете узнать это, выполнив запрос SHOW MASTER STATUS; на мастере. Для новой настройки это обычно 4.
Важно: Вам нужно определить правильные значения MASTER_LOG_FILE и MASTER_LOG_POS на мастере. На мастере выполните следующий запрос:
```
SHOW MASTER STATUS;
```
Вывод покажет File (соответствует MASTER_LOG_FILE) и Position (соответствует MASTER_LOG_POS). Используйте эти значения в команде CHANGE MASTER TO на слейве.

После выполнения команды CHANGE MASTER TO запустите слейв:
```
START SLAVE;
```
Выйдите из монитора MySQL на слейве:
```
exit
```

Теперь вы настроили слейв - сервер для репликации данных с мастера. На следующем этапе мы проверим, что репликация работает корректно.

Проверка репликации с помощью SHOW SLAVE STATUS

На этом этапе мы проверим, успешно ли слейв - сервер реплицирует данные с мастера. Мы будем использовать команду SHOW SLAVE STATUS для проверки статуса потоков репликации и выявления возможных проблем.

Проверка статуса слейва:

На слейве войдите в сервер MySQL как пользователь root:
```
sudo mysql -u root
```
Выполните следующую команду, чтобы посмотреть статус слейва:
```
SHOW SLAVE STATUS\G
```
Символ \G в конце команды форматирует вывод вертикально, что делает его более удобным для чтения.
Интерпретация вывода:

Вывод команды SHOW SLAVE STATUS содержит подробную информацию о процессе репликации. Вот некоторые важные поля, на которые стоит обратить внимание:
- Slave_IO_State: Это показывает текущее состояние I/O - потока, который отвечает за чтение событий бинарного журнала с мастера. Идеально должно быть Waiting for master to send event.
- Slave_IO_Running: Это показывает, запущен ли I/O - поток. Должен быть Yes.
- Slave_SQL_Running: Это показывает, запущен ли SQL - поток. SQL - поток применяет события, полученные от мастера, к базе данных слейва. Должен быть Yes.
- Last_IO_Error: Если есть ошибки, связанные с I/O - потоком, они будут отображены здесь.
- Last_SQL_Error: Если есть ошибки, связанные с SQL - потоком, они будут отображены здесь.
- Master_Log_File: Имя файла бинарного журнала на мастере, из которого в данный момент читает слейв.
- Read_Master_Log_Pos: Позиция в файле бинарного журнала, до которой слейв прочитал.
- Relay_Log_File: Имя файла реплицирующего журнала на слейве, из которого в данный момент читает SQL - поток.
- Relay_Log_Pos: Позиция в файле реплицирующего журнала, до которой SQL - поток прочитал.
- Seconds_Behind_Master: Это показывает, на сколько секунд слейв отстает от мастера. Значение 0 означает, что слейв актуален. Более высокое значение означает, что слейв отстает.
При успешной настройке репликации Slave_IO_Running и Slave_SQL_Running должны быть установлены в Yes, а Seconds_Behind_Master должен быть близок к 0.

Пример успешного вывода команды SHOW SLAVE STATUS (сокращенный):
```
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master_host
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 311
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_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_Master_Log_Pos: 154
              Relay_Log_Space: 472
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
            Master_SSL_CA_File:
            Master_SSL_CA_Path:
               Master_SSL_Cert:
             Master_SSL_Cipher:
                Master_SSL_Key:
         Seconds_Behind_Master: 0
...
```
Создание тестовой базы данных и таблицы на мастере:

Чтобы дополнительно проверить репликацию, создайте тестовую базу данных и таблицу на мастере:
```
sudo mysql -u root
```
```
CREATE DATABASE test_replication;
USE test_replication;
CREATE TABLE test_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(255)
);
INSERT INTO test_table (data) VALUES ('This is a test record from master');
exit
```
Проверка данных на слейве:

На слейве войдите в сервер MySQL как пользователь root:
```
sudo mysql -u root
```
Проверьте, были ли реплицированы база данных test_replication и таблица test_table:
```
USE test_replication;
SELECT * FROM test_table;
exit
```
Вы должны увидеть данные, вставленные на мастере:
```
+----+-----------------------------------------+
| id | data                                    |
+----+-----------------------------------------+
|  1 | This is a test record from master       |
+----+-----------------------------------------+
1 row in set (0.00 sec)
```

Если вы видите тестовую базу данных, таблицу и данные на слейве, репликация работает корректно.

Симуляция ошибки репликации и ее исправление

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

Симуляция ошибки:

Одна из распространенных ошибок репликации возникает, когда на слейве обнаруживается несоответствие данных. Мы имитируем это, вставляя строку в таблицу test_replication.test_table на слейве вручную. Это вызовет конфликт, когда мастер попытается реплицировать ту же строку.

На слейве войдите в сервер MySQL как пользователь root:
```
sudo mysql -u root
```
Вставьте строку в таблицу test_replication.test_table:
```
USE test_replication;
INSERT INTO test_table (data) VALUES ('This is an intentionally conflicting record on the slave');
exit
```
Запуск репликации:

Теперь вставьте еще одну строку в таблицу test_replication.test_table на мастере:
```
sudo mysql -u root
```
```
USE test_replication;
INSERT INTO test_table (data) VALUES ('This is a new record from master');
exit
```
Эта вставка на мастере запустит репликацию на слейв. Однако, так как мы вручную вставили конфликтующую строку на слейве, процесс репликации, скорее всего, столкнется с ошибкой.

Проверка статуса слейва:

На слейве войдите в сервер MySQL как пользователь root:

sudo mysql -u root

Проверьте статус слейва:

SHOW SLAVE STATUS\G

Просмотрите вывод. Вероятно, вы увидите, что Slave_SQL_Running установлено в No, а в поле Last_SQL_Error будет сообщение об ошибке, указывающее на дубликат ключа или другой конфликт.

Пример вывода с ошибкой:

...
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master_host
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 311
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_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: 1062
                   Last_Error: Duplicate entry '1' for key 'PRIMARY'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 472
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
            Master_SSL_CA_File:
            Master_SSL_CA_Path:
               Master_SSL_Cert:
             Master_SSL_Cipher:
                Master_SSL_Key:
         Seconds_Behind_Master: NULL
...

Исправление ошибки репликации:

Чтобы исправить эту ошибку, мы пропустим проблемное событие на слейве. Таким образом, слейв проигнорирует событие, вызвавшее ошибку, и продолжит репликацию с следующего события.

Сначала остановите SQL - поток слейва:
```
STOP SLAVE SQL_THREAD;
```
Затем пропустите ошибочное событие:
```
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
```
Эта команда сообщает слейву пропустить следующее событие в реплицирующем журнале.

Наконец, запустите SQL - поток слейва:
```
START SLAVE SQL_THREAD;
```
Проверка восстановления репликации:

Проверьте статус слейва еще раз:
```
SHOW SLAVE STATUS\G
```
Теперь вы должны увидеть, что Slave_SQL_Running установлено в Yes, а Last_SQL_Error пусто. Репликация должна снова работать нормально.
Проверка согласованности данных:

На слейве проверьте содержимое таблицы test_replication.test_table:
```
USE test_replication;
SELECT * FROM test_table;
exit
```
Вы должны увидеть как преднамеренно конфликтующую запись, так и новую запись с мастера. Конфликтующая запись уже была на месте, а новая запись была успешно реплицирована после пропуска ошибки.
```
+----+-----------------------------------------------------+
| id | data                                                |
+----+-----------------------------------------------------+
|  1 | This is a test record from master                   |
|  2 | This is an intentionally conflicting record on the slave |
|  3 | This is a new record from master                    |
+----+-----------------------------------------------------+
3 rows in set (0.00 sec)
```

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

Резюме

В этом практическом занятии (лабораторной работе) первоначальный этап заключается в настройке сервера MySQL в качестве мастера в схеме репликации путем включения бинарного логирования. Это включает редактирование файла конфигурации MySQL, обычно расположенного по пути /etc/mysql/mysql.conf.d/mysqld.cnf, и добавление строк log_bin = mysql-bin и server_id = 1 в раздел mysqld.

Включение бинарного логирования имеет решающее значение, так как оно позволяет мастер - серверу записывать все изменения данных, которые впоследствии используются слейв - сервером для репликации изменений и обеспечения синхронизации данных. Директива log_bin задает базовое имя для файлов бинарного журнала, а server_id назначает уникальный идентификатор мастер - серверу в среде репликации.