How To настроки репликации в MySQL с использованием шифрования SSL на Debian Lenny

http://howtoforge.com/how-to-set-up-mysql-database-replication-with-ssl-encryption-on-debian-lenny
  • Перевод
Это руководство описывает, как настроить репликацию базы данных в MySQL с использованием SSL соединение для шифрования.
MySQL репликация синхронизирует базу данных, что позволяет иметь точную копию БД на другом сервере. Все обновления БД на главном сервере автоматически реплицируются на другой сервер, что позволяет защитить базу от аппаратных сбоев. В этой статье будет показано, как реализовать репликации БД exampledb с сервера server1.example.com(ip адресом 192.168.0.100) на сервер server2.example.com(ip адресом 192.168.0.101) с использованием SSL соединения

Оба сервера работают на Debian Lenny но конфигурация может применяться практически на всех дистрибутивах без изменений. БД exampledb с таблицами и данными уже существующих только на главном. Все команды выполняются с привилегиями пользователя root.
Если на обоих серверах не установлен сервер MySQL то приступим к инсталляции, выполнив на главном и на вторичном сервере команду:
aptitude install mysql-server mysql-client

Вам будет предложено ввести пароль root для MySQL, как на главном так и на вторичном сервере.
Теперь проверим поддержку соединения по протоколу SSL с MySQL. Давайте войдем в MySQL и введем команду в командной строке MySQL:
mysql -u root -p

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>quit;

Если вывод выглядит следующим образом то это означает что MySQL был скомпилирован с поддежкой SSL, но у нас как видно из таблици have_openssl DISABLED и have_ssl DISABLED не активен.

Что бы включить поддержку SSL нужно отредактировать файл my.cnf который расположен в /etc/mysql/
vi /etc/mysql/my.cnf

Найдите строки * Security Features и добавьте строку ssl
[...]
# * 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%';

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;

Вывод показывает что ssl включен.
Далее настроить MySQL для прослушивался на всех интерфейсах, закоментировав строчку bind-address = 127.0.0.1 в /etc/mysql/my.cnf:
server1:
vi /etc/mysql/my.cnf

[...]
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
[...]

рестар MySQL:
/etc/init.d/mysql restart

Теперь посмотрим что висит у нас на портах:
netstat -tap | grep mysql

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

Видимо что MySQL транслируется на всех интерфейсах.
Теперь нам необходимо создать CA, серверов и клиентских сертификатов, для SSL соединений. Я создаю их обычно в каталоге /etc/mysql/newcerts
Создадим каталог newcerts:
mkdir /etc/mysql/newcerts && cd /etc/mysql/newcerts

Убедимся что OpenSSL унас установлен:
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#

Теперь мы должны передать сертификаты ca-cert.pem, client-cert.pem и client-key.pem на второй сервер. Создадим директорию на втором сервере:
server2:
mkdir /etc/mysql/newcerts

Вернемся на server1 и передадим сертификаты следующим образом:
server1:
scp /etc/mysql/newcerts/ca-cert.pem root@192.168.0.101:/etc/mysql/newcerts
scp /etc/mysql/newcerts/client-cert.pem root@192.168.0.101:/etc/mysql/newcerts
scp /etc/mysql/newcerts/client-key.pem root@192.168.0.101:/etc/mysql/newcerts

Далее откроем на server1 /etc/mysql/my.cnf и внесем изменения в область * Security Features раскоментировав строки ssl-ca, ssl-cert и ssl-key:
vi /etc/mysql/my.cnf

[...]
# * 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/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

Теперь мы создадим slave_user пользователя репликации, который будет использоваться на server2, для доступа к базе данных на server1
mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password' REQUIRE SSL;

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

(Если пользователь репликации уже создан и нужно указать только SSL соединение то нужно изменить пользователя следующим образом:
GRANT USAGE ON *.* TO 'slave_user'@'%' REQUIRE SSL;

)
FLUSH PRIVILEGES;
quit;

Кроме того, мы должны MySQL указать где хранить наши логи для репликации, а также указать какой сервер является главным, а какой репликационным:
vi /etc/mysql/my.cnf

[...]
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
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

Далее нам нужно провести несколько операций:
1. Заблокировать базу exampledb на server1
2. Узнать master status server1
3. Создать дамп SQL exampledb (для импорта на server2)
4. Разблокировать нашу базу
mysql -u root -p
USE exampledb;
FLUSH TABLES WITH READ LOCK;

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(т.к. блокировка бд будет удалена) мы сделаем бекап и перенесем его на server2
server1:
cd /tmp
mysqldump -u root -pyourrootsqlpassword --opt exampledb > snapshot.sql
scp snapshot.sql root@192.168.0.101:/tmp

После проделаной операции можно смело разблокировать нашу БД
server1:
UNLOCK TABLES;
quit;

На этом настройка server1 завершена. Приступим к настройке server2 открыв конфиг мускуля:
server2:
vi /etc/mysql/my.cnf

убедимся что у нас есть следующие настройки строки(если их нет то добавим):
[...]
server-id=2
master-connect-retry=60
replicate-do-db=exampledb
[...]

Значение server-id=2 уникальное и оно должно отличатся от значения которое на server1

Перезапустим MySQL:
/etc/init.d/mysql restart

Прежде чем начать настройку репликации создадим пустую БД:
mysql -u root -p

CREATE DATABASE exampledb;
quit;

Теперь можно импортировать на server2 дамп базы snapshot.sql
/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword stop-slave
cd /tmp
mysql -u root -pyourrootsqlpassword exampledb < snapshot.sql

Заходим в MySQL и выполним команду, что бы server2 был вторичным сервером, а server1 определялся как первычный сервер:
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';

* MASTER_HOST — ip адрес или имя хоста в нашем случае это ip
* MASTER_USER — пользователь репликации для первичного сервера
* MASTER_PASSWORD — Пароль пользователя
* MASTER_LOG_FILE — Значение Лог файла на server1 которое мы узнали выполним команду SHOW MASTER STATUS;
* MASTER_LOG_POS — Значение полученное при выполнении команды SHOW MASTER STATUS;
* MASTER_SSL — Создает соединение главного и вторичного сервера с использованием SSL
* MASTER_SSL_CA — Путь к сертификату CA (server2)
* MASTER_SSL_CERT — Путь к сертификату client-cert.pem (server2)
* MASTER_SSL_KEY — Путь к сертификату client-key.pem (server2)

И наконец
START SLAVE;

Теперь проверим статус server2
SHOW SLAVE STATUS \G

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.0.100
                Master_User: slave_user
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 98
             Relay_Log_File: mysqld-relay-bin.000002
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: exampledb
        Replicate_Ignore_DB:
         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: 98
            Relay_Log_Space: 235
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: Yes
         Master_SSL_CA_File: /etc/mysql/newcerts/ca-cert.pem
         Master_SSL_CA_Path:
            Master_SSL_Cert: /etc/mysql/newcerts/client-cert.pem
          Master_SSL_Cipher:
             Master_SSL_Key: /etc/mysql/newcerts/client-key.pem
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

mysql>

После этого можно выйти с MySQL:
quit;

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

Большое спасибо за внимание!
P.S. Если вы нашли очепятку или не правильно сформулированное предложение сообщите пожалуйста в ЛС.
Поделиться публикацией

Похожие публикации

Комментарии 24

    +2
    а какой оверхед на шифрование, кто-нибудь тестил?
      0
      Создаем CA сертификат:
      openssl genrsa 2048 > ca-key.pem
      openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

      Далее создаем сертификат для сервера…:
      openssl genrsa 2048 > ca-key.pem
      openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem


      Это опечатка? Для сервера нужно
      openssl genrsa 2048 > server-key.pem
      openssl req -new -x509 -nodes -days 1000 -key server-key.pem > server-cert.pem
      ?
        0
        Спасибо поправил.
        –1
        В настройке репликации через шифрованное соединение было просто умилительно увидеть
        aptitude install mysql-server mysql-client

        :))))
          0
          А что не так?
            +2
            Репликации, тем более с шифрованием — это уже достаточно «продвинутые» настройки мускула, и логично было бы предположить, что если человек заинтересован темой, то базовые навыки по установке и работе с мускулом у него уже есть.
              0
              А мне 3 года назад очень кстати были туториалы по таким замысловатым темам с нуля, когда я только осваивал unix(и так получилось, что это была freebsd), и приходилось проектировать сложные системы. Когда какая-то мелочь была опущена в статье, я терялся и иногда тратил довольно много времени на поиск информации, а то и хуже — действуя по интуиции, все ломал.
                +1
                Мне это кажется не логичным — стоит разделять базу и эдвансд уровни. Но тут это мелочь, не стоящая спора.

                В любом случае спасибо за хорошую и актуальную для меня статью. )

                  +1
                  Я не автор статьи, а — ее переводчик :)
                    0
                    *script88
          0
          CentOS 5.5, MySQL 5.0.84
          Не стартует после внесения в my.cnf строк
          log_bin = /var/log/mysql/mysql-bin.log
          expire_logs_days = 10
          max_binlog_size = 100M
          binlog_do_db = exampledb


          Причем у меня была закомментированная строчка
          # Uncomment the following if you want to log updates
          #log-bin = /var/log/mysql-bin.log

          Но даже если просто раскомментировать эту строку — уже MySQL не стартует.
          В логе при этом /var/log/mysql-bin.index' not found (Errcode: 13)
          [ERROR] Aborting
          [Note] /usr/libexec/mysqld: Shutdown complete
          mysqld ended


          В чем тут может быть дело?
            0
            Попробуйте выполнить touch /var/log/mysql-bin.index.
            Директория /var/log существует?
              0
              Да, директория существует. После создания этого файла все равно не стартует, в логе
              /usr/libexec/mysqld: File '/var/log/mysql-bin.000001' not found (Errcode: 13)
              100917 4:42:52 [ERROR] Could not use /var/log/mysql-bin for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.
              100917 4:42:52 [ERROR] Aborting

              100917 4:42:52 [Note] /usr/libexec/mysqld: Shutdown complete
                0
                попробуйте оставить:
                log_bin = /var/log/mysql/mysql-bin.log
                создать папку /var/log/mysql
                и указать ей chown mysql:mysql /var/log/mysql или другие пользователь-группа под которым запускается mysqld
            –1
            Может стоит подправить заголовок. Избавится от английского оборота в предложении. Исправить ошибку.
            Да и в тексте можно повстречать несколько досадных опечаток. Запятые.…

            Ну, а так тема востребованная, скоро придётся что-то подобное делать.
              +1
              Лучше написать не «c помощью SSL», а «с использованием SSL» в заголовке.
                0
                Укажите в статье ссылку на оригинал: «How To Set Up MySQL Database Replication With SSL Encryption On Debian Lenny»
                www.howtoforge.com/how-to-set-up-mysql-database-replication-with-ssl-encryption-on-debian-lenny
                  0
                  Это статья-перевод ссылка в футере.
                  +1
                  Вот зачем блокировать, смотреть мастер-статус, делать дамп, разблокировать, года достаточно:

                  mysqldump --all-databases --master-data > all_databases.dump
                    0
                    А мне нравится.
                    Так больше понимания механизма репликации остается у читающего.
                      0
                      В репликацию не нужно лезть не понимая её работы. А тут, банальное удобство, вместо «открыли окно А, заморозили, запомнили строчки и т.п.»
                        –1
                        Ну, вы можете и не лезть.
                    0
                    Настраивал репликацию по этой статье. Всё Ok, но нарисовалась проблема с сертификатами/ключами.

                    На мастере это выглядело так:
                    SSL error: Unable to get private key from '/etc/mysql/certs/server-key.pem'
                    160413 17:21:09 [Warning] Failed to setup SSL
                    160413 17:21:09 [Warning] SSL error: Unable to get private key
                    

                    На слейве вот так:
                    SSL error: Unable to get private key from '/etc/mysql/certs/client-key.pem'
                    160414 12:08:45 [ERROR] Slave I/O: error connecting to master 'replication@mysql.server:3306' - retry-time: 60  retries: 86400, Error_code: 2026
                    

                    Фикс проблемы на стороне сервера:
                    openssl rsa -in server-key.pem -out server-key.pem
                    

                    На стороне клиента:
                    cat client-cert.pem >> ca-cert.pem
                    openssl rsa -in client-key.pem -out client-key.pem
                    

                    Вдруг кому-то поможет.

                    И небоольшая очепятка в тексте статьи:
                    Сторока REQUIRE SSL не является обязательной, если Вы оставите ее, slave_user будет разрешено подключатся через зашифрованное и не зашифрованное соединение.

                    «если Вы оставите ее» нужно заменить на «если Вы уберёте ее»
                      0
                      Подскажите, зачем вообще клиенту делать сертификаты и ключи? Вам нужна двухсторонняя аутентификация? Но ведь пользователь слейва всё равно идентифицируется по паролю. Зачем тогда? На клиенте нужно только через CHANGE MASTER TO установить MASTER_SSL_CA, чтобы клиент мог убедиться, что у сервера корректно подписанный сертификат (а для этого ему нужен корневой). Разве не так?

                      И да, стоит отметить, что проблема с запароленным ключём приводит к ошибке 'Unable to get private key from'. Об это в статье нет информации, только в комментариях.

                      Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                      Самое читаемое