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