MySQL
Connect
Users
Create user
-- Local user
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
--
GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost';
--
FLUSH PRIVILEGES;
Create remote user
CREATE USER 'username'@'%' IDENTIFIED BY 'PASSWORD';
--
GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'%';
--
FLUSH PRIVILEGES;
Delete user
Reset root password
# Остановить сервес 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;
# Перезапускаем сервис в рабочем состоянии
systemctl restart mysql
What we have?
-- Logs
SHOW GLOBAL VARIABLES LIKE "log%"
-- Databases
SHOW Databases;
-- Tables in database
use {database};
show tables;
-- Fields in table
desc ;
-- Triggers
SHOW TRIGGERS [FROM db_name] [LIKE 'pattern']
-- ... or
SHOW TRIGGERS
Top 30 tables by row count
-- 30 tables with the most records
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;
Slow queries
# in config
slow_query_log = ON
#
long_query_time = 0.8000000
#
log_queries_not_using_indexes = OFF
#
log_slow_queries=/var/log/mysql_slow_queries.log
-- Get runtime values
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
-- In config file
general_log_file = /var/log/mysql/mysql_general.log
general_log = 1
-- Turn on general log
SET global log_output = FILE;
SET global general_log_file=/var/log/mysql/mysql_general.log;
SET global general_log = 1;
-- Turn off
SET global general_log = 0;
Подключение к удаленному хосту
mysql_install_db
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
Backup
Backup all databases to separate gzip files
U=USER
P=PASSWORD
D=$(date +"%Y%m%d-%H%M%")
mysql -u $U -p$P -N -e 'show databases' | while read dbname; do echo $dbname; mysqldump -u $U -p$P \
--complete-insert --routines --triggers --single-transaction --events --databases "$dbname" \
| gzip > "$dbname-$D".mysql.sql.gzip; done
Other cases
# Backup one database
mysqldump -u user -p --single-transaction --databases --events --routines --triggers {database} > /tmp/db.sql
# ... to archive
mysqldump -u user -p --single-transaction --databases --events --routines --triggers {database} | gzip > /tmp/db.sql.gz
# Backup some tables
mysqldump -u user -p {database} table1 table1 table2 > /tmp/db_tables.sql
Export each table to separate file
Restore
# Restore backup from dump
mysql -u root -p < /tmp/db.sql
# Restore dump from archove
gunzip < /tmp/db.sql.gzip | mysql -u user -p
Экспорт/импорт части данных
Замечания:
- Абсолютное равенство структуры/кодировки таблиц источника и приемника
- Разрешить файловые операции на сервере:
-- выгрузка
SELECT * from cdr WHERE datetime >'2017-10-31' INTO OUTFILE '/tmp/cdr_dump.txt'
-- загрузка
LOAD DATA INFILE '/tmp/cdr_dump.txt' INTO TABLE cdr;
Установка сервиса MySQL на Windows
# подсчет количества запросов в соединении по логу
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
Решение:
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