Настройка репликации с помощью mysql admin. Что такое репликация в MySQL? Настройка репликации MySQL без остановки мастера

Настройка репликации MySQL без остановки мастера.

1. Настройка Master сервера:

Смотрим где должен лежать конфиг.

# ps aux | grep my.cnf

mysql 51189 0.0 0.0 17064 1912 — Is 6:35PM 0:00.05 /bin/sh /usr/local/bin/mysqld_safe —defaults-extra-file=/var/db/mysql/my.cnf —user=mysql —datadir=/var/db/mysql

Если файл отсутствует его можно скопировать из примера.

# cp /usr/local/share/mysql/my-small.cnf /var/db/mysql/my.cnf

Или создать пустой.

# touch /var/db/mysql/my.cnf

В созданный конфиг в секции пишем.

#Уникальный ID сервера. У мастера должен быть ниже реплики и не дублироваться

server — id = 1

#Формат лога

binlog — format = mixed

#Путь где будет лежать бинлог (По умолчанию размер одного лога 1г)

#Время хранения бинлогов

expire_logs_days = 30

replicate-do-db = database_1

replicate-do-db = database_2

replicate-do-db = database_3

replicate-do-db = database_4

#Лог ошибок

На этом закругляемся с редактированием и рестартим MySQL с новым конфигом.

# /usr/local/etc/rc.d/mysql-server restart

Теперь надо добавить пользователя на Master для Slave сервера.

Для репликации достаточно будет прав REPLICATION SLAVE. Заходим под root на cервер MySQL.

# mysql -uroot -p

Создаем пользователя:

mysql> use mysql;

mysql>CREATE USER ‘replica’@’ip_address_slave_server’ ;

mysql>GRANT REPLICATION SLAVE ON *.* TO ‘replica’@’ip_address_slave_server’ IDENTIFIED BY ‘password_for_user_replica’ ;

Теперь можно или перегрузить сервер или сказать

mysql>FLUSH PRIVILEGES;

2. Создаем дамп нужных баз:

Все базы.

# mysqldump -uroot -p —skip-lock-tables —single-transaction —flush-logs —hex-blob —master-data=2 -A > /usr/home/Timur/dump.sql

Определенные базы.

# mysqldump -uroot -p —skip-lock-tables —single-transaction —flush-logs —hex-blob —master-data=2 -B DATABASE DATABASE1 DATABASE2 DATABASE3 > /usr/home/Timur/dump.sql

3. Смотрим какой бинлог использовать и его позицию:

# head -n80 /usr/home/Timur/dump.sql | grep «MASTER_LOG_POS»

— CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000049 ‘, MASTER_LOG_POS=107 ;

Желательно записать!!!

4. Жмем дамп и переносим на Slave сервер:

# gzip /usr/home/Timur/dump.sql

Переносим.

# scp /usr/home/Timur/dump.sql.gz _address_slave_server:/usr/home/Timur

5. Настройка Slave сервера (my.cnf).

server — id =2

binlog — format = mixed

log-bin=/var/log/mysql/mysql-bin

expire_logs_days = 30

#Бинлоги Slave

relay-log = /var/log/mysql/mysql-relay.log
relay-log-index = /var/log/mysql/mysql-relay-bin.index

#Указывает подчиненному серверу, чтобы тот вел записи об обновлениях, происходящих на подчиненном сервере, в двоичном журнале. По умолчанию эта опция выключена. Ее следует включить, если требуется организовать подчиненные серверы в гирляндную цепь.

log-slave-updates = 1

#Ставим базы только на чтение. На суперпользователей данная опция не распространяется!!!

read-only = 1

#Пропустить дублирующие записи. После того как Seconds_Behind_Master станет 0, закоментировать и ребутнуть SLAVE

slave-skip-errors=all

#Указываем какие базы нам нужно реплицировать

replicate-do-db = database_1

replicate-do-db = database_2

replicate-do-db = database_3

replicate-do-db = database_4

#Лог ошибок

log-error=/var/log/mysql/mysqld-error.log

#Для того чтобы при запуске сервера не стартовал Slave. Запустить можно в ручную START SLAVE;

skip-slave-start = On

Перезагружаем сервер (MySQL).

6. Заливаем дамп на Slave и стартуем репликацию:

Разахивируем.

# gunzip /usr/local/Timur/dump.sql.gz

Заливаем дамп.

# mysql -uroot -p < /usr/local/Timur/dump.sql

Говорим Slave откуда тащить данные и стартуем. MASTER_LOG_FILE и MASTER_LOG_POS берем то, что записали при дампе баз на Master 😉

mysql>CHANGE MASTER TO MASTER_HOST = ‘<>’ , MASTER_USER = ‘replica’ , MASTER_PASSWORD = ‘password_for_user_replica’ , MASTER_LOG_FILE = ‘mysql-bin.000049 ‘ , MASTER_LOG_POS =107 ; START SLAVE ;

Смотрим командой SHOW SLAVE STATUS\G все ли у нас стартануло.

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: Тут адрес Master сервера
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000049
Read_Master_Log_Pos: 1919771
Relay_Log_File: mysql-relay.000050
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000049
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: database_1,database_2,database_3,database_4,database_1,database_2,database_3,database_4
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: 1919771
Relay_Log_Space: 3125
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 5
1 row in set (0.00 sec)

Все завелось.

Должен рости Exec_Master_Log_Pos: 1919771

Если появилась ошибка, то можно ее пропустить выполнив:

mysql> STOP SLAVE;SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;START SLAVE;

Репликация данных Mysql позволяет иметь точную копию базы данных с одного сервера – мастер сервера (ведущий сервер) на одном или нескольких других серверах (ведомый сервер). По умолчанию репликация Mysql является асинхронной.
Что обозначает, что мастер сервер никак не контролирует и не знает читает ли лог файл ведомые сервера и делают ли они это верно.
Есть также другие типы синхронизации синхронный и полусинхронный, где эти процессы контролируются.
В зависимости от настроек реплицировать можно как все базы данных целиком, так и отдельные таблицы баз данных.

Для чего можно использовать репликацию:
1. Распределение нагрузки между хостами для повышения производительности.
В такой схеме главный узел будет выполнять операции чтения и записи, узлы имеющие подписку на главном узле будут предоставлять базу для чтения, таким образом, мы разгрузим мастер сервер от операций чтения
2. Безопасность данных и удобство обслуживания, поскольку подчиненный узел содержит данные только для чтения, то изменение данных на подписчике будет ограничено, удобство обслуживания – возможность запускать процессы обслуживающие базу не прерывая работу приложений
3. Распределение данных на большие расстояния. Можно создать копию данных на любом хосте в независимости от его местоположения
Mysql поддерживает следующие методы репликации:
Традиционный - метод основан на тиражировании событий из бинарного файла лога мастера и требует файлы логов. Позиции между ведущим и ведомым серверами должны быть синхронизированы.
Метод с использованием глобальных идентификаторов транзакций GTIDs (транзакционный метод)
Mysql поддерживает следующие типы синхронизации:
асинхронную (односторонняя синхронизация)
полусинхронную (частичный контроль подписчиков)
синхронную (полный контроль подписчиков)

Настройка репликации баз данных Mysql традиционный метод

Принцип работы
Мастер сервер содержит bin файлы логов, в которые записываются все изменения, происходящие в базе данных мастера, файл описывающий имена bin файлов, а также позицию в журнале где были записаны последние данные мастера
Подчиненный узел получает данные, с мастера имея информацию об именах bin файлов и позиции в файле лога.

Настройка Мастера
my.ini должен содержать уникальный идентификатор – число от 1 до 2 в 32 степени – 1, server-id.
По умолчанию server-id=0, что означает не принимать подписки от подчиненных серверов

log-bin=mysql-bin
server-id=1
Этих двух строк достаточно для запуска
Примечание: однако если используется InnoDB, то дополнительно рекомендуется внести
innodb_flush_log_at_trx_commit=1
sync_binlog=1
И нужно проверить, что не отключена возможность работать с сетью не выставлен параметр skip-networking
Ведомый сервер подключается к главному, используя имя пользователя и пароль, поэтому на мастер сервере предварительно создаем пользователя
CREATE USER repl@%.mydomain.com IDENTIFIED BY slavepass;
GRANT REPLICATION SLAVE ON *.* TO repl@%.mydomain.com;
Смотрим состояние
SHOW MASTER STATUS
Если ранее уже была запущена процедура создания бинарных журналов, то для таблиц InnoDB, предварительно в одном из сеансов нужно залочить таблицы
FLUSH TABLES WITH READ LOCK;
Если выйти из сеанса, то блокировка таблиц автоматически снимается
В другом сеансе получаем значения имени bin лога и позицию
Оба значения представляют собой координаты репликации при которых ведомый сервер должен начать чтение из файла в нужном месте, чтобы начать репликацию.
Следующий шаг зависит от того есть ли данные на ведомом сервере, данные от мастера
Если они есть, то оставляем таблицы залоченными, создаем dump (это рекомендуемый способ при использовании InnoDB)
Узнать тип базы можно командой
mysqlshow -u mysql_user -p -i database-name
Если база хранится в бинарных файлах, то допускается их копирование с ведущего на ведомый сервер
Делаем dump
mysqldump --all-databases --master-data dbdump.db
для выбора баз mysqldump --databases --master-data dbdump.db
Параметр master-data, автоматически добавляет CHANGE MASTER TO на подчиненном узле, если параметр не добавлять, то необходимо блокировать все таблицы в сессии в ручную
Снять блокировку
UNLOCK TABLES;

Настройка ведомого узл а
Добавляем в my.ini server-id от личный от мастера и от других узлов

server-id=2
Создаем подписку
CHANGE MASTER TO
MASTER_HOST=master_host_name,
MASTER_USER=replication_user_name,
MASTER_PASSWORD=replication_password,
MASTER_LOG_FILE=recorded_log_file_name,
MASTER_LOG_POS=recorded_log_position;
При настройке репликации с существующими данными нужно передать снимок от ведущего к ведомому перед началом репликации
Используем mysqldump
1.Запускаем подчиненный узел используя --skip-slave-start параметр, чтобы репликация не запускалась
2.Импортируем файл дампа
mysql fulldb.dump
3. Запускаем процесс подписки
START SLAVE;
Проверка состояния репликации
SHOW SLAVE STATUS\G
Slave_IO_State: - текущее состояние ведомого устройства
Slave_IO_Running: - читается ли поток данных с мастера
Slave_SQL_Running: - работают ли sql запросы, должно быть yes

Пример Настроим Мастер (ведущий) сервер – ip 11.11.11.10 В my.ini
[ mysqld] log-bin=mysql-bin server-id=1
Создаем пользователя mysql -u root -p GRANT REPLICATION SLAVE ON *.* TO replica@% IDENTIFIED BY password; FLUSH PRIVILEGES;
Далее блокируем все таблицы в базе данных FLUSH TABLES WITH READ LOCK;
Смотрим статус SHOW MASTER STATUS; Запоминаем имя файла и позицию, их будем мы будем использовать на Ведомом сервере для подписки

На Слейве В my.ini
log-bin=mysql-bin server-id=2
Создаем подписку CHANGE MASTER TO MASTER_HOST=11.11.11.10, MASTER_PORT=3306,
MASTER_USER=replica, MASTER_PASSWORD=password,
MASTER_LOG_FILE=server-mysql-bin.000002,
MASTER_LOG_POS=1151664, MASTER_CONNECT_RETRY=10;
START SLAVE;
Статус репликации SHOW SLAVE STATUS\G

В наши дни база данных MySQL используется уже практически везде, где только можно. Невозможно представить сайта, который бы работал без MySQL. Конечно, есть некоторые исключения, но основную часть рынка занимает именно эта система баз данных. И самая популярная из реализаций - MariaDB. Когда проект небольшой, для его работы достаточно одного сервера, на котором расположены все службы: веб-сервер, сервер баз данных и почтовый сервер. Но когда проект становится более большим может понадобится выделить для каждой службы отдельный сервер или даже разделить одну службу на несколько серверов, например, MySQL.

Для того чтобы поддерживать синхронное состояние баз данных на всех серверах одновременно нужно использовать репликацию. В этой статье мы рассмотрим как настраивается репликация MySQL с помощью MariaDB Galera Cluster.

ЧТО ТАКОЕ MARIADB GALERA?

MariaDB Galera - это кластерная система для MariaDB типа master-master. Начиная с MariaDB 10.1 программное обеспечение Galera Server и MariaDB Server поставляются в одном пакете, так что вы получаете все необходимое программное обеспечение сразу. На данный момент MariaDB Galera может работать только с движками баз данных InnoDB и XtraDB. Из преимуществ использования репликации можно отметить добавление избыточности для базы данных сайта. Если одна из баз данных, даст сбой, то вы сразу же сможете переключиться на другой. Все сервера поддерживают синхронизированное состояние между собой и гарантируют отсутствие потерянных транзакций.

Основные возможности MariaDB Galera:

Репликация с постоянной синхронизацией;
Автоматическое объединение узлов;
Возможность подключения нескольких узлов master;
Поддержка записи на любой из узлов;
Прозрачная параллельная репликация;
Масштабируемость чтения и записи, минимальные задержки;
Давшие сбой ноды автоматически отключаются от кластера;
Нельзя блокировать доступ к таблицам.

НАСТРОЙКА РЕПЛИКАЦИИ MYSQL

В этой инструкции мы будем использовать для примера Ubuntu 16.04 и MariaDB версии 10.1. Перед тем, как начать полностью обновите систему:

sudo apt-get update -y
sudo apt-get upgrade -y

Поскольку мы будем развертывать нашу конфигурацию на нескольких узлах, нужно выполнить операции обновления на всех них. Если сервер баз данных MariaDB еще не установлен, его нужно установить. Сначала добавьте репозиторий и его ключ:

sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8

sudo add-apt-repository "deb http://ftp.utexas.edu/mariadb/repo/10.1/ubuntu xenial main"

sudo apt-get update -y

Когда обновление списка пакетов завершено, установите MariaDB командой:

sudo apt install mariadb-server rsync -y

Пакет rsync нам понадобится для выполнения непосредственно синхронизации. Когда установка будет завершена, вам необходимо защитить базу данных с помощью скрипта mysql_secure_installation:

sudo mysql_secure_installation

По умолчанию разрешен гостевой вход, есть тестовая база данных, а для пользователя root не задан пароль. Все это надо исправить. Читайте подробнее в статье . Если кратко, то вам нужно будет ответить на несколько вопросов:

Enter current password for root (enter for none):
Change the root password? n
Remove anonymous users? Y
Disallow root login remotely? Y
Remove test database and access to it? Y
Reload privilege tables now? Y

Когда все будет готово, можно переходить к настройке нод, между которыми будет выполняться репликация баз данных mysql. Сначала рассмотрим настройку первой ноды. Можно поместить все настройки в my.cnf, но лучше будет создать отдельный файл для этих целей в папке /etc/mysql/conf.d/.

Добавьте такие строки:

binlog_format=ROW

innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

wsrep_on=ON

wsrep_sst_method=rsync
# Galera Node Configuration
wsrep_node_address="192.168.56.101"
wsrep_node_name="Node1"

Здесь адрес 192.168.56.101 - это адрес текущей ноды. Дальше перейдите на другой сервер и создайте там такой же файл:

sudo vi /etc/mysql/conf.d/galera.cnf

binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://192.168.56.101,192.168.56.102"
# Galera Synchronization Configuration
wsrep_sst_method=rsync
# Galera Node Configuration
wsrep_node_address="192.168.56.102"
wsrep_node_name="Node2"

Аналогично тут адрес ноды - 192.168.0.103. Остановимся на примере с двумя серверами, так как этого достаточно чтобы продемонстрировать работу системы, а добавить еще один сервер вы можете, прописав дополнительный IP адрес в поле wsrep_cluster_address. Теперь рассмотрим что означают значения основных параметров и перейдем к запуску:

binlog_format - формат лога, в котором будут сохраняться запросы, значение row сообщает, что там будут храниться двоичные данные;
default-storage-engine - движок SQL таблиц, который мы будем использовать;
innodb_autoinc_lock_mode - режим работы генератора значений AUTO_INCREMENT;
bind-address - ip адрес, на котором программа будет слушать соединения, в нашем случае все ip адреса;
wsrep_on - включает репликацию;
wsrep_provider - библиотека, с помощью которой будет выполняться репликация;
wsrep_cluster_name - имя кластера, должно соответствовать на всех нодах;
wsrep_cluster_address - список адресов серверов, между которыми будет выполняться репликация баз данных mysql, через запятую;
wsrep_sst_method - транспорт, который будет использоваться для передачи данных;
wsrep_node_address - ip адрес текущей ноды;
wsrep_node_name - имя текущей ноды.

Настройка репликации MySQL почти завершена. Остался последний штрих перед запуском - это настройка брандмауэра. Сначала включите инструмент управления правилами iptables в Ubuntu - UFW:

Затем откройте такие порты:

sudo ufw allow 3306/tcp
sudo ufw allow 4444/tcp
sudo ufw allow 4567/tcp
sudo ufw allow 4568/tcp
sudo ufw allow 4567/udp

ЗАПУСК MARIADB GALERA

После успешной настройки всех нод нам останется только запустить кластер Galera на первой ноде. Перед тем как мы сможем запустить кластер, вам нужно убедиться, что сервис MariaDB остановлен на всех серверах:

sudo galera_new_cluster

Проверить запущен ли кластер и сколько к нему подключено машин можно командой:

Сейчас там только одна машина, теперь перейдите на другой сервер и запустите ноду там:

sudo systemctl start mysql

Вы можете проверить прошел ли запуск успешно и были ли какие-либо ошибки командой:

sudo systemctl status mysql

Затем, выполнив ту же команду, вы убедитесь, что новая нода была автоматически добавлена к кластеру:

mysql -u root -p -e "show status like "wsrep_cluster_size""

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

mysql -u root -p

MariaDB [(none)]> create database test_db;
MariaDB [(none)]> show databases;

mysql -u root -p

MariaDB [(none)]> show databases;

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

Здесь кратко описано как настроить полную репликацию вашего MySQL-сервера. Предполагается, что реплицироваться будут все базы данных и репликация ранее не настраивалась. Для того чтобы выполнить указанные здесь действия, вам придется на короткое время остановить головной сервер.

Это самый простой способ установки подчиненного сервера, однако он не единственный. Например, если уже имеется образ головного сервера, на головном сервере уже установлен ID сервера и производятся записи в журнал, подчиненный сервер можно установить, не останавливая головной сервер и даже не устанавливая блокировки обновлений (за дополнительной информацией обращайтесь к разделу See section 4.10.7 Часто задаваемые вопросы по репликации .

Чтобы стать настоящим гуру по репликации в MySQL, советуем сначала изучить, осмыслить и опробовать все команды, упомянутые в разделе See section 4.10.6 SQL-команды, относящиеся к репликации . Необходимо также ознакомиться с опциями запуска репликации из файла `my.cnf" в разделе See section 4.10.5 Опции репликации в файле `my.cnf" .

Удостоверьтесь, что на головном и подчиненном(ых) серверах установлена свежая версия MySQL. Используйте версию 3.23.29 или выше. В предыдущих релизах применялся другой формат двоичного журнала и содержались ошибки, которые были исправлены в более новых релизах. Большая просьба: пожалуйста, не посылайте сообщения об ошибках, не проверив, присутствует ли эта ошибка в последнем релизе.
Установите на головном сервере отдельного пользователя для репликации с привилегией FILE (в версиях MySQL ниже 4.0.2) или REPLICATION SLAVE в более новых версиях MySQL. У этого пользователя должно быть также разрешение подсоединяться со всех подчиненных серверов. Если пользователь будет выполнять только репликацию (рекомендуется), то ему не нужно предоставлять какие-либо дополнительные привилегии. Например, чтобы создать пользователя с именем repl , который может иметь доступ к головному серверу с любого хоста, можно использовать такую команду: mysql> GRANT FILE ON *.* TO repl@"%" IDENTIFIED BY " ";
Завершите работу MySQL на головном сервере. mysqladmin -u root -p shutdown
Создайте образ всех данных на головном сервере. Легче всего сделать это (на Unix), создав при помощи tar архив всей своей директории данных. Точное местоположение директории данных зависит от вашей инсталляции. tar -cvf /tmp/mysql-snapshot.tar /path/to/data-dir Пользователи Windows для создания архива каталога данных могут использовать WinZIP или другую подобную программу.
В my.cnf на головном сервере добавьте записи к разделу записи log-bin и server-id=уникальный номер к разделу и перезапустите сервер. Очень важно, чтобы ID подчиненного сервера отличался от ID головного сервера. Можно считать, что server-id играет роль IP-адреса - он уникально идентифицирует сервер среди участников репликации. log-bin server-id=1
Перезапустите MySQL на головном сервере.
Добавьте в my.cnf на подчиненном сервере(ах) следующий фрагмент: master-host= master-user= master-password= master-port= server-id= заменяя значения в значениями, соответствующими вашей системе. Значения server-id должны быть различными на каждом сервере, участвующем в репликации. Если значение server-id не определено, оно будет установлено в 1, если также не определено значение master-host , оно будет установлено в 2. Обратите внимание, что если значение server-id опущено, то головной сервер будет отказывать в соединении всем подчиненным серверам, а подчиненный сервер - отказывать в соединении головному серверу. Таким образом, опускать установку значения server-id можно лишь в случае резервного копирования с использованием двоичного журнала.
Скопируйте данные снимка в директорию данных на подчиненном сервере (ах). Удостоверьтесь в правильности привилегий для файлов и каталогов. Пользователь, от имени которого запускается MySQL, должен иметь возможность читать и записывать данные в них так же, как и на головном сервере.
Перезапустите подчиненный(ые) сервер(ы).

После выполнения указанных действий подчиненный(ые) сервер(ы) должен(ы) подсоединиться к головному серверу и подгонять свои данные под любые изменения, произошедшие на головном сервере после принятия образа.

Если не установлен идентификатор server -id для подчиненного сервера, в журнальный файл регистрации ошибок будет внесена следующая ошибка:

Warning: one should set server_id to a non-0 value if master_host is set. The server will not act as a slave. (Предупреждение: если задан master_host, следует установить server_id в ненулевое значение. Сервер не будет работать как подчиненный сервер.)

Если не установлен идентификатор головного сервера, подчиненные серверы не смогут подключиться к головному серверу.

Если подчиненный сервер по какой-либо причине не может выполнять репликацию, соответствующие сообщения об ошибках можно найти в журнале регистрации ошибок на подчиненном сервере.

После того как подчиненный сервер начнет выполнять репликацию, в той же директории, где находится журнал регистрации ошибок, появится файл `master.info" . Файл `master.info" используется подчиненным сервером для отслеживания того, какие записи двоичных журналов головного сервера обработаны. Не удаляйте и не редактируйте этот файл, если не уверены в том, что это необходимо. Даже если такая уверенность есть, все равно лучше использовать команду CHANGE MASTER TO .

Репликация — прием, применяемый в архитектуре систем работающих под нагрузкой, результатом которого является распределение нагрузки при работе с одной базой данных на несколько серверов. MySQL MASTER SLAVE репликация используется чаще, но применяется и второй тип репликации — Master-Master.

Что такое MySQL MASTER SLAVE репликация и для чего она применяется

Репликация Master-Slave предполагает дублирование данных на подчиненный сервер MySQL, производится подобное дублирование большей частью с целью обеспечения надежности. В случае выхода из строя Master сервера его функции переключаются на Slave.

Репликация может осуществляться и с целью повышения производительности системы, однако производительность здесь практически всегда вторична.
При работе приложения с БД самыми частыми операциями являются операции SELECT — запросы на считывание данных, модификация данных — запросы DELETE , INSERT , UPDATE , ALTER статистически происходит гораздо реже.

Чтобы в случае выхода из строя одного из серверов не произошло потери данных операции на изменение информации в таблицах всегда обрабатываются Master-сервером. Затем изменения реплицируются на Slave. Считывание же можно производить с сервера играющего роль Slave.
За счет этого можно получить выигрыш в производительности вместе с надежностью.

Решение популярно, но не всегда применимо поскольку при репликации могут наблюдаться задержки — если такое случается считывать информацию также приходится с Master-сервера.

Направление запросов определенного типа к тому или иному серверу баз данных в любом случае реализуется на уровне приложения.

Если выполнять разделение SELECT запросов и всех остальных на программном уровне отправляя их на нужный сервер при выходе из строя одного из них приложение, которое обслуживает инфраструктура окажется неработоспособно. Чтобы это работало нужно предусматривать более сложную схему и резервировать каждый из серверов.

Репликация служит для отказоустойчивости, не для масштабирования.

MySQL MASTER SLAVE репликация — настройка на Debian

Будем использовать два сервера с адресами:

Master сервер 192.168.0.1
Slave сервер 192.168.0.2

Для демонстрации используются VDS объединенные в локальную сеть.
Чтобы всегда наверняка знать на каком сервере мы выполняем ту или иную команду отредактируем файлы /etc/hosts на обоих серверах

192.168.0.1 master

192.168.0.2 slave

Заменим существующие значения в /etc/hostname на master и slave соответственно, чтобы изменения вступили в силу сервера перезагрузим.

1. Производим настройки на мастер сервере.

root@master:/#

Редактируем основной конфигурационный файл сервера баз данных

mcedit /etc/mysql/my.cnf

Выбираем ID сервера — число можно указать любое, по умолчанию стоит 1 — строку достаточно раскомментировать

server-id = 1

Задаем путь к бинарному логу — также указано по умолчанию, раскомментируем

Задаем название базы данных, которую будем реплицировать на другой сервер

binlog_do_db = db1

Перезапускаем Mysql чтобы конфигурационный файл перечитался и изменения вступили в силу:

/etc/init.d/mysql restart

2. Задаем пользователю необходимые права

Заходим в консоль сервера баз данных:

Даем пользователю на подчиненном сервере необходимые права:

GRANT REPLICATION SLAVE ON *.* TO "slave_user"@"%" IDENTIFIED BY "123";

Блокируем все таблицы в БД

FLUSH TABLES WITH READ LOCK;

Проверяем статус Master-сервера:

3. Создаем дамп базы данных на сервере

Создаем дамп базы данных:

mysqldump -u root -p db1 > db1.sql

Разблокируем таблицы в консоли mysql:

4. Переносим дамп базы на Slave-сервер

scp db1.sql [email protected]:/home

Дальнейшие действия производим на Slave-сервере

root@slave:/#

5. Созданием базу данных

Загружаем дамп:

mysql -u root -p db1 < db1.sql

6. Вносим изменения в my.cnf

mcedit /etc/mysql/my.cnf

Назначаем ID инкрементируя значение установленное на Мастер сервере

server-id = 2

Задаем путь к relay логу

relay-log = /var/log/mysql/mysql-relay-bin.log

и путь bin логу на Мастер сервере

log_bin = /var/log/mysql/mysql-bin.log

Указываем базу

binlog_do_db = db1

Перезапускаем сервис

/etc/init.d/mysql restart

7. Задаем подключение к Master серверу

CHANGE MASTER TO MASTER_HOST="192.168.0.1", MASTER_USER="slave_user", MASTER_PASSWORD="123", MASTER_LOG_FILE = "mysql-bin.000001", MASTER_LOG_POS = 327;

Запускаем репликацию на подчиненном сервере:

Проверить работу репликации на Слейве можно запросом:

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.1
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 107
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 107
Relay_Log_Space: 555
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)

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

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

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