Есть работающая база данных MySQL. Нам нужно включить репликацию.
Наши исходные данные:
IP-адрес мастера 192.168.1.101, реплики — 192.168.1.102.
MySQL установлен и настроен требуется настроить репликацию БД redmine мы можем приостановить работу мастера на некоторое время у нас, разумеется, есть root на обеих машинах
Обязательно укажем уникальный ID сервера, путь для бинарных логов и имя БД для репликации в секции [mysqld] файла my.cnf:
server-id = 1
#Местоположение лога
log-bin = /var/lib/mysql/mysql-bin
replicate-do-db = redmine
#Максимальный размер по умолчанию 1 гигабайт
#max_binlog_size= 500M
# Удаление устаревших бинарных логов
expire_logs_days = 14
Добавление пользователя на MASTER для репликаций под правами которого будет производится репликация. Будет достаточно привилегии replication slave
mysql> CREATE USER 'replication'@'%' IDENTIFIED BY 'you_pass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
Задать пароль уже созданному пользователю:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'you_pass';
flush privileges;
exit;
Перезагружаем сервис mysql.
Если все прошло успешно, команда show master status должна показать примерно следующее:
mysql@master> SHOW MASTER STATUS\G;
File: mysql-bin.000001
Position: 981
Binlog_Do_DB:
Binlog_Ignore_DB:
Значение position должно увеличиваться по мере того, как вносятся изменения в БД на мастере.
Укажем ID сервера, имя БД для репликации и путь к relay-бинлогам в секции [mysqld] конфига, затем перезагрузим MySQL:
server-id = 2
log-bin = /var/lib/mysql/mysql-bin
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
replicate-do-db = redmine
Перезагружаем сервис mysql.
Блокируем базу мастера:
mysql@master> FLUSH TABLES WITH READ LOCK;
mysql@master> SET GLOBAL read_only = ON;
Смотрим состояние мастера командой show master status и запомним значения File и Position
mysql@master> SHOW MASTER STATUS\G;
File: mysql-bin.000001
Position: 981
Binlog_Do_DB:
Binlog_Ignore_DB:
Делаем дамп БД:
mysqldump -u you_user -p redmine | gzip > $PWD/redmine.sql.gz
и после завершения операции снимаем блокировку мастера
mysql@master> UNLOCK TABLES;
mysql@master> SET GLOBAL read_only = OFF;
Переносим дамп на реплику и восстанавливаем из него данные.
gunzip < docker-entrypoint-initdb.d/redmine.sql.gz | mysql -u root -p -D redmine
После чего запускаем репликацию на SLAVE
CHANGE MASTER TO MASTER_HOST='192.168.1.101', MASTER_PORT=3307, MASTER_USER='replication', MASTER_PASSWORD='you_pass', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS = 981;
start slave;
Проверка как идет репликация SHOW SLAVE STATUS\G:
mysql@replica> SHOW SLAVE STATUS\G
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.101
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 981
Relay_Log_File: mysql-relay-bin.00052
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: testdb,testdb
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 98
Relay_Log_Space: 235
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 5
Переключить реплику в режим мастера бывает необходимо, например, в случае отказа мастера или при проведении на нем технических работ. Для возможности такого переключения необходимо настроить реплику подобно мастеру, или сделать её пассивным мастером.
Включим ведение бинарных логов на SLAVE (дополнительно к relay-бинлогам) в конфиге в секции [mysqld]:
log-bin = /var/lib/mysql/mysql-bin
И добавим пользователя на SLAVE для ведения репликации:
mysql> CREATE USER 'replication'@'%';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
Пассивный мастер ведет репликацию как и обычная реплика, но кроме этого создает бинарные логии — то есть, мы можем начать репликацию с него. Убедимся в этом командой show master status:
mysql@replica> SHOW MASTER STATUS\G
File: mysql-bin.000001
Position: 61
Binlog_Do_DB:
Binlog_Ignore_DB:
Теперь, чтобы перевести пассивный мастер в активный режим, необходимо остановить репликацию на нем и включить репликацию на бывшем активном мастере. Чтобы в момент переключения данные не были утеряны, активный мастер необходимо заблокировать на запись.
mysql@master> FLUSH TABLES WITH READ LOCK
mysql@master> SET GLOBAL read_only = ON;
mysql@replica> STOP SLAVE;
mysql@replica> SHOW MASTER STATUS;
File: mysql-bin.000001
Position: 61
mysql@master> CHANGE MASTER TO MASTER_HOST='192.168.1.102', MASTER_PORT=3306, MASTER_USER='replication', MASTER_PASSWORD='you_pass', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS = 61;
mysql@master> start slave;
Теперь можно снять блокировку с бывшего мастера.
SET GLOBAL read_only = OFF;
Иногда после добавления процедур SLAVE вываливается в ошибку чтобы это исправить
On master database:
mysql> reset master;
On slave database:
mysql> stop slave;
mysql> reset slave;
mysql> reset master;
mysql> start slave;
Бэкап только процедур и функций mysql:
mysqldump -u root -h localhost -p --routines --no-create-info --no-data --no-create-db --skip-opt redmine > docker-entrypoint-initdb.d/procedure.sql
SHOW PROCESSLIST;
SHOW ENGINE INNODB STATUS\G;