Содержание
MySQL. Настройка репликации Master-Slave
Master-Slave репликация в MySQL часто используется для обеспечения отказоустойчивости приложений. Кроме этого, она позволяет распределить нагрузку на базу данных между несколькими серверами (репликами). Читайте подробнее о применении репликации.
Настройка репликации происходит в несколько шагов. Мы будем использовать два сервера с адресами:
- Master сервер, 10.1.0.11
- Slave сервер, 10.1.10.22
Шаг 1. Настройка Мастера
На сервере, который будет выступать мастером, необходимо внести правки в my.cnf :
- server-id - идентификатор сервера, должен быть уникален. Лучше не использовать 1;
- log_bin - путь к бинарному логу;
- binlog_do_db - позволяет перечислить отдельные базы, для которых будет использоваться реплика.Если не инициализирована, то реплицируются все.
[mysqld] # предлагаю указать последний октет IP-адреса server-id = 11 log_bin = /var/lib/mysql/mysql-bin.log # название Вашей базы данных, которая будет реплицироваться binlog_do_db = newdatabase
Перезагружаем MySQL:
# В зависимости от системы и ПО: /etc/init.d/mysql restart # или systemctl restart mysqld.service # или systemctl restart mariadb.service
Шаг 2. Права на репликацию
Далее необходимо создать профиль пользователя, из под которого будет происходить репликация. Для этого запускаем консоль:
mysql -u root -p
Далее создаем и назначаем права пользователю для реплики:
- REPLICATION SLAVE - привилегия позволяющая подключиться к серверу т запросить обновлённые на мастере данные;
- REPLICATION CLIENT - привилегия, позволяющая использовать статистику:
- SHOW MASTER STATUS
- SHOW SLAVE STATUS
- SHOW BINARY LOGS
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave_user'@'10.1.%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;
Далее блокируем все таблицы в нашей базе данных:
USE newdatabase; FLUSH TABLES WITH READ LOCK;
Проверяем статус Мастер-сервера:
SHOW MASTER STATUS;
Мы увидим что-то похожее на:
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 107 | newdatabase | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
Шаг 3. Дамп базы
Теперь необходимо сделать дамп базы данных:
- --master-data - включить в дамп информацию о бинарном логе мастер хоста;
- -R - включить в дамп процедуры и функции.
mysqldump --master-data -R -u root -p newdatabase > newdatabase.sql
Разблокируем таблицы в консоли mysql:
USE newdatabase; UNLOCK TABLES;
Шаг 4. Создание базы на слейве
В консоли mysql на Слейве создаем базу с таким же именем, как и на Мастере:
CREATE DATABASE newdatabase;
После этого загружаем дамп (из bash):
mysql -u root -p newdatabase < newdatabase.sql
Шаг 5. Настройка Слейва
В настройках my.cnf на Слейве указываем следующие параметры:
- server-id - идентификатор сервера, должен быть уникален. Лучше не использовать 1. Это единственный обязательный параметр;
- log_bin - путь к бинарному логу. Оптимально указывать по аналогии с мастером;
- log_slave_updates - включает запись реляционных событий в собственный журнал на подчинённом сервере
- binlog_do_db - позволяет перечислить отдельные базы, для которых будет использоваться реплика.Если не инициализирована, то реплицируются все.
server-id = 22 log_bin = /var/log/mysql/mysql-bin.log relay_log = mysql-relay-bin # База данных для репликации binlog_do_db = newdatabase # если необходимо сделать базу доступной только для чтения # read_only = 1
Шаг 6. Запуск Слейва
Нам осталось включить репликацию, для этого необходимо указать параметры подключения к мастеру. В консоли mysql на Слейве необходимо выполнить запрос:
Для запуска slave-сервера необходимо:
- указать параметры соединения (master-data).
- запустить репликацию.
Если дамп базы делали с параметром --master-data, то первый пункт можно пропустить - информация будет указана при восстановлении дампа. В противном случае выполняем:
CHANGE MASTER TO MASTER_HOST='10.1.0.11', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 107;
Запуск репликации выполняется следующей командой:
START SLAVE;
Статус репликации
Проверить работу репликации на Слейве можно запросом:
mysql> SHOW SLAVE STATUS\G Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: root Master_Port: 3306 Connect_Retry: 3 Master_Log_File: gbichot-bin.005 Read_Master_Log_Pos: 79 Relay_Log_File: gbichot-relay-bin.005 Relay_Log_Pos: 548 Relay_Master_Log_File: gbichot-bin.005 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: 79 Relay_Log_Space: 552 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: 8
Траблшутинг
show master status возвращает пустой вывод
Если
SHOW MASTER STATUS;
возвращает пустой результат, проверьте, включены ли бинарные логи:
SHOW BINARY LOGS;
Если на выходе получаем ошибку:
ERROR 1381 (HY000) at line 1: You are not using binary logging
то смотрим информацию ниже.
ERROR 1381 (HY000) at line 1: You are not using binary logging
Ошибка возвращается при запросе статистики по бинарным логам:
SHOW BINARY LOGS;
Не включили бинарные логи. Проверьте корректно ли задали параметр log_bin - важно, чтобы он был определён в секции [mysql].
Last_IO_Error: error connecting to master...
Если SHOW SLAVE STATUS выводим примерно следующую ошибку:
*************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 10.1.0.11 Master_User: slave_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 419 Relay_Log_File: mysql-relay-bin.000005 Relay_Log_Pos: 529 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Connecting 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: 419 Relay_Log_Space: 1281 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 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 2003 Last_IO_Error: error connecting to master 'slave_user@10.1.0.11:3306' - retry-time: 60 retries: 86400 message: Can't connect to MySQL server on '10.1.0.11' (113) Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 11
то у slave-сервера отсутствует возможность соединения с master-сервером. Причины:
- некорректные авторизационные данные пользователя репликации;
- закрыт порт MySQL для исходящих соединений на slave-сервере;
- закрыт порт MySQL для входящих соединений на master-сервере.
Проверяем соединение:
$ telnet 10.1.0.11 3306
Trying 10.1.0.11...
telnet: connect to address 10.1.0.11: No route to host
Добавим правило на slave-сервере
iptables -I OUTPUT -p tcp -m tcp --dport 3306 -j ACCEPT
Добавим правило на master-сервере:
iptables -I INPUT -p tcp -m tcp --dport 3306 -j ACCEPT
Проверим возможность соединения:
$ telnet 10.1.0.11 3306 Trying 10.1.0.11... Connected to 10.1.0.11. Escape character is '^]'. V 5.5.47-MariaDB-log 0P$_6/&�}K;%Gt7Po\aQmysql_native_password