На нерабочей ноде
Создаем дамп баз:
# mysqldump -uroot -p -v —databases db1 db2 > /tmp/mydb_dump_slave.sql
Заходим в оболочку управления MySQL:
mysql> stop slave;
Удаляем старые базы:
mysql> drop database db1;
mysql> drop database db2;
* в данном примере удаляются базы, для которых мы сделали резервные копии.
И создаем их заново:
mysql> CREATE DATABASE db1 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
mysql> CREATE DATABASE db2 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
Выходим из оболочки:
Теперь восстанавливаем базы из ранее созданного дампа:
# mysql -v -uroot -p change master to master_host = «192.168.166.155», master_user = «replmy», master_password = «password», master_log_file = «mysql-bin.000015», master_log_pos = 6315;
* 192.168.166.155: IP-адрес моего первого сервера. replmy: учетная запись для репликации, которая была создана при создании кластера. password: пароль для учетной записи replmy. mysql-bin.000015: имя файла, которое мы должны были записать или запомнить (у вас может быть другим). 6315: номер позиции, с которой необходимо начать репликацию (также должны были записать или запомнить ранее).
Запускаем репликацию следующей командой:
mysql> start slave;
И проверяем состояние репликации:
mysql> SHOW SLAVE STATUS\G
Состояние Slave_IO_Running и Slave_SQL_Running должно быть Yes, а ошибки должны исчезнуть:
Источник
Шаг 6. Запуск Слейва
Нам осталось включить репликацию, для этого необходимо указать параметры подключения к мастеру. В консоли mysql на Слейве необходимо выполнить запрос:
Для запуска slave-сервера необходимо:
- указать параметры соединения (master-data).
- запустить репликацию.
Если дамп базы делали с параметром —master-data, то первый пункт можно пропустить — информация будет указана при восстановлении дампа. В противном случае выполняем:
CHANGE MASTER TO MASTER_HOST='10.1.0.11', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 107;
Запуск репликации выполняется следующей командой:
START SLAVE;
Разное
Для mysqldump есть 2 опции для вписывания имени лога и позиции в файл дампа: —master-data и —dump-slave. Вторая есть не везде:
root@import:~# mysqldump --help | grep 'dump-slave' root@import:~# mysqldump --version mysqldump Ver 10.13 Distrib 5.1.61, for portbld-freebsd8.2 (amd64)
--dump-slave This option is similar to --master-data except that it is used to dump a replication slave server to produce a dump file that can be used to set up another server as a slave that has the same master as the dumped server. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped slave's master (rather than the coordinates of the dumped server, as is done by the --master-data option). These are the master server coordinates from which the slave should start replicating. This option was added in MySQL 5.5.3.
Соответственно, одна опция — для клонирования слейва, вторая — для создания субслейва. Иначе говоря, dump-slave позволяет в цепочке master-slave1-slave2 создать (с помощью slave1) еще один slave1 (в дамп запишется позиция в логе и файл лога относительно логов master), master-data позволяет создать slave2 — в дамп запишется позиция/лог относительно бинлогов slave1.
Создание учётных записей
Некоторые сложности в настройке репликации Master-Slave для Битрикс могут вызывать учетные записи, которые представлены в количестве трёх штук. В официальной документации битрикса вроде бы описан процесс, но может показаться недостаточно подробным и понятным, поэтому ниже будут инструкции с пояснениями по созданию необходимых учетных записей для корректной настройки репликации с моими дополнениями.
Всего необходимо три учётки:
- Основная УЗ из dbconn.php и .settings.php – для подключения и работы приложения с БД;
- УЗ для управления слейв-серверами из веб-интерфейса битрикс;
- УЗ для подключения сервера реплики к мастеру и работы непосредственно самой репликации.
Для каждой из вышеописанных УЗ необходимы отдельные права:
Для основной УЗ необходимы все права на БД, с которой работает приложение:
И REPLICATION CLIENT для получения статистики с мастера, но на все базы:
А также REPLICATION CLIENT и SUPER для управления слейвами, но уже на уровне всех БД:
Для выполнения непосредственно репликации следующие права:
- APP_SRV_IP – IP-адрес сервера приложений, где запущен веб-сервер;
- MASTER_SRV_IP – IP-адрес основного ведущего сервера MySQL;
- SLAVE_SRV_IP – IP-адрес подчиненного сервера MySQL.
Очистить привилегии:
Обзор
Особенности репликации в MySQL
Репликация (от лат. replico -повторяю) — это тиражирование изменений данных с главного сервера БД на одном или нескольких зависимых серверах. Главный сервер будем называть мастером, а зависимые — репликами.
Изменения данных, происходящие на мастере, повторяются на репликах (но не наоборот). Поэтому запросы на изменение данных (INSERT, UPDATE, DELETE и т. д.) выполняются только на мастере, а запросы на чтение данных (проще говоря, SELECT) могут выполняться как на репликах, так и на мастере. Процесс репликации на одной из реплик не влияет на работу других реплик, и практически не влияет на работу мастера.
Репликация производится при помощи бинарных логов, ведущихся на мастере. В них сохраняются все запросы, приводящие (или потенциально приводящие) к изменениям в БД (запросы сохраняются не в явном виде, поэтому если захочется их посмотреть, придется воспользоваться утилитой mysqlbinlog). Бинлоги передаются на реплики (бинлог, скачанный с мастера, называется «relay binlog «) и сохраненные запросы выполняются, начиная с определенной позиции
Важно понимать, что при репликации передаются не сами измененные данные, а только запросы, вызывающие изменения.
При репликации содержимое БД дублируется на нескольких серверах. Зачем необходимо прибегать к дублированию? Есть несколько причин:
- производительность и масштабируемость. Один сервер может не справляться с нагрузкой, вызываемой одновременными операциями чтения и записи в БД. Выгода от создания реплик будет тем больше, чем больше операций чтения приходится на одну операцию записи в вашей системе.
- отказоустойчивость. В случае отказа реплики, все запросы чтения можно безопасно перевести на мастера. Если откажет мастер, запросы записи можно перевести на реплику (после того, как мастер будет восстановлен, он может принять на себя роль реплики).
- резервирование данных. Реплику можно «тормознуть » на время, чтобы выполнить mysqldump, а мастер — нет.
- отложенные вычисления. Тяжелые и медленные SQL-запросы можно выполнять на отдельной реплике, не боясь помешать нормальной работе всей системы.
Кроме того, есть некоторые другие интересные возможности. Поскольку на реплики передаются не сами данные, а запросы, вызывающие их изменения, мы можем использовать различную структуру таблиц на мастере и репликах.
Подготовка к работе в MySQL
MySQL репликация синхронизирует базу данных, что позволяет иметь точную копию БД на другом сервере. Все обновления БД на главном сервере автоматически реплицируются на другой сервер, что позволяет защитить базу от аппаратных сбоев. В этой статье будет показано, как реализовать репликации БД exampledb с сервера server1.example.com(ip адресом 192.168.0.100) на сервер server2.example.com(ip адресом 192.168.0.101) с использованием SSL соединения.
Траблшутинг
show master status возвращает пустой вывод
Если
SHOW MASTER STATUS;
возвращает пустой результат, проверьте, включены ли бинарные логи:
SHOW BINARY LOGS;
Если на выходе получаем ошибку:
ERROR 1381 (HY000) at line 1: You are not using binary logging
то смотрим информацию ниже.
ERROR 1381 (HY000) at line 1: You are not using binary logging
Ошибка возвращается при запросе статистики по бинарным логам:
SHOW BINARY LOGS;
Не включили бинарные логи
Проверьте корректно ли задали параметр log_bin — важно, чтобы он был определён в секции
Last_IO_Error: error connecting to master…
Если SHOW SLAVE STATUS выводим примерно следующую ошибку:
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 10.1.0.11
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 419
Relay_Log_File: mysql-relay-bin.000005
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Connecting
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos: 419
Relay_Log_Space: 1281
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'slave_user@10.1.0.11:3306' - retry-time: 60 retries: 86400 message: Can't connect to MySQL server on '10.1.0.11' (113)
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
то у slave-сервера отсутствует возможность соединения с master-сервером. Причины:
- некорректные авторизационные данные пользователя репликации;
- закрыт порт MySQL для исходящих соединений на slave-сервере;
- закрыт порт MySQL для входящих соединений на master-сервере.
Проверяем соединение:
$ telnet 10.1.0.11 3306 Trying 10.1.0.11... telnet: connect to address 10.1.0.11: No route to host
Добавим правило на slave-сервере
iptables -I OUTPUT -p tcp -m tcp --dport 3306 -j ACCEPT
Добавим правило на master-сервере:
iptables -I INPUT -p tcp -m tcp --dport 3306 -j ACCEPT
Проверим возможность соединения:
$ telnet 10.1.0.11 3306
Trying 10.1.0.11...
Connected to 10.1.0.11.
Escape character is '^]'.
V
5.5.47-MariaDB-log
0P$_6/&�}K;%Gt7Po\aQmysql_native_password
Настройте главный сервер
Первым шагом является настройка главного сервера MySQL. Мы внесем следующие изменения:
- Настройте сервер MySQL для прослушивания частного IP
- Установите уникальный идентификатор сервера
- Включить бинарное ведение журнала
Для этого откройте файл конфигурации MySQL и раскомментируйте или установите следующее:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
master:/etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 192.168.121.190 server-id = 1 log_bin = /var/log/mysql/mysql-bin.log
После этого перезапустите службу MySQL, чтобы изменения вступили в силу.
sudo systemctl restart mysql
Следующим шагом является создание нового пользователя репликации. Войдите на сервер MySQL от имени пользователя root, введя:
sudo mysql
В командной строке MySQL выполните следующие SQL-запросы, которые создадут пользователя replica и предоставят пользователю привилегию REPLICATION SLAVE:
CREATE USER 'replica'@'192.168.121.122' IDENTIFIED BY 'replica_password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.121.122';
Убедитесь, что вы изменили IP-адрес с вашего ведомого IP-адреса. Вы можете назвать пользователя, как вы хотите.
Находясь в приглашении MySQL, выполните следующую команду, которая выведет двоичное имя файла и положение.
SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 629
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
Запомните имя файла «mysql-bin.000001» и «Position 629». Эти значения понадобятся вам при настройке подчиненного сервера. Эти значения, вероятно, будут отличаться на вашем сервере.
Проверьте конфигурацию
На этом этапе у вас должна быть работающая настройка репликации Master/Slave.
Чтобы убедиться, что все работает должным образом, мы создадим новую базу данных на главном сервере:
sudo mysql
CREATE DATABASE replicatest;
Войдите в подчиненную оболочку MySQL:
sudo mysql
Список баз данных:
SHOW DATABASES;
Вы заметите, что база данных, которую вы создали на главном сервере, реплицируется на ведомое устройство:
+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | replicatest | | sys | +--------------------+ 5 rows in set (0.00 sec)
Настройка репликации
Допустим, у нас есть работающая база данных MySQL, уже наполненная данными и включенная в работу. И по одной из причин, описанных выше, мы собираемся включить репликацию нашего сервера. Наши исходные данные:
- IP-адрес мастера 192.168.1.101, реплики — 192.168.1.102.
- MySQL установлен и настроен
- требуется настроить репликацию БД testdb
- мы можем приостановить работу мастера на некоторое время
- у нас есть root на обеих машинах
Настройки мастера
Обязательно укажем уникальный ID сервера, путь для бинарных логов и имя БД для репликации в секции :
Убедитесь, что у вас достаточно места на диске для бинарных логов.
Перезагрузим MySQL, чтобы изменения в конфиге вступили в силу:
Значение position должно увеличиваться по мере того, как вносятся изменения в БД на мастере.
Настройки реплики
Укажем ID сервера, имя БД для репликации и путь к relay-бинлогам в секции конфига, затем перезагрузим MySQL:
Переносим данные
Посмотрим состояние мастера командой «show master status» и запомним значения File и Position (после успешной блокировки мастера они не должны изменятся):
Значения MASTER_LOG_FILE и MASTER_LOG_POS мы берем с мастера.
Если репликация идет нормально, реплика будет следовать за мастером (номер лога в Master_Log_File и позиция Exec_Master_Log_Pos будут расти). Время отставания реплики от мастера (Seconds_Behind_Master), в идеале, должно быть равно нулю. Если оно не сокращается или растет, возможно, что нагрузка на реплику слишком высока — она просто не успевает повторять изменения, происходящие на мастере.
Если же значение Slave_IO_State пусто, а Seconds_Behind_Master равно NULL, репликация не началась. Смотрите лог MySQL для выяснения причины, устраняйте её и заново запускайте репликацию:
Путем этих нехитрых действий мы получаем реплику, данные которой идентичны данным на мастере.
Кстати, время блокировки мастера — это время создания дампа. Если он создается недопустимо долго, можно попробовать поступить так:
- заблокировать запись в мастер флагом read_only, запомнить позицию и остановить MySQL.
- после этого скопировать файлы БД на реплику и включить мастер.
- начать репликацию обычным способом.
Настройте подчиненный сервер
Как и в случае с главным сервером, мы внесем следующие изменения в подчиненный сервер:
- Настройте сервер MySQL для прослушивания частного IP
- Установите уникальный идентификатор сервера
- Включить бинарное ведение журнала
Откройте файл конфигурации MySQL и отредактируйте следующие строки:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
master:/etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 192.168.121.190 server-id = 1 log_bin = /var/log/mysql/mysql-bin.log
Перезапустите службу MySQL:
sudo systemctl restart mysql
Следующим шагом является настройка параметров, которые подчиненный сервер будет использовать для подключения к главному серверу. Войдите в оболочку MySQL:
sudo mysql
STOP SLAVE;
Выполните следующий запрос, который настроит подчиненное устройство для репликации мастера:
CHANGE MASTER TO MASTER_HOST='192.168.121.125',MASTER_USER='replica',MASTER_PASSWORD='replica_password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=629;
Убедитесь, что вы используете правильный IP-адрес, имя пользователя. и пароль. Имя и позиция файла журнала должны совпадать со значениями, которые вы получили от главного сервера.
После этого запустите подчиненные темы.
START SLAVE;
Подготовка Master-сервера
В конфигурационном файле MySQL необходимо изменить следующие параметры в зависимости от доступной оперативной памяти и физического размещения MySQL на сервере:
- datadir – путь, где физически располагаются файлы MySQL. В данной инструкции предполагается, что это директория /mnt/u01/mysql/data
- socket – путь до сокета для консольного клиента, в той же директории /mnt/u01/mysql/data
- innodb_buffer_pool_size – требует тонкой настройки, для начала можно выставить в 50% от свободной оперативной памяти (значение в 4096М или 4G – мегабайты или гигабайты соответственно).
- server-id – в зависимости от роли Master или Slave
- innodb_strict_mode=0 – данный параметр нужно указать явно в конфиге
Прочие параметры можно оставить по умолчанию, они подлежат корректировке в случае необходимости.
Настроить подчиненный сервер
Как и в случае с главным сервером выше, мы внесем следующие изменения в подчиненный сервер:
- Настройте сервер MySQL для прослушивания частного IP-адреса
- Установите уникальный идентификатор сервера
- Включите двоичное ведение журнала
Откройте файл конфигурации MySQL и отредактируйте следующие строки:
подчиненный: /etc/mysql/mysql.conf.d/mysqld.cnf
Перезапустите службу MySQL:
Следующим шагом является настройка параметров, которые подчиненный сервер будет использовать для подключения к главному серверу. Войдите в оболочку MySQL:
Сначала остановите подчиненные потоки:
Выполните следующий запрос, который настроит подчиненное устройство для репликации главного устройства:
Убедитесь, что вы используете правильный IP-адрес, имя пользователя и пароль. Имя и позиция файла журнала должны совпадать со значениями, полученными от главного сервера.
После этого запустите подчиненные потоки.
Настройка slave
Настройка slave или, как сейчас стало модно писать, stand by сервера
начинается также с определения директории с настройками.
su — postgres -c «psql -c ‘SHOW data_directory;'»
Password:
data_directory
———————
/var/lib/pgsql/data
(1 row)
Или
echo $PGDATA
/var/lib/pgsql/data
-bash-4.2$ su — postgres -c «psql -c ‘SHOW config_file;'»
Password:
config_file
————————————-
/var/lib/pgsql/data/postgresql.conf
(1 row)
Залогиньтесь под обычным пользователем (не postgres)
Остановите сервис postgresql:
sudo systemctl stop postgresql
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
Authentication is required to manage system services or units.
Multiple identities can be used for authentication:
1. andrei
2. tester
Choose identity to authenticate as (1-2): 1
Password:
==== AUTHENTICATION COMPLETE ===
Залогиньетсь под postgres и на всякий случай, создайте архив базы:
sudo su — postgres
tar -czvf /tmp/data_pgsql.tar.gz /var/lib/pgsql/data
В данном примере мы сохраним всё содержимое каталога
/var/lib/pgsql/data
в виде архива
/tmp/data_pgsql.tar.gz.
ls /tmp | grep pgsql
data_pgsql.tar.gz
Либо просто скопируете в другую папку
sudo su — postgres
bash-4.2$ mv /var/lib/pgsql/data/ /var/lib/pgsql/data.old
Удаляем содержимое каталога с данными:
rm -rf /var/lib/pgsql/data/*
ls /var/lib/pgsql/data
Теперь нужно распаковать архив, который получен с мастера
tar xvfP /tmp/db_file_backup.tar
Удалите
postmaster.pid
чтобы слейв не видел pid мастера как свой
rm -f /var/lib/pgsql/data/postmaster.pid
В файле
postgresql.conf
Укажите listen_addresses и включите hot_standby
vi /var/lib/pgsql/data/postgresql.conf
Инициализация кластера
Запуск первого узла Galera. Выберите любой из узлов и инициализируйте кластер. Операция не деструктивная, никакие данные не удаляются и не повреждаются:
sudo galera_new_cluster
Запуск оставшихся узлов. Каждый из оставшихся узлов запускается как обычно:
sudo systemctl start mariadb
Проверьте состояние кластера с помощью значения переменных wsrep_*:
$ mysql -uroot -e "SHOW GLOBAL STATUS LIKE 'wsrep_cluster%';"
+--------------------------+--------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------+
| wsrep_cluster_conf_id | 5 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | 15a6ec3c-648e-11e9-ae72-0715b07d89d9 |
| wsrep_cluster_status | Primary |
| wsrep_cluster_weight | 3 |
+--------------------------+--------------------------------------+
$ mysql -uroot -e "SHOW GLOBAL STATUS LIKE 'wsrep_incoming%'\G"
*************************** 1. row ***************************
Variable_name: wsrep_incoming_addresses
Value: 10.120.28.219:3306,10.120.29.124:3306,10.120.28.207:3306
Если теперь на одном из узлов выполнить команду , то вы немедленно увидите как изменится вывод вышеприведенных команд:
$ mysql -uroot -e "SHOW GLOBAL STATUS LIKE 'wsrep_cluster%';"
+--------------------------+--------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------+
| wsrep_cluster_conf_id | 6 |
| wsrep_cluster_size | 2 |
| wsrep_cluster_state_uuid | 15a6ec3c-648e-11e9-ae72-0715b07d89d9 |
| wsrep_cluster_status | Primary |
| wsrep_cluster_weight | 2 |
+--------------------------+--------------------------------------+
$ mysql -uroot -e "SHOW GLOBAL STATUS LIKE 'wsrep_incoming%'\G"
*************************** 1. row ***************************
Variable_name: wsrep_incoming_addresses
Value: 10.120.28.219:3306,10.120.29.124:3306
Важно. Когда вы отключаете узлы штатным способом, как сделали выше, кластер понимает, что общий вес кластера надо уменьшить (был 3/3, стал 2/2), поэтому состояние кластера считается целостным, а не деградированным
Если же узлы отключаются аварийно, то вес кластера не уменьшается (был 3/3, стал 2/3).
