Log in interactively so the password never lands in shell history
mysql -h 127.0.0.1 -P 3306 -u root -pMySQL Common Commands - CodeSwordsman
High-frequency commands for connections, queries, backups, grants and lock troubleshooting, with risk notes for locking and large tables.
MySQL 5.7 / 8.0Updated 2026-10-2934 commands
Connection & Schema
Log in and switch straight to the given database
mysql -h 127.0.0.1 -P 3306 -u root -p -D <DB>List all databases visible to the current account
show databases;Switch the current database
use <DB>;List all tables in the current database
show tables;Show a table structure; same as show columns from
desc <TABLE>;Risky
Empty the whole table and reset auto-increment; cannot be rolled back
truncate table <TABLE>;Queries & Execution Plan
Inspect the execution plan and confirm index usage
explain select * from <TABLE> where id = 1;Run the query and report real timing; requires MySQL 8.0.18+
explain analyze select * from <TABLE> where id = 1;Paginate with ordering; avoid full scans without a limit
select id, name
from <TABLE>
where status = 1
order by id desc
limit 20;Show active connections and their running statements
show processlist;Show index definitions and cardinality for a table
show index from <TABLE>;Count rows matching a condition; use with care on large tables
select count(*) from <TABLE> where created_at >= '2026-01-01';Backup & Restore
Dump one database including routines and triggers
mysqldump -u root -p --single-transaction --routines --triggers <DB> > <DB>.sqlUse --single-transaction on InnoDB to avoid locking tables during the dump
Dump all databases into a single file
mysqldump -u root -p --single-transaction --all-databases > all.sqlDump the schema only, without any data
mysqldump -u root -p --single-transaction --no-data <DB> > schema.sqlDump several databases and keep their CREATE DATABASE statements
mysqldump -u root -p --single-transaction --databases <DB1> <DB2> > multi.sqlRestore a dump file into the target database
mysql -u root -p <DB> < <DB>.sqlRun inside the mysql client to import a dump file
source /backup/<DB>.sqlUsers & Grants
Create an account restricted to a subnet
create user 'app'@'10.0.%' identified by '<PASSWORD>';Grant CRUD privileges on a single database
grant select, insert, update, delete on <DB>.* to 'app'@'10.0.%';No flush privileges needed after GRANT; only after editing grant tables directly
Show the privileges granted to an account
show grants for 'app'@'10.0.%';Revoke one privilege without touching the others
revoke delete on <DB>.* from 'app'@'10.0.%';Risky
Drop the account; existing sessions lose access at once
drop user 'app'@'10.0.%';Variables & Status
Show the max_connections setting
show variables like 'max_connections';Show the number of currently open connections
show global status like 'Threads_connected';Show InnoDB transactions, lock waits and buffer pool state
show engine innodb status\GEnable the slow query log to catch inefficient SQL
set global slow_query_log = on;Applies to new sessions only and is lost on restart; persist it in my.cnf
Set the slow query threshold to one second
set global long_query_time = 1;Locks & Troubleshooting
List running transactions ordered by start time
select * from information_schema.innodb_trx order by trx_started;Show the current lock wait relationships
select * from performance_schema.data_lock_waits;MySQL 8.0 view; on 5.7 use information_schema.innodb_lock_waits
Find long-running queries ordered by elapsed time
select * from information_schema.processlist where command <> 'Sleep' order by time desc;Kill only the statement running on that connection
kill query 12345;Risky
Terminate the connection and roll back its uncommitted transaction
kill 12345;