Databases notes

Mysql

• Show database data length

SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.tables GROUP BY table_schema;

• Show table data length

SELECT table_name AS 'Table', ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)' FROM information_schema.tables WHERE table_schema = 'database_name' ORDER BY (data_length + index_length) DESC;

• Purge bin log

PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;

• Change pass user

 ALTER USER 'root'@'%' IDENTIFIED BY 'abc123';

• Show all user

SELECT user, host, plugin, authentication_string FROM mysql.user;

• Create user and grant

CREATE USER 'test'@'%' IDENTIFIED BY 'abc123';
GRANT ALL PRIVILEGES ON *.* TO 'test'@'%';