# My MySQL Cheat Sheet

This is a collection of commonly used MySQL statements in my daily work. Mainly for myself to copy-paste and save time.
![copy-paste-meme](https://media.tenor.com/0heitU7-tg4AAAAC/copy-paste-paste.gif align="center")

## Create User and GRANT READ-ONLY permission
```sql
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
```sql
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
```sql
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
```sql
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
```bat
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<br>
`-m`: Max number of returned matchings<br>

## Find all referencing tables
If we want to find all tables which reference `parent_table`, we can use following command.
```sql
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>';
```