Репликация MySQL - Rascal's... home?

Данная запись является некоторой заметкой-инструкцией-велосипедом по репликации БД MySQL 5.X. Репликация может обеспечить отказоустойчивость, резервирование и маштабируемость.

В пятой ветке MySQL реализовано два механизма репликации: Master-Slave и NDBCLUSTER. Причем последний начиная с версии 5.1 является отдельным продуктом — MySQL Cluster и поддерживает только NBD таблицы. NDBCLUSTER далее рассматриваться не будет, может быть позднее ему будет посвящена отдельная запись.

Master-Slave

Master-Slave репликация производится при помощи бинарных логов, ведущихся на мастере. В них сохраняются запросы потенциально приводящие к изменениям в БД. Очень важно понимать, что реплицируются не сами измененные данные, а только запросы, вызывающие изменения. Это дает нам некоторую свободу относительно таблиц на мастере и репликах, например различный тип таблиц или произвольные индексы.

Итак, предположим что у нас есть работающий MySQL сервер 192.168.0.1 и требуется настроить репликацию баз данных расположенных на нем на только что подготовленный сервер 192.168.0.2. Алгоритм действий изложен далее.

1. Обеспечить отсутствие изменений на сервере 192.168.0.1.

Здесь есть несколько вариантов, например, запуск MySQL с опцией —skip-networking или использование LOCK TABLES.

2. Создать дамп всех баз данных сервера 192.168.0.1.

Наверняка лучшем вариантом будет:

root@localhost:~$ mysqldump --opt --all-databases > dump.sql

3. Включить бинарный лог на сервере 192.168.0.1.

Для этого добавим в конфиг MySQL (обычно /etc/mysql/my.cnf) следующее:

server-id         = 1
log_bin           = /var/log/mysql/mysql-bin.log
expire_logs_days  = 3
max_binlog_size   = 100M

После этого перезапускаем сервис и разблокируем таблицы:

root@localhost:~$ /etc/init.d/mysql restart

Создаем пользователя для репликации:

mysql> USE mysql;
mysql> INSERT INTO user 
(Host, User, Password, Repl_slave_priv) 
VALUES ('192.168.0.2', 'mysql-node2', password('secret'), 'Y');
mysql> FLUSH PRIVILEGES;

4. Загрузка дампа на сервер 192.168.0.2 и запуск репликации

Копируем дамп с сервера 192.168.0.1 на сервер 192.168.0.2 любым доступным способом, как то ftp или scp, после чего загружаем дамп:

root@localhost:~$ mysql < dump.sql

Добавляем в конфиг MySQL на сервере 192.168.0.2 строки:

server-id         = 2
log_bin           = /var/log/mysql/mysql-bin.log
expire_logs_days  = 3
max_binlog_size   = 100M
master-host       = 192.168.0.1
master-user       = mysql-node2
master-password   = secret
master-port       = 3306

Перерезапускаем сервис:

root@localhost:~$ /etc/init.d/mysql restart

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

mysql> start slave;

После этого можно посмотреть состояние репликации. На сервере 192.168.0.2:

mysql> show slave status\G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.0.1
                Master_User: mysql-node2
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000004
        Read_Master_Log_Pos: 98
             Relay_Log_File: mysqld-relay-bin.0000023
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000004
           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: 98
            Relay_Log_Space: 235
            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: 0
1 row in set (0.00 sec)

На сервере 192.168.0.1:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |       98 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Это говорит нам о том, что репликация работает.

Master-Master

Мы настроили репликацию всех изменений сервера 192.168.0.1 на сервер 192.168.0.2, на этом можно остановится, а можно реплицировать изменения 192.168.0.2 на 192.168.0.1, что мы и продемонстрируем.

Создаем еще одного пользователя на сервере 192.168.0.1:

mysql> USE mysql;
mysql> INSERT INTO user 
(Host, User, Password, Repl_slave_priv) 
VALUES ('192.168.0.1', 'mysql-node1', password('secret'), 'Y');
mysql> FLUSH PRIVILEGES;

Так как мы реплицируем все базы, пользователь так же был создан и на втором сервере. Редактируем конфиги к примерному виду.

На сервере 192.168.0.1:

server-id         = 1
replicate-same-server-id = 0
auto_increment_increment = 2
auto_increment_offset    = 1
log_bin           = /var/log/mysql/mysql-bin.log
expire_logs_days  = 3
max_binlog_size   = 100M
master-host       = 192.168.0.2
master-user       = mysql-node1
master-password   = secret
master-port       = 3306

На сервере 192.168.0.2:

server-id         = 2
replicate-same-server-id = 0
auto_increment_increment = 2
auto_increment_offset    = 2
log_bin           = /var/log/mysql/mysql-bin.log
expire_logs_days  = 3
max_binlog_size   = 100M
master-host       = 192.168.0.1
master-user       = mysql-node2
master-password   = secret
master-port       = 3306

После этого перезапускаем MySQL на обоих серверах:

root@localhost:~$ /etc/init.d/mysql restart

И запускаем репликацию на сервере 192.168.0.1:

mysql> start slave;

Проверяем состояние репликации:

mysql> show slave status\G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.0.2
                Master_User: mysql-node1
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000004
        Read_Master_Log_Pos: 421
             Relay_Log_File: mysqld-relay-bin.000020
              Relay_Log_Pos: 558
      Relay_Master_Log_File: mysql-bin.000004
           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: 421
            Relay_Log_Space: 558
            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: 0
1 row in set (0.00 sec)

На сервере 192.168.0.2:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      421 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

При работе в режиме репликации Master-Master возможны проблемы со значениями AUTO_INCREMENT, для устранения этих проблем используются два параметра:

auto-increment-increment - шаг изменения AUTO_INCREMENT, должен равняться числу узлов n.
auto-increment-offset - начальное значение AUTO_INCREMENT, от 1 до n, на каждом узле.

Как показала практика - такое решение волне жизнеспособно.

20121217. Начиная с версии MySQL 5.5 директивы master-host, master-user, master-password, master-port конфигурационного файла считаются устаревшими, а реплиция настраивается с помощью sql:

mysql> CHANGE MASTER TO MASTER_HOST = '192.168.0.1', MASTER_USER = 'mysql-node2',
     > MASTER_PASSWORD='secret';
mysql> START SLAVE;