Периодически возникает такая задача, и каждый раз приходится лазить в какие-то доки, что не очень удобно. Поэтому сформировал инструкцию, как настроить репликацию типа Master-Master в MySQL, прежде всего, для себя, ну и для тех, вдруг кто сюда попадёт случайно 🙂 Допустим, у нас был один сервер БД, и нужно подключить второй. Например, мы задумываем кластер из двух серверов, на каждом из которых будет происходить постоянная активность, либо второй вариант – новый сервер будет выполнять роль запасного standby-сервера, готового войти в работу вот прямо сейчас.

Итак, у нас уже есть база данных blog на первом сервере, нужно создать её также и на втором:

mysql> CREATE DATABASE blog;
Query OK, 1 row affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON blog.* TO 'blog'@'%' identified by 'secret123';

В этот момент желательно запретить все подключения на первый сервер, и скопировать имеющуюся базу данных blog на второй сервер. В основной конфигурационный файл my.cnf вносим изменения на первом сервере:

server-id                = 1
auto_increment_increment = 2
auto_increment_offset    = 1
log_bin                  = /var/log/mysql/mysql-bin
expire_logs_days         = 5
max_binlog_size          = 100M
binlog_do_db             = blog
replicate_do_db          = blog

и на втором:

server-id                = 2
auto_increment_increment = 2
auto_increment_offset    = 2
log_bin                  = /var/log/mysql/mysql-bin
expire_logs_days         = 5
max_binlog_size          = 100M
binlog_do_db             = blog
replicate_do_db          = blog

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

# /usr/local/etc/rc.d/mysql-server restart
Stopping mysql.
Waiting for PIDS: 12345.
Starting mysql.

На обоих серверах создаём пользователя для репликации:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'replicationsecret456';

На всякий случай, сбрасываем параметры репликации:

mysql> STOP SLAVE;
mysql> RESET SLAVE;
mysql> RESET MASTER;

Теперь выполняем следующую команду на первом сервере (10.0.0.1):

mysql> SHOW MASTER STATUS;
+------------------+----------+-------------------+------------------+
| File             | Position | Binlog_Do_DB      | Binlog_Ignore_DB |
+------------------+----------+-------------------+------------------+
| mysql-bin.000003 |      107 | blog,blog         |                  |
+------------------+----------+-------------------+------------------+
1 row in set (0.00 sec)

и на втором (10.0.0.2):

mysql> SHOW MASTER STATUS;
+------------------+----------+-------------------+------------------+
| File             | Position | Binlog_Do_DB      | Binlog_Ignore_DB |
+------------------+----------+-------------------+------------------+
| mysql-bin.000004 |      107 | blog,blog         |                  |
+------------------+----------+-------------------+------------------+
1 row in set (0.00 sec)

Из вывода последней команды следует взять значения параметров File и Position и, вместе с IP-адресом или именем противоположного сервера, подставить в команду CHANGE MASTER текущего сервера. Таким образом, на первом сервере выполняем:

mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.2',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='replicationsecret456',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=107,
MASTER_CONNECT_RETRY=10;
START SLAVE;

На втором сервере:

mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.1',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='replicationsecret456',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=107,
MASTER_CONNECT_RETRY=10;
START SLAVE;

Чтобы убедиться, что репликация работает, смотрим вывод команды на обоих серверах:

mysql> SHOW SLAVE STATUS\G

Должны присутствовать строки:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Если же значения этих параметров равны No, то смотрим логи и разбираемся с этой проблемой – выставляем правильные параметры, разрешаем соединения на файрволле и т.п. В диагностике может помочь строка Last_IO_Error из этого же вывода, в которой будет отображён текст ошибки.

Более подробная документация, конечно же: MySQL 5.0 Reference Manual. Chapter 16. Replication

Вот ещё хорошая книга по MySQL: