MySQL cheatsheet

mysql client

Command	Description
`mysql -h host -u user -p`	Connect to server
`mysql -u root -p -e "SELECT 1"`	Run single query
`SHOW DATABASES;`	List databases
`SHOW TABLES FROM db;`	List tables
`DESCRIBE table_name;`	Column definitions
`SHOW PROCESSLIST;`	Active sessions

Service and health

Command	Description
`systemctl status mysql`	Service status (MySQL)
`systemctl status mariadb`	Service status (MariaDB)
`mysqladmin ping -h localhost`	Check if server is up
`ss -tlnp \| grep 3306`	Confirm listener
`tail -f /var/log/mysql/error.log`	Follow error log

Session and locks

SQL	Description
`KILL QUERY id;`	Cancel running query
`KILL id;`	Terminate connection
`SHOW ENGINE INNODB STATUS\G`	InnoDB locks, deadlocks, buffer pool
`SHOW VARIABLES LIKE 'max_connections';`	Connection limit
`EXPLAIN SELECT ...;`	Query execution plan

Replication — primary / source

SQL	Description
`SHOW MASTER STATUS;`	Binlog file and position (legacy)
`SHOW BINARY LOGS;`	List binlog files
`SHOW BINLOG EVENTS IN 'binlog.000003';`	Inspect binlog events
`SELECT @@gtid_executed;`	Applied GTID set (if enabled)

Replication — replica

SHOW REPLICA STATUS\G
-- Legacy: SHOW SLAVE STATUS\G
-- Key fields: Replica_IO_Running, Replica_SQL_Running,
--   Last_Error, Seconds_Behind_Source

START REPLICA;    -- or START SLAVE;
STOP REPLICA;
RESET REPLICA ALL;  -- careful: removes relay logs and connection settings

Set up replication (GTID example)

-- On replica (after backup restore):
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='primary.example.com',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='secret',
  SOURCE_AUTO_POSITION=1;
START REPLICA;

Database size

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

Backup and restore

Command	Description
`mysqldump --single-transaction mydb > mydb.sql`	Consistent InnoDB logical backup
`mysqldump --all-databases > all.sql`	Full server logical backup
`mysql mydb < mydb.sql`	Restore from SQL dump
`mariabackup --backup --target-dir=/backup/full`	Physical hot backup (MariaDB)
`mysqlbinlog binlog.000012 > replay.sql`	Extract binlog for PITR

Pro tips

MariaDB uses the same client — service name may be mariadb not mysql
SHOW REPLICA STATUS\G — always check Last_Error when replication stops
Prefer binlog_format=ROW for fewer replication surprises
Never write to a read replica — set read_only=1 and super_read_only=1
Replicas are not backups — use mysqldump --single-transaction or mariabackup; test restores

Practice scenarios

Hands-on MySQL scenarios on live Linux VMs: mysql