Настройка репликации в MySQL

На тесте выполню настройку репликации в MySQL.

Допустим имеется два сервера с установленной системой Ubuntu Server, на обеих установим MySQL сервер и клиент, если они не установлены:

sudo apt-get install mysql-server mysql-client

Установку MySQL сервера я также описывал в статье — Установка и настройка MySQL сервера на Ubuntu.

Предположим что основной MySQL Master сервер имеет IP-адрес 192.168.1.8, второй — 192.168.1.9 мы настроим как Slave.

На мастер сервере откроем в текстовом редакторе файл конфигурации MySQL:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Укажем на каком IP работает сервер, назначим ему ID, укажем путь к bin логам, и с какой базы они будут записываться:

bind-address = 192.168.1.8
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = TESTDATABASE
max_binlog_size = 500M

В bin логах сохраняются запросы которые выполняются к указанной базе дынных на Master сервере и в дальнейшем они будут выполняться на Slave серверах, таким образом приводя базу к актуальному состоянию, при желании их можно увидеть например через утилиту mysqlbinlog.

Перезапустим MySQL сервер чтобы применить изменения:

sudo service mysql restart

Создадим пользователя, который будет выполнять подключение со Slave сервера и получать данные:

mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO "replication"@"192.168.1.9" IDENTIFIED BY "password";
FLUSH PRIVILEGES;
exit

Если Slave серверов много, то добавим аналогично пользователей указав нужные IP вместо 192.168.1.9, либо укажем символ * чтобы разрешить пользователю доступ с любого IP.

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

mysql -u root -p
USE TESTDATABASE;
FLUSH TABLES WITH READ LOCK;

Смотрим статус Master сервера:

SHOW MASTER STATUS;
exit

Когда я смотрел статус мастер севера, то увидел следующее:

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000040 |      154 | TESTDATABASE |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0,00 sec)

Так как мы заблокировали таблицы в базе и данные не изменяются, а mysql-bin.000040 и 154 нам пригодятся в дальнейшем чтобы указать с какого момента запустить перенос данных.

Теперь делаем дамп базы данных:

mysqldump -u root -p --opt TESTDATABASE > TESTDATABASE.sql

Теперь можно разблокировать таблицы:

mysql -u root -p
UNLOCK TABLES;

После разблокировании таблиц, если в базе на Master сервере будут выполняться изменения, то значение Position будет увеличиваться.

Приступим к настройке Slave сервера, создадим базу данных:

mysql -u root -p
CREATE DATABASE TESTDATABASE;
exit

Импортируем в нее ранее сделанный дамп:

mysql -u root -p TESTDATABASE < TESTDATABASE.sql

Откроем файл конфигурации MySQL сервера:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Укажем параметры:

server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
relay-log = /var/log/mysql/mysql-relay-bin.log
binlog_do_db = TESTDATABASE
max_binlog_size = 500M

Перезапустим MySQL сервер чтобы применить изменения:

sudo service mysql restart

Выполним запрос который укажет параметры для репликации, в том числе File и Position которые мы видели на мастер сервере перед тем как сделать дамп базы с заблокированными таблицами, тем самым указав с какой позиции запустить репликацию:

CHANGE MASTER TO MASTER_HOST='192.168.1.8', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE = 'mysql-bin.000040', MASTER_LOG_POS = 154;

Запускаем:

START SLAVE;

Смотрим статус:

SHOW SLAVE STATUS;

Похожим образом можно настроить и следующие Slave сервера, но дамп уже можно делать со Slave сервера остановим его командой:

STOP SLAVE;

После чего делаем дамп базы данных и смотрим значения File и Position чтобы указать их на следующем Slave сервере:

SHOW SLAVE STATUS;
START SLAVE;

Репликация отлично подойдет для того чтобы направить часть SELECT запросов вместо основного сервера на Slave, а также для создания резервных копий большой базы данных на Slave сервере, чтобы не останавливать основной и не нагружать его этим. Репликация не является резервным копированием.

Список процессов, подключенных клиентов и Slave можно посмотреть командами:

SHOW PROCESSLIST;
SHOW SLAVE HOSTS;

Смотрите также:
Решение ошибки ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

Подписаться на IT Blog (RU) по Email
Subscribe to IT Blog (EN) by Email

Did my article help you? How about buying me a cup of coffee as an encouragement? Buy me a coffe.

Добавить комментарий