This is a collection of commonly used MySQL statements in my daily work. Mainly for myself to copy-paste and save time.

Create User and GRANT READ-ONLY permission

SELECT * FROM mysql.user;
CREATE USER 'read-only-user'@'%' IDENTIFIED BY 'aSecurePassword1';
GRANT SELECT ON MyDB.* TO 'read-only-user'@'%';
SHOW GRANTS FOR  'read-only-user'@'%';

Show total size of a table

SELECT
    table_name AS 'Table'
    round(((data_length + index_length) / 1024/1024/1024), 2) AS 'Size in GB'
FROM information_schema.TABLES
WHERE table_schema = '<db_name>'
AND table_name = '<table_name>';

Show total size of all tables

SELECT
    table_schema AS 'Database'
    round(((data_length + index_length) / 1024/1024/1024), 2) AS 'GB'
FROM information_schema.TABLES
ORDER BY (data_length + index_length) desc;

Show total size of Database

SELECT
    table_schema AS 'Database',
    table_name AS 'Table'
    round(((data_length + index_length) / 1024/1024/1024), 2) AS 'GB'
FROM information_schema.TABLES
WHERE table_schema = '<db_name>'
ORDER BY (data_length + index_length) desc;

Search mysqlbinlog

cd /var/log/mysql
mysqlbinlog mysql-bin.00001 | grep -m 100 -C 5 'searchString or position'

-C: Prints additional x lines surrounding the matching line
-m: Max number of returned matchings

Find all referencing tables

If we want to find all tables which reference parent_table, we can use following command.

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE
    REFERENCED_TABLE_NAME = '<parent_table>';