Настройка репликации MySQL с GTID в Bitrix

Предварительная информация

Что касается репликации в битриксе, то по этому вопросу уже была написана обширная статья. Но спустя время был выявлен один нюанс: данный метод блокирует публичную часть сайта и начинает дропать все таблицы и переносить их заново. А если база по 20, 200, 1000 гб? Ждать приходится очень долго. Если на небольшом проекте такой вариант еще прокатит, то при использовании в продакшене, где ограничено время на выполнение работ, придётся столкнуться с гневным заказчиком.

Также я писал про другой метод репликации с использованием GTID. И вот теперь можно применить его при настройке репликации MySQL в битрикс + избежать нюанса, который описан в первом абзаце. Более подробное описание метода и используемых параметров можно найти в оф. документации и применение того или иного зависит от конкретных целей.

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

Для работы репликации на уровне приложения необходимо наличие модуля “Веб-кластер” и необходимой лицензии.

Используемый форк MySQL – Percona 5.7.30.

Инструкция написана для установки MySQL и настройки репликации с нуля.

Директория для MySQL – /mnt/u01/mysql/data

Настройка Master

После установки изменить домашней директории путь для пользователя mysql:

usermod -d /mnt/u01/mysql mysql

Полный конфиг после установки Percona располагается в файле /etc/percona-server.conf.d/mysqld.cnf. Сформировать эталонный конфигурационный файл для MySQL Master:

cat > /etc/percona-server.conf.d/mysqld.cnf << EOF
[client]
port = 3306
socket = /mnt/u01/mysql/data/mysqld.sock
default-character-set = utf8

[mysqld_safe]
nice = 0
socket = /mnt/u01/mysql/data/mysqld.sock

[mysqld]

# default settings
datadir=/mnt/u01/mysql/data/
socket=/mnt/u01/mysql/data/mysqld.sock
user = mysql
port = 3306
skip-external-locking
default-storage-engine = innodb
pid-file = /var/run/mysqld/mysqld.pid
transaction-isolation = READ-COMMITTED
max_allowed_packet = 16M
myisam-recover-options = BACKUP
explicit_defaults_for_timestamp = 1
expire_logs_days = 10
max_binlog_size = 100M
sql_mode = ""

# Cache parameters
query_cache_size = 32M
table_open_cache = 4096
thread_cache_size = 32
key_buffer_size = 16M
thread_stack = 128K
join_buffer_size = 2M
sort_buffer_size = 2M

# Parameters for temporary tables
tmpdir = /tmp
max_heap_table_size = 32M
tmp_table_size = 32M

# InnoDB parameters
innodb_file_per_table
innodb_buffer_pool_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 128M
innodb_flush_method = O_DIRECT

# Database charset parameters
character-set-server = utf8
collation-server = utf8_unicode_ci
init-connect = "SET NAMES utf8 COLLATE utf8_unicode_ci"
#skip-character-set-client-handshake
skip-name-resolve

# Logging
log_error=/var/log/mysqld.log
pid_file=/var/run/mysqld/mysqld.pid

# Replication
gtid_mode = ON
log_bin = mysql-bin
log-slave-updates
enforce-gtid-consistency
server_id = 1
replicate-do-db = db_name

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqldump]
quick
quote-names
max_allowed_packet = 16M
default-character-set = utf8

[isamchk]
key_buffer = 16M
EOF

В конфигурационном файле MySQL необходимо изменить следующие параметры в зависимости от доступной оперативной памяти и физического размещения MySQL на сервере:

datadir – путь, где физически располагаются файлы MySQL. В данной инструкции предполагается, что это директория /mnt/u01/mysql/data
socket – путь до сокета для консольного клиента, в той же директории /mnt/u01/mysql/data
innodb_buffer_pool_size – требует тонкой настройки, для начала можно выставить в 50% от свободной оперативной памяти (значение в 4096М или 4G – мегабайты или гигабайты соответственно).
server-id – в зависимости от роли Master или Slave

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

Вышеописанный конфиг был взят из виртуальной машины bitrix для конфигурации: HDD, 1vCPU, 2 RAM. Он не является эталонным и должен настраиваться индивидуально.

Выполнить инициализацию базы данных в директорию /mnt/u01/mysql/data:

/usr/sbin/mysqld --defaults-file=/etc/percona-server.conf.d/mysqld.cnf --initialize --user=mysql --basedir=/mnt/u01/mysql --datadir=/mnt/u01/mysql/data 

Запустить MySQL и проверить, что запуск успешен:

systemctl enable mysqld --now && systemctl status mysql

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

cat /var/log/mysqld.log | grep "A temporary password" | awk '{print $11}'

Подключиться к консоли MySQL и сменить временный пароль root:

mysql -uroot -p
alter user 'root'@'localhost' identified by '3dY6FNFD^DJ!o';
flush privileges;

Сформировать конфигурационный файл для запуска консольного клиента mysql без ввода пароля:

cat > /root/.my.cnf << EOF
[client]
user=root
password='3dY6FNFD^DJ!o'
socket=/mnt/u01/mysql/data/mysqld.sock
EOF

Создать базу данных:

CREATE DATABASE `db_name` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;

Создать пользователя и необходимые привилегии для подключения с сервера приложения APP_SERVER:

grant all privileges on db_name.* to 'user'@'APP-IP' identified by 'FNDHBD&^DJK';

Выдать права для выполнения репликации под той же учётной записью с сервера приложений APP_SERVER:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, INDEX, ALTER, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user'@'IP_APP' identified by 'FNDHBD&^DJK';

Выдать права для выполнения репликации под той же учётной записью с сервера DB-SLAVE:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, INDEX, ALTER, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user'@'IP-DB-SLAVE' identified by 'FNDHBD&^DJK';

Очистить привилегии:

flush privileges;

Распаковать дамп базы на сервер MySQL Master:

zcat /root/db.sql.gz | mysql db_name

Открыть в браузере адрес сервера приложений APP-SERVER для проверки корректного подключения к базе данных со стороны приложения: должно открыться окно для ввода логина и пароля.

Для подготовки репликации необходимо снять дамп базы с DB-MASTER следующей командой:

mysqldump --single-transaction db_name | gzip > /root/db_name_slave.sql.gz

И скопировать его на сервер DB-SLAVE:

rsync -av /root/db_name_slave.sql.gz IP-DB-SLAVE:/root


Настройка Slave

Настройки данного сервера почти не отличаются от настроек DB-MASTER, за исключение конфигурационного файла MySQL.

После установки изменить домашней директории путь для пользователя mysql:

usermod -d /mnt/u01/mysql mysql

Сформировать конфигурационный файл для MySQL Slave:

cat > /etc/percona-server.conf.d/mysqld.cnf << EOF
[client]
port = 3306
socket = /mnt/u01/mysql/data/mysqld.sock
default-character-set = utf8

[mysqld_safe]
nice = 0
socket = /mnt/u01/mysql/data/mysqld.sock

[mysqld]

# default settings
datadir=/mnt/u01/mysql/data/
socket=/mnt/u01/mysql/data/mysqld.sock
user = mysql
port = 3306
skip-external-locking
default-storage-engine = innodb
pid-file = /var/run/mysqld/mysqld.pid
transaction-isolation = READ-COMMITTED
max_allowed_packet = 16M
myisam-recover-options = BACKUP
explicit_defaults_for_timestamp = 1
expire_logs_days = 10
max_binlog_size = 100M
sql_mode = ""

# Cache parameters
query_cache_size = 32M
table_open_cache = 4096
thread_cache_size = 32
key_buffer_size = 16M
thread_stack = 128K
join_buffer_size = 2M
sort_buffer_size = 2M

# Parameters for temporary tables
tmpdir = /tmp
max_heap_table_size = 32M
tmp_table_size = 32M

# InnoDB parameters
innodb_file_per_table
innodb_buffer_pool_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 128M
innodb_flush_method = O_DIRECT

# Database charset parameters
character-set-server = utf8
collation-server = utf8_unicode_ci
init-connect = "SET NAMES utf8 COLLATE utf8_unicode_ci"
#skip-character-set-client-handshake
skip-name-resolve

# Logging
log_error=/var/log/mysqld.log
pid_file=/var/run/mysqld/mysqld.pid

# Replication
server-id=2
gtid_mode=ON
enforce-gtid-consistency
replicate-do-db = db_name

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqldump]
quick
quote-names
max_allowed_packet = 16M
default-character-set = utf8

[isamchk]
key_buffer = 16M
EOF

Выполнить инициализацию базы данных в директорию MYSQL-PATH:

/usr/sbin/mysqld --defaults-file=/etc/percona-server.conf.d/mysqld.cnf --initialize --user=mysql --basedir=/mnt/u01/mysql --datadir=/mnt/u01/mysql/data 

Запустить MySQL и проверить, что запуск успешен:

systemctl enable mysqld --now && systemctl status mysql

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

cat /var/log/mysqld.log | grep "A temporary password" | awk '{print $11}'

Подключиться к консоли MySQL и сменить временный пароль root:

mysql -uroot -p
alter user 'root'@'localhost' identified by '3dY6FNFD^DJ!o';
flush privileges;

Сформировать конфигурационный файл для запуска консольного клиента mysql без ввода пароля:

cat > /root/.my.cnf << EOF
[client]
user=root
password='3dY6FNFD^DJ!o'
socket=/mnt/u01/mysql/data/mysqld.sock
EOF

Создать базу данных:

CREATE DATABASE `db_name` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;

Выдать права для управления репликацией под той же учётной записью с сервера приложений APP_SERVER:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, INDEX, ALTER, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user'@'IP_APP' identified by 'FNDHBD&^DJK';

Выдать права для выполнения репликации с сервера DB-MASTER:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, INDEX, ALTER, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user'@'IP-DB-MASTER' identified by 'FNDHBD&^DJK';

Очистить привилегии:

flush privileges;

Распаковать дамп базы на для сервера DB-SLAVE:

 zcat /root/db_name_slave.sql.gz | mysql db_name

Настроить подключение для выполнения репликации:

CHANGE MASTER TO MASTER_HOST="IP-MASTER-DB", MASTER_PORT=3306, MASTER_USER="user", MASTER_PASSWORD="FNDHBD&^DJK", MASTER_AUTO_POSITION=1;

Запустить репликацию:

start slave;

Проверить статус:

show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.4.38
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

Настройка репликации на уровне Битрикс24

На данном этапе осталось лишь подхватить созданный слейв в админке битрикса.

  • В административной панели зайди в модуль “Веб-кластер“-> Группа #1-> Репликация
  • Нажать “Добавить Slave базу данных“, пройти проверку при создании слейва
  • Несмотря на красные пункты, можно продолжать далее – это всего лишь рекомендации и дальнейшей настройке они не помешают
  • После остается указать имя соединения для репликации и, если всё было сделано правильно, слейв подхватится без всяких дополнительных проверок и закрытия публичной части.

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

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