MySQL репликация one-slave-multi-master

Предисловие.


Понадобилось сделать репликацию несколькими мастер-серверами с mysql, чтобы данные со всех них грузились на один слэйв-сервер. Готового решения стандартными средствами не нашлось. Но так как проблема оставалась актуальной, со временем подоспел немного усложненный, но работоспособный вариант c использованием средств самой mysql.

Собственно, решение.
Мастер-серверы настраиваются как при обычной репликации. Все колдовство со слэйвом.
Репликация с каждого мастера производится отдельными вспомогательными процессами mysqld. Таблицы в целевой базе работают на движке FEDERATED и подключены к базам основного процесса mysqld.
Если вы знакомы с mysql_multi и federated, то на этом, в общем-то, все. Далее немного тонкостей реализации и полезностей.

Настраивался слэйв с ubuntu-server 10.04.

mysqld_multi

Не буду описывать основную настройку mysqld_multi, да ее и нет таковой. В /usr/share/mysql/mysqld_multi.server есть init.d скрипт.
Настройки mysqld_multi лучше делать в отдельном файле, так как для управления демонами скрипту нужны имя пользователя и пароля для доступа к серверам.
Так же чтобы иметь общии настройки для всех процессов репликации оставляем секцию mysqld.
Для каждой реплицируемой базы создаем секцию mysqldN с настройками сервера и репликации. И нужно не забыть включить federated.

$sudo cp /etc/mysql/my.cnf /etc/mysql/my_multi.cnf
$sudo chmod 600 /etc/mysql/my_multi.cnf

редактируем:
[mysqld_multi]
log = /var/log/mysql/mysqld_multi.log
mysqld = /usr/sbin/mysqld
mysqladmin = /usr/bin/mysqladmin
user = root
password = pass

[mysqld1]
datadir = /var/lib/mysql_multi/mysql1
#datadir = /var/lib/mysql_multi
socket = /var/run/mysqld/mysqld1.sock
port = 33061
tmpdir = /var/tmp/mysql/mysqld1
pid-file = /var/run/mysqld/mysqld1.pid
log_error = /var/log/mysql/error1.log
federated
#skip-innodb

server-id = 101
replicate-do-db = db
#replicate-do-db = my_db1
#replicate-rewrite-db = db->my_db1
master-info-file = mysql1-master.info
relay-log = mysql1-relay-bin
relay-log-index = mysql1-relay-bin.index
relay-log-info-file = mysql1-relay-log.info
replicate-wild-ignore-table = mysql.%

[mysqld2]
....


Есть 2 варианта как хранить базы: все вместе в одном каталоге (раскоментить строки) или для каждого сервера указывать свою datadir.
При первом будет меньше каталогов и общие системные схемы mysql (пароли в ней), но не работает innodb.
2й способ более гибкий, устойчивый и работает с innodb. Я остановился на нем. Да и query-browser, как оказалось, дописывает полные имена таблицам в любом случае, а репликация с реврайтом этого не поймет.

Запускать mysqld_multi надо теперь с параметром --defaults-extra-file=/etc/mysql/my_multi.cnf
В init.d скрипте тоже добавляем.

Если работает apparmor, то и его настройки нужно подправить:
/etc/apparmor.d/usr.sbin.mysqld:
....
/usr/share/mysql/** r,
/var/log/mysql.log rw,
/var/log/mysql.err rw,
/var/lib/mysql/ r,
/var/lib/mysql/** rwk,
/var/lib/mysql_multi/ r,
/var/lib/mysql_multi/** rwk,
/var/log/mysql/ r,
/var/log/mysql/* rw,
/var/run/mysqld/mysqld.pid w,
/var/run/mysqld/mysqld.sock w,
/var/run/mysqld/mysqld?.pid w,
/var/run/mysqld/mysqld?.sock w,
/var/run/mysqld/mysqld??.pid w,
/var/run/mysqld/mysqld??.sock w,
....

$sudo service apparmor reload


Создаем директории под базы. Если основная база чистая еще, то можно ее копировать, иначе используем mysql_install_db
$sudo mkdir -pm700 /var/lib/mysql_multi/mysqlN
$sudo chown -R mysql:mysql /var/lib/mysql_multi
$sudo mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql_multi/mysqlN

Запускаем и проверяем.
$sudo service mysqld_multi start
$mysql -uroot -p -h127.0.0.1 -P33061

federated.

На основном сервере создаем пользователя для подключения к таблицам по federated:
mysql>CREATE USER 'fdrt_local'@'localhost';
mysql>GRANT SELECT, UPDATE, DELETE ON 'rpl_%' TO 'fdrt_local'@'localhost';

Перед запуском.

Развернуть текущую базу на основном сервере.

На мастере
$mysqldump --opt bd | gzip > dbN_full.sql.gz
$mysqldump --opt -d bd | gzip > dbN_nodata.sql.gz

Переносим архивы на сервер, затем
$zcat bd_full.sql.gz | mysql repl_dbN
Я еще добавлял триггеры на некоторые таблицы в целевых базах, для создания обобщающих таблиц.

Создать базу с federated таблицами на слэйв-сервере.

С помощью следующей регулярки создаем файл dbN_fdrt.sql и заливаем его в на слэйв-сервер.
#(CREATE[\s\w]*)`(\w*)`((.|[\n\r])*?ENGINE\s*=\s*)(\w)+((.|[\n\r])*?;)#iu
меняем на
$1`$2`$3FEDERATED CONNECTION=\'mysql://fdrt_local@localhost/%db_name%/$2\'$6


Можно в регулярку поставлять имя базы, либо sed'ом поменять перед рестором.
$sed 's/%db_name%/repl_dbN/g' bd_fdrt.sql | mysql -h127.0.0.1 -P33061 dbN


Или даже лучше и проще использовать команду CREATE SERVER одну на базу.

UPD: с CREATE SERVER не заработало в mysqld 5.1.41-3ubuntu12.7.

Далее настраиваем слэйв и запускаем репликацию. Готово.

Поправил: несколько небольших скриптов помогают мне управляться с репликациями, проверять статус и прочее. Если кому интересно, с радостью поделюсь.
Share post

Comments 14

    0
    А записи со всех серверов там все строго уникальные, и вы сливаете их все в одну кучу в итоге?
    Просто если нет, то не ясно как вы разруливаете конфликты.
      0
      каждая удаленная бд пишется в отдельную базу на слэйве. на несколько таблиц повесил триггеры, которые пишут из всех баз в одну. все усложнено немного, но пока это самое оптимальное решение
        0
        хм, но это отменяет наличия конфликтов при совпадении ID
          0
          не отменяет точнее
            0
            в общей базе уже дргие ид. это, конечно, специфично для моего случая, требовались новые ид.
            но для всех можно настроить auto_increment_increment и auto_increment_offset на мастерах, чтобы с триггерами не мудрить.
            тогда даже, если все базы одинаковы, можно и таргет у федерэйтэдов один сделать.
              0
              Ну я так и подумал что в итоге все записи получали новые ID, просто очень близка данная тема, сами сейчас мудрим как бы так всё это красиво собрать, но собираем в итоге на самом клиенте и кэшируем, присваивать новые ID в нашем случае нельзя.
        0
        С технической точки зрения всё ясно. А какая архитектурная цель этого решения?
          0
          для меня — объединение нескольких автономных бд на одном сервере, для выполнения общих запросов
          0
          А можно вопрос: зачем? «Понадобилось сделать репликацию несколькими мастер-серверами с mysql, чтобы данные со всех них грузились на один слэйв-сервер.»

          И например рассматривался ли вариант с цепочной репликацией и BLACKHOLE (и если да, то чем не подошёл)?:
          MASTER1 -> MASTER2,BLACKHOLE1 — > MASTER3,BLACKHOLE1,BLACKHOLE2 ->......->MASTER_N,BLACKHOLE1...BLACKHOLE_N-1 -> SLAVE

          Такой вариант проще конфигурировать и обслуживать и никаких скриптов.
          Формально у такой цепочки ниже надежность, но мне кажется что для агрегатора некоторый downtime не критичен. Точней так: мне кажется что нет большой разницы между тем, что 30 минут не поступают данные репликации (которые потом подтянутся) и тем, что 30 минут не поступают данные от одного из мастеров — в том смысле что в обоих этих случаях агрегированные значения будут отсутствовать/неверны
            0
            Каждая из баз автономная, и есть возможность, что в любой момент она может оказаться недоступной. И вовсе не надо было, чтобы на мастерах оказывались данные другого мастера.
            Я посмотрел много вариантов репликации, включая предложенные. Добавление/удаление звена в кольце, изыски с replicate-rewrite-db (в посте писал, что query-browser выполняет запросы только с полными именами таблиц, вместе с именем базы. Но тут можно и базы назвать по-разному, в принципе) гораздо сложнее используемого способа. Так вся конфигурация находится на 1м сервере и при остановке мастера вообще ничего не требуется, а при добавлении маленький скрипт быстро поднимает репликацию.
            Я не предлагаю использовать этот способ вместо каких-то предложенных вами. Он реализует one-slave-multi-master без лишних мастер-мастер репликаций.
            Позже нашел tungsten-replicator. Пока не смотрел, но уже опасаюсь полных путей таблиц, а без q-b пока никак.
              0
              blackhole не хранит данных. только логи для репликации.
              это не кольцо
              здесь нет master-master репликаций. толькоmaster-slave
                0
                Про мастер-мастер я писал к комменту ниже. Почему не использовал цепочку, я написал в первом абзаце и конце второго предыдущего ответа. Может только не уточнил, что «оказаться недоступной» — быть отключена навсегда.
            +1
            А зачем так сложно? Сделали бы обычную циркулярку, вот по этой статье хотя бы Advanced MySQL Replication Techniques, и имели бы себе «мультимастер».
              0
              Ответил в комменте выше.

            Only users with full accounts can post comments. Log in, please.