Репликация MySQL с SSL на CentOS 5.4

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

Репликация MySQL создаёт точную копию базы данных с master-сервера на slave-сервере. Все обновления на master немедленно реплицируются на slave, поэтому данные синхронизированы. Репликация не заменяет резервное копирование: случайный DELETE на master также удалит данные на slave. Шифрование SSL защищает данные и пароли от перехвата в сети.

Определения в одной строке:

Репликация — механизм передачи изменений binlog от master к slave.
SSL/TLS — протоколы для шифрования сетевого трафика.

Important: все шаги показаны под root-пользователем. Убедитесь, что вы выполняете команды от root или через sudo.

Сценарий примера

В примере мы реплицируем базу exampledb с server1.example.com (IP 192.168.0.100) на server2.example.com (IP 192.168.0.101). Оба сервера работают под CentOS 5.4. В примере база уже существует на master, но отсутствует на slave.

Содержание инструкции

Предварительные требования
Установка MySQL и включение поддержки SSL
Настройка master (сертификаты, my.cnf, создание пользователя реплики, дамп базы)
Настройка slave (импорт дампа, конфигурация, подключение к master через SSL)
Проверка и отладка
Когда репликация может не сработать
Альтернативы (VPN/SSH туннель, современные версии MySQL)
Рекомендуемая проверочная таблица и плейбук
Безопасность и рекомендации по эксплуатации
Краткая сводка

1 Предварительные требования

root-доступ на обоих серверах (или sudo).
MySQL 5.x (в руководстве используется MySQL 5).
Сетевое соединение между серверами (порты 3306 по умолчанию).
Оба сервера должны иметь корректные временные метки / синхронизацию времени (желательно NTP).

Notes: если сеть ненадёжна, рассмотрите использование VPN или SSH-туннеля для дополнительной надёжности/безопасности.

2 Установка MySQL 5 и включение SSL

Если MySQL 5 не установлен на server1 и server2 — установите пакет:

server1/server2:

yum install mysql mysql-devel mysql-server

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

chkconfig --levels 235 mysqld on
/etc/init.d/mysqld start

Установите пароль для root в MySQL (на обоих серверах):

server1:

mysqladmin -u root password yourrootsqlpassword
mysqladmin -h server1.example.com -u root password yourrootsqlpassword

server2:

mysqladmin -u root password yourrootsqlpassword
mysqladmin -h server2.example.com -u root password yourrootsqlpassword

Без пароля root любой сможет подключиться к базе — это критично!

Проверьте, поддерживает ли MySQL SSL. Войдите в MySQL:

mysql -u root -p

Выполните в MySQL shell:

show variables like '%ssl%';

Если вывод показывает have_openssl и have_ssl как DISABLED, то SSL собран, но выключен. В таком случае выйдите из оболочки:

quit;

И отредактируйте /etc/my.cnf, добавив параметр ssl в секцию [mysqld]:

vi /etc/my.cnf

Пример секции (обновите или добавьте строку ssl):

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1
ssl

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Перезапустите MySQL и проверьте снова:

/etc/init.d/mysqld restart
mysql -u root -p
show variables like '%ssl%';
quit;

Ожидаемый результат: have_openssl и have_ssl должны стать YES.

3 Настройка master (server1)

Создайте директорию для binlog-логов и установите владельца:

mkdir /var/log/mysql
chown mysql:mysql /var/log/mysql

Создайте сертификаты (CA, серверный и клиентский) для SSL. В примере создаём их в /etc/mysql/newcerts:

mkdir -p /etc/mysql/newcerts && cd /etc/mysql/newcerts

# Создать CA
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

# Серверный сертификат
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem
openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

# Клиентский сертификат (для slave как клиента)
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem
openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

Ожидаемый список файлов (пример вывода ls -l):

-rw-r--r-- 1 root root 1375 Feb  8 17:37 ca-cert.pem
-rw-r--r-- 1 root root 1679 Feb  8 17:37 ca-key.pem
-rw-r--r-- 1 root root 1119 Feb  8 17:37 client-cert.pem
-rw-r--r-- 1 root root 1675 Feb  8 17:37 client-key.pem
-rw-r--r-- 1 root root  968 Feb  8 17:37 client-req.pem
-rw-r--r-- 1 root root 1119 Feb  8 17:37 server-cert.pem
-rw-r--r-- 1 root root 1679 Feb  8 17:37 server-key.pem
-rw-r--r-- 1 root root  968 Feb  8 17:37 server-req.pem

Скопируйте клиентские сертификаты и CA на slave (server2). На server2 создайте каталог:

server2:

mkdir -p /etc/mysql/newcerts

На server1 выполните передачу файлов (scp):

scp /etc/mysql/newcerts/ca-cert.pem [email protected]:/etc/mysql/newcerts
scp /etc/mysql/newcerts/client-cert.pem [email protected]:/etc/mysql/newcerts
scp /etc/mysql/newcerts/client-key.pem [email protected]:/etc/mysql/newcerts

Настройка my.cnf на master: добавьте пути к сертификатам и параметры репликации. Отредактируйте /etc/my.cnf и в секции [mysqld] добавьте:

ssl
ssl-ca=/etc/mysql/newcerts/ca-cert.pem
ssl-cert=/etc/mysql/newcerts/server-cert.pem
ssl-key=/etc/mysql/newcerts/server-key.pem
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = exampledb

Перезапустите MySQL:

/etc/init.d/mysqld restart

Создайте пользователя для репликации и требуйте SSL (опционально). Войдите в MySQL и выполните:

mysql -u root -p

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password' REQUIRE SSL;
FLUSH PRIVILEGES;
quit;

Примечание: параметр REQUIRE SSL запрещает нешифрованные подключения для этого пользователя. Если хотите разрешить и зашифрованные, и незашифрованные подключения — опустите REQUIRE SSL.

Заблокируйте базу, получите статус master и сделайте дамп. Это нужно, чтобы slave получил точную начальную позицию репликации.

mysql -u root -p

USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Запишите значения File и Position из вывода SHOW MASTER STATUS; они нужны для настройки slave. Выход может выглядеть так:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |  3096416 | exampledb    |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Не покидайте MySQL shell — пока он открыт, блокировка сохранится. Откройте второе окно терминала и создайте дамп и передайте его на slave:

server1 (в другом окне):

cd /tmp
mysqldump -u root -pyourrootsqlpassword --opt exampledb > snapshot.sql
scp snapshot.sql [email protected]:/tmp

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

server1 (первое окно):

UNLOCK TABLES;
quit;

4 Настройка slave (server2)

Убедитесь, что на server2 установлены сертификаты (мы уже скопировали ca-cert.pem, client-cert.pem и client-key.pem). Проверьте права доступа и при необходимости защитите закрытые ключи:

chown -R mysql:mysql /etc/mysql/newcerts
chmod 600 /etc/mysql/newcerts/client-key.pem

Импортируйте дамп на slave:

mysql -u root -p
# если БД не создана, создайте
CREATE DATABASE IF NOT EXISTS exampledb;
quit;

mysql -u root -p exampledb < /tmp/snapshot.sql

Настройка /etc/my.cnf на slave: укажите уникальный server-id и параметры SSL. Пример секции [mysqld]:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1
ssl
ssl-ca=/etc/mysql/newcerts/ca-cert.pem
# client-cert и client-key можно не указывать для slave, если используете только CA для проверки
server-id               = 2
relay-log               = /var/log/mysql/mysql-relay-bin
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
replicate-do-db         = exampledb

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Перезапустите MySQL на slave:

/etc/init.d/mysqld restart

Подключение slave к master с использованием SSL. В MySQL shell на server2 выполните:

mysql -u root -p

CHANGE MASTER TO
  MASTER_HOST='192.168.0.100',
  MASTER_USER='slave_user',
  MASTER_PASSWORD='slave_password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=3096416,
  MASTER_SSL=1,
  MASTER_SSL_CA='/etc/mysql/newcerts/ca-cert.pem';

START SLAVE;

SHOW SLAVE STATUS\G

Замените MASTER_LOG_FILE и MASTER_LOG_POS на значения, которые вы записали на master при SHOW MASTER STATUS.

В выводе SHOW SLAVE STATUS\G обратите внимание на поля:

Slave_IO_Running: должен быть Yes
Slave_SQL_Running: должен быть Yes
Last_IO_Error / Last_SQL_Error: пустые при отсутствии ошибок

quit;

5 Проверка и отладка

Проверки:

На master: создайте тестовую запись в exampledb и убедитесь, что она появляется на slave.
На slave: SHOW SLAVE STATUS\G — обе строки IO и SQL должны быть Yes.

Распространённые ошибки и их решения:

Ошибка: Slave_IO_Running = No, Last_IO_Error содержит “Access denied”
Решение: проверьте имя пользователя и пароль репликации, а также хост-правила у пользователя (‘%’, ‘server2.example.com’ и т. п.). Убедитесь, что пароль введён правильно и у пользователя есть привилегия REPLICATION SLAVE.
Ошибка: SSL handshake или certificate verify failed
Решение: проверьте корректность и доступность ca-cert.pem на slave, права доступа на ключи, совпадение CN (необязательно для самоподписанных CA). Попробуйте временно подключиться без REQUIRE SSL, чтобы проверить соединение, затем вернуть SSL.
Ошибка: master не отвечает по сети
Решение: проверьте firewall/iptables, убедитесь, что порт 3306 открыт, и mysqld слушает на нужном интерфейсе. Проверьте bind-address в my.cnf (см. секцию Безопасность).
Несоответствие данных после начальной загрузки
Решение: проверьте, что дамп был сделан после FLUSH TABLES WITH READ LOCK и что вы использовали те же File/Position в CHANGE MASTER TO. Если нет, выполните новый дамп.

6 Когда репликация может не сработать (примеры)

Неправильная начальная позиция binlog (указаны неверные File/Position).
Разные версии MySQL с несовместимостью форматов binlog.
Сетевые перебои или закрытые порты.
Проблемы с правами доступа к сертификатам/ключам.
Если на master выполняются операции, несовместимые с репликацией (например, локальные изменения, не записываемые в binlog).

Counterexample: если вы ожидаете, что репликация защитит от всех ошибок — это неверно. Репликация дублирует любые изменения, включая ошибочные DELETE/UPDATE.

7 Альтернативные подходы

VPN между серверами (IPSec/OpenVPN) — шифрует весь трафик на сетевом уровне и упрощает настройки SSL в MySQL.
SSH-туннель (ssh -L) — быстрый способ зашифровать соединение без изменения конфигурации MySQL.
Использовать современные версии MySQL/MariaDB, которые поддерживают улучшенные TLS-настройки и параметры безопасности (например, require_secure_transport в новых версиях).

8 Плейбук / SOP (шаг за шагом)

Подготовка: проверить доступ по SSH и root-права.
Установить MySQL на обеих машинах.
Включить SSL в my.cnf и перезапустить mysqld.
На master: создать CA, server- и client-сертификаты.
Скопировать ca/client-cert/client-key на slave, настроить права.
На master: настроить my.cnf (server-id=1, log_bin, binlog_do_db).
На master: создать пользователя реплики с REQUIRE SSL.
На master: FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; сделать дамп; передать дамп на slave; UNLOCK TABLES.
На slave: импортировать дамп; настроить my.cnf (server-id=2, ssl-ca); перезапустить.
На slave: CHANGE MASTER TO … MASTER_SSL=1, START SLAVE; проверить SHOW SLAVE STATUS\G.

Критерии приёмки:

Slave_IO_Running и Slave_SQL_Running = Yes.
Тестовые изменения на master появляются на slave в течение ожидаемого времени.

9 Роль‑ориентированные чек‑листы

Администратор баз данных (DBA):

Проверить версии MySQL и совместимость binlog.
Убедиться в наличии резервной копии перед началом.
Настроить binlog и параметры retention.
Настроить мониторинг репликации.

Системный администратор (Сисопс):

Настроить firewall, открыть порт 3306 для доверенных IP.
Обеспечить защиту приватных ключей (600, owner mysql/root).
Проверить SELinux/ACL при необходимости.

Разработчик/QA:

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

10 Безопасность и рекомендации по эксплуатации

Храните ca-key.pem и server-key.pem в безопасном месте, ограничьте доступ.
Избегайте передачи приватных ключей в ненадёжных каналах.
Используйте ограничение хостов для пользователя реплики вместо ‘%’ где это возможно.
Регулярно ротация сертификатов и ключей (плановая операция).
Настройте мониторинг поломок репликации и оповещения в случае остановки.

Security hardening (кратко):

Запретите анонимные подключения и удалите тестовые базы.
В my.cnf, если нужно, укажите bind-address = 0.0.0.0 только если требуется доступ извне; иначе ограничьте интерфейс.
Настройте firewall и, при возможности, VPN между дата-центрами.

11 Тест-кейсы и критерии приёмки

После настройки: запишите 10 тестовых строк в таблицу на master — они должны появиться на slave.
Отключите сеть между slave и master на небольшое время — после восстановления соединения репликация должна догнать backlog.
Попробуйте подключиться по SSL и по не-SSL для пользователя с REQUIRE SSL — не-SSL должен быть отвергнут.

12 Примеры команд для диагностики

Проверки статуса:

# На master
mysql -u root -p -e "SHOW MASTER STATUS;"

# На slave
mysql -u root -p -e "SHOW SLAVE STATUS\G"

# Проверить SSL-переменные
mysql -u root -p -e "SHOW VARIABLES LIKE '%ssl%';"

13 Визуальная подсказка — решение ветвления (Mermaid)

flowchart TD
  A[Начало: проверка требований] --> B{MySQL установлен?}
  B -- Нет --> C[Установить MySQL]
  C --> D[Включить SSL в my.cnf]
  B -- Да --> D
  D --> E[Создать CA и сертификаты на master]
  E --> F[Скопировать ca и клиентские cert на slave]
  F --> G[Настроить my.cnf master и slave]
  G --> H[Создать пользователя реплики на master]
  H --> I[Сделать дамп с блокировкой и перенести на slave]
  I --> J[Импортировать дамп на slave и настроить CHANGE MASTER]
  J --> K[START SLAVE и проверить SHOW SLAVE STATUS]
  K --> L{Работает?}
  L -- Да --> M[Готово]
  L -- Нет --> N[Диагностика: логи, права, SSL, сеть]
  N --> D

14 Глоссарий в одну строку

binlog — журнал двоичных изменений MySQL, используемый для репликации.
master — сервер, откуда исходят изменения.
slave — сервер, принимающий и применяющий изменения.
CA — центр сертификации, подписывающий сертификаты.

15 Краткая сводка

Включите SSL в MySQL и сгенерируйте CA/сертификаты.
Настройте master: binlog, server-id, user для реплики с REQUIRE SSL.
Сделайте дамп с блокировкой, передайте и импортируйте на slave.
На slave выполните CHANGE MASTER TO с MASTER_SSL=1 и проверьте SHOW SLAVE STATUS.

Summary:

Репликация с SSL защищает трафик репликации.
Обязательно проверяйте File/Position при инициализации.
Мониторьте и применяйте best practices для ключей и сетевой безопасности.

Important: репликация — это гибкое, но не стопроцентное средство защиты от человеческой ошибки. Всегда имейте резервные копии и тестируйте процедуры восстановления.