Реплицкация в MySQL

В MySQL есть разные методы репликации, наиболее популярны master binary log based replication и GTIDs-based replication.
По умолчанию, репликация работает в асинхронном режиме, это значит, что master не знает скопированы ли и выполнены ли все изменения на slave. Есть возможность включить синхронную репликацию, однако это может повлиять на производительность mysql. Есть также отложенная репликация, когда slave копирует данные с задержкой.
Есть три формата репликации:
Statement Based Replication (SBR) - реплицируются целые SQL запросы
Row Based Replication (RBR) - реплицируются только измененные строки
Mixed Based Replication (MBR) - смешанный тип

Этот тип репликации основан на binary log, который пишет все events (изменения) на master сервере. Затем slave читает этот лог и выполняет events в своей базе.

НАСТРОЙКИ MASTER СЕРВЕРА
1. Создаем аккаунт с правами REPLICATION SLAVE

CREATE USER 'REPLICATION_USER'@'%' IDENTIFIED BY 'PASS';
GRANT REPLICATION SLAVE ON *.* TO 'USER'@'%';

2. Вносим нужные правки в my.cnf на master и перезапускаем mysql

[mysqld]
server-id=1 				#уникальный идентификатор сервера
log-bin=mysql-bin.log 			#лог, куда пишутся все изменения
innodb_flush_log_at_trx_commit=1 	#актуально для InnoDB таблиц, значение 1 указывает, что запись в лог происходит после каждой успешной транзакции
sync_binlog=1				#определяет логику синхронизации данных из бинлога с диском. Если значение равно 1, запись на диск будет происходить после каждой транзакции
#skip-networking			#должна быть заккоментирована, опция запрещает подклюение по сети к mysql
binlog_do_db=DATABASE_FOR_REPLICA	#определяет базу, для которой будет делаться репликация, по умолчанию для всех баз делается

НАСТРОЙКИ SLAVE СЕРВЕРА
1. Вносим нужные правки в my.cnf на master и перезапускаем mysql

[mysqld]
server-id=2 				#уникальный идентификатор сервера
log-bin=mysql-bin 			#лог, куда пишутся все изменения
relay-log=mysql-relay-bin
innodb_flush_log_at_trx_commit=1 	#актуально для InnoDB таблиц, значение 1 указывает, что запись в лог происходит после каждой успешной транзакции
sync_binlog=1				#определяет логику синхронизации данных из бинлога с диском. Если значение равно 1, запись на диск будет происходить после каждой транзакции
#skip-networking			#должна быть заккоментирована, опция запрещает подклюение по сети к mysql
binlog_do_db=DATABASE_FOR_REPLICA	#определяет базу, для которой будет делаться репликация, по умолчанию для всех баз делается

2. Создаем базу данных, которая будет реплицироватся.

CREATE DATABASE DATABASE_FOR_REPLICA;

ПЕРЕНОС ДАННЫХ И ЗАПУСК РЕПЛИКАЦИИ
1. На master делаем дамп нужной базы для последующего импорта на slave методом mysqldump

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;
mysql> SHOW MASTER STATUS; #здесь нужно запомнить значение File и Position
mysqldump --socket=/tmp/mysql.sock -v DATABASE_FOR_REPLICA | ssh -p 222 IP "cat > /home/TRASH/DATABASE_FOR_REPLICA.sql" #дампим сразу на slave
mysql> SET GLOBAL read_only = OFF;

2. На slave импортируем sql и запускаем репликаицию

mysql --socket=/tmp/mysql.sock -v DATABASE_FOR_REPLICA < /home/TRASH/DATABASE_FOR_REPLICA.sql
mysql> CHANGE MASTER TO MASTER_HOST = "IP", MASTER_USER = "USER_FOR_REPLICA", MASTER_PASSWORD = "PASS", MASTER_LOG_FILE = "mysql-bin.xxxxx", MASTER_LOG_POS = "XX";
mysql> START SLAVE;

МОНИТОРИНГ РЕПЛИКАЦИИ
На slave сервере выполняем

mysql> SHOW SLAVE STATUS;

Основные показатели Slave_IO_State, Seconds_Behind_Master.

ДОБАВЛЕНИЕ ЕЩЕ ОДНОЙ РЕПЛИКИ
1. настраиваем mysql на реплике по примеру предыдущей реплики, только указываем другое значение для server-id 2. останаливаем репликацию на текущей реплике и запоминаем позицию репликации на которой она остановилась, дампим базу

mysql> STOP SLAVE;
mysql> SHOW SLAVE STATUS\G;		#запомнить значения Master_Log_File и Exec_Master_Log_Pos
mysqldump --socket=/tmp/mysql.sock -v DATABASE_FOR_REPLICA | ssh -p 222 IP "cat > /home/TRASH/DATABASE_FOR_REPLICA.sql"
mysql> START SLAVE;

3. восстанавливаем sql дамп на новой реплике, прописываем настройки подклчения к мастеру и запускаем реплику

mysql --socket=/tmp/mysql.sock -v < /home/TRASH/DATABASE_FOR_REPLICA.sql
mysql> CHANGE MASTER TO MASTER_HOST = "IP", MASTER_USER = "USER_FOR_REPLICA", MASTER_PASSWORD = "PASS", MASTER_LOG_FILE = "mysql-bin.xxxxx", MASTER_LOG_POS = "XX";		     # MASTER_LOG_FILE = Master_Log_File и MASTER_LOG_POS = Exec_Master_Log_Pos
mysql> START SLAVE;

ПЕРЕКЛЮЧЕНИЕ SLAVE В РЕЖИМ MASTER
По сути, для восстановления работы сервера можно в приложении переключить коннект к slave серверу. Что бы сделать slave активным master, а master после восстановления работы slave-ом, нужно:
1. проверить, что бы в my.cnf на slave были включены log-bin=mysql-bin.log и был создан пользователя с грантами REPLICATION SLAVE
2. на текущем master блокируем запись «FLUSH TABLES WITH READ LOCK» и «SET GLOBAL read_only = ON;»
3. на текущем slave останавливаем репликацию «STOP SLAVE» и смотрим «SHOW MASTER STATUS», запоминаем значения File и Position
4. на текущем master указываем настройки подключения к новому master и запускаем репликацию

mysql> CHANGE MASTER TO MASTER_HOST = "IP", MASTER_USER = "USER_FOR_REPLICA", MASTER_PASSWORD = "PASS", MASTER_LOG_FILE = "mysql-bin.xxxxx", MASTER_LOG_POS = xx;			# MASTER_LOG_FILE = File c текущего slave, MASTER_LOG_POS = Position c текущего slave
mysql> START SLAVE;
mysql> SET GLOBAL read_only = OFF;