MySQL Basics

Disable history

export MYSQL_HISTFILE=/dev/null

Connect to local host

mysql -u [user] -p 

Connect to remote host

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

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

DROP USER username@localhost;

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%"
-- one global variable
selec @@{variable};
-- Databases
show Databases;
-- Tables in database
use {database};
show tables;
-- Fields in table
desc {table};
-- Triggers
SHOW TRIGGERS [FROM db_name] [LIKE 'pattern'];
-- ... or 
SHOW TRIGGERS;

Top 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;

Tables Engine

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'dbname';
-- change engine
ALTER TABLE s_schemes_options ENGINE = InnoDB;

Slow query log

In config file: maraidb: /etc/mysql/mariadb.conf.d/50-server.cnf

slow_query_log = 1
long_query_time = 0.5
log_queries_not_using_indexes = 0
log_slow_queries=/var/log/mysql/mariadb-slow.log

Checking values on runtime

> 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/mariadb-slow.log |
+---------------------+---------------------------------+

> select @@log_queries_not_using_indexes;
+---------------------------------+
| @@log_queries_not_using_indexes |
+---------------------------------+
|                               0 |
+---------------------------------+

Turn in slow query log in runtime

set global log_slow_queries = ON;
SET global log_queries_not_using_indexes = ON;

Checking by query

select sleep(2);

General log

In config file

general_log_file = /var/log/mysql/mysql_general.log
general_log = 1

Turn on general log on runtime

SET global log_output = FILE;
SET global general_log_file=/var/log/mysql/mysql_general.log;
SET global general_log = 1;

Turn off on runtime

SET global general_log = 0;

Utility mysql_install_db

mysql_install_db --user=mysql --ldata=/var/lib/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.gz; done

Other cases

# Backup one database
mysqldump -u user -p --single-transaction  --events --routines --triggers --databases {database} > /tmp/db.sql

# ... to archive
mysqldump -u user -p --single-transaction --events --routines --triggers --databases {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

mysqldump -u user -p --tab=/path/to {databasename}

Restore

# Restore backup from dump
mysql -u root -p < /tmp/db.sql

# Restore dump from archove
gunzip < /tmp/db.mysql.gz | mysql -u user -p

Export/import part of data

Notes:
- The full equal of tables schemes on source and destination - Enable file operations

[mysqld]
secure-file-priv = ""

Export

SELECT * from cdr WHERE datetime >'2017-10-31' INTO OUTFILE '/tmp/cdr_dump.txt'

Import

LOAD DATA INFILE '/tmp/cdr_dump.txt' INTO TABLE cdr;

Active processes

Show

SHOW FULL PROCESSLIST;

Kill process by ID

KILL 675

MariaDB unix_socket plugin

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

Check plugins

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

Remove plugin:

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 mysql

Install plugin:

use mysql;
update user set plugin='unix_socket' where User='root';
quit;

Restart service

systemctl restart mariadb

Mysqlimport

mysqlimport -u user -p --local --ignore-lines=1 --fields-terminated-by=, databasename tablename.csv

# cat tablename.csv for ID as auto-incremental field
id,field1,field2
,John,123