SQL запросы для ABillS

В этой статье я приведу примеры некоторых SQL запросов для базы биллинговой системы ABillS.

Первым делом подключимся к MySQL серверу и перейдем к базе abills (либо можно использовать phpmyadmin или внешний MySQL клиент):

mysql -u root
use abills;

Указать DNS и очистить во всех IP POOLs (Настройка>Сервер доступа>IP POOLs) можно sql такими запросами:

update ippools SET dns='8.8.8.8,8.8.4.4';
update ippools SET dns='';

Изменить логин пользователю можно так:

update users set id='new_login' where id='old_Login';

Посчитать количество пользователей:

SELECT count(*) FROM users;

Количество пользователей с указанным кредитом без даты обнуления кредита:

SELECT count(*) FROM users WHERE credit>0 AND credit_date="0000-00-00";

Количество пользователей с указанной скидкой без даты окончания скидки:

SELECT count(*) FROM users WHERE reduction>0 AND reduction_date="0000-00-00";

Количество пользователей с указанной датой активации для учетной записи:

SELECT count(*) FROM users where activate<>0000-00-00;

Количество сервисов для пользователей и количество сервисов в которых указана маска 255.255.255.255:

SELECT COUNT(*) FROM internet_main;
SELECT count(*) FROM internet_main where netmask=4294967295;

Количество сервисов в которых маска не равна 255.255.255.255:

SELECT count(*) FROM internet_main where netmask<>4294967295;

Количество сервисов в которых не указана дата активации:

SELECT count(*) FROM internet_main WHERE activate=0000-00-00;

Количество учетных записей в которых указана дата активации:

SELECT count(*) FROM users where activate<>0000-00-00;

Чтобы абонентов не отключало если на счету 0 и тариф оплачен:

SELECT * FROM tarif_plans where credit=0.00 LIMIT 200;
UPDATE tarif_plans SET credit='0.01' where credit=0.00;

Подсчет количества тарифов на которых не разрешено устанавливать скидку и включение скидки на всех тарифах:

SELECT count(*) FROM tarif_plans WHERE reduction_fee=0;
UPDATE tarif_plans SET reduction_fee=1 WHERE reduction_fee=0;

Тарифы в которых не выбрано действие при недостаточном депозите, и тарифы в котором выбрано «Приостановление»:

SELECT * FROM tarif_plans WHERE small_deposit_action=0;
SELECT count(*) FROM tarif_plans WHERE small_deposit_action='-1';

Тарифы в которых указан или не указан фильтр негативного депозита:

SELECT count(*) FROM tarif_plans WHERE neg_deposit_filter_id='NEG_DEPOSIT';
SELECT count(*) FROM tarif_plans WHERE neg_deposit_filter_id='';
SELECT * FROM tarif_plans WHERE neg_deposit_filter_id='';

Просмотр всего списка платежей и по конкретному типу (например 65 это Privat-Terminal):

SELECT * FROM payments LIMIT 750;
SELECT * FROM payments WHERE method=65;

Просмотр логинов и паролей в расшифрованном виде («secretkey» смотрим в config.pl):

SELECT id, DECODE(password, 'secretkey') FROM users;
SELECT id, DECODE(password, 'secretkey') FROM users WHERE uid=11;

Количество Zap сессий:

SELECT count(*) FROM internet_online WHERE status=2;

Количество гостевых сессий:

SELECT count(*) FROM internet_online where guest=1;

Количество всех сессий:

SELECT count(*) FROM internet_online WHERE (status=1 or status>=3);

Количество сессий без гостевых и Zap:

SELECT count(*) FROM internet_online WHERE (status=1 or status>=3) AND guest=0;

Выборка IP из онлайн сессий:

SELECT INET_NTOA(framed_ip_address) FROM internet_online WHERE (status=1 or status>=3) AND guest=0;

Смотрите также мой скрипт ipset — Скрипт добавления IP адресов из файла в ipset

Размер базы данных:

SELECT SUM( data_length + index_length ) AS 'size' FROM information_schema.TABLES WHERE table_schema = 'abills' LIMIT 1;

Количество сервисов для пользователей с определенным статусом (0 — Активно, 1 — Отключено, 2 — Не активизирован, 3 — Приостановление, 4 — Отключено: Неуплата, 5 — Cлишком маленький депозит):

SELECT count(*) FROM internet_main WHERE disable=0;

Изменение формата телефонов:

UPDATE users_pi SET phone=REPLACE(phone, '+38050', '050') WHERE phone like '+38050%';

Скрипт добавления IP адресов из файла в ipset

Понадобилось однажды написать скрипт чтобы добавить в ipset все IP для которых били подняты сессии на сервере доступа, использовался биллинг Abills, поэтому я решил взять IP адреса из MySQL таблицы биллинга.

Первым делом создадим тестовый ipset:

ipset create test iphash

Создадим файл скрипта:

nano iplist.sh

Добавим в него содержимое:

mysql -u root -e "SELECT INET_NTOA(framed_ip_address) FROM abills.internet_online WHERE (status=1 or status>=3) AND guest=0;" -s -N > iplist.txt
iplist_data=$(cat iplist.txt)
for row_data in $iplist_data; do ipset -exist add test ${row_data}; done
rm iplist.txt

Выполняем скрипт и проверяем:

chmod +x iplist.sh
./iplist.sh
ipset list test | wc -l
ipset -L

Решение MySQL ERROR 1055 (42000)

Недавно выполнял необходимые SQL запросы и заметил следующую ошибку:

ERROR 1055 (42000): Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database_name.table_name.column_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Читать далее Решение MySQL ERROR 1055 (42000)

Как увидеть процесс импорта SQL файла?

Для того чтобы видеть процесс импорта SQL файлов я использую утилиту Pipe Viewer.
Pipe Viewer очень полезен особенно когда SQL файл большого размера, позволяет примерно оценить оставшееся время до завершения импорта файла, отображает процесс импорта в процентах и перешедшее время.

Установить Pipe Viewer в Ubuntu/Debian можно командой:

sudo apt install pv

В Fedora так:

yum install pv

Допустим раньше мы импортировали sql файл и не видели процесс:

mysql -u root DATABASE_NAME < dump.sql

Теперь чтобы увидеть процесс выполним импорт с использованием Pipe Viewer:

pv dump.sql | mysql -u root DATABASE_NAME

Как решить «ERROR 1062 (23000) at line X: Duplicate entry ?????? for key X»

Импортировал недавно дамп в MySQL и обнаружил следующую ошибку:

ERROR 1062 (23000) at line 14096: Duplicate entry '????????' for key 'name'

Читать далее Как решить «ERROR 1062 (23000) at line X: Duplicate entry ?????? for key X»

Решение ошибки с binary-mode при импорте MySQL dump из командной строки

Однажды решил импортировать sql дамп:

mysql -u root database < database.sql.gz

И заметил следующую ошибку:

ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: ''.

Ошибка появляется из-за того что дамп сжат и его необходимо сначала распаковать:

gunzip database.sql.gz

А потом импортировать:

mysql -u root database < database.sql

Смотрите другие мои статьи в категории MySQL

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

На тесте выполню настройку репликации в MySQL.

Допустим имеется два сервера с установленной системой Ubuntu Server, на обеих установим MySQL сервер и клиент, если они не установлены:

sudo apt-get install mysql-server mysql-client

Читать далее Настройка репликации в MySQL

Решение ошибки ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

Выполнял однажды SQL запрос:

GRANT REPLICATION SLAVE ON TESTDATABASE.* TO "replication"@"192.168.1.9" IDENTIFIED BY "password";

И обнаружил следующую ошибку:

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

Так как привилегии REPLICATION SLAVE являются глобальными и не могут быть назначены конкретной базе данных, то и в запросе их нужно указывать глобально, поэтому SQL запрос должен выглядеть так:

GRANT REPLICATION SLAVE ON *.* TO "replication"@"192.168.1.9" IDENTIFIED BY "password";

После этого запрос выполнился успешно:

Query OK, 0 rows affected, 1 warning (0,01 sec)

Восстановление MySQL таблиц

Приведу пример простой проверки таблиц всех баз данных используя mysqlcheck:

mysqlcheck -u root -p -A

Где опция -u определяет имя пользователя под которым выполняется подключение к MySQL, -p для запроса пароля, -A для проверки таблиц всех баз данных.

Пример восстановления конкретной базы данных:

mysqlcheck -u root -p -r db_name

Пример восстановления конкретной таблицы в указанной базе данных:

mysqlcheck -u root -p -r db_name table_name

Опишу возможные опции запуска.

Проверка всех баз данных и их таблиц, кроме INFORMATION_SCHEMA и performace_schema:

--all-databases, -A

Указать перечень баз можно используя опцию:

--databases, -B

Выполнить все одним запросом вместо отдельных для каждой базы:

--all-in-1, -1

Анализировать таблицы:

--analyze, -a

После проверки выполнить ремонт поврежденных таблиц:

--auto-repair

Указание адреса подключения к MySQL серверу (например если их несколько):

--bind-address=ip_address

Подключение к MySQL серверу на указанном узле:

--host=host_name, -h host_name

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

--character-sets-dir=dir_name

Проверка таблиц на наличие ошибок (выполняется по умолчанию):

--check, -c

Проверить таблицы которые были изменены с момента последней проверки или которые не были закрыты должным образом:

--check-only-changed, -C

Проверка таблиц на совместимость с текущей версией сервера:

--check-upgrade, -g

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

--compress

Запись и отображение отладочной информации (на тесте стандартный mysqlcheck в Ubuntu 16.04 был скомпилирован без поддержки debug):

--debug=debug_options
--debug-check
--debug-info

Указание набора символов по умолчанию:

--default-character-set=charset_name

Использование только указанного файла параметров:

--defaults-file=file_name

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

--extended, -e

Проверить только таблицы которые не были закрыты должным образом:

--fast, -F

Продолжать даже в случае SQL ошибки:

--force, -f

Быстрее чем extended, находится 99.99% ошибок:

--medium-check, -m

Не читать файлы опций, однако файл .mylogin.cnf будет читаться:

--no-defaults

Смотрите также мою статью — Подключение к MySQL из localhost без ввода пароля.

Оптимизировать таблицы:

--optimize, -o

Указание пароля при подключении:

--password[=password], -p[password]

Номер порта TCP/IP для подключения:

--port=port_num, -P port_num

Отображение имени программы и всех параметров, которые она получает из файлов параметров:

--print-defaults

Указание протокола подключения:

--protocol={TCP|SOCKET|PIPE|MEMORY}

Быстрый метод проверки таблиц, предотвращает сканирование строк для проверки неправильных ссылок:

--quick, -q

Выполнение ремонта:

--repair, -r

Тихий режим, отображаются только ошибки:

--silent, -s

Пропустить указанную базу:

--skip-database=db_name

Указание сокета при подключении:

--socket=path, -S path

Подключение с использованием SSL:

--ssl*

Переопределить -databases или -B, все аргументы имени, следующие за опцией, рассматриваются как имена таблиц:

--tables

Указание перечня протоколов через запятую, которые разрешено использовать при зашифрованных соединениях:

--tls-version=protocol_list

При восстановление получить структуру таблицы из файла .frm:

--use-frm

Имя пользователя MySQL, которое будет использоваться при подключении:

--user=user_name, -u user_name

Подробный режим, отображается больше информации:

--verbose, -v

Отображение версии mysqlcheck и выход:

--version, -V

Смотрите также:
Установка и настройка MySQL сервера на Ubuntu

Просмотр информации о базах данных MySQL

Чтобы посмотреть информацию о базах данных MySQL и их таблицах первым делом подключимся к консоли MySQL, например под пользователем root:

mysql -u root

Или так, если необходимо ввести пароль:

mysql -u root -p

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

SHOW DATABASES;

Выбрать базу данных можно так:

USE dbname;

Посмотреть какая в данный момент выбрана база:

SELECT DATABASE();

Чтобы посмотреть какие таблицы содержит выбранная база данных:

SHOW TABLES;

Пример просмотра содержимого указанной таблицы:

SELECT * FROM tablename LIMIT 25;

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

DESCRIBE tablename;

Где Field — это имя столбца, Type — тип данных, Null — определяет, может ли столбец содержать значения NULL, Key — является ли столбец индексным, Default — определяет значение по умолчанию для столбца.

Посмотреть информацию о индексах таблицы можно так:

SHOW INDEX FROM tablename;

Посмотреть информацию о таблице, кодировке, её типе (ENGINES) и т.д. можно командой:

SHOW CREATE TABLE tablename;

Посмотреть какие типы поддерживает сервер можно так:

SHOW ENGINES;