Шпора по MySQL/MariaDB

# Пользователи, права и пароли
create user 'alexey_li'@'%' identified by 'D5';
grant select on tlight.* to 'alexey_li'@'%';
grant select on mysql.* to 'alexey_li'@'%' identified by 'D5';
revoke select on mysql.* from 'alexey_li'@'%' identified by 'D5';
grant update(column-name) on table-name to user-name;
grant all privileges on database_name.* TO 'username'@'localhost';
flush privileges;

# Update password:
alter user 'userName'@'localhost' identified by 'New-Password-Here';
# For older mysql:
set password for 'user-name-here'@'hostname' = PASSWORD('new-password');

show grants for 'replication'@'5.11.19.29';
select User,Host from mysql.user;
select * from mysql.user where User='root'\G
select * from table where YourColumn IS NOT NULL;

# Процессы (аналог show full processlist):
select * from INFORMATION_SCHEMA.PROCESSLIST;
# Самый долгий запрос
select * from INFORMATION_SCHEMA.PROCESSLIST order by TIME_MS desc limit 1\G

# Таблицы
TRUNCATE `table`;
show FULL COLUMNS FROM table;
DESCRIBE `table`;
# Структура - показать команду для создания таблицы в текущем виде:
show create table orders\G

# Кодировки:
# Посмотреть https://database.guide/show-the-collation-in-mariadb/ :
show variables like "character_set_database";
SHOW VARIABLES LIKE 'collation%';
SHOW SESSION VARIABLES LIKE 'collation%';
SELECT @@collation_server;
# Посмотреть у базы:
SELECT      default_character_set_name,      default_collation_name  FROM information_schema.schemata  WHERE schema_name = 'dbname';

# Изменить кодировки
alter database DBNAME CHARACTER SET utf8 COLLATE utf8_general_ci;
mysql --database=DBNAME -B -N -e "SHOW TABLES" | awk '{print "SET foreign_key_checks = 0; ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1; "}' | mysql --database=DBNAME

# Добавить столбец:
alter TABLE tablename ADD columnname datatype;
# Удалить столбец:
alter TABLE tablename DROP COLUMN columname;

# Переменные посмотреть:
show variables like "expire_logs_days";
show variables like "skip_name_resolve";
select @@hostname;
show variables where Variable_name like '%host%';
select host from information_schema.processlist WHERE ID=connection_id(); # Client IP
show status where `variable_name` = 'Threads_connected'; # Number of current connections
SHOW STATUS WHERE `variable_name` = 'Max_used_connections'; # Max used connections
SHOW VARIABLES LIKE "max_connections";

long_query_time
set GLOBAL log_slave_updates=1;
set GLOBAL slow_query_log=1;

# Изменить:
set GLOBAL expire_logs_days=5;
SHOW BINARY LOGS;
PURGE BINARY LOGS BEFORE '2023-07-02 23:46:26';

INSERT INTO COLUMN1 (orderkey, customer) values (1, 'flycat');
select customer from COLUMN1 where customer like '%lyc%';

# Просто SELECT
select column from table ORDER BY RAND() LIMIT 1;

# Работа с временем
select CURRENT_TIMESTAMP;
select now();
select (now()-INTERVAL 1 HOUR);

# Установка MariaDB:
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

# Данные пользователя для доступа - файл ~/.my.cnf
[client]
port=3306
host=127.0.0.1
user=flycat
password=******

mysql -s # Не выводить таблицу
mysql -ss # Выводить только результат

# Size of databases
SELECT table_schema 'DB name', SUM(data_length + index_length) 'Size in Bytes', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 'Size in MiB' FROM information_schema.tables  GROUP BY table_schema;

# Size of tables database bookstore:
SELECT
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "bookstore"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

# Конвертация таблиц
req ()
{
    mar="/usr/bin/mariadb -s"
    echo $@| $mar
}

#for i in $(req "SELECT DISTINCT * FROM information_schema.columns WHERE table_schema = 'zabbix' AND COLLATION_NAME LIKE 'utf8mb4%' order by table_name;")
for i in $(req "SELECT DISTINCT table_name FROM information_schema.columns WHERE table_schema = 'zabbix'order by table_name;")
do
    req "show create table zabbix.${i};"|grep -q "utf8mb4" && req "ALTER TABLE zabbix.${i} CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_bin;"
done

# my.cnf:
skip-name-resolve=1
performance_schema       = ON
max_allowed_packet     = 1G
max_connections        = 160
plugin_load_add = wsrep_info

log_error = /var/log/mysql/error.log
slow_query_log
log_slow_query_file    = /var/log/mysql/mariadb-slow.log
log_slow_query_time    = 10
log_slow_verbosity     = query_plan,explain
log-queries-not-using-indexes
log_slow_min_examined_row_limit = 1000

innodb_buffer_pool_size = 18G