Описание

Реляционная СУБД, используемая в проекте 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;

Дополнительные учетные записи пользователей баз данных

Сбор rsyslog

Для сбора логов с серверов DHCPи RADIUS в базе данных должна быть создана учетная запись пользователя rsyslog.

Команды:

CREATE USER 'rsyslog'@'HOST' identified by 'root';
GRANT ALL on Syslog.* TO 'rsyslog'@'HOST' identified by 'root';

где  HOST – адрес серверов RADIUS или DHCP в сети управления. Для каждого сервера должна быть создана отдельная запись.

Проверка репликации из GUI EMS

Для того чтобы сервер EMS имел возможность проверить состояние репликации, нужны дополнительные учетные записи, создать их можно из консоли MySQL:

команды:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'HOST' IDENTIFIED BY 'root';
FLUSH PRIVILEGES;

где HOST – адрес серверов EMS в сети VRRP. Для каждого сервера должна быть создана отдельная запись.

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