MySQL мастер-мастер репликация

Многие используют в MySQL функцию Master - Slave репликации для зеркалирования или бекапа данных. А что, если slave должен иметь возможность записать данные в БД, которые затем должны реплицироваться на Master? Настройка Master - Master репликации на самом деле не представляет из себя ничего сложного.

Дано:

• Хост 1 (192.168.1.1) - главный сервер
• Хост 2 (192.168.1.2) - второй сервер, зеркало первого, который должен реплицировать все с главного, а также передавать ему свои изменения

Необходимо настроить мастер-мастер репликацию между главным сервером и зеркалом. Поехали!

На главном сервере:

• В файле конфигурации MySQL (my.cnf) отключаем параметр skip-networking и прописываем в bind-address внешний IP данного сервера
• Туда же добавляем параметры:
  
  #  уникальный ID сервера, участвующего в репликации
  server-id = 1
  
  # путь к файлу журнала и индекса репликации для зеркала
  log-bin = /var/log/mysql/mysql-bin.log
  log-bin-index = /var/log/mysql-bin.index
  
  # здесь указываем, какие базы игнорировать при записи в журнал
  binlog-ignore-db = information_schema
  binlog-ignore-db = mysql
  
  # чтобы не было конфликтов автоинкремента, говорим серверу,
  # чтобы айдишники он генерил начиная с 1го прибавляя по 2,
  # например 1, 3, 5, 7... Зеркало будет генерить 2, 4, 6, 8...
  auto_increment_offset = 1
  auto_increment_increment = 2
  
  # указываем адрес зеркального хоста
  master-host = 192.168.1.2
  master-port = 3306
  master-user = main_replica
  master-password = qwerty
  master-connect-retry = 60
  
  # говорим, какие базы сервер не будет читать из журнала зеркального
  # сервера
  replicate-ignore-db = mysql                    
  replicate-ignore-db = information_schema
  
  # путь к файлам журнала изменений, полученных с зеркала
  relay-log = /var/log/mysql/slave-relay-bin
  relay-log-index = /var/log/mysql/slave-relay-bin.index
• Перезапускаем сервер и создаем пользователя, для доступа к БД с зеркального сервера:
  
  $ mysql -uroot -p
  
  mysql> GRANT REPLICATION SLAVE ON *.* TO 'mirror_replica'@'%' IDENTIFIED BY 'qwerty';
  mysql> FLUSH PRIVILEGES;
• Теперь нужно исключительно точно синхронизировать 2 базы данных, так что для этого сначала блокируем базу данных на запись:
  
  mysql> FLUSH TABLES WITH READ LOCK;
  mysql> SHOW MASTER STATUS;
  
  +------------+----------+--------------+------------------+
  | File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  +------------+----------+--------------+------------------+
  | bin.000002 |      654 |                  | mysql            |
  +------------+----------+--------------+------------------+

  Запоминаем параметры File и Position и создаём дамп реплицируемых баз:
  

  $ mysqldump -uroot -p ourdatabase > sql.dump
  
  Внимание! Блокировка таблиц будет снята, если выйти из консоли MySQL

На зеркальном сервере:

• Переносим этот дамп на зеркальный сервер и импортируем его:
  
  $ mysqladmin -uroot -p create ourdatabase
  $ mysql -uroot -p ourdatabase < sql.dump
• В файле конфигурации MySQL снова отключаем skip-networking и делаем в принципе тоже самое:
  
  server-id = 2
  master-host = 192.168.1.1
  master-port = 3306
  master-user = mirror_replica
  master-password = qwerty
  master-connect-retry = 60
  
  auto_increment_increment = 2
  auto_increment_offset = 2
  
  log-bin = /var/log/mysql/mysql-bin.log
  binlog-ignore-db = information_schema
  binlog-ignore-db = mysql
  replicate-ignore-db = mysql
  replicate-ignore-db = information_schema
  relay-log = /var/log/mysql/slave-relay-bin
  relay-log-index = /var/log/mysql/slave-relay-bin.index
• Перезапускаем mysqld и запускаем процесс репликации в консоли MySQL (используя параметры File и Position с главного сервера):
  $ mysql -uroot -p
  
  mysql> SLAVE STOP;
  mysql> CHANGE MASTER TO MASTER_LOG_FILE='bin.000002', MASTER_LOG_POS=654;
  mysql> START SLAVE;
  mysql> SHOW SLAVE STATUS \G;

  Убеждаемся в том, что параметры Slave_IO_Running и Slave_SQL_Running установлены в Yes. На данный момент у нас получилась работающая связка Master - Slave

• Создаем пользователя, для доступа к БД с главного сервера и записываем показания журнала:
  
  mysql> GRANT REPLICATION SLAVE ON *.* TO 'main_replica'@'%' IDENTIFIED BY 'qwerty';
  mysql> FLUSH PRIVILEGES;
  mysql> SHOW MASTER STATUS;
  
  +------------+----------+--------------+------------------+
  | File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  +------------+----------+--------------+------------------+
  | bin.000006 |       12 |                 | mysql             |
  +------------+----------+--------------+------------------+

На главном сервере:

• В открытой ранее консоли MySQL разблокируем ранее блокированные таблицы:
  
  mysql> UNLOCK TABLES;
• Запускаем процесс репликации в консоли MySQL (используя параметры File и Position с зеркала):
  mysql> SLAVE STOP;
  mysql> CHANGE MASTER TO MASTER_LOG_FILE='bin.000006', MASTER_LOG_POS=12;
  mysql> START SLAVE;
  mysql> SHOW SLAVE STATUS \G;
  
  Убеждаемся в том, что параметры Slave_IO_Running и Slave_SQL_Running установлены в Yes.

Обращаю внимание на то, что процесс репликации, а тем более мастер-мастер репликации, очень хрупок и, не смотря на то, что мы позаботились об autoincrement, конфликты все равно будут. Взять хотя бы CMS Drupal - при репликации его БД рано или поздно вылезет ошибка при добавлении записи в таблицу cache с уже существующим, жестко вбитым в запрос, primary key. Чтобы этого избежать, добавьте в my.cnf: