MySQL - полезняки

• установка MySQL определенной версии
• установка mariaDB
• Настройка
• создание пользователей и БД
• создание бэкапов и восстановление 
• факты для обслуживание и разработки
• Mysql Реплики
• Решения проблем
• Функции

установка MySQL определенной версии

$ apt update
$ apt install gnupg
# выбрать версию тут https://dev.mysql.com/downloads/mysql/ Например 0.8.26-1
$ wget https://dev.mysql.com/get/mysql-apt-config_0.8.26-1_all.deb
# Установить
$ sudo dpkg -i mysql-apt-config_0.8.25-1_all.deb
# При установке пакета выберите продукт и версию MySQL,
# Установить
$ apt-get update
$ sudo apt-get install mysql-community-server

Установка mariaDB

$ apt install mariadb-server

Настройка

Общие быстрые настройки

$ mysql_secure_installation

Доступ по сети

# /etc/mysql/mysql.conf.d/mysqld.cnf

bind-address = 127.0.0.1
# заменить на 
bind-address = 192.168.0.22
$ systemctl restart mysql

создание пользователей и БД

# создание базы
CREATE DATABASE user2db;

# список баз
SHOW DATABASES;


# создание пользователя локально
CREATE USER 'user2'@'localhost' IDENTIFIED BY 'secret';

# или с доступом по сети
CREATE USER 'user2'@'%' IDENTIFIED BY 'secret';

# назначение прав
GRANT ALL PRIVILEGES ON user2db.* TO 'user2'@'localhost';

# или на всё локально
GRANT ALL PRIVILEGES ON *.* TO 'user2'@'localhost';

или на чтение локально
GRANT SELECT ON *.* TO 'user2'@'localhost';

или для пользователя для создания бэкапов
GRANT SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER, LOCK TABLES, PROCESS, EXECUTE ON *.* TO 'user2'@'localhost';

# или с доступом по сети
GRANT ALL PRIVILEGES ON user2db.* TO 'user2'@'%';

# применение прав
FLUSH PRIVILEGES;

# Список пользователей
Select user from mysql.user;

# Права пользователя
SHOW GRANTS FOR 'user2';

создание бэкапов и восстановление 

# Создать бекап
$ mysqldump -u USER -pPASSWORD DATABASE > /backups/dump.sql

# Создаём структуру базы без данных
$ mysqldump --no-data - u USER -pPASSWORD DATABASE > /backups/schema.sql

# Если нужно сделать дамп только одной или нескольких таблиц
$ mysqldump -u USER -pPASSWORD DATABASE TABLE1 TABLE2 TABLE3 > /backups/dump_table.sql

$ Создать бекап и сразу его запаковать
# mysqldump -u USER -pPASSWORD DATABASE | gzip > /backups/dmname.$(date +"%y-%m-%d").sql.gz

# Восстановление бэкапа в пустую базу
$ mysql -u USER -pPASSWORD DATABASE < /backups/dump.sql

Если в дампе указано явно создание BD - заменяем его
$ sed -i 's/`MYDATABASE`/`MYNEWDATABASE`/g' mydump.sql

# Восстановление архива бэкапа в пустую базу
$ gunzip < /backups/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE
# или 
$ zcat /backups/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE

Удобно использовать бекап с дополнительными опциями -Q -c -e, т.е. 

mysqldump -Q -c -e -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql, где:
-Q оборачивает имена обратными кавычками
-c делает полную вставку, включая имена колонок
-e делает расширенную вставку. Итоговый файл получается меньше и делается он чуть быстрее

факты для обслуживание и разработки

Для таблиц InnoDB надо добавлять --single-transaction, это гарантирует целостность данных бекапа. 

Для таблиц MyISAN это не актуально, ибо они не поддерживают транзакционность. 

Полезно под каждую базу на боевом сервере создавать своего пользователя

Кодировка базы может быть любой, если она UTF8

В большинстве случаев лучше использовать движок InnoDB

В php лучше забыть про сильно устаревшее расширение mysql и по-возможности использовать pdo или mysqli

Новую копию MySQL всегда можно настроить и оптимизировать

Без особой нужды не стоит открывать MySQL наружу. Вместо этого можно сделать проброс портов

ssh -fNL LOCAL_PORT:localhost:3306 REMOTE_USER@REMOTE_HOST

Работа с данными

Числа

На 32-битных системах практически нет смысла ставить для типа INTEGER свойство UNSIGNED, так как такие большие числа в php не поддерживаются.

На 64-битных системах, php поддерживает большие числа, вплоть до MySQL BIGINT со знаком.

Связанные таблицы («Foreign keys») должны иметь полное сходство по структуре ключей. Т.е. если у нас на одной таблице для поля указано «INTEGER UNSIGNED DEFAULT 0 NOT NULL» то и на другой должно быть указано аналогично

Для хранения булевых значений, нужно использовать TINYINT(1)

А деньги лучше хранить в DECIMAL(10, 2), где первое число обозначает количество всех знаков, включая запятую, а второе — количество знаков после запятой. Итого, у нас получится что DECIMAL(10,2) может сохранить 9999999,99

Строки

В старых версиях (до 5.0.3) VARCHAR была ограничена 255 символами, но сейчас можно указывать до 65535 символов

Помните, что тип TEXT ограничен только 64 килобитами, поэтому что бы сохранять «Войну и Мир» пользуйтесь «LONGTEXT»

Самая правильная кодировка для вашей БД UTF8

Даты

Не забывайте, что

DATE, TIME, DATETIME — выводятся в виде строк, поэтому поиск и сравнение дат происходит через преобразование

TIMESTAMP — хранится в виде UNIX_TIMESTAMP, и можно указать автоматически обновлять колонку

Сравнивая типы данных DATETIME и TIMESTAMP, не забывайте делать преобразование типов, например:

SELECT * FROM table WHERE `datetime` = DATE(`timestamp`)

Перечисления

Для перечислений правильно использовать тип ENUM

Правильно пишется так: ENUM('мама', 'мыла', 'раму')

Можно ставить значение по-умолчанию, как и для любой строки

В базе поле с перечислением хранится как число, поэтому скорость работы — потрясающе высокая

Количество перечислений ~ 65 тысяч

Отладка

Если запросы тормозят, то можно включить лог для медленных запросов в /etc/mysql/my.cnf

А потом оптимизировать запросы через EXPLAIN

И наблюдать за запросами удобно через программу mytop

 

Mysql Реплики     Master<>Slave

Master  mysql.cnf:

# serverID
server-id = 1
# bin log
log_bin = /var/log/mysql/mysql-bin.log
# BD for replication
binlog_do_db = newdatabase
....
binlog_do_db = newdatabase2

Создать репликационного пользователя

GRANT REPLICATION SLAVE ON *.* TO 'slaver'@'%' IDENTIFIED BY 'ReplY_Pass';
FLUSH PRIVILEGES;

Сдампить базы

SET GLOBAL read_only = ON;
mysqldump -uroot -p -B newdatabase newdatabase2 > test1.sql

SHOW MASTER STATUS;
mysql-bin.000002 | 993 | test1
, где
327 - position
mysql-bin.000002 - binlog

 SET GLOBAL read_only = OFF;

Slave mysql.cnf

server-id = 2
report_host = slave2
log_bin = /var/lib/mysql/mariadb-bin
#log_bin_index = /var/lib/mysql/mariadb-bin.index
relay_log = /var/lib/mysql/relay-bin
#relay_log_index = /var/lib/mysql/relay-bin.index
# BD for replication
binlog_do_db = newdatabase
...
binlog_do_db = newdatabase2

Восстановление баз

mysql -u root -p < databases.sql

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='master_IP', MASTER_USER='slaver', MASTER_PASSWORD='ReplY_Pass', MASTER_LOG_FILE = 'mysql-bin.000002', MASTER_LOG_POS = 327 ;
START SLAVE;

SHOW SLAVE STATUS\G;

Обнулить состояние slave

STOP SLAVE;
RESET SLAVE ALL;

Посмотреть состояние реплик на мастере

SELECT * FROM information_schema.PROCESSLIST AS p WHERE p.COMMAND = 'Binlog Dump';

Решения проблем

WEB администрирование

• phpmyadmin
• phpminiadmin

СБрос root пароль

# Остановить сервис
$ sudo /etc/init.d/mysql stop
$ sudo /etc/init.d/mariadb stop

# Запустить без пароля
$ sudo mysqld_safe --skip-grant-tables &

# подключиться 
$ mysql -uroot

# установить новый пароль

# MySQL
> use mysql;
> update user set password=PASSWORD("mynewpassword") where User='root';

# mariaDB:
> UPDATE mysql.user SET authentication_string = PASSWORD('mynewpassword') WHERE User = 'root' AND Host = 'localhost'; 
> flush privileges;

Удаление MySQL определенной версии

sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-5.5 mysql-client-core-5.5
sudo rm -rf /etc/mysql /var/lib/mysql
sudo apt-get autoremove
sudo apt-get autoclean

Проверка базы или таблицы

mysqlcheck -c database table -u root -p

Восстановление таблиц "...is marked as crashed"

myisamchk -r $TABLE_NAME
или
myisamchk -r -v -f $TABLE_NAME

Быстрое восстановление базы из дампа (для больших объемов)

SET NAMES utf8;
DROP DATABASE `mydb`;
CREATE DATABASE `mydb` DEFAULT CHARACTER SET utf8;
USE `mydb`;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; 
SET autocommit=0;
SET foreign_key_checks=0;
SET unique_checks=0;
SOURCE /path/to/db_dump.sql;
COMMIT;
SET autocommit=1;
SET foreign_key_checks=1;
SET unique_checks=1;

MySQL ошибка при выкатывании дампа новой версии mysql на старую - Unknown collation: 'utf8mb4_0900_ai_ci'

Заменить utf8mb4_0900_ai_ci  на utf8mb4_unicode_ci

Примеры запросов для вычисления занятого пространста

посмотреть размер всех баз данных :

SELECT table_schema "DB Name",
Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM   information_schema.tables
GROUP  BY table_schema;

узнать размер таблиц конкретной базы данных:

SELECT table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
WHERE table_schema = "my_table";

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

SELECT table_schema as `Database`, table_name AS `Table`, 
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC

ФУНКЦИИ

Строковые операции

CONCAT: объединяет строки. В качестве параметра принимает от 2-х и более строк, которые надо соединить:

SELECT CONCAT('Tom', ' ', 'Smith')  -- Tom Smith

При этом в функцию можно передавать не только непосредственно строки, но и числа, даты - они будут преобразовываться в строки и также объединяться.

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

SELECT CONCAT_WS(' ', 'Tom', 'Smith', 'Age:', 34)  -- Tom Smith Age: 34

LENGTH: возвращает количество символов в строке. В качестве параметра в функцию передается строка, для которой надо найти длину:

SELECT LENGTH('Tom Smith')  -- 9

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

SELECT LTRIM('  Apple')

RTRIM: удаляет конечные пробелы из строки. В качестве параметра принимает строку:

SELECT RTRIM(' Apple    ')

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

SELECT TRIM('  Tom Smith   ')

С помощью дополнительного оператора можно задать где имеено удалить пробелы: BOTH (в начале и в конце), TRAILING (только в конце), LEADING (только в начале):

SELECT TRIM(BOTH FROM '  Tom Smith   ')

LOCATE(find, search [, start]): возвращает позицию первого вхождения подстроки find в строку search. Дополнительный параметр start позволяет установить позицию в строке search, с которой начинается поиск подстроки find. Если подстрока search не найдена, то возвращается 0:

SELECT LOCATE('om', 'Tom Smith');       -- 2

LEFT: вырезает с начала строки определенное количество символов. Первый параметр функции - строка, а второй - количество символов, которые надо вырезать сначала строки:

SELECT LEFT('Apple', 3) -- App

RIGHT: вырезает с конца строки определенное количество символов. Первый параметр функции - строка, а второй - количество символов, которые надо вырезать сначала строки:

SELECT RIGHT('Apple', 3)    -- ple

SUBSTRING(str, start [, length]): вырезает из строки str подстроку, начиная с позиции start. Третий необязательный параметр передает количество вырезаемых символов:

SELECT SUBSTRING('Galaxy S8 Plus', 8),          -- S8 Plus
(SELECT SUBSTRING('Galaxy S8 Plus', 8, 2) );    -- S8

SUBSTRING_INDEX(str, delimiter, count): вырезает из строки str подстроку. Параметр delimiter определяет разделитель внутри строки. А параметр count определяет, до какого вхождения разделителя надо вырезать подстроку. Если count положительный, то подстрока вырезается с начала, если count отрицательный, то с конца строки str:

SELECT SUBSTRING_INDEX('Galaxy S8 Plus', ' ', 1),           -- Galaxy
(SELECT SUBSTRING_INDEX('Galaxy S8 Plus', ' ', 2) ),        -- Galaxy S8
(SELECT SUBSTRING_INDEX('Galaxy S8 Plus', ' ', -2) );           -- S8 Plus

REPLACE(search, find, replace): заменяет в строке find подстроку search на подстроку replace. Первый параметр функции - строка, второй - подстрока, которую надо заменить, а третий - подстрока, на которую надо заменить:

SELECT REPLACE('Galaxy S8 Plus', 'S8 Plus', 'Note 8')   -- Galaxy Note 8

INSERT(str, start, length, insert): вставляет в строку str, заменяя length символов с позиции start подстрокой insert. Первый параметр функции - строка, второй - позиция, с которой надо заменить, третий - сколько символов с позиции start надо заменить вставляемой подстрокой, четвертый параметр - вставляемая подстрока:

SELECT INSERT('Galaxy S9', 8, 3, 'Note 9');   -- Galaxy Note 9

REVERSE: переворачивает строку наоборот:

SELECT REVERSE('123456789') -- 987654321

LOWER: переводит строку в нижний регистр:

SELECT LOWER('Apple')   -- apple

UPPER: переводит строку в верхний регистр

SELECT UPPER('Apple')   -- APPLE

SPACE: возвращает строку, которая содержит определенное количество пробелов

REPEATE(str, count): возвращает строку, которая содержит определенное количество повторов подстроки str. Количество повторов задается через параметр count.

SELECT REPEAT('ab', 5);   -- ababababab

LPAD(str, length, pad): добавляет слева от строки str некоторое количество символов, которые определены в параметре pad. Количество добавляемых символов вычисляется по формуле length - LENGTH(str). Если параметр length меньше длины строки str, то эта строка усекается до length символов.

SELECT LPAD('Tom Smith', 13, '*');   -- ****Tom Smith

RPAD(str, length, pad): добавляет справа от строки str некоторое количество символов, которые определены в параметре pad. Количество добавляемых символов вычисляется по формуле length - LENGTH(str). Если параметр length меньше длины строки str, то эта строка усекается до length символов.

SELECT RPAD('Tom Smith', 13, '*');   -- Tom Smith****

логироние SNMP Trap 

SNMP Trap receiver (former EventDB) on RedHat

Install snmp package:
apt-get -y install snmptrapd

now we have to decide if Variant 1 or 2 (2 is preferred because of translated traps)

VARIANT 1:

Creating just a normal trap database

Now, we need to create the database the traps will be stored in, as well as the database user we will use when logging traps.

mysql -u root -p
Create the database we will log traps to:
MariaDB [(none)]> create database net_snmp;

MariaDB [(none)]> create user 'netsnmp'@'localhost' identified by 'sekritpass';
MariaDB [(none)]> grant all on net_snmp.* to 'netsnmp'@'localhost' identified by 'sekritpass';

Create the schema we will enter data into.

A database schema is the actual structure we write data to, including the tables. Consider it the 'shape' and 'function' of the database. The file describing the database schema is, as far as I can tell, not installed with snmpd. But it is present in the net-snmp source code distribution.

I reproduced it below.

Copy and paste this at the MariaDB prompt:

USE net_snmp;

DROP TABLE IF EXISTS notifications;

CREATE TABLE IF NOT EXISTS `notifications` (
  `trap_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `date_time` datetime NOT NULL,
  `host` varchar(255) NOT NULL,
  `auth` varchar(255) NOT NULL,
  `type` ENUM('get','getnext','response','set','trap','getbulk','inform','trap2','report') NOT NULL,
  `version` ENUM('v1','v2c', 'unsupported(v2u)','v3') NOT NULL,
  `request_id` int(11) unsigned NOT NULL,
  `snmpTrapOID` varchar(1024) NOT NULL,
  `transport` varchar(255) NOT NULL,
  `security_model` ENUM('snmpV1','snmpV2c','USM') NOT NULL,
  `v3msgid` int(11) unsigned,
  `v3security_level` ENUM('noAuthNoPriv','authNoPriv','authPriv'),
  `v3context_name` varchar(32),
  `v3context_engine` varchar(64),
  `v3security_name` varchar(32),
  `v3security_engine` varchar(64),
  PRIMARY KEY  (`trap_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS varbinds;

CREATE TABLE IF NOT EXISTS `varbinds` (
  `trap_id` int(11) unsigned NOT NULL default '0',
  `oid` varchar(1024) NOT NULL,
  `type` ENUM('boolean','integer','bit','octet','null','oid','ipaddress','counter','unsigned','timeticks','opaque','unused1','counter64','unused2') NOT NULL,
  `value` blob NOT NULL,
  KEY `trap_id` (`trap_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

/etc/snmp/snmp.conf

mibdirs  +/etc/snmp/translatedMIBS
# mibwarninglevel: Should warnings about mibs be displayed when the mibs are loaded
#   arguments: 1|2
mib 1warninglevel

/etc/snmp/snmptrapd.conf

# Example configuration file for snmptrapd
# No traps are handled by default, you must edit this file!
# authCommunity   log,execute,net public
# traphandle SNMPv2-MIB::coldStart    /usr/bin/bin/my_great_script cold

#authCommunity log YOURCOMMUNITYSTRING
disableAuthorization yes
sqlMaxQueue 1
sqlSaveInterval 9
/etc/my.cnf.d/snmptrapd.cnf

[snmptrapd]
user=USER
password=PASSSWORD
host=localhost
So you can send traps to the database using:
snmptrap -v 2c -c public IPofTRAPRECEIVER "TEXT" 1.2.3.4.0