Репликация базы данных mysql: Репликация баз данных MySQL. Введение / Хабр

Репликация баз данных mysql. | Помощник Админа

22 апреля, 2020Linux, Windows, Windows & Linux, Железо и СофтCentos, Linux, MySQL, Server, Windows, Защита информации, Сеть, Хостингadmin

MySQL репликация Master-Slave используется для обеспечения отказоустойчивости приложений и позволяет распределить нагрузку на базу данных между несколькими серверами.
Настроим репликации базы данных на примере из 2 серверов:
1. Master-server, 192.168.100.1
2. Slave-server, 192.168.100.2

Часть 1. Настройка Master-server для репликации базы данных.

1.Откроем для редактирования файл конфигурации базы данных командой:

sudo nano /etc/mysql/my.cnf

#адрес нашего master-server
bind-address = 192.168.100.1

# ID сервера, мастер - 1
server-id = 1

# путь к log-bin
log_bin = /var/log/mysql/mysql-bin. log

# название базы данных, которая будет реплицироваться
binlog_do_db = ithelp21ru_db

2. Перезапускаем службу Mysql:

sudo service mysqld restart

3. Подключаемся к БД и создаем пользователя replication, под которым будет происходить репликация.

mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'password_user_replication';
FLUSH PRIVILEGES;

4. Блокируем все таблицы базы данных от изменений:

USE ithelp21ru_db;
FLUSH TABLES WITH READ LOCK;

5. Вводим команду для просмотра статуса мастер:

SHOW MASTER STATUS;

Вывод примерный в табличном виде:

File: mysql-bin.000001
Position: 72
Binlog_Do_DB: ithelp21ru_db
Binlog_Ignore_DB:

Запомним данные значения, для дальнейшей настройки.

Часть 2. Дамп базы данных.

1. Переносим базу данных в новое окно с помощью mysqldump:

mysql -u root -p
mysqldump -u root -p --opt ithelp21ru_db > ithelp21ru_db. sql

2.снова входим в БД и разблокируем базу:

mysql -u root -p

USE ithelp21ru_db;
UNLOCK TABLES;
QUIT;

Часть 3. Настройка Slave-server для репликации базы данных.

1.Откроем для редактирования файл конфигурации базы данных командой:

sudo nano /etc/mysql/my.cnf

#адрес slave-server
bind-address = 192.168.100.2

# ID сервера, слейв - 2
server-id = 2

# путь к log-bin и relay-log
log_bin = /var/log/mysql/mysql-bin.log
relay-log = /var/log/mysql/mysql-relay-bin.log

# название базы данных, которая будет реплицироваться
binlog_do_db = ithelp21ru_db

2. Перезапускаем службу Mysql:

sudo service mysqld restart

3. Настроим в консоли mysql:

mysql -u root -p

CHANGE MASTER TO MASTER_HOST='192.168.100.1', MASTER_USER='replication', MASTER_PASSWORD='password_user_replication',
MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 72;

4.

Активируем репликацию на slave-сервере:

START SLAVE;

смотрим состояние репликации командой:

SHOW SLAVE STATUS\G

в выводе ищем строки, которые говорят об успешном запуске репликации:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Как настроить репликацию Master-Master на MySQL (MariaDB)

В MySQL существуют два типа репликации данных:

• Master-Master
• Master-Slave

В данной статье мы рассмотрим репликацию Master-Master. При данной репликации данные, попавшие на тот или иной сервер в кластере будут реплицированы между собой.

Тестовый стенд:

• Master_1 — сервер на Centos 7 c адресом 192.168.15.2
• Master_2 — сервер на Centos 7 с адресом 192.168.15.3

Разделим процесс настройки репликации Master-Master на 2 этапа:

1. Организация Master-Slave репликации. Главным сервером будет выступать Master_1, а вспомогательным будет Master_2.
2. Организация обратной Master-Slave репликации — серверы будут синхронизировать информацию из логов соседа.

Установку MySQL мы рассматривали в данной статье, поэтому этот этап мы не будем рассматривать.

Настройка репликации типа Master-Master на MySQL

На двух серверах поднят MySQL. Первым делом нам необходимо провести редактирование файла my.cnf на каждом сервере.

Сервер Master_1 (192.168.15.2)

# mcedit /etc/mysql/my.cnf

В открывшимся файле приводим всё к данному виду:

[mysqld]
#Уникальный идентификатор сервера
server-id = 1
 
#Логи ошибок
log_error = /var/log/mysql/mysql.err
 
#Путь к bin-логам сервера(бин-лог, который заполняет мастер)
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
 
#Путь к relay-логам слейва (бин-лог, скачанный с мастера)
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.
  index
 
#БД, которые нужно/не нужно реплицировать
#replicate-do-db = testdb
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
 
#Не вести журнал бин-лога для БД
binlog-ignore-db = information_schema
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
 
#Чтобы не было конфликтов автоматического инкремента, сообщаем серверу,
#чтобы id генерировались начиная с 1-го прибавляя по 2,
# например 13, 23, 33, 43...
auto_increment_increment = 2
auto_increment_offset = 1
 
#Сохранять логи с мастера в свой бин-лог, чтобы передать слейв-серверу
log-slave-updates
#log_slave_updates = 1

# Сколько дней хранить бин-логи
expire_logs_days = 7

# Максимальный размер бин-лога
max_binlog_size = 500M

# Адрес 1-го мастера
bind-address = 192.168.15.2

Далее создаем пользователя для репликации:

mysql> create user 'replica'@'%' identified by '%repl2021'; 
Query OK, 0 rows affected (0. 00 sec)

mysql> grant replication slave on *.* to 'replica'@'%'; 
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit

Указываем IP адрес для пользователя репликации:

# create user 'replica'@'192.168.15.2' identified by '%repl2021'; 
# GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.15.2' IDENTIFIED BY '%repl2021';

Производим перезапуск MySQL сервера:

# service mysql restart

Первый Master-сервер развернут.

Проверяем статус созданного нами мастер сервера:

mysql> show master status; 
+------------------+----------+--------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000002 |      107 |              | information_schema,mysql,performance_schema |
+------------------+----------+--------------+---------------------------------------------+
1 row in set (0.  00 sec)

mysql>

Далее переходим ко второму серверу Master_2 (192.168.15.3):

#mcedit /etc/my.cnf

Редактируем файл аналогичным образом, как в конфиге ниже:

#Уникальный идентификатор сервера
server-id = 2
 
#Логи ошибок
log_error = /var/log/mysql/mysql.err
 
#Путь к bin-логам сервера(бин-лог, который заполняеи мастер)
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
 
#Путь к relay-логам слейва (бин-лог, скачанный с мастера)
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
 
#БД, которые нужно/не нужно реплицировать
#replicate-do-db = testdb
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
 
#Не вести журнал бин-лога для БД
binlog-ignore-db = information_schema
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
 
#Чтобы не было конфликтов автоинкремента, говорим серверу,
#чтобы id генерировались начиная с 2-го прибавляя по 2,
# например 13, 23, 33, 43.
   ..
auto_increment_increment = 2
auto_increment_offset = 2
 
#Сохранять логи с мастера в своий бин-лог, чтобы передать слейву
log-slave-updates
#log_slave_updates = 1

# Сколько дней хранить бин-логи
expire_logs_days = 7

# Максимальный размер бин-лога
max_binlog_size = 500M

# Адрес 2-го мастера
bind-address = 192.168.15.3

На данном этапе создаем папку и файлы, куда будут записываться логи):

# mkdir /var/log/mysql
# chown -R mysql. /var/log/mysql

Создаем пользователя для репликации

mysql> create user 'replica'@'%' identified by '%repl2021'; 
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'replica'@'%'; 
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit

Перезапускаем MySQL сервер:

# service mysql restart

Настройка репликации

На данном этапе мы настраиваем непосредственно репликацию. Запускаем репликацию на втором сервере (Master_2). Для того, чтобы это сделать нам необходимо узнать MASTER_LOG_FILE и MASTER_LOG_POS на первом сервере (Master_1).

Статус мастера на сервере Master_1

# mysql -u root -p -e 'show master status;'

Вывод команды:

+------------------+----------+--------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000002 |      107 |              | information_schema,mysql,performance_schema |
+------------------+----------+--------------+---------------------------------------------+
1 row in set (0.00 sec)

Обратим внимание, что MASTER_LOG_FILE это mysql-bin.000002, а MASTER_LOG_POS равен 107.

На втором сервере (Master_2) выполняем:

mysql> slave stop; 
Query OK, 0 rows affected, 1 warning (0. 00 sec)

mysql> CHANGE MASTER TO MASTER_HOST = '192.168.15.2', MASTER_USER = 'replica', MASTER_PASSWORD = '%repl2021', MASTER_LOG_FILE = 'mysql-bin.000002', MASTER_LOG_POS = 107; 
Query OK, 0 rows affected (0.02 sec)

mysql> slave start;
Query OK, 0 rows affected (0.00 sec)

mysql>

Далее производим репликацию на 1-ом сервере. Для этого нужно посмотреть статус мастера на Master_2 сервере:

mysql> SHOW MASTER STATUS; 
+------------------+----------+--------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000002 |      106 |              | information_schema,mysql,performance_schema |
+------------------+----------+--------------+---------------------------------------------+
1 row in set (0. 00 sec)

mysql>

Обратим внимание, что MASTER_LOG_FILE это mysql-bin.000002, а MASTER_LOG_POS равен 106.

На сервере Master_1 выполняем команду:

mysql> slave stop; 
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST = '192.168.15.3', MASTER_USER = 'replica', MASTER_PASSWORD = '%repl2021', MASTER_LOG_FILE = 'mysql-bin.000002', MASTER_LOG_POS = 106; 
Query OK, 0 rows affected (0.03 sec)

mysql> slave start;
Query OK, 0 rows affected (0.00 sec)

mysql>

После всех действий просматриваем статус слейва на всех мастер-серверах:

Master_1

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.15.3
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin. 000002
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 252
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: information_schema,mysql,performance_schema
           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: 106
              Relay_Log_Space: 408
              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: 2
1 row in set (0. 00 sec)

ERROR: 
No query specified

mysql>

Master_2

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.15.2
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 252
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: information_schema,mysql,performance_schema
           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: 407
              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: 
1 row in set (0. 00 sec)

ERROR: 
No query specified

mysql>

Кластер из двух мастер-серверов готов.

MySQL :: Справочное руководство по MySQL 8.0 :: 17.4 Решения для репликации

версия 8.0

5.7
5.6

8,0 Японский
5.6 Японский

17.4.1 Использование репликации для резервного копирования

17.4.2 Обработка неожиданной остановки реплики

17. 4.3 Мониторинг репликации на основе строк .5 Использование репликации для горизонтального масштабирования

17.4.6 Репликация разных баз данных на разные реплики

17.4.7 Повышение производительности репликации

17.4.8 Переключение источников при отказоустойчивости

17.4.9 Переключение источников и реплик с аварийным переключением асинхронного соединения

17.4.10 Полусинхронная репликация

17.4.11 Отложенная репликация

Репликация может использоваться во многих различных средах для диапазона целей. В этом разделе приведены общие замечания и советы по использованию репликация для конкретных типов решений.

Для получения информации об использовании репликации в среде резервного копирования см. включая примечания по настройке, процедуре резервного копирования и файлам для резервного копирования см. раздел 17.4.1, «Использование репликации для резервных копий».

Советы и подсказки по использованию различных механизмов хранения в исходном коде и реплика см. Раздел 17.4.4, «Использование репликации с разными механизмами хранения исходного кода и реплики».

Использование репликации в качестве масштабируемого решения требует внесения некоторых изменений в логика и работа приложений, использующих решение. Видеть Раздел 17.4.5, «Использование репликации для масштабирования».

Из соображений производительности или распределения данных вы можете захотеть реплицировать разные базы данных на разные реплики. Видеть Раздел 17.4.6, «Репликация разных баз данных в разные реплики»

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

Руководство по переключению источников или преобразованию реплик в источники как часть решения аварийного переключения, см. Раздел 17.4.8, «Переключение источников во время аварийного переключения».

Для получения информации о мерах безопасности, характерных для серверов в топологию репликации, см. раздел 17.3, «Безопасность репликации».

НАЗАД ДОМ ВВЕРХ ДАЛЕЕ

2.1 Как настроить репликацию

2.1.1 Настройка конфигурации источника репликации

2.1.2 Настройка конфигурации реплики

2.1.3 Создание пользователя для репликации

2.1.4 Получение двоичного файла журнала репликации Координаты

2.1.5 Создание моментального снимка данных с помощью mysqldump

2.1.6 Создание моментального снимка данных с использованием файлов необработанных данных

2.1.7 Настройка репликации с новым источником и репликами

2.1.8 Настройка репликации с существующими данными

2.1.9 Введение дополнительных реплик в существующую среду репликации

2.1.10 Настройка исходной конфигурации реплики

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

Есть несколько общих задач, которые являются общими для всех репликаций. настройки:

• В источнике необходимо включить ведение двоичного журнала и настроить уникальный идентификатор сервера. Это может потребовать перезагрузки сервера. Видеть Раздел 2.1.1, «Настройка конфигурации источника репликации».

• На каждой реплике, которую вы хотите подключить к источнику, вы необходимо настроить уникальный идентификатор сервера. Для этого может потребоваться сервер запустить снова. Видеть Раздел 2.1.2, «Настройка конфигурации реплики».

• При желании создайте отдельного пользователя для ваших реплик. при аутентификации с источником при чтении бинарника журнал для репликации. Видеть Раздел 2.1.3, «Создание пользователя для репликации».

• Перед созданием моментального снимка данных или запуском репликации процесс, вы должны записать позицию двоичного входа в систему источник. Эта информация понадобится вам при настройке реплика, чтобы реплика знала, где в двоичном журнале чтобы начать выполнение событий. Видеть Раздел 2.1.4, «Получение координат двоичного журнала источника репликации».

• Если у вас уже есть данные об источнике и вы хотите использовать их для синхронизируйте свою реплику, вам нужно создать моментальный снимок данных. Существуют различные способы создания моментального снимка базы данных. в зависимости от размера базы данных и местоположения файлы. Создайте снимок, используя mysqldump (см. Раздел 2.1.5, «Создание моментального снимка данных с помощью mysqldump») или путем копирования файлы данных напрямую (см. Раздел 2.1.6, «Создание моментального снимка данных с использованием файлов необработанных данных»).

• Настройте реплику с параметрами для подключения к источник, такой как имя хоста, учетные данные для входа и двоичный имя файла журнала и его положение. Видеть Раздел 2.1.10, «Настройка исходной конфигурации реплики».

После настройки основных параметров выберите свой сценарий:

• Чтобы настроить репликацию для новой установки источника и реплики, не содержащие данных, см. Раздел 2.1.7, «Настройка репликации с новым источником и репликами».

• Чтобы настроить репликацию нового источника, используя данные из существующий сервер MySQL, см. Раздел 2.1.8, «Настройка репликации с существующими данными».

• Чтобы добавить реплики в существующую среду репликации, см.

  No related posts.