Репликация в MySQL с использованием SSL

Материал из Национальной библиотеки им. Н. Э. Баумана
Последнее изменение этой страницы: 17:35, 27 июня 2018.

Репликация (англ. replication) в MySQL — механизм синхронизации содержимого нескольких копий содержимого базы данных. Репликация — это процесс, под которым понимается копирование данных из одного источника на другой (или на множество других) и наоборот.[1]

При репликации изменения, сделанные в одной копии объекта, могут быть распространены в другие копии.

Обзор

Особенности репликации в MySQL

Репликация (от лат. replico -повторяю) — это тиражирование изменений данных с главного сервера БД на одном или нескольких зависимых серверах. Главный сервер будем называть мастером, а зависимые — репликами.[2] Изменения данных, происходящие на мастере, повторяются на репликах (но не наоборот). Поэтому запросы на изменение данных (INSERT, UPDATE, DELETE и т. д.) выполняются только на мастере, а запросы на чтение данных (проще говоря, SELECT) могут выполняться как на репликах, так и на мастере. Процесс репликации на одной из реплик не влияет на работу других реплик, и практически не влияет на работу мастера.

Репликация производится при помощи бинарных логов, ведущихся на мастере. В них сохраняются все запросы, приводящие (или потенциально приводящие) к изменениям в БД (запросы сохраняются не в явном виде, поэтому если захочется их посмотреть, придется воспользоваться утилитой mysqlbinlog). Бинлоги передаются на реплики (бинлог, скачанный с мастера, называется "relay binlog ") и сохраненные запросы выполняются, начиная с определенной позиции. Важно понимать, что при репликации передаются не сами измененные данные, а только запросы, вызывающие изменения.

При репликации содержимое БД дублируется на нескольких серверах. Зачем необходимо прибегать к дублированию? Есть несколько причин:


  1. производительность и масштабируемость. Один сервер может не справляться с нагрузкой, вызываемой одновременными операциями чтения и записи в БД. Выгода от создания реплик будет тем больше, чем больше операций чтения приходится на одну операцию записи в вашей системе.
  2. отказоустойчивость. В случае отказа реплики, все запросы чтения можно безопасно перевести на мастера. Если откажет мастер, запросы записи можно перевести на реплику (после того, как мастер будет восстановлен, он может принять на себя роль реплики).
  3. резервирование данных. Реплику можно «тормознуть » на время, чтобы выполнить mysqldump, а мастер — нет.
  4. отложенные вычисления. Тяжелые и медленные SQL-запросы можно выполнять на отдельной реплике, не боясь помешать нормальной работе всей системы.

Кроме того, есть некоторые другие интересные возможности. Поскольку на реплики передаются не сами данные, а запросы, вызывающие их изменения, мы можем использовать различную структуру таблиц на мастере и репликах.

Подготовка к работе в MySQL

MySQL репликация синхронизирует базу данных, что позволяет иметь точную копию БД на другом сервере. Все обновления БД на главном сервере автоматически реплицируются на другой сервер, что позволяет защитить базу от аппаратных сбоев. В этой статье будет показано, как реализовать репликации БД exampledb с сервера server1.example.com(ip адресом 192.168.0.100) на сервер server2.example.com(ip адресом 192.168.0.101) с использованием SSL соединения.[3]

Приступая к работе

Оба сервера работают на 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:

$  d /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

убедимся что у нас есть следующие настройки строки(если их нет то добавим):

  ##g#[...]
   server-id=2
   master-connect-retry=60
   replicate-do-db=exampledb
  ##g#[...]

Значение 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>

Заключение

В данном курсовом проекте был рассмотрен практический пример как реализовать репликации БД exampledb с сервера server1 на сервер server2 с использованием SSL соединения, используя базу данных MySQL. При репликации содержимое БД дублируется на нескольких серверах. Зачем необходимо прибегать к дублированию? Есть несколько причин:

  • производительность и масштабируемость. Один сервер может не справляться с нагрузкой, вызываемой одновременными операциями чтения и записи в БД. Выгода от создания реплик будет тем больше, чем больше операций чтения приходится на одну операцию записи в вашей системе.
  • отказоустойчивость. В случае отказа реплики, все запросы чтения можно безопасно перевести на мастера. Если откажет мастер, запросы записи можно перевести на реплику (после того, как мастер будет восстановлен, он может принять на себя роль реплики).
  • резервирование данных. Реплику можно «тормознуть » на время, чтобы выполнить mysqldump, а мастер — нет.
  • отложенные вычисления. Тяжелые и медленные SQL-запросы можно выполнять на отдельной реплике, не боясь помешать нормальной работе всей системы.

Кроме того, есть некоторые другие интересные возможности. Поскольку на реплики передаются не сами данные, а запросы, вызывающие их изменения, мы можем использовать различную структуру таблиц на мастере и репликах. В частности, может отличаться тип таблицы (engine) или набор индексов.

Примечания

  1. https://habr.com/post/56702/
  2. https://habr.com/post/104412/
  3. https://www.youtube.com/watch?v=TLsOvbpXuT4