Описание

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

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

Объем памяти

MyISAM используется в БД eltex_alert и syslog. InnoDB во всех остальных БД, включая высоконагруженную таблицу radius.radacct

После установки БД необходимо сконфигурировать объем  памяти, выделяемый для служб MyISAM и InnoDB (/etc/mysql/conf.d/eltex-ems.cnf  параметры key_buffer_size и innodb_buffer_pool_size соответственно).
Рекомендации по настройке:

• При установке БД на выделенном хосте, рекомендуемый объем памяти 30-40% от общей RAM для каждой из служб.

• При установке всех сервисов на один хост объем памяти, выделяемый для каждой из служб, рассчитывается исходя из того, что основному Java приложению (eltex-ems), веб-серверу tomcat6, NBI, ЛК, eltex-apb, так же требуются большие объемы виртуальной памяти. Настраиваются отдельно для каждой из служб в соответствующих конфигурационных файлах.

• Объем памяти, выделяемый  для службы InnoDB, может быть 2-3 раза больше, чем для MyISAM в том случае, если не планируется собирать syslog с большого количества устройств.

Например, при установке всех служб SoftWLC на один хост, выделены следующие объемы:

Для сервера на 16G:

- key_buffer_size = 2G
- innodb_buffer_pool_size = 4G

Для сервера на 64G:

- key_buffer_size=8G
- innodb_buffer_pool_size=24G

 После изменения конфигурационного файла файле /etc/mysql/conf.d/eltex-ems.cnf  перезапустите MySQL командой:

service mysql restart

Примечание! Параметры key_buffer_size и innodb_buffer_pool_size могут быть изменены в любое время на основании показателей производительности машины. Смена значений влияет на скорость обработки данных во время работы службы MySQL и не может привести к потере уже сохраненных данных или смене структуры хранениях их на файловой системе.

Настройка репликации 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. Для каждого сервера должна быть создана отдельная запись.

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

Анализ работы сервера mysql c помощью MySQLTuner

MySQLTuner — это perl-скрипт, который анализирует статистику работы MySQL и выдает свои рекомендации по оптимизации настроек MySQL сервера.
 Примечание! Для корректного анализа и сбора статистики сервер MySQL должен проработать некоторое время без смены параметров конфигурации и без перезагрузок, по рекомендации самого MySQLTuner не менее 24 часов.

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

/usr/lib/eltex-ems-db/tools/mysqltuner.pl -- user root --pass root

 После запуска скрипт произведет анализ и выдаст следующую информацию:
>> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.43-0ubuntu0.14.04.1-log
[OK] Operating on 64-bit architecture
 
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive BDB -Federated +InnoDB -ISAM -NDBCluster
[-] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in InnoDB tables: 3G (Tables: 39)
[--] Data in MyISAM tables: 22G (Tables: 5)
[!!] Total fragmented tables: 10
 
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 127d 22h 20m 14s (746M q [67.539 qps], 25M conn, TX: 302B, RX: 411B)
[--] Reads / Writes: 21% / 79%
[--] Total buffers: 48.1G global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 48.5G (77% of installed RAM)
[OK] Slow queries: 0% (13K/746M)
[!!] Highest connection usage: 100% (152/151)
[OK] Key buffer size / total MyISAM indexes: 24.0G/9.9G
[OK] Key buffer hit rate: 99.9% (4B cached / 5M reads)
[OK] Query cache efficiency: 33.6% (114M cached / 340M selects)
[!!] Query cache prunes per day: 735211
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 5M sorts)
[OK] Temporary tables created on disk: 0% (3K on disk / 394K total)
[OK] Thread cache hit rate: 99% (8K created / 25M connections)
[!!] Table cache hit rate: 8% (324 open / 3K opened)
[OK] Open file limit used: 19% (3K/16K)
[OK] Table locks acquired immediately: 99% (9B immediate / 9B locks)
[OK] InnoDB data size / buffer pool: 3.6G/24.0G
 
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Reduce or eliminate persistent connections to reduce connection usage
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
max_connections (> 151)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 16M)
table_cache (> 400)

Особое внимание стоит уделить строкам, помеченным символами [!!], параметру "Maximum possible memory usage" (он должен быть меньше общего объёма RAM) и секции Recommendations.