Как сделать MySQL (Master-Slave) репликацию в RHEL, CentOS, Fedora

Репликация базы данных – это синхронное\асинхронное копирование данных с мастера на слейв, в нашем случае это не совсем данные, а бинарные логи ведущиеся на сервере. Вариантов репликации бывает много, но чаще всего используется именно Master-Slave который позволяет решать популярную для баз данных проблему чтения данных.

Стоит помнить, что репликация это масштарбированное решение, если одного слейв-сервера становится недостаточно, добавляют второй, третий и т.д. Репликация может использоваться для бэкапа данных базы данных, изолирования нагрузки, балансирования нагрузки между слейвами, отказоустойчивости. Мы рассмотрим установку MySQL базы данных на RHEL, CentOS, Fedora сервера с дальнешем созданием реплики базы данных сервера на слейв.

Установка MySQL базы данных в CentOS

Вся система выглядит следующим образом:

– Три сервера CentOS 6.5 (lab1, lab2)
– MySQL сервер (мастер) – lab1 172.17.17.211
– MySQL сервер (слейв) – lab2 172.17.17.213
– MySQL сервер (мастер) разрешает соединения на порт 3306

Начиниаем установку и стандартную конфигурацию на всех серверах, установка дублируется для обоих мастера и слейва и представляет собой стандартную процедуру:

yum -y install mysql-server
yum -y install mysql-server

Добавляем в автозагрузку:

chkconfig mysqld on
chkconfig mysqld on

Запускаем MySQL:

/etc/init.d/mysqld start
/etc/init.d/mysqld start

Запускаем скрипт который поможет сделать начальную конфигурацию, задать пароль и прочее:

mysql_secure_installation
mysql_secure_installation

Настройка MySQL Master Server

Открываем файл my.cnf:

vim /etc/my.cnf
vim /etc/my.cnf

В секцию [mysqld] добавьте:

#Replication
server-id = 1
bind-address = 172.17.17.211
binlog-do-db = linuxspa_linuxspa
 
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin
#Replication
server-id = 1
bind-address = 172.17.17.211
binlog-do-db = linuxspa_linuxspa

relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin

где:
server-id – некий уникальный id
bind-address – реальный адрес сервера
binlog-do-db – БД которую мы будем бэкапить
log-bin – собственно бинарный лог
Это наиболее важные параметры для успешной репликации.

Перезапустите БД:

/etc/init.d/mysqld restart
/etc/init.d/mysqld restart

Зайдите в БД под рутом и создайте пользователя slave_user:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'your_password';
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+-------------------+------------------+
| File             | Position | Binlog_Do_DB      | Binlog_Ignore_DB |
+------------------+----------+-------------------+------------------+
| mysql-bin.000011 |      468 | linuxspa_linuxspa |                  |
+------------------+----------+-------------------+------------------+
1 row in set (0.00 sec)
mysql> quit;
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'your_password';
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+-------------------+------------------+
| File             | Position | Binlog_Do_DB      | Binlog_Ignore_DB |
+------------------+----------+-------------------+------------------+
| mysql-bin.000011 |      468 | linuxspa_linuxspa |                  |
+------------------+----------+-------------------+------------------+
1 row in set (0.00 sec)
mysql> quit;

Нужно записать данные столбцов File mysql-bin.000011 (это название бинарного лога) и Position 468 (а это позиция в логе), они нам пригодятся.

Делаем дамп БД linuxspa_linuxspa:

mysqldump -u root -p --opt linuxspa_linuxspa > linuxspa_linuxspa.sql
mysqldump -u root -p --opt linuxspa_linuxspa > linuxspa_linuxspa.sql

Теперь можно разлочить таблицы:

mysql> UNLOCK TABLES;
mysql> quit;
mysql> UNLOCK TABLES;
mysql> quit;

Посмотреть список БД на сервере:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linuxspa_linuxspa  |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linuxspa_linuxspa  |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

Загружаем дамп на слейв:

scp linuxspa_linuxspa.sql root@172.17.17.213:/root/
scp linuxspa_linuxspa.sql [email protected]:/root/

Настойка MySQL Slave Server

Открываем my.cnf файл

vi /etc/my.cnf
vi /etc/my.cnf

Туда записываем в секцию [mysqld]:

#Replication
server-id = 2
binlog-do-db = linuxspa_linuxspa
 
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin
master-connect-retry = 60
#Replication
server-id = 2
binlog-do-db = linuxspa_linuxspa

relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin
master-connect-retry = 60

Импортируем дамп и рестартуем БД:

mysql -u root -p < /root/linuxspa_linuxspa.sql
/etc/init.d/mysqld restart
mysql -u root -p < /root/linuxspa_linuxspa.sql
/etc/init.d/mysqld restart

Настраиваем слейв одним длинным запросом, где MASTER_LOG_FILE это значение которое мы извлекли до этого из команды show master status, так-же важно указать позицию MASTER_LOG_POS верно:

mysql> CHANGE MASTER TO MASTER_HOST='172.17.17.211', MASTER_USER='slave_user', MASTER_PASSWORD='your_password', MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=468;
mysql> CHANGE MASTER TO MASTER_HOST='172.17.17.211', MASTER_USER='slave_user', MASTER_PASSWORD='your_password', MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=468;

Запускаем слейв:

mysql> START SLAVE;
mysql> START SLAVE;

Смотрим состояние слейва:

SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.17.211
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000011
          Read_Master_Log_Pos: 468
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 384
        Relay_Master_Log_File: mysql-bin.000011
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          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: 468
              Relay_Log_Space: 540
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)
SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.17.211
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000011
          Read_Master_Log_Pos: 468
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 384
        Relay_Master_Log_File: mysql-bin.000011
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          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: 468
              Relay_Log_Space: 540
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

В примере описанном выше будет производится репликация базы данных linuxspa_linuxspa с Master Server (lab1 172.17.17.211) на Slave Server (lab2 172.17.17.213). Только одна база данных будет реплицироваться.

Проверить работает ли репликация или нет, можно следующим образом, идем на Master Server (lab1 172.17.17.211) заходим в базу данных, и смотрим список существующих таблиц в базе данных:

mysql -u root -p
mysql -u root -p

Смотрим список баз данных, и выбираем необходимую:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linuxspa_linuxspa  |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
 
mysql> use linuxspa_linuxspa;
Database changed
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linuxspa_linuxspa  |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

mysql> use linuxspa_linuxspa;
Database changed

Смотрим список таблиц:

mysql> show tables;
+-----------------------------+
| Tables_in_linuxspa_linuxspa |
+-----------------------------+
| wp_commentmeta              |
| wp_comments                 |
| wp_easy_captcha_sessions    |
| wp_last_logins              |
| wp_links                    |
| wp_myStat_data              |
| wp_myStat_dbsize            |
| wp_myStat_main              |
| wp_nstatistics              |
| wp_nstatistics_arhive       |
| wp_nstatistics_bots         |
| wp_nstatistics_log          |
| wp_nstatistics_pages        |
| wp_nstatistics_refer        |
| wp_options                  |
| wp_pollsa                   |
| wp_pollsip                  |
| wp_pollsq                   |
| wp_postmeta                 |
| wp_posts                    |
| wp_simpleviews              |
| wp_statistics_exclusions    |
| wp_statistics_useronline    |
| wp_statistics_visit         |
| wp_statistics_visitor       |
| wp_sticky                   |
| wp_term_relationships       |
| wp_term_taxonomy            |
| wp_terms                    |
| wp_usermeta                 |
| wp_users                    |
| wp_vc_data                  |
+-----------------------------+
32 rows in set (0.00 sec)
mysql> show tables;
+-----------------------------+
| Tables_in_linuxspa_linuxspa |
+-----------------------------+
| wp_commentmeta              |
| wp_comments                 |
| wp_easy_captcha_sessions    |
| wp_last_logins              |
| wp_links                    |
| wp_myStat_data              |
| wp_myStat_dbsize            |
| wp_myStat_main              |
| wp_nstatistics              |
| wp_nstatistics_arhive       |
| wp_nstatistics_bots         |
| wp_nstatistics_log          |
| wp_nstatistics_pages        |
| wp_nstatistics_refer        |
| wp_options                  |
| wp_pollsa                   |
| wp_pollsip                  |
| wp_pollsq                   |
| wp_postmeta                 |
| wp_posts                    |
| wp_simpleviews              |
| wp_statistics_exclusions    |
| wp_statistics_useronline    |
| wp_statistics_visit         |
| wp_statistics_visitor       |
| wp_sticky                   |
| wp_term_relationships       |
| wp_term_taxonomy            |
| wp_terms                    |
| wp_usermeta                 |
| wp_users                    |
| wp_vc_data                  |
+-----------------------------+
32 rows in set (0.00 sec)

И удаляем на выбор три таблицы, например: wp_vc_data, wp_users, wp_usermeta.

mysql> drop tables wp_vc_data;
Query OK, 0 rows affected (0.00 sec)
 
mysql> drop table wp_users;
Query OK, 0 rows affected (0.00 sec)
 
mysql> drop table wp_usermeta;
Query OK, 0 rows affected (0.00 sec)
mysql> drop tables wp_vc_data;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table wp_users;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table wp_usermeta;
Query OK, 0 rows affected (0.00 sec)

Если снова посмотрим на вывод show tables, то этих таблиц вы больше не найдете. Теперь идем на Slave Server (lab2 172.17.17.213) и смотрим (по аналогии с мастером) удалились ли эти таблицы на слейве, в моем случае таблицы исчезли и со слейва.