Перезапуск mysql

На нерабочей ноде

Создаем дамп баз:

# 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 '[email protected]: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:

Сначала остановите подчиненные потоки:

Выполните следующий запрос, который настроит подчиненное устройство для репликации главного устройства:

Убедитесь, что вы используете правильный 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).