Репликация 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.

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

[email protected]:~$ 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

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

[email protected]:~$ /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, после чего загружаем дамп:

[email protected]:~$ 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

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

[email protected]:~$ /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 на обоих серверах:

[email protected]:~$ /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;