MySQL basics: connect with the mysql client, create users, grant privileges and check what is running

MySQL accounts are not just a username: they are user@host. Most "access denied" puzzles for beginners come from that one detail.

#Connect

mysql -h db01.internal -u alice -p                 # prompts for password
mysql -h mydb.mysql.database.azure.com -u alice -p --ssl-mode=REQUIRED

Avoid passwords on the command line. Use an option file, ~/.my.cnf (mode 600):

[client]
host=db01.internal
user=alice
password=S3cret!
ssl-mode=REQUIRED

Or the encrypted login path:

mysql_config_editor set --login-path=db01 --host=db01.internal --user=alice --password
mysql --login-path=db01

#Look around

SELECT VERSION(), CURRENT_USER(), @@hostname;
SHOW DATABASES;
USE shop;
SHOW TABLES;
DESCRIBE orders;
SHOW CREATE TABLE orders\G

\G at the end prints one column per line, handy for wide rows.

#Accounts are user@host

SELECT user, host, plugin, account_locked FROM mysql.user ORDER BY user, host;

'app'@'10.0.%' and 'app'@'localhost' are different accounts with different passwords and privileges. MySQL picks the most specific host match first, which is why an anonymous ''@'localhost' account (old installs) can shadow yours.

#Create an application user

CREATE USER 'app_shop'@'10.0.%' IDENTIFIED BY 'change-me-long-random';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app_shop'@'10.0.%';
SHOW GRANTS FOR 'app_shop'@'10.0.%';

The host pattern limits where the account can connect from. % alone means anywhere; avoid it for privileged accounts.

A read-only user for reports:

CREATE USER 'report_ro'@'10.0.20.%' IDENTIFIED BY 'another-long-random';
GRANT SELECT ON shop.* TO 'report_ro'@'10.0.20.%';

Warning

Never give applications ALL PRIVILEGES ON *.* or SUPER. A compromised app could then drop every database or change server settings.

#Roles (MySQL 8.0+)

CREATE ROLE 'shop_read', 'shop_write';
GRANT SELECT ON shop.* TO 'shop_read';
GRANT INSERT, UPDATE, DELETE ON shop.* TO 'shop_write';

GRANT 'shop_read', 'shop_write' TO 'app_shop'@'10.0.%';
SET DEFAULT ROLE ALL TO 'app_shop'@'10.0.%';

Without SET DEFAULT ROLE, granted roles are not active at login, and the user seems to have no privileges.

#Change and expire passwords

ALTER USER 'app_shop'@'10.0.%' IDENTIFIED BY 'new-long-random';
ALTER USER 'alice'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'olduser'@'%' ACCOUNT LOCK;

MySQL 8.0+ supports dual passwords (RETAIN CURRENT PASSWORD), so applications can switch to the new password before the old one is discarded:

ALTER USER 'app_shop'@'10.0.%' IDENTIFIED BY 'new-pass' RETAIN CURRENT PASSWORD;
-- after all app instances use the new password
ALTER USER 'app_shop'@'10.0.%' DISCARD OLD PASSWORD;

#What is running right now

SHOW PROCESSLIST;
SELECT id, user, host, db, command, time, state, LEFT(info, 80) AS query
FROM information_schema.processlist
WHERE command <> 'Sleep' ORDER BY time DESC;

Stop a runaway query (keeps the connection) or the whole connection:

KILL QUERY 12345;
KILL 12345;
-- on Amazon RDS: CALL mysql.rds_kill_query(12345);

#Basic health

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'Uptime';
SELECT table_schema, ROUND(SUM(data_length + index_length)/1024/1024/1024, 2) AS gb
FROM information_schema.tables GROUP BY table_schema ORDER BY gb DESC;

#Common errors

#Prevent it next time

• One account per application and environment, restricted by host pattern.
• Roles for privilege sets, SET DEFAULT ROLE ALL for every user.
• Credentials in option files or a secrets manager, never in shell history.