«Причесываем трафик — динамический шейпер на Linux Linux*» и «MySql»: разница между страницами

Материал из support.qbpro.ru
(Различия между страницами)
imported>Vix
Нет описания правки
 
imported>Vix
Нет описания правки
 
Строка 1: Строка 1:
Предположим у вас есть домашняя сеть (или не домашняя, а сеть небольшого офиса) с выходом в интернет через не очень скоростной канал.<br>
==Help MySql==
А пользователей — много, и каждый хочет что-то скачивать, да с максимальной скоростью. Вот тут перед нами встатет задача, как максимально эффективно распределить наш интернет-канал между пользователями так, чтобы они не мешали друг другу.<br>
* Список пользователей:
В этой статье я опишу, как можно решить такую задачу с помощью Linux-сервера.
SELECT USER from mysql.user;
* Создание пользователя:
CREATE USER 'user'@'localhost' IDENTIFIED BY 'secret';
* Удаление пользователя:
DROP USER user@localhost;
* Предоставить права пользователю:
GRANT ALL PRIVILEGES ON dbname.* TO 'user'@'localhost';
* Предоставить супер права пользователю:
GRANT ALL PRIVILEGES ON dbname.* TO 'user'@'localhost' WITH GRANT OPTION;
* Права равные root для работы через phpmyadmin
GRANT ALL PRIVILEGES ON  *.* TO 'user'@'%' REQUIRE NONE WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;


Сформулируем, что же мы хотим получить в результате:<br>
==Настройка PhpMyAdmin==
1. Чтобы канал поровну делился между пользователями.<br>
Процедура «переподключения» phpMyAdmin на работу с внешним сервером достаточно проста, но вызывает вопросы у тех, кто занимается всем этим недавно или неглубоко.
2. Чтобы канал зря не простаивал.<br>
3. Чтобы онлайн-игры, ssh и telnet не «лагали» даже при полной загрузке канала, например торрентами.<br>


В данной статье мы рассмотрим несколько вариантов подключения phpMyAdmin ко внешнему серверу, так как задачи и условия у всех разные. В примере я использую сервер баз данных MariaDB, ОС Ubuntu и phpMyAdmin версии 4.5.4, что практически не важно, так как всё достаточно просто. PhpMyAdmin установлен на домашний ПК, но с тем же успехом он может быть настроен и на удалённом сервере. Имеется ввиду, что всё прикладное ПО, такое как непосредственно сам сервер БД, http-сервер и php у вас уже установлено.


Если интернетом будут одновременно пользоваться 10 пользователей — каждый получит в свое распоряжение 1/10 часть канала, если в данный момент активен только один пользователь — он будет использовать весь канал сам.<br>
* Пример первый (доступ с паролем).
Добиться этого можно используя планировщик пакетов HTB, который входит в ядро linux начиная с версии 2.4.20.<br>
<br>
Можно конфигурировать шейпер с помощью команды tc, но для более удобной и наглядной настройки я рекомендую скачать скрипт [http://www.nixtech.ru/datatrans/linux/shaiper/htb.init-v0.8.5 htb.init.] Они использует для конфигурации htb набор конфигурационных файлов, именуемых так, что при сортировке по алфавиту их имена позволяют визуально представить себе дерево классов шейпера и удобно его редактировать.
* внимание - в скрипте выставите пути в соответствии с вашими настройками!<br>
Предположим, что у нас на сервере есть интерфейс eth0, через который мы подключены к интернет, и eth1, который «смотрит» в локальную сеть.<br>


Управлять можно только исходящим из интерфейса трафиком, поэтому для eth0 будут правила для upload трафика пользователей, а для — eth1 — download трафика.<br>
Сервер баз данных расположен на машине с адресом 10.10.1.10. PhpMyAdmin устанавливаем на локальный ПК с адресом 10.10.1.20. Необходимо убедиться, что сервер баз данных принимает внешние соединения. Правим my.cnf (/etc/mysql/my.cnf). В строке bind-address по умолчанию указан адрес 127.0.0.1, что запрещает серверу принимать внешние соединения. Заменяем его на 0.0.0.0, что разрешит внешние соединения с любого адреса. В целях безопасности лучше уточнить адрес, если вы не планируете подключаться к серверу из разных источников. В этом примере будет использоваться строка следующего вида:


По умолчанию конфигурационные файлы htb.init находятся в /etc/htb/. Для начала напишем правила шейпинга для upload трафика, они у нас будут простые.<br>
bind-address = 10.10.1.20
Создаем файл с именем eth0 (интерейс «смотрящий» в интернет), в него напищем следующие строки:<br>
DEFAULT=20
R2Q=1


Параметр DEFAULT задает номер класса, к которому будет относиться трафик «по умолчанию» — обычно это класс с минимальным приоритетом. Параметр R2Q влияет на работу алгоритма разделения канала и зависит от ширины канала. Я подбирал его значение эмпирическим путем, для моего исходящего канала в 2 Mbit.<br>
Сохраняем и перезагружаем.


Далее, создадим файл eth0-2.full2MBit, для класса включающего в себя весь доступный интернет-канал. Имя файла состоит из имени интерфейса и id класса, после точки идет смысловое имя класса, используется как комментарий и системой игнорируется.<br>
  sudo service mysql restart
  RATE=2Mbit
CEIL=2Mbit


RATE — это наша гарантированная полоса, CEIL — максимальная полоса. Так как у меня канал с гарантированной максимальной полосой в 2 Mbit, то эти параметры у меня равны.<br>
На ПК (или другой удалённый сервер) устанавливаем phpMyAdmin.


Теперь мы создадим по одному файлу для каждого класса трафика, который у нас будет. Я у себя создал отдельные классы для ssh трафика, а так же трафика игр World Of Warcraft и Counter Strike, хотя вы можете сделать для всего высокоприоритетного трафика один класс.<br>
sudo apt install phpmyadmin


Пример для ssh — создаем файл eth0-2:10.ssh. В имени файла через двоеточие указан id родительского класса 2 и id текущего класса — 10. Идентификаторы для класса вы можете выбирать произвольно.<br>
В процессе установки отказываемся от конфигурации (конфигурировать нечего; сервер баз данных на удалённой машине).
# class for outgoing ssh
RATE=128Kbit
CEIL=2Mbit
RULE=*:22
PRIO=1
BURST=100Kb


В параметре RATE указана гарантированная полоса для этого класса, в CEIL — максимальная. Мы выделяем для ssh 128 KBit (как минимум) и разрешаем ему загрузить весь канал (я закачивать файлы по sftp). PRIO задает приоритет класса трафика (1- максимальный, чем больше число — тем меньш приоритет). BURST задает максимальный объем трафика, который будет передан на максимальной скорости перед тем, как перейти к передаче данных из дургих классов. Установив этот параметр в достаточно высокое значение мы добиваемся того, что трафик ssh будет передан с минимальными задержками.
Далее правим конфигурационный файл phpMyAdmin config.inc.php (/etc/phpmyadmin/config.inc.php). Если это была свежая установка и на локальном хосте нет сервера баз данных, то удаляем все строки (кроме первой «<?php») и пишем следующее:
RULE задает правило, по которому будет отбираться трафик в этот класс.
Формат — RULE=[[saddr[/prefix]][:port[/mask]],][daddr[/prefix]][:port[/mask]]
Обратите внимание на запятую! RULE=*:22 обозначает трафик, у которого порт назначения 22, а RULE=*:22, обозначает трафик, у которого исходящий порт — 22.


Создадим так же классы для других видов трафика, и класс для трафика «по умолчанию» с id 20 (мы указали вначале что именно в класс номер 20 надо направлять трафик «по умолчанию»). В нем укажем используемую дисциплину разделения канала LEAF=sfq, для того чтобы upload поровну делился между TCP сессиями разных пользователей.
/* External Server #1 */
  $i++;
  $cfg['Servers'][$i]['verbose'] = 'Server Name';
  $cfg['Servers'][$i]['host'] = '10.10.1.10';
  $cfg['Servers'][$i]['connect_type'] = 'tcp';
  $cfg['Servers'][$i]['extension'] = 'mysqli';
  $cfg['Servers'][$i]['auth_type'] = 'cookie';
  $cfg['Servers'][$i]['AllowNoPassword'] = false;


Для eth1 правила будут почти такие же, только с учетом что общая ширина канала — 100 Mbit, мы ведь хотим чтобы можно было обращаться к локальным ресурсам сервера на полной скорости, для интернет-трафика выделен отдельный класс на 2 MBit, у которого как потомки добавлены классы отдельных пользователей, разделение по классам я делал по IP адресам. Для каждого пользователя можно указать максимальную и гарантированную скорость, а так же приоритет.
Вместо Server Name вписываем что угодно. Сохраняем и закрываем файл.


После правки конфигурации перезапускаем htb.init:
==Конвертирование базы из postgresql в mysql==
/etc/init.d/htb.init restart
* скачиваем [https://github.com/ChrisLundquist/pg2mysql pg2mysql]
И правила шейпинга трафика сразу же вступают в силу.


В процессе состевления правил обычно возникает необходимость как-то визуализировать трафик, в целях отладки и мониторинга, поэтому решил написать плагин для системы мониторинга серверов munin, который бы визуализировал распределение по классам HTB трафика. Выводить решил загрузку только классов-листьев дерева, так как именно они обычно несут смысловую нагрузку.
git clone https://github.com/ChrisLundquist/pg2mysql.git
Скачать плагин вы можете из официального репозитория плагинов munin, называется он qos_, просто скопируйте его в папку плагинов munin /usr/share/munin/plugins/ и в папке используемых плагинов /etc/munin/plugins сделайте на него символическую ссылку вида qos_eth1, где eth1 — имя интерфейса, на котором нужно мониторить загрузку.<br>
  cd pg2mysql
В файле конфигурации плагинов можно добавить следущее:<br>
[qos_eth1]
env.ignore_queue1_10 yes
  env.label_name1_31 Viperet
env.label_name1_32 Cornet


Параметр env.ignore_queue позволяет не отображать на графике состояние класса с указанным id, а параметр env.label_name — задать человекопонятную метку для класса на графике.
* сохраняем дамп из postgresql ('''пример сохранение всех таблиц для wordpress - wp*''')
sudo -u postgres pg_dump --quote-all-identifiers --no-acl --no-owner --table=wp_* --format p --data-only wordpress -f wordpress_pgsql.sql


В итоге должно получиться что то такое:<br>
* конвертируем в формат mysql
  /sbin/tc qdisc del dev eth1 root
  php pg2mysql_cli.php worpress_pgsql.sql wordpress_mysql.sql
/sbin/tc qdisc add dev eth1 root handle 1 htb default 20 r2q 1
 
* теперь необходимо заменить все таблицы с `"public"."wp_*"` на 'wp_*` это можно сделать в редакторе по F4
/sbin/tc qdisc del dev eth0 root
* не забудьте исправить '''"` (''' на ''''('''
/sbin/tc qdisc add dev eth0 root handle 1 htb default 20 r2q 1
* теперь меняем sql команду с INSERT на REPLACE так же по F4
* все, дамп можно заливать в базу, предварительно создайте из wordpress начальную структуру таблиц через install и потом вносите данные.
/sbin/tc qdisc del dev tun0 root
/sbin/tc qdisc add dev tun0 root handle 1 htb default 20 r2q 1
/sbin/tc class add dev eth1 parent 1: classid 1:2 htb rate 100Mbit
/sbin/tc class add dev eth1 parent 1:2 classid 1:10 htb rate 100Mbit
/sbin/tc filter add dev eth1 parent 1:0 protocol ip prio 100 u32 match ip src 192.168.0.5 classid 1:10
/sbin/tc filter add dev eth1 parent 1:0 protocol ip prio 100 u32 match ip src 192.168.0.6 classid 1:10
/sbin/tc filter add dev eth1 parent 1:0 protocol ip prio 100 u32 match ip src 192.168.0.9 classid 1:10
/sbin/tc class add dev eth1 parent 1:2 classid 1:20 htb rate 2Mbit ceil 2Mbit
/sbin/tc class add dev eth1 parent 1:20 classid 1:21 htb rate 36Kbit ceil 0.5Mbit burst 100Kb prio 1
/sbin/tc filter add dev eth1 parent 1:0 protocol ip prio 100 u32 match ip sport 22 0xffff classid 1:21
   
   
/sbin/tc class add dev eth1 parent 1:20 classid 1:30 htb rate 1000Kbit ceil 2000Kbit prio 5
/sbin/tc qdisc add dev eth1 parent 1:30 handle 30 sfq perturb 10
   
   
/sbin/tc class add dev eth1 parent 1:30 classid 1:31 htb rate 100Kbit ceil 0.5Mbit prio 5
/sbin/tc filter add dev eth1 parent 1:0 protocol ip prio 100 u32 match ip src 192.168.0.101 classid 1:31
/sbin/tc filter add dev eth1 parent 1:0 protocol ip prio 100 u32 match ip src 192.168.0.100 classid 1:31
/sbin/tc filter add dev eth1 parent 1:0 protocol ip prio 100 u32 match ip src 192.168.0.99 classid 1:31
/sbin/tc class add dev eth1 parent 1:30 classid 1:52 htb rate 100Kbit ceil 0.5Mbit prio 5
/sbin/tc filter add dev eth1 parent 1:0 protocol ip prio 100 u32 match ip src 192.168.0.22 classid 1:52
/sbin/tc filter add dev eth1 parent 1:0 protocol ip prio 100 u32 match ip src 192.168.0.27 classid 1:52
/sbin/tc class add dev eth1 parent 1:30 classid 1:60 htb rate 100Kbit ceil 1Mbit prio 5
/sbin/tc filter add dev eth1 parent 1:0 protocol ip prio 100 u32 match ip dst 192.168.0.0/24 classid 1:60
/sbin/tc class add dev eth0 parent 1: classid 1:2 htb rate 2Mbit ceil 2Mbit
/sbin/tc class add dev eth0 parent 1:2 classid 1:10 htb rate 128Kbit ceil 2Mbit burst 100Kb prio 1
/sbin/tc filter add dev eth0 parent 1:0 protocol ip prio 100 u32 match ip dport 22 0xffff classid 1:10
/sbin/tc class add dev eth0 parent 1:2 classid 1:10 htb rate 512Kbit ceil 1Mbit burst 100Kb prio 1
/sbin/tc filter add dev eth0 parent 1:0 protocol ip prio 100 u32 match ip dport 1194 0xffff classid 1:10
/sbin/tc class add dev eth0 parent 1:2 classid 1:20 htb rate 50Kbit ceil 2Mbit prio 5
/sbin/tc qdisc add dev eth0 parent 1:20 handle 20 sfq perturb 10
/sbin/tc class add dev tun0 parent 1: classid 1:2 htb rate 0.5Mbit ceil 1Mbit
Хочу заметить, что у меня несколько нетипичная ситуация, два интернет канала на 2 и 1 Mbit, и для каждого пользователя ограничение в 2 Mbit скорости загрузки, поэтому на графике видно, что если активен один пользователь — его скорость урезается на 2 Mbit, а если несколько — суммарная скорость может достигать и трех. На таком достаточно «тесном» канале работают более 20 человек, и вполне комфортно себя чувствуют, не мешая друг другу.<br>
Эта картинка с реально действующего сервера, и она обновляется каждые 5 минут и отображает актуальную картину загрузки канала.


[http://www.nixtech.ru/datatrans/linux/shaiper/htb.tar.gz примеры конфигурационных файлов]
'''ИСТОЧНИКИ'''
<hr>
* [http://www.opennet.ru/docs/RUS/mysqladm/userman.html Управление пользователями в MySQL]
* [https://proft.me/2011/07/19/mysql-poleznye-komandy-i-nastrojki/ mysql: полезные команды и настройки ]
* [https://www.newalive.net/190-upravlenie-vneshnimi-bazami-dannyh-cherez-phpmyadmin.html PhpMyAdmin]
<hr>
* [http://gahcep.github.io/blog/2013/03/11/linux-mysql-setup/ Правильная установка и настройка MySQL]
* [https://www.hostcms.ru/documentation/server/mysql/ Настройка MySQL]
* [http://linux.mixed-spb.ru/databases/mysql_setup.php Установка и первоначальная настройка MySQL в linux]
* [http://linux.cpms.ru/?p=180 Руководство по MySQL для начинающих]
* [http://cruw.blogspot.ru/2011/11/mysql.html Установка и настройка MySql]
* [https://firstwiki.ru/index.php/%D0%9A%D0%B0%D0%BA_%D0%B7%D0%B0%D0%B3%D1%80%D1%83%D0%B7%D0%B8%D1%82%D1%8C_%D0%B4%D0%B0%D0%BC%D0%BF_MySQL_%D0%B1%D0%B0%D0%B7%D1%8B_%D1%87%D0%B5%D1%80%D0%B5%D0%B7_%D0%BA%D0%BE%D0%BD%D1%81%D0%BE%D0%BB%D1%8C Как загрузить дамп MySQL базы через консоль]
* [https://habr.com/post/105954/ MySQL шпаргалки]
* [https://www.newalive.net/190-upravlenie-vneshnimi-bazami-dannyh-cherez-phpmyadmin.html Управление внешними базами данных через phpMyAdmin]
* [https://geeksmagazine.org/post/7/kak-sozdat-polzovatelya-i-dat-emu-prava-v-mysql Как создать пользователя и дать ему права в MySQL]
* [https://andy-blog.ru/kak-sozdat-novogo-polzovatelya-mysql Как создать нового пользователя MySQL и настроить права доступа ]
* [https://ruhighload.com/%D0%9A%D0%B0%D0%BA+%D0%BD%D0%B0%D1%81%D1%82%D1%80%D0%BE%D0%B8%D1%82%D1%8C+mysql+master-slave+%D1%80%D0%B5%D0%BF%D0%BB%D0%B8%D0%BA%D0%B0%D1%86%D0%B8%D1%8E%3F Как настроить MySQL Master-Slave репликацию?]
* [https://server-gu.ru/mysql-replication-master-slave/ Настройка репликации MySQL MASTER SLAVE]
* [http://blog.anthonyaxenov.ru/2018/10/15/%D1%88%D0%BF%D0%B0%D1%80%D0%B3%D0%B0%D0%BB%D0%BA%D0%B0-master-slave-%D1%80%D0%B5%D0%BF%D0%BB%D0%B8%D0%BA%D0%B0%D1%86%D0%B8%D1%8F-mysql-5-7/ Шпаргалка: Master-slave репликация MySQL 5.7]


[http://habrahabr.ru/post/60095/ взято тут...]
'''ПОЛЕЗНОЕ:'''
<hr>
* [https://www.8host.com/blog/replikaciya-baz-dannyx-mysql-po-tipu-master-slave/ Репликация баз данных MySQL по типу Master/Slave]

Версия от 03:46, 5 мая 2020

Help MySql

  • Список пользователей:
SELECT USER from mysql.user;
  • Создание пользователя:
CREATE USER 'user'@'localhost' IDENTIFIED BY 'secret';
  • Удаление пользователя:
DROP USER user@localhost;
  • Предоставить права пользователю:
GRANT ALL PRIVILEGES ON dbname.* TO 'user'@'localhost';
  • Предоставить супер права пользователю:
GRANT ALL PRIVILEGES ON dbname.* TO 'user'@'localhost' WITH GRANT OPTION;
  • Права равные root для работы через phpmyadmin
GRANT ALL PRIVILEGES ON  *.* TO 'user'@'%' REQUIRE NONE WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

Настройка PhpMyAdmin

Процедура «переподключения» phpMyAdmin на работу с внешним сервером достаточно проста, но вызывает вопросы у тех, кто занимается всем этим недавно или неглубоко.

В данной статье мы рассмотрим несколько вариантов подключения phpMyAdmin ко внешнему серверу, так как задачи и условия у всех разные. В примере я использую сервер баз данных MariaDB, ОС Ubuntu и phpMyAdmin версии 4.5.4, что практически не важно, так как всё достаточно просто. PhpMyAdmin установлен на домашний ПК, но с тем же успехом он может быть настроен и на удалённом сервере. Имеется ввиду, что всё прикладное ПО, такое как непосредственно сам сервер БД, http-сервер и php у вас уже установлено.

  • Пример первый (доступ с паролем).

Сервер баз данных расположен на машине с адресом 10.10.1.10. PhpMyAdmin устанавливаем на локальный ПК с адресом 10.10.1.20. Необходимо убедиться, что сервер баз данных принимает внешние соединения. Правим my.cnf (/etc/mysql/my.cnf). В строке bind-address по умолчанию указан адрес 127.0.0.1, что запрещает серверу принимать внешние соединения. Заменяем его на 0.0.0.0, что разрешит внешние соединения с любого адреса. В целях безопасности лучше уточнить адрес, если вы не планируете подключаться к серверу из разных источников. В этом примере будет использоваться строка следующего вида:

bind-address		= 10.10.1.20

Сохраняем и перезагружаем.

sudo service mysql restart

На ПК (или другой удалённый сервер) устанавливаем phpMyAdmin.

sudo apt install phpmyadmin

В процессе установки отказываемся от конфигурации (конфигурировать нечего; сервер баз данных на удалённой машине).

Далее правим конфигурационный файл phpMyAdmin config.inc.php (/etc/phpmyadmin/config.inc.php). Если это была свежая установка и на локальном хосте нет сервера баз данных, то удаляем все строки (кроме первой «<?php») и пишем следующее:

/* External Server #1 */
 $i++;
 $cfg['Servers'][$i]['verbose'] = 'Server Name';
 $cfg['Servers'][$i]['host'] = '10.10.1.10';
 $cfg['Servers'][$i]['connect_type'] = 'tcp';
 $cfg['Servers'][$i]['extension'] = 'mysqli';
 $cfg['Servers'][$i]['auth_type'] = 'cookie';
 $cfg['Servers'][$i]['AllowNoPassword'] = false;

Вместо Server Name вписываем что угодно. Сохраняем и закрываем файл.

Конвертирование базы из postgresql в mysql

git clone https://github.com/ChrisLundquist/pg2mysql.git 
cd pg2mysql
  • сохраняем дамп из postgresql (пример сохранение всех таблиц для wordpress - wp*)
sudo -u postgres pg_dump --quote-all-identifiers --no-acl --no-owner --table=wp_* --format p --data-only wordpress -f wordpress_pgsql.sql
  • конвертируем в формат mysql
php pg2mysql_cli.php worpress_pgsql.sql wordpress_mysql.sql
  • теперь необходимо заменить все таблицы с `"public"."wp_*"` на 'wp_*` это можно сделать в редакторе по F4
  • не забудьте исправить "` ( на '(
  • теперь меняем sql команду с INSERT на REPLACE так же по F4
  • все, дамп можно заливать в базу, предварительно создайте из wordpress начальную структуру таблиц через install и потом вносите данные.


ИСТОЧНИКИ



ПОЛЕЗНОЕ: