Handy MySQL Tips & Tricks

README.md

MySQL Notes

Scripts

1. database-sizes.sql - To determine the sizes of all of your databases.
2. table-sizes.sql - To determine the sizes of all of the tables in a specific database. Replace database_name with the name of the database that you want to check.
3. new-database-with-user.sql - To create a new database and a user that has access to that database.

Sources

• How to determine the size of MySQL databases and tables

database-sizes.sql

-- Get size in MB
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;

-- Get size in GB
SELECT table_schema "Database",
ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 1) "Size (GB)" 
FROM information_schema.TABLES 
GROUP BY table_schema;

encoding-and-collation-check.sql

mysql> SELECT default_character_set_name, default_collation_name FROM information_schema.schemata;
+----------------------------+------------------------+
| default_character_set_name | default_collation_name |
+----------------------------+------------------------+
| utf8                       | utf8_general_ci        |
| utf8                       | utf8_general_ci        |
| utf8                       | utf8_general_ci        |
| utf8mb4                    | utf8mb4_bin            |
| utf8mb4                    | utf8mb4_bin            |
| utf8                       | utf8_general_ci        |
+----------------------------+------------------------+
6 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE  'char%';
+--------------------------+----------------------------------------------------------+
| Variable_name            | Value                                                    |
+--------------------------+----------------------------------------------------------+
| character_set_client     | utf8                                                     |
| character_set_connection | utf8                                                     |
| character_set_database   | utf8                                                     |
| character_set_filesystem | binary                                                   |
| character_set_results    | utf8                                                     |
| character_set_server     | utf8                                                     |
| character_set_system     | utf8                                                     |
| character_sets_dir       | /usr/local/Cellar/mysql@5.7/5.7.31/share/mysql/charsets/ |
+--------------------------+----------------------------------------------------------+
8 rows in set (0.00 sec)

new-database-with-user.sql

-- Create new database with new user
CREATE DATABASE IF NOT EXISTS database_name;
CREATE USER 'database_user'@'%' IDENTIFIED BY 'SoMePaSsWoRd';
GRANT ALL PRIVILEGES ON `database_name`.* TO 'database_user'@'%';
FLUSH PRIVILEGES;
SELECT host,user from mysql.user;

other.sql

-- Change MySQL user password
ALTER USER 'database_user'@'%' IDENTIFIED BY 'SoMePaSsWoRd';
FLUSH PRIVILEGES;

table-sizes.sql

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;