MySQL репликация от простого к сложному

Задача: Научится репликации, понимать что и зачем на простых примерах.

Репликация master-slave.

Вводные:

Есть два VM сервера ubuntu linux
Server1: IP 192.168.1.1
Server2: IP 192.168.1.2

Hа серверах установлен MySQL.
На сервере1 установлена база данных 'testdb' в базе есть несколько таблиц.
Пользователь testuser имеет полные права на базу test.
Вся работа ведется именно с сервером 1, база данных сервера 2 нужна как копия (схема master — slave)
Считается что вы знаете как работать с MySQL а именно как создавать пользователей, присваивать им права, создавать базы, бекапы.

Итак приступим.

Настройка Server1 (master)

На Server1 нужно отредактировать конфигурационный файл MySQL my.cnf.
В ubuntu он по умолчанию находится в /etc/mysql/ я использую консольный редактор nano

sudo nano /etc/mysql/my.cnf

Все что нужно – это в раздел [mysqld] добавить 2 строки

server-id = 10
log_bin = /var/log/mysql/mysql-bin.log

Server-id это уникальный номер сервера MySQL в системе.
Я советую не использовать единицу как id. В реальной жизни часто встречается что сервер с id 1 уже существует в системе что в дальнейшем приведет к ошибкам.
log_bin это файл лога где хранятся все команды к базе данных (они будут нужны для синхронизации). Подробнее об этом файле будет рассказано ниже.

Сохраните изменения и перегрузите MySQL сервер

sudo /etc/init.d/mysql restart

Далее нужно создать бекап базы и перенести его на сервер 2
Для начала репликации нам нужно иметь две идентичные базы данных.
Если база Server1 (master) пуста то все проще – надо только создать базу с таким же именем и структурой на Server2 и создать пользователя с полными правами к базе.

Зайдите в консоль MySQL на Server1

sudo mysql -u testuser -p

проверьте правильность выставленого значения server_id

mysql> show variables like 'server%';

Этот шаг может показаться излишним но в мой практике бывало что id в my.cnf было указано более одного раза или ошибочно.

Получим информацию о файле Bin лога

mysql> SHOW MASTER STATUS\G

пример выполнения команды:

*************************** 1. row ***************************
 File: mysql-bin.000005
 Position: 3960490
 Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

Важные значения для нас это file и position
file – текущий индекс файла лога хранящий команды которые получает сервер
position — номер последней строки в файле
запомним эти значения и перейдем к настройке Server2

Настройка Server2 (slave)

Oтредактируем my.cnf

server_id = 20

replicate-do-db = testdb

Вторая строка нужна если вы хотите настроить репликацию только для базы testdb.
Эту строку можно и проигнорировать – тогда репликация будет проходить для всех баз данных на Server1.
Или же можно просто запретить пользователю testuser доступ к тем базам которым репликация не нужна.
Сохраняем изменения в файле и перегружаем MySQL server

/sudo/etc/init.d/mysql restart

Зайдите в консоль MySQL на Server2

sudo mysql -u testuser -p

Запустите команд остановки slave

mysql> stop slave; 

Возможно вы уже пытались настроить репликацию, в любом случае не пропускайте этот шаг.

Далее команды:

change master to MASTER_HOST='192.168.1.1';

change master to MASTER_USER='testuser';

change master to MASTER_PASSWORD='password',

change master to MASTER_LOG_FILE='имя файла лога мастера',

(значение команды show master status с сервера 1 — в моем примере это mysql-bin.000005;)

change master to MASTER_LOG_POS=recorded_log_position; 

(значение команды show master status с сервера 1 — в моем примере это 3960490 — обратите внимание, значение указывается без кавычек!)

И запускаем репликацию командой

mysql> startslave;

Проверим состояние репликации командой

mysql> show slave status \G

Если команда вернула значения: Slave_IO_Running: Yes и Slave_SQL_Running: Yes — это значит репликация работает и никаких ошибок нет.

Но к сожалению редко все проходит гладко с первого раза.

Если Slave_IO_Running: No — значит нет обмена межу серверами.
Возможые причины – нет ping межу серверами, пользователь не testuser а testuser@localhost что значит пользователь доступен только в рамках сервера и пользователь с таким же именем но с другого сервера не получит доступ к базе что приведет к ошибке подключения.
Slave_SQL_Running: No – значит произошла ошибка на уровне sql. Посмотрите детали в Last_Error.

Исправив ошибку запустите slave командой

mysql> start slave

Провертие состояние репликации командой

mysql> show slave status \G

Измените данные в любой таблице в базе данных testdb на Server1, проверьте ту же таблицу на Server2 данные так же должны поменяться.
Простая репликация master-server в действии!

Еще немного теории

Понимание работы файла лога это ключевой момент в настройке репликации.
С момента как мы запустили базу на Server1 и начали настраивать репликацию на Server2 прошло какое то время.
Возможно данные в базе Server1 были изменены – какие то строки добавлены пользователями, какие то возможно были удалены.
Но, репликация еще не запущена и база на Server2 имеет только данные из бекапа.
Как я писал выше MASTER_LOG_FILE (он же log_bin ) хранит информацию о всех командах (insert, delete update).
Указав имя текущего файла (в моем случае mysql-bin.000005) и позицию в нем на момент бекапа ( в примере: 3960490 ) slave при обращению к мастеру запросит текущую позицию лога ( например пусть будет 3960495), получит все команды (5) и выполнит их с свой базой.
Упрощенно, имеено так и происходит синхронизация при репликации.
Если связь Server 2 и Server1 будет потеряна, при восстановлении slave доберет данных с master именно через файл лога.
Если не указать файл и позицию то будут выбраны и выполнены на slave все команды – и теоретически бекап изначально при настройке репликации можно бы было и не делать
но опять же если только файл mysql-bin.log существовал с самого начала создания баз данных на Server1 и данных не много — иначе сверка займет много времени.

Попробуйте поиграться с настройками.
Oстановите репликацию на Server2 и поменйте данные в базе данных Server1.
Запустите репликацию на Server2 без изменения информации о файле лога master и позиции.
Попробуйте добавить еще одну базу данных в список репликации.
Вариантов много. Пробуйте, учитесь, запоминайте.
Если что то не получается пишите я с удовольствем отвечу на вопросы.