====== 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 ---- ===== Источники ===== * [[http://ruhighload.com/post/Как настроить MySQL Master-Slave репликацию|Как настроить MySQL Master-Slave репликацию?]] * [[http://oz.by/books/more10132838.html?sbtoken=b229c4880c7d8212efab52af6c36655f | MySQL. Оптимизация производительности ]]