Как настроить MySQL Master/Slave репликацию?

Коллеги всем привет.

В сегодняшней статье я вам буду рассказывать о такой вещи в Mysql как репликация на основе положения двоичного файла журнала. Этот вариант считается самым простим в исполнении, но не менее аффективным. Сам процесс репликации в Mysql позволяет копировать данные с одного сервера базы данных на другой, такие сервера принято называть master slave. Репликация по умолчанию асинхронна.

Версию базы данных я буду использовать: 8.0.29

Сервера я буду использовать для демонстрации:

192.168.2.229 mysql1.local - MASTER
192.168.2.230 mysql2.local - SLAVE

Настройки 192.168.2.229 mysql1.local и 192.168.2.230 mysql2.local будут абсолютно одинаковые так как у вас будут случаи когда вам нужно будет переключать репликацию в обратную сторону, и чтобы потом ничего не перенастраивать то мы сразу все сделать одинаковым.

Содержимое статьи:

1. Настройка master сервера.
2. Создания учетной записи на master сервере.
3. Настройка slave сервера.
4. Резервная копия master сервера.
5. Поиск позиции двоичного журнала на master сервере.
6. Подключаем slave сервер к master серверу.
7. Запуск репликации.
8. Проверка репликации.
9. Итоги.

1. Настройка master сервера.

Будем начинать с настройки конфигурационного файла базы данных. По умолчанию он находится по пути /etc/my.cnf.

$. vim /etc/my.cnf

1.1. Первый делом нам нужно настроить server_id. server_id это идентификатор сервера и он используется для идентификации сервера в топологии репликации и он обязательно должен быть положительным целым числом от 1 до 32.

[mysqld]
server_id = 10

1.2. Настройка ведение двоичного журнала (binary log).

Ведения двоичного журнала на сервере master является обязательным действием потому что сервер slave будет подключаться на сервер master и читать эти двоичные журналы. Ведение двоичного журнала по умолчанию включено. Но по умолчанию все двоичные журналы будут создаваться и хранится в директории там где лежат все файлы базы данных. Нам это не подходит и мы сами укажем место хранения двоичных журналов.

[mysqld]
log_bin = /app/mysql/binary_log/mysql-bin.log
log_bin_index = /app/mysql/binary_log/binlog.index

1.3. Настройка согласованности репликации.

За согласованность репликации отвечает переменная innodb_flush_log_at_trx_commit и sync_binlog. Всегда ставьте значения этих переменных в значения 1. Данная переменная может иметь значения 0, 1, 2. Если вы укажите 0 или 2, то есть риск потерять транзакции.

[mysqld]
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

1.4. Настройка на реплике ведения двоичного журнала.

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

[mysqld]
log_replica_updates = ON

1.5. Настройка ведения журнала ретрансляции.

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

[mysqld]
relay_log = /app/mysql/relay_log/mysql-relay.log
relay_log_index = /app/mysql/relay_log/mysql-relay.index

1.6. Результат.

В итоги у вас в конфигурационном файле должно быть как у меня. Это сервер master 192.168.2.229 mysql1.local

[mysqld]
server_id = 10
log_bin = /app/mysql/binary_log/mysql-bin.log
log_bin_index = /app/mysql/binary_log/binlog.index
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
log_replica_updates = ON
relay_log = /app/mysql/relay_log/mysql-relay.log
relay_log_index = /app/mysql/relay_log/mysql-relay.index

2. Создания учетной записи на master сервере.

Каждый slave сервер будет подключаться к master серверу с использованием имени пользователя и пароля, поэтому на master сервере должна быть учетная запись пользователя, которую slave сервер будет использовать для подключения.

2.1. Создаем учетную запись на master серверу.

mysql> CREATE USER 'replication'@'%' IDENTIFIED BY 'kjs&#&d89h3G';

2.2. Назначаем этому пользователю необходимые права.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';

3. Настройка slave сервера.

В конфигурационный файл slave сервера мы должны добавить все что мы добавляли на master сервер, но за исключением server_id. server_id должен быть уникальный на каждом сервере в той топологии репликации где он участвует. Ну и добавим параметр read_only чтобы не было возможности делать операции вставки, редактирования и удаления данных, а только их чтения.

[mysqld]
server_id = 11
log_bin = /app/mysql/binary_log/mysql-bin.log
log_bin_index = /app/mysql/binary_log/binlog.index
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
log_replica_updates = ON
relay_log = /app/mysql/relay_log/mysql-relay.log
relay_log_index = /app/mysql/relay_log/mysql-relay.index
read_only = ON

На master сервере server_id у нас 10, а slave сервер будет 11.

4. Резервная копия master сервера.

4.1. Настала очередь сделать резервную копию базы данных master сервера и перенести эту копию на slave сервер. Сделать резервную копию есть множество вариантов, но мы будем использовать самый простой, утилита mysqldump.

$. mysqldump -u root -p --all-databases --source-data > /app/backup/backup.db

4.2. После того как мы сделали резервную копию базы данных, нам нужно её перенести на slave сервер.

$. scp /app/backup/backup.db  root@192.168.2.230:/app/backup/

4.3. На slave сервере теперь нам нужно эту резервную копию импортировать.

$. mysql -u root -p < /app/backup/backup.db

5. Поиск позиции двоичного журнала на master сервере.

На master сервере нам нужно найти последнюю позицию в двоичном журнале, та и сам журнал как называется на текущий момент.

mysql> SHOW MASTER STATUS;

В этом результате нам важно записать значения File и Position.

6. Подключаем slave сервер к master серверу.

Теперь после всего что мы сделали выше нам осталось указать slave серверу как подключатся к master серверу.

mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.2.229', SOURCE_USER='replication', SOURCE_PASSWORD='kjs&#&d89h3G', SOURCE_LOG_FILE='mysql-bin.000004', SOURCE_LOG_POS=871;

Успех! Осталось запустить репликацию.

7. Запуск репликации.

За включения и остановку репликации в базе данных существуют команды START REPLICA и STOP REPLICA. Нам же нужно выполнить START REPLICA.

mysql> START REPLICA;

8. Проверка репликации.

После запуска репликации нам нужно проверить правильно ли все работает. Для проверки на slave сервере воспользуемся командой:

mysql> SHOW REPLICA STATUS\G;

Самые важные параметры которые показывают что репликация проходит успешно это:

Replica_IO_Running: Yes
Replica_SQL_Running: Yes

9. Итоги.

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

Постепенно в следующих статьях мы будем разбирать более сложные реализации репликации включая такое понятия как GTID и так далее.