Настройка репликации в 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

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

Оставьте комментарий

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