MySql tips and snippets

mysql-snippets

-- размер баз на текущем сервере
SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema

-- размер таблиц
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;

-- protect root after install MariaDb installation https://mariadb.com/kb/en/library/set-password/
>mysql.exe -u root
MariaDB [(none)]> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('new root password');
MariaDB [(none)]> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new root password');
MariaDB [(none)]> SET PASSWORD FOR 'root'@'::1' = PASSWORD('new root password');

--показать серверную переменную по имени
SHOW VARIABLES LIKE 'character_set_server';

-- показать все переменные
SHOW VARIABLES;

--загрузить query_file.sql файл с корректной кодировкой target_encoding в базу target_db
>mysql.exe -u root
mysql>use target_db;
mysql>set names 'target_encoding';
mysql>source Drive:/with/full/path/to/query_file.sql;

--таймаут клиентской сессии mysql в секундах (например, полчаса 1800 сек)
mysql.ini>>>
[mysqld]
wait_timeout=1800
interactive_timeout=1800
<<<
sql>>>
set global wait_timeout = 1800;
set global interactive_timeout = 1800;
<<<