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

Настройка репликации происходит в несколько шагов. Мы будем использовать два сервера с адресами:

• Master сервер, 10.1.0.11
• Slave сервер, 10.1.10.22

На сервере, который будет выступать мастером, необходимо внести правки в my.cnf :

• server-id - идентификатор сервера, должен быть уникален. Лучше не использовать 1;
• log_bin - путь к бинарному логу;
• binlog_do_db - позволяет перечислить отдельные базы, для которых будет использоваться реплика.Если не инициализирована, то реплицируются все.

[mysql]
# предлагаю указать последний октет IP-адреса
server-id = 11
log_bin = /var/log/mysql/mysql-bin.log
 
# название Вашей базы данных, которая будет реплицироваться
binlog_do_db = newdatabase

Перезагружаем MySQL:

# В зависимости от системы и ПО:
/etc/init.d/mysql restart
# или 
systemctl restart mysqld.service
# или 
systemctl restart mariadb.service

Далее необходимо создать профиль пользователя, из под которого будет происходить репликация. Для этого запускаем консоль:

mysql -u root -p

Далее создаем и назначаем права пользователю для реплики:

• REPLICATION SLAVE - привилегия позволяющая подключиться к серверу т запросить обновлённые на мастере данные;
• REPLICATION CLIENT - привилегия, позволяющая использовать статистику:
  1. SHOW MASTER STATUS
  2. SHOW SLAVE STATUS
  3. 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)

Теперь необходимо сделать дамп базы данных:

• --master-data - включить в дамп информацию о бинарном логе мастер хоста;
• -R - включить в дамп процедуры и функции.

mysqldump --master-data -R -u root -p newdatabase > newdatabase.sql

Разблокируем таблицы в консоли mysql:

USE newdatabase;
UNLOCK TABLES;

В консоли mysql на Слейве создаем базу с таким же именем, как и на Мастере:

CREATE DATABASE newdatabase;

После этого загружаем дамп (из bash):

mysql -u root -p newdatabase < newdatabase.sql

В настройках my.cnf на Слейве необходимо указать такие параметры:

# ID Слейва, удобно выбирать следующим числом после Мастера
server-id = 2

# Путь к relay логу
relay-log = /var/log/mysql/mysql-relay-bin.log

# Путь к bin логу на Мастере
log_bin = /var/log/mysql/mysql-bin.log

# База данных для репликации
binlog_do_db = newdatabase

Нам осталось включить репликацию, для этого необходимо указать параметры подключения к мастеру. В консоли mysql на Слейве необходимо выполнить запрос:

CHANGE MASTER TO MASTER_HOST='10.10.0.1', MASTER_USER='slave_user', MASTER_PASSWORD='password',
MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 107;
</mysdl>
 
После этого запускаем репликацию на Слейве:
 
<code mysql>
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 BINARY LOGS;

Если на выходе получаем ошибку:

ERROR 1381 (HY000) at line 1: You are not using binary logging

то смотрим информацию ниже.

Ошибка возвращается при запросе статистики по бинарным логам:

SHOW BINARY LOGS;

Не включили бинарные логи. Проверьте корректно ли задали параметр log_bin - важно, чтобы он был определён в секции [mysql].

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