MySQL notes

Create user for database

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
--
GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost';
--
FLUSH PRIVILEGES;
-- delete user if exists
DROP USER [email protected];
FLUSH PRIVILEGES;

Смена пароля root-a

# Остановить сервес mysql
service mysql stop
# Запустить сервис с параметром --skip-grant-tables
mysqld_safe --skip-grant-tables
# заходим в консоль mysql
mysql -u root mysql
# Выполняем в консоли MYSQL:
flush privileges;
set password for 'root'@'localhost' = PASSWORD('new-password');
flush privileges;
quit;
# Перезапускаем сервис в рабочем состоянии
service mysql stop
service mysql start

Что есть?

-- Что с логами?
SHOW GLOBAL VARIABLES LIKE "log%"
-- смотрим базы
SHOW Databases;
-- 
use {database};
show tables;
--
desc ;
-- смотрим триггеры
SHOW TRIGGERS [FROM db_name] [LIKE 'pattern']
-- или
SHOW TRIGGERS

-- 30 таблиц в базе, с наибольшим количеством записей.
SELECT table_name, ENGINE, table_rows, DATA_LENGTH, INDEX_LENGTH,DATA_FREE, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA = '' ORDER BY table_rows DESC LIMIT 30;

Медленные запросы

# включить в конфиге /etc/my.cnf
# признак ведения лога
slow_query_log = ON
#
long_query_time = 0.8000000
# запросы не использующие индексы считаются медленным, время выполнения не важно
log_queries_not_using_indexes = OFF
# файл хранения лога
log_slow_queries=/var/log/mysql_slow_queries.log
-- Посмотрим текущие значения
SHOW GLOBAL VARIABLES LIKE "%slow%";
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| log_slow_queries    | OFF                             |
| slow_launch_time    | 2                               |
| slow_query_log      | OFF                             |
| slow_query_log_file | /var/log/mysql_slow_queries.log |
+---------------------+---------------------------------+
-- Запросы без индексов
 SHOW GLOBAL VARIABLES LIKE "log_queries_not_using_indexes";
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+

-- Включить лог медленных запросов на ходу
set global log_slow_queries = ON;
SET global log_queries_not_using_indexes = ON;

Общий лог

-- В конфиге
general_log_file = /var/log/mysql/mysql.log
general_log = 1

-- Включить общий лог на ходу
SET global log_output = FILE;
SET global general_log_file=/var/log/mysql/general.log;
SET global general_log = 1;

Подключение к удаленному хосту

mysql -P 3306 -h [host] -u [login] -p

MySQL 5.5.17, FreeBSD 10 Ошибка mysql_install_db

# При запуске (из home)
mysql_install_db
# Получаем
FATAL ERROR: Could not find ./bin/my_print_defaults
# Решение
cd /usr/local
mysql_install_db --user=mysql --ldata=/var/db/mysql

БЭКАП:

# Создать полный бекап:
# с параметром --databases формируются команды CREATE DATABASE
mysqldump -u user -ppass --databases db1 db2 > /tmp/db.sql

# Создать бекап нескольких таблиц:
mysqldump -u user -ppass {database} table1 table1 table2  > /tmp/db_tables.sql

# Создаём сразу в архиве:
mysqldump -u user -ppass --databases {db1} {db2} | gzip > /tmp/db.sql.gz

# Восстановить бекап:
mysql -u user -ppass db < /tmp/db.sql

# Восстановить бекап иp архива:
gunzip < /tmp/db.sql.gz | mysql -u user -ppass db

Экспорт/импорт части данных
Замечания:
- Абсолютное равенство структуры/кодировки таблиц источника и приемника
- Разрешить файловые операции на сервере:

[mysqld]
secure-file-priv = ""
-- выгрузка
SELECT * from cdr WHERE datetime >'2017-10-31' INTO OUTFILE '/tmp/cdr_dump.txt'
-- загрузка
LOAD DATA INFILE '/tmp/cdr_dump.txt' INTO TABLE cdr;
-- Посмотрим на активные запрос
SHOW FULL PROCESSLIST;
-- Убить запрос по ID
KILL QUERY 675

Установка сервиса MySQL на Windows

# установка
mysqld --install MySQL --defaults-file="C:\sys\mysql\my.ini"
# удаление
sc delete MySQL
# подсчет количества запросов в соединении по логу
cat /var/log/mysql/mysql_general.log | grep "Query" | awk '{print $1}' | sort | uniq -c

Ошибка: 1728 Cannot load from mysql.proc. The table is probably corrupted
Решение:

$ mysql_upgrade

MariaDB set root password

Vendor link: https://mariadb.com/kb/en/library/authentication-plugin-unix-socket/

Проверка:

# run
mysql mysql -e "select * from user where user='root' \G;" | grep plugin
# answer
plugin: unix_socket

Удалить плагин:

# connect to mysq
mysql -u root
# 
use mysql;
update user set password=PASSWORD("password111") where User='root';
flush privileges;
update user set plugin='' where User='root';
quit;
# restart service
systemctl restart mariadb

Установить плагин:

#
mysql -u root -p
#
use mysql;
update user set plugin='unix_socket' where User='root';
quit;
#
systemctl restart mariadb

Экспорт каждой таблицы в отдельный файл

mysqldump -u dbuser -p --tab=/path/to databasename

Появилась ошибка

mysqldump: Got error: 1045: "Access denied for user 'dbuser'@'localhost' (using password: YES)" when executing 'SELECT INTO OUTFILE'

Варианты
1) добавить в конфиг mysqld, потом убрать
skip-grant-tables