MySQL репликация с SSL на Debian Lenny

Версия 1.0
Автор: Falko Timme
Следите за мной в Twitter

Кратко: в этой инструкции показано, как настроить репликацию базы данных MySQL между мастером и слейвом с использованием SSL для шифрования трафика. Описаны шаги по установке MySQL, включению поддержки SSL, генерации сертификатов, настройке мастера и слейва, переносу дампа базы и проверке статуса репликации. Важно: репликация не заменяет резервное копирование — удалённые изменения также попадут на слейв.

Введение

Эта инструкция объясняет, как настроить репликацию базы данных exampledb с сервера server1.example.com (master, IP 192.168.0.100) на сервер server2.example.com (slave, IP 192.168.0.101) на Debian Lenny. Оба сервера работают под root-пользователем на момент выполнения шагов. Репликация делает точную копию БД с мастера на слейв и автоматически применяет изменения; это удобно для отказоустойчивости и нагрузочного разделения, но не заменяет регулярные бэкапы.

Важно: я не даю гарантий применимости этой инструкции к вашей инфраструктуре — адаптируйте под свои версии MySQL и ОС.

Кому пригодится эта инструкция

Системным администраторам, которые хотят защитить трафик репликации.
Инженерам по доступности и резервированию, настраивающим отказоустойчивую архитектуру.
Тем, кто работает с устаревшими дистрибутивами (Debian Lenny) и MySQL 5.x.

Краткая проверка терминов

Master — основной сервер, откуда читаются бинарные логи.
Slave — сервер, который подключается к мастеру и воспроизводит изменения.
binlog — бинарный лог MySQL, содержащий изменения; используется для репликации.
SSL — слой шифрования, защищающий трафик между мастером и слейвом.

1 Предварительная заметка

В примере база exampledb уже существует на мастере и отсутствует на слейве. Все команды в инструкции выполняются с правами root. Если вы используете другой пользовательский контекст, добавьте sudo там, где нужно.

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

Если MySQL 5 не установлен на обоих серверах, установите пакет:

server1/server2:

aptitude install mysql-server mysql-client

Во время установки потребуется указать пароль для MySQL-пользователя root. Этот пароль будет действовать для root@localhost и для root@127.0.0.1.

После установки проверьте, поддерживает ли сервер SSL. Подключитесь к MySQL:

mysql -u root -p

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

show variables like '%ssl%';

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

mysql> show variables like '%ssl%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_key       |          |
+---------------+----------+
7 rows in set (0.00 sec)

mysql>

Выйдите из MySQL:

quit;

Откройте конфигурацию /etc/mysql/my.cnf и в секции [mysqld] в блоке безопасности добавьте строку ssl (если её нет):

vi /etc/mysql/my.cnf

В разделе * Security Features добавьте/раскомментируйте:

[...]
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
ssl
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
[...]

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

/etc/init.d/mysql restart

Снова проверьте переменные SSL:

mysql -u root -p
show variables like '%ssl%';

После включения вывод должен показать YES для have_openssl и have_ssl:

mysql> show variables like '%ssl%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl  | YES   |
| have_ssl      | YES   |
| ssl_ca        |       |
| ssl_capath    |       |
| ssl_cert      |       |
| ssl_cipher    |       |
| ssl_key       |       |
+---------------+-------+
7 rows in set (0.00 sec)

mysql>

Выйдите:

quit;

Примечание: названия опций в современных версиях MySQL могут отличаться (например, require_secure_transport). Для Lenny и MySQL 5.x приведённая конфигурация актуальна.

3 Настройка мастера

Включите прослушивание всех интерфейсов на мастере, закомментировав bind-address = 127.0.0.1 в /etc/mysql/my.cnf:

server1:

vi /etc/mysql/my.cnf

В секции [mysqld] закомментируйте или удалите строку:

#bind-address           = 127.0.0.1

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

/etc/init.d/mysql restart

Проверьте, что MySQL слушает на всех интерфейсах:

netstat -tap | grep mysql

Ожидаемый вывод:

server1:~# netstat -tap | grep mysql
tcp        0      0 *:mysql                 *:*                     LISTEN      3771/mysqld
server1:~#

Создайте каталог для хранения сертификатов и ключей, затем сгенерируйте CA, серверный и клиентский сертификаты. Я использую /etc/mysql/newcerts.

mkdir /etc/mysql/newcerts && cd /etc/mysql/newcerts
aptitude install openssl

Сгенерируйте 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

Клиентский сертификат (для слейва):

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

Пример вывода:

server1:/etc/mysql/newcerts# ls -l
total 32
-rw-r--r-- 1 root root 1346 2010-08-18 20:13 ca-cert.pem
-rw-r--r-- 1 root root 1675 2010-08-18 20:13 ca-key.pem
-rw-r--r-- 1 root root 1099 2010-08-18 20:14 client-cert.pem
-rw-r--r-- 1 root root 1675 2010-08-18 20:14 client-key.pem
-rw-r--r-- 1 root root 956 2010-08-18 20:14 client-req.pem
-rw-r--r-- 1 root root 1099 2010-08-18 20:14 server-cert.pem
-rw-r--r-- 1 root root 1679 2010-08-18 20:14 server-key.pem
-rw-r--r-- 1 root root 956 2010-08-18 20:14 server-req.pem
server1:/etc/mysql/newcerts#

Важно: установите права доступа к приватным ключам так, чтобы только root (или владелец mysql, если вы переносите файлы в директорию, доступную демону) мог читать ключи: chmod 600 .pem и chown root:root .pem.

Передайте на слейв файлы ca-cert.pem, client-cert.pem и client-key.pem. На слейве создайте ту же директорию:

server2:

mkdir /etc/mysql/newcerts

server1:

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

На мастере укажите пути к сертификатам в /etc/mysql/my.cnf, раскомментировав и заполнив опции ssl-ca, ssl-cert и ssl-key:

vi /etc/mysql/my.cnf

Добавьте/раскомментируйте следующие строки в разделе * Security Features:

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

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

/etc/init.d/mysql restart

Создайте пользователя репликации, ограничив подключение по SSL (опционально):

mysql -u root -p

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

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

Примечание: если вы не используете REQUIRE SSL, slave_user сможет подключаться и по незашифрованному каналу. Для перевода уже созданного пользователя на обязательный SSL используйте:

GRANT USAGE ON *.* TO 'slave_user'@'%' REQUIRE SSL;

Включите бинарный лог и назначьте уникальный server-id мастера в /etc/mysql/my.cnf, а также сузьте круг репликации до нужной базы:

vi /etc/mysql/my.cnf

В разделе [mysqld] добавьте:

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/mysql restart

Заблокируйте базу, запишите позиции в мастере и создайте дамп для переноса на слейв.

Подключитесь к MySQL и выполните:

mysql -u root -p
USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Запишите значения File и Position из SHOW MASTER STATUS — они потребуются при запуске репликации на слейве. Пример вывода:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |       98 | exampledb    |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

Не выходите из MySQL — блокировка должна оставаться до момента создания дампа. Откройте в этой же системе второе окно терминала и выполните экспорт и передачу:

server1 (в новом окне):

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

После успешной передачи можно во втором окне закрыть процесс экспорта. Вернитесь в первое окно и разблокируйте таблицы:

UNLOCK TABLES;
quit;

4 Настройка слейва

На сервере server2 импортируйте дамп в пустую базу exampledb:

server2:

mysql -u root -p
CREATE DATABASE exampledb; -- если требуется
exit
mysql -u root -p exampledb < /tmp/snapshot.sql

На слейве в /etc/mysql/my.cnf укажите уникальный server-id и при необходимости путь к CA/сертификатам клиента, если требуются клиентские ключи:

vi /etc/mysql/my.cnf

В разделе [mysqld] добавьте:

server-id               = 2
# при необходимости можно указать ssl-файлы клиента:
# ssl-ca=/etc/mysql/newcerts/ca-cert.pem
# ssl-cert=/etc/mysql/newcerts/client-cert.pem
# ssl-key=/etc/mysql/newcerts/client-key.pem

Перезапустите MySQL на слейве:

/etc/init.d/mysql restart

Настройте параметры подключения к мастеру с использованием SSL. В MySQL-CLI на слейве выполните (подставьте ранее записанные значения MASTER_LOG_FILE и MASTER_LOG_POS):

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=98,
  MASTER_SSL=1,
  MASTER_SSL_CA='/etc/mysql/newcerts/ca-cert.pem',
  MASTER_SSL_CERT='/etc/mysql/newcerts/client-cert.pem',
  MASTER_SSL_KEY='/etc/mysql/newcerts/client-key.pem';

START SLAVE;
SHOW SLAVE STATUS\G

Обратите внимание: опции MASTER_SSL_CERT и MASTER_SSL_KEY не всегда обязательны, но их указание позволяет использовать клиентский сертификат при двухсторонней аутентификации.

Пример ожидаемых полей в SHOW SLAVE STATUS:\G:

Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_Host: 192.168.0.100
Master_User: slave_user
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 98
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Error:
Last_SQL_Error:

Если обе строки Slave_IO_Running и Slave_SQL_Running равны Yes, репликация работает.

Диагностика распространённых проблем

Slave_IO_Running: No / Last_IO_Error содержит «Access denied» — проверьте логин и пароль, а также разрешение подключения для slave_user@’%’. Убедитесь, что на мастере пользователь создан с GRANT REPLICATION SLAVE и, при необходимости, REQUIRE SSL.
Ошибки сертификатов — проверьте наличие CA и клиентских сертификатов на слейве и права доступа к файлам (chmod 600). Укажите корректные пути в CHANGE MASTER TO.
Сервер не принимает подключения — проверьте bind-address, firewall (iptables), что порт 3306 открыт между серверами только для нужных IP.
Несовпадение позиции — если дамп был создан не после SHOW MASTER STATUS, возможна рассинхронизация; всегда создавайте дамп при заблокированных таблицах или используйте Percona XtraBackup для горячих бэков.

Важно: исследуйте поля Last_IO_Error и Last_SQL_Error в SHOW SLAVE STATUS\G — они содержат детальные подсказки.

Безопасность и лучшие практики

Ограничьте доступ к порту MySQL в фаерволе — разрешите подключение к 3306 только с IP мастера/слейвов.
Приватные ключи (server-key.pem, client-key.pem, ca-key.pem) никогда не размещайте в публично доступных каталогах. Для работы демона mysql используйте ключи с правами 600 и владельцем root или mysql, в зависимости от политики вашей системы.
По возможности используйте отдельный пользователь репликации с минимальными привилегиями: только REPLICATION SLAVE.
Рассмотрите шифрование бэкапов и защиту доступа к архивам дампов.
План тестирования: настройте регулярный мониторинг SHOW SLAVE STATUS и оповещения при перестановке репликации.

Альтернативные подходы и когда SSL может не подойти

VPN между серверами (например, IPsec или OpenVPN). Плюсы: прозрачность для приложений, шифрование всего трафика. Минусы: добавляет сложность сетевой топологии.
TLS/SSL, встроенный в MySQL (описанный способ) — проще в настройке для репликации, но требует управления сертификатами.
SSH-туннель для MySQL-портов — полезно в экстренных ситуациях, но менее устойчив и сложен в автоматизации.

Когда SSL не помогает:

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

Руководство по откату и аварийные шаги

Если после настройки репликации слейв получает некорректные данные или ломается, остановите слейв:

STOP SLAVE;

Если нужно пересоздать слейв: удалите базу на слейве, заново импортируйте свежий дамп, затем выполните CHANGE MASTER TO с новой позицией/файлом.
При потере ключа CA пересоздайте новую CA и выпустите новые сертификаты для всех узлов; затем замените файлы на мастере и слейвах и перезапустите MySQL.

Чек-лист ролей и шаги по внедрению

Администратор (подготовка инфраструктуры):

Убедиться, что сервера доступны по сети и открыт порт 3306 только между нужными адресами.
Установить пакеты mysql-server, mysql-client, openssl.

Инженер по безопасности:

Сгенерировать CA и ключи, утвердить политику хранения ключей.
Обеспечить права доступа (chmod 600) и резервное копирование приватных ключей.

Оператор базы данных:

Настроить /etc/mysql/my.cnf для мастера и слейва (server-id, log_bin и т.д.).
Создать пользователя репликации с минимальными привилегиями.
Выполнить заморозку базы, экспорт, импорт и запуск репликации.

Модель принятия решений и сценарии развития

Если вы планируете масштабировать чтение, репликация с SSL — хорошее решение для горизонтального масштабирования чтения.
Для полного шифрования всех сетевых каналов рассмотрите настройку VPN и использование require_secure_transport (в новых версиях MySQL) для обязательного TLS.

Совместимость и миграция с Debian Lenny и MySQL 5.x

Debian Lenny и MySQL 5.x имеют ограниченный набор современных опций безопасности, поэтому:

Проверьте документацию вашей версии MySQL перед применением опций (np. require_secure_transport встречается в более новых релизах).
При миграции на более новые дистрибутивы и версии MySQL/Percona/MariaDB, проверьте опции ssl-сертификации и способ задания ssl-файлов.

Контрольные тесты и приёмка

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

SHOW SLAVE STATUS\G показывает Slave_IO_Running: Yes и Slave_SQL_Running: Yes.
В логах мастера и слейва отсутствуют ошибки по SSL и по аутентификации.
Трафик репликации шифруется (можно проверить через tcpdump — в зашифрованном виде).
Права на приватные ключи установлены как минимум 600.

Часто встречающиеся команды-справка

Проверка переменных SSL на сервере:

mysql -u root -p -e "show variables like '%ssl%';"

Проверка статуса мастера (на мастере):

mysql -u root -p -e "SHOW MASTER STATUS;"

Проверка статуса слейва (на слейве):

mysql -u root -p -e "SHOW SLAVE STATUS\G"

Остановка/запуск слейва:

mysql -u root -p -e "STOP SLAVE;"
mysql -u root -p -e "START SLAVE;"

Дополнительные рекомендации

Автоматизируйте мониторинг репликации через инструменты метрик (Zabbix, Prometheus, Nagios) с триггерами по полям Slave_IO_Running/Slave_SQL_Running и задержке репликации (Seconds_Behind_Master).
Регулярно тестируйте восстановление из дампа и процедуру пересоздания слейва — репликация не заменяет регулярные тестовые восстановления.

Краткое резюме

Включите SSL в my.cnf, сгенерируйте CA/сертификаты и расставьте их на мастер/слейв.
Настройте бинарный лог и server-id на мастере, создайте пользователя репликации с опцией REQUIRE SSL при желании.
Заблокируйте таблицы, создайте и передайте дамп, затем на слейве выполните CHANGE MASTER TO … MASTER_SSL=1 и START SLAVE.
Проверяйте SHOW SLAVE STATUS\G и журналы на предмет ошибок; обеспечьте безопасность ключей и сетевого доступа.

Важно: перед массовым применением тестируйте процесс на стенде и документируйте все изменения.