Essential MySQL Commands Every DBA Should Know

Connection & Basic Management

Login to MySQL: mysql -h 127.0.0.1 -P 3306 -u root -p (short form: mysql -uroot -p)

Exit the client: exit or \q or quit Show the current user: SELECT CURRENT_USER(); or SELECT USER(); Change the password of the current user (MySQL 8.0+): ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'; Refresh privileges after changes:

FLUSH PRIVILEGES;

Database Operations

Create a database:

CREATE DATABASE testdb DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

Select or switch to a database: USE testdb; List all databases: SHOW DATABASES; Drop a database (use with caution):

DROP DATABASE IF EXISTS testdb;

Table Operations

Show tables in the current database: SHOW TABLES; Create a table (example):

CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Describe a table (three common ways): DESCRIBE users;, SHOW COLUMNS FROM users;, SHOW CREATE TABLE users\G Add a column: ALTER TABLE users ADD COLUMN age TINYINT UNSIGNED DEFAULT 0; Modify a column type:

ALTER TABLE users MODIFY COLUMN username VARCHAR(100) NOT NULL;

Drop a table: DROP TABLE IF EXISTS old_table; Truncate a table (keep structure): TRUNCATE TABLE users; (or DELETE FROM users;)

Data Manipulation (CRUD)

Insert a single row: INSERT INTO users (username, age) VALUES ('tom', 28); Batch insert (best performance):

INSERT INTO users (username, age) VALUES ('a',22),('b',25),('c',30);

Update rows: UPDATE users SET age = age + 1 WHERE id = 1; Delete rows with a limit: DELETE FROM users WHERE id > 1000 LIMIT 500; Basic select with filter, order and limit:

SELECT id, username, age FROM users WHERE age >= 18 ORDER BY id DESC LIMIT 10;

Aggregate with GROUP BY and HAVING:

SELECT age, COUNT(*) AS cnt FROM users GROUP BY age HAVING cnt > 5 ORDER BY cnt DESC;

Query Optimization & Diagnostics

Explain execution plan: EXPLAIN SELECT ...; or EXPLAIN ANALYZE SELECT ...; (MySQL 8.0+)

Enable and configure slow‑query log:

SHOW VARIABLES LIKE '%slow_query%';
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;

Show currently running statements (useful for troubleshooting): SHOW PROCESSLIST; or

SELECT * FROM information_schema.processlist ORDER BY TIME DESC;

View InnoDB engine status (locks, transactions, buffer pool, etc.): SHOW ENGINE INNODB STATUS\G Show indexes of a table: SHOW INDEX FROM users; Force or ignore an index for debugging:

SELECT * FROM users FORCE INDEX(idx_age) WHERE age = 25;

Status Monitoring & Performance

Inspect global or session variables (example for buffer pool and max connections):

SHOW GLOBAL VARIABLES LIKE '%innodb_buffer_pool%';
SHOW SESSION VARIABLES LIKE 'max_connections';

Check server status counters:

SHOW GLOBAL STATUS LIKE 'Threads_%';
SHOW GLOBAL STATUS LIKE 'Queries';

Monitor connection usage:

SHOW STATUS LIKE 'Max_used_connections';
SHOW STATUS LIKE 'Threads_connected';

Query table space and row count for the current database:

SELECT table_name, engine, table_rows,
       data_length/1024/1024 AS MB
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY data_length DESC;

Replication status (if replication is configured):

SHOW MASTER STATUS\G
SHOW SLAVE STATUS\G

User & Permission Management

Create a user: CREATE USER 'appuser'@'%' IDENTIFIED BY '123456'; Grant privileges (common example):

GRANT SELECT, INSERT, UPDATE, DELETE ON testdb.* TO 'appuser'@'%';

Or grant all privileges:

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;

Show privileges of the current user: SHOW GRANTS FOR CURRENT_USER(); or

SHOW GRANTS;