MySQL Cheat Sheet

Henrik Enger Milo included in Cheat Sheet

2023-05-08 100 words One minute

Contents

Here is a list of different useful commands for MySQL:

Change password for user:

ALTER USER 'userName'@'localhost' IDENTIFIED BY 'New-Password-Here';

Show all users on mysql server, with hosts(localhost, %, etc.):

SELECT host, user FROM mysql.user;

Show grants for a database:

SHOW GRANTS FOR "DATABASE";

Dump the actual size of all the databases on the server in MB:

SELECT table_schema "DB Name",
		ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM information_schema.tables 
GROUP BY table_schema;

See active queries:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;

Change Default authentication method to “old style”:

ALTER USER 'db_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';