> For the complete documentation index, see [llms.txt](https://sys.disetti.it/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://sys.disetti.it/system-administration/mysql.md).

# MySql

## Query

### List sizes of all databases

```
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;
```

### List the sizes of all of the tables in a specific database

```
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;
```

### Kill all sleep connections

```
for proc in $(mysql --host IP -uroot -pPWD -e "SHOW PROCESSLIST" | grep Sleep | awk '{ print $1  }'); do mysql --host IP -uroot -pPWD -e "KILL $proc"; done
```

### Show engine for all tables

```
SHOW TABLE STATUS WHERE Name = 'xxx'
```