MySQL

mysql client, databases and users, backups, troubleshooting.

Connecting

Connect to a server

mysql -h <host> -P <port> -u <user> -p

Connect straight to a database

mysql -h <host> -u <user> -p <database>

Run a query non-interactively

mysql -h <host> -u <user> -p -e "SELECT NOW();"

Run an SQL file

mysql -h <host> -u <user> -p <database> < script.sql

Databases and tables

List databases

SHOW DATABASES;

Switch database

USE <database>;

List tables

SHOW TABLES;

Table structure

DESCRIBE <table>;

Table DDL

SHOW CREATE TABLE <table>\G

Create a utf8mb4 database

CREATE DATABASE <database> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Database sizes in MBadvanced

SELECT table_schema AS db,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;

Users and privileges

Create a user

CREATE USER '<user>'@'%' IDENTIFIED BY 'password';

Grant all privileges on a database

GRANT ALL PRIVILEGES ON <database>.* TO '<user>'@'%';

Show user privileges

SHOW GRANTS FOR '<user>'@'%';

Change password

ALTER USER '<user>'@'%' IDENTIFIED BY 'new_password';

Backup and restore

Dump a single database

mysqldump -h <host> -u <user> -p <database> > <database>.sql

Dump without locking (InnoDB)advanced

mysqldump -h <host> -u <user> -p --single-transaction --quick --routines --triggers <database> | gzip > <database>.sql.gz

--single-transaction gives a consistent snapshot without LOCK TABLES.

Restore from a gzipped dump

gunzip < <database>.sql.gz | mysql -h <host> -u <user> -p <database>

Troubleshooting

Running queries

SHOW FULL PROCESSLIST;

Kill a query

KILL <process_id>;

Query execution planadvanced

EXPLAIN ANALYZE SELECT * FROM <table> WHERE id = 1;

InnoDB status (locks, deadlocks)advanced

SHOW ENGINE INNODB STATUS\G

Replication statusadvanced

SHOW REPLICA STATUS\G

Before 8.0.22 — SHOW SLAVE STATUS\G