Синхронизация данных MySQL на двух удаленных площадках

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

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

1. Репликация MySQL

MySQL репликация — это стандартный метод для синхронизации данных между серверами MySQL. Существует два основных типа репликации:

• Мастер-Слэйв (Master-Slave): Один сервер (мастер) обрабатывает все записи данных, а другой сервер (слэйв) получает копии этих данных. Это односторонняя репликация, при которой слэйв может быть использован для чтения данных, но не для записи.
• Мастер-Мастер (Master-Master): Оба сервера могут выполнять операции чтения и записи данных, синхронизируя изменения между собой. Этот тип репликации сложнее в настройке, но обеспечивает большую гибкость и отказоустойчивость.

Настройка Мастер-Слэйв репликации:

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

   • В my.cnf на мастере добавить:
     python

      

     [mysqld] server-id=1 log-bin=mysql-bin
   • Перезапустить сервер MySQL:
     bash

      

     sudo service mysql restart
   • Создать пользователя для репликации и предоставить ему необходимые привилегии:
     sql

      

     CREATE USER 'replica'@'%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%'; FLUSH PRIVILEGES;
   • Создать дамп базы данных:
     bash

      

     mysqldump -u root -p --all-databases --master-data > dump.sql

2. Настройка слэйва:

   • Скопировать дамп базы данных на слэйв сервер и импортировать его:
     bash

      

     mysql -u root -p < dump.sql
   • В my.cnf на слэйве добавить:
     csharp

      

     [mysqld] server-id=2
   • Перезапустить сервер MySQL:
     bash

      

     sudo service mysql restart
   • Настроить слэйв для репликации:
     sql

      

     CHANGE MASTER TO MASTER_HOST='master_host', MASTER_USER='replica', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=4; START SLAVE;

2. Управление расписанием репликации

Для управления расписанием репликации можно использовать события MySQL или планировщик задач cron. Это позволяет настроить выполнение репликации в определенные временные окна, что может быть полезно для минимизации нагрузки на серверы в пиковые периоды.

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

1. Создать скрипт для запуска репликации, например replicate.sh:

   bash

    

   #!/bin/bash mysql -u root -p -e "START SLAVE;"

2. Добавить задачу в cron для выполнения репликации каждый час:

   bash

    

   crontab -e

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

   bash

    

   0 * * * * /path/to/replicate.sh

3. Настройка iptables

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

Настройка iptables на мастере и слэйве:

1. Разрешить входящий трафик на порт MySQL (TCP 3306):

   bash

    

   sudo iptables -A INPUT -p tcp --dport 3306 -j ACCEPT

2. Разрешить трафик для Group Replication (если используется) на порты TCP 33061 и UDP 33062:

   bash

    

   sudo iptables -A INPUT -p tcp --dport 33061 -j ACCEPT sudo iptables -A INPUT -p udp --dport 33062 -j ACCEPT

3. Сохранить правила iptables:

   bash

    

   sudo iptables-save > /etc/iptables/rules.v4

4. Использование MySQL Group Replication

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

1. Настройка MySQL Group Replication:
   • В my.cnf добавить следующие настройки на каждом сервере:
     makefile

      

     [mysqld] server-id=1 gtid_mode=ON enforce-gtid-consistency=ON master-info-repository=TABLE relay-log-info-repository=TABLE binlog_checksum=NONE log_slave_updates=ON log_bin=binlog binlog_format=ROW transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose-group_replication_start_on_boot=off loose-group_replication_local_address="192.0.2.1:33061" loose-group_replication_group_seeds="192.0.2.1:33061,192.0.2.2:33061" loose-group_replication_bootstrap_group=off
   • На первом сервере:
     sql

      

     SET GLOBAL group_replication_bootstrap_group=ON; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group=OFF;
   • На остальных серверах:
     sql

      

     START GROUP_REPLICATION;

5. Инструменты и сервисы

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

• MySQL Shell для создания и управления MySQL InnoDB Cluster.
• Percona XtraDB Cluster — решение на основе Galera Cluster для MySQL.
• AWS Database Migration Service (DMS) для миграции и репликации баз данных в облаке AWS.

Заключение

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

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

Настройка iptables и управление расписанием репликации помогут повысить безопасность и эффективность работы системы.