MySQL

Описание

Реляционная СУБД, используемая в проекте SoftWLC для хранения основных данных, необходимых для работы системы управления. Используется версия mysql-server-5.5.

В проекте используются следующие базы данных:

eltex_ems - содержит данные о доменной структуре, дереве объектов, пользователях EMS, конфигурационные данные  EMS и др. База создается/обновляется при установке пакета eltex-ems-db.

eltex_alert - содержит журнал событий EMS и устройств. База создается/обновляется при установке пакета eltex-ems-db.

wireless - хранятся настройки SSID. База создается/обновляется при установке пакета eltex-ems-db.

eltex_auth_service - база, используемая пакетом eltex-auth-service для хранения данных о пользователях ЛК, конструктора порталов и NBI. База создается/обновляется при установке пакета eltex-auth-service-db.

radius - в этой базе хранятся данные о Wi-Fi пользователях, их учетные записи настройки и аккаунттинг. База создается/обновляется при установке пакета freeradius-eltex-db.

ELTEX_PORTAL - содержит данные о виртуальных порталах, используемых в проекте. База создается/обновляется при установке пакета eltex-portal-mysql.

payments - здесь хранятся данные о платежах Wi-Fi пользователей за подключение к платным тарифам. База создается/обновляется при установке пакета eltex-portal-mysql.

Способ запуска/остановки

Сервис mysql запускается автоматически при старте сервера.

Для остановки сервиса используется команда:

service mysql stop

Для запуска сервиса после остановки используется команда:

service mysql start

Для проверки — запущен ли сервис в данный момент или нет, используется команда:

service mysql status

В ответ последует сообщение:

mysql start/running, process <pid>

в случае если сервис запущен (где <pid> - это номер процесса),

или

mysql stop/waiting

в случае если сервис не запущен.

Конфигурация

Настройка MySQL осуществляется при помощи конфигурационных фалойв /etc/mysql/my.cnf (дефолтный файл,устанавливается вместе с пакетом mysql-server) и /etc/mysql/conf.d/eltex-ems.cnf (содержит дополнительную конфигурацию, важную для работы системы управления,устанавливается вместе с пакетом eltex-ems-db).

В зависимости от предполагаемого количества используемых точек доступа, необходимо указать максимальное количество коннектов к БД. За это отвечает параметр max_connections. По умолчанию он прописан в файле /etc/mysql/my.cnf, но закомментирован. Раскомментируйте его и пропишите необходимое значение, например так:

max_connections        = 500

Это значение можно выбрать из таблицы Расчет количества коннектов к БД (нижняя строка)

В файле /etc/mysql/conf.d/eltex-ems.cnf нужно установить размеры буферов для таблиц MyISAM и Innodb. В сумме они не должны превышать 70-80% от общей памяти сервера (если на сервере установлена только база данных, если есть и другие службы, то необходимо оставить память и для их работы). При этом на Innodb нужно выделить 60-70 % от общей суммы.  Например, для сервера с 32 Гб оперативной памяти распределение получилось следующим:

key_buffer_size=6G

innodb_buffer_pool_size=22G

Настройка репликации MySQL

Резервирование данных, хранящихся в таблице СУБД MySQL, осуществляется путём встречной репликации по принципу master-master (ведущий-ведущий). При такой схеме работы все изменения в БД на одном хосте в реальном режиме времени транслируются на второй хост. При этом каждый экземпляр сервиса Eltex.EMS работает по общему виртуальному адресу с одним из экземпляров СУБД (http://dev.mysql.com/doc/refman/5.5/en/replication.html). Это позволяет получить актуальную копию БД на двух хостах одновременно. При разрыве связи изменения накапливаются, после восстановления происходит синхронизация.

1. Настройка конфигурации

В секции [mysqld] файла конфигурации /etc/mysql/my.cnf произвести следующие изменения:

Закомментировать либо удалить строку:

bind-address = 127.0.0.1

Указать server-id. Для серверов необходимо задать разные идентификаторы, к примеру, для первого:

server-id = 1

для второго:

server-id = 2

Включить бинарные логи:

log_bin = /var/log/mysql/mysql-bin.log;

указать параметры auto_increment_increment (шаг приращения) и auto_increment_offset (стартовую точку).

Для первого сервера:

auto_increment_increment= 2
auto_increment_offset = 1

Для второго сервера:

auto_increment_increment= 2
auto_increment_offset = 2

Указать базы, для которых будут вестись логи:

binlog-do-db = eltex_alert
binlog-do-db = eltex_ems
binlog-do-db = wireless
binlog-do-db = radius
binlog-do-db = eltex_auth_service
binlog-do-db = ELTEX_PORTAL
binlog-do-db = payments

Указать базы, для которых не будут вестись логи:

binlog-ignore-db = mysql
binlog-ignore-db = Syslog
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema

 Перезапустить сервис mysql на каждом сервер и создать БД для репликации.

2. Создание учетных записей для репликации

Создать учетную запись для репликации на первом сервере:

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'<ip_server2>' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Создать учетную запись для репликации на втором сервере:

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'<ip_server1>' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

3. Запуск slave репликации на втором сервере

3.1 На первом сервере в консоли MySQL выполнить команду show master status и проанализировать полученные значения:

show master status \G

Ожидать ответ вида:

mysql> show master status \G
*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 00000107
    Binlog_Do_DB: eltex_alert,eltex_ems,eltex_ont,radius,wireless,eltex_auth_service,payments,ELTEX_PORTAL
Binlog_Ignore_DB: mysql,Syslog,performance_schema,information_schema
1 row in set (0.00 sec)

Запомнить параметры File и Position.

3.2 Настроить и запустить репликацию второго сервера с первого (выполнить действия на втором сервере):

mysql>  CHANGE MASTER TO MASTER_HOST='<ip_server1>', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
START SLAVE;

MASTER_LOG_FILE='mysql-bin.000001' – указать значение File, полученное в предыдущем пункте.

MASTER_LOG_POS=107 – указать значение Position, полученное в предыдущем пункте.

3.3 Проверить состояние репликации:

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: <ip_server1>
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.001783
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 107
        Relay_Master_Log_File: mysql-bin.001783
             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: 107
              Relay_Log_Space: 107
              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: 2
1 row in set (0.00 sec)

Если параметры Slave_IO_Running и Slave_SQL_Running имеют значение «Yes», репликация успешно запустилась.

3.4 На первом сервере необходимо создать таблицу в БД и наполнить ее данными либо добавить дамп.

use eltex_ems;
create table test1 (mid int(11) auto_increment, PRIMARY KEY (mid)) Engine=MyISAM;

3.4 Проверить на втором сервере, что новая таблица скопирована.

4. Запуск репликации на первом сервере

4.1 На втором сервере выполнить команду show master status:

show master status \G

Ожидать ответ вида:

mysql> show master status \G
*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 00000107
    Binlog_Do_DB: eltex_alert,eltex_ems,eltex_ont,radius,wireless,eltex_auth_service,payments,ELTEX_PORTAL
Binlog_Ignore_DB: mysql,Syslog,performance_schema,information_schema
1 row in set (0.00 sec)

4.2 Настроить и запустить репликацию первого сервера со второго:

CHANGE MASTER TO MASTER_HOST='<ip_server2>', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
START SLAVE;

4.3 Проверить состояние репликации:

show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: <ip_server2>
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 107
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

(пример вывода приведен не полностью ввиду того, что остальные данные не так важны)

Если значения верны, репликация выполняется в обе стороны. Далее следует наполнить БД реальными данными.

5. Скрипт для запуска репликации на серверах

Вместо того, чтобы прописывать все позиции вручную, можно использовать скрипт:

#!/bin/bash
REMOTE_HOST='<IP встречного сервера>'
LOGIN='<логин для рутового доступа на локальный и удаленный сервер>'
PASS='<пароль для рутового доступа на локальный и удаленный сервер>'

REMOTE_FILE=`mysql --host=$REMOTE_HOST --user=$LOGIN --password=$PASS -e "show master status \G" | grep "File" | awk '{print $2}'`
REMOTE_POS=`mysql --host=$REMOTE_HOST --user=$LOGIN --password=$PASS -e "show master status \G" | grep "Position" | awk '{print $2}'`

echo REMOTE_FILE=$REMOTE_FILE
echo REMOTE_POS=$REMOTE_POS

mysql --user=$LOGIN --password=$PASS -e "stop slave"
mysql --user=$LOGIN --password=$PASS -e "CHANGE MASTER TO MASTER_HOST='$REMOTE_HOST', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE='$REMOTE_FILE', MASTER_LOG_POS=$REMOTE_POS;"
mysql --user=$LOGIN --password=$PASS -e "start slave"

Для того чтобы скрипт работал необходимо, чтобы в mysql были созданны одинаковые учетные записи с удаленным и локальным рутовым доступом.

Автоматическое восстановление репликации при потере связи между серверами

Необходимо создать файл /etc/keepalived/revive_mysql_replication.sh на обоих серверах с содержимым:

#!/bin/bash
LOCAL_FILE=`mysql --user=root --password=root -e "show slave status \G" | grep" Master_Log_File" | awk ' {print $2}'`
REMOTE_FILE=`mysql --host=<IP адрес встречного сервера> --user=root --password=root -e "show master status \G" | grep "File" | awk '{print $2}'`

if [ $LOCAL_FILE != $REMOTE_FILE ]
    then
        mysql --user=root --password=root -e "stop slave"
        mysql --user=root --password=root -e "start slave"
fi

где

host=172.16.110.3 – адрес встречного сервера.

Добавить в cron выполнение скрипта каждую минуту:

crontab -l | { cat; echo "*/1 * * * * /etc/keepalived/revive_mysql_replication.sh"; } | crontab

Добавить пользователей на первом сервере (указать IP-адрес встречного сервера):

GRANT ALL PRIVILEGES ON *.* TO 'root'@'<ip_server2>' IDENTIFIED BY 'root';
FLUSH PRIVILEGES;

И на втором сервере:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'<ip_server1>' IDENTIFIED BY 'root';
FLUSH PRIVILEGES;

Расчет количества коннектов к БД в зависимости от нагруженности серверов