TechEarl

MySQL Cheat Sheet

MySQL cheat sheet covering CLI commands, database and table operations, joins, indexes, backups, user management, and transactions, with version notes for 5.7, 8.0, and 8.4.

Ishan KarunaratneIshan Karunaratne⏱️ 19 min readUpdated
Open vintage hardcover reference manual on a dark slate desk, dense columned print on warm cream pages lit by a single warm amber side lamp

A MySQL cheat sheet is a single-page reference of the most common MySQL commands and SQL syntax, grouped by what you are trying to do: log into the server, create databases and tables, manipulate rows, join across tables, manage users, take backups, debug a stuck query. This one covers MySQL 5.5 through 8.4 and 9.x. Every category has copy-paste syntax, an "as of which version" note where it matters, and an input box at the top to swap the placeholder names (mydb, users, etc.) for your own table and column names — the rest of the card updates as you type.

What is the MySQL cheat sheet for?

This cheat sheet is the one-page lookup for the MySQL commands you reach for most often: logging in (mysql -u root -p), database operations (SHOW DATABASES, CREATE DATABASE, USE mydb), backup and restore (mysqldump --single-transaction --routines --events), table operations (CREATE TABLE, ALTER TABLE, DESCRIBE), data manipulation (INSERT, UPDATE, DELETE, SELECT), joins (INNER, LEFT, RIGHT), indexes (CREATE INDEX, EXPLAIN), user management (CREATE USER, GRANT), and transactions (START TRANSACTION, COMMIT, ROLLBACK). Each entry has the copy-paste syntax plus a one-line explanation; the version-compatibility table further down spells out which features (window functions, CTEs, ALGORITHM=INSTANT, the caching_sha2_password auth plugin, the new VECTOR type in 9.0) require MySQL 8.0 or later. Use it as a single-page reference when you remember the shape of a command but not the exact flags.

MySQL Cheat Sheet

MySQL Cheat Sheet reference covering essential CLI commands, database operations, functions, joins, indexes, and best practices. Includes practical examples and syntax for common MySQL operations.

Logging In from CLI

mysql -u root -pLog in to MySQL as root with a password prompt
mysql -u username -pLog in to MySQL as a specific user with a password prompt
mysql -u username -p passwordLog in to MySQL as a specific user by providing the password directly (not recommended for security reasons)
mysql -u username -h 192.168.1.100 -P 3306 -pLog in to MySQL on a remote server with a specific IP address and port
mysql --defaults-file=/path/to/my.cnfLog in to MySQL using a configuration file for credentials

Database Operations

SHOW DATABASES;List all databases
CREATE DATABASE mydb;Create a new database
USE mydb;Switch to a database
DROP DATABASE mydb;Delete a database
SHOW TABLES;List all tables in current database
DESCRIBE users;Show structure of 'users' table

Backup and Restore

mysqldump -u root -p mydb > backup.sqlBackup a single database to a file
mysqldump -u root -p --all-databases > alldb_backup.sqlBackup all databases to a file
mysqldump -u root -p mydb --routines --triggers > backup_with_triggers.sqlBackup a database including stored procedures and triggers
mysql -u root -p mydb < backup.sqlRestore a database from a backup file
mysql -u root -p < alldb_backup.sqlRestore all databases from a backup file
mysqldump -u root -p mydb --no-data > schema_only.sqlBackup only the schema (no data) of a database
mysqlpump -u root -p --databases mydb --result-file=backup.sqlUse mysqlpump for faster, parallelized backup of a database
mysqlcheck -u root -p --databases mydb --repairRepair and optimize databases before taking a backup

User Management

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password';Create a new MySQL user
GRANT ALL PRIVILEGES ON mydb.* TO 'user1'@'localhost';Grant all privileges to a user
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user1'@'localhost';Revoke all privileges from a user
SHOW GRANTS FOR 'user1'@'localhost';Display privileges of a user
DROP USER 'user1'@'localhost';Delete a MySQL user
CREATE ROLE 'developer';Create a role to manage permissions
GRANT 'developer' TO 'user1'@'localhost';Assign a role to a user
ALTER USER 'user1'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;Set a password expiry policy for a user

Debugging and Diagnostics

SHOW PROCESSLIST;View currently running MySQL processes
SHOW ENGINE INNODB STATUS;Display detailed InnoDB status for debugging
SHOW VARIABLES LIKE 'max_connections';Check the maximum number of allowed connections
SHOW STATUS LIKE 'Threads_connected';View the number of currently active connections
EXPLAIN SELECT * FROM users WHERE id = 1;Analyze the query execution plan
SHOW FULL PROCESSLIST;Display all processes, including those from other users
SHOW WARNINGS;Display warnings generated by the last command
SHOW ERRORS;Display errors generated by the last command
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;View active InnoDB transactions
SHOW SLAVE STATUS \G;Check the status of a replication slave
SHOW MASTER STATUS;Check the status of the replication master

Table Operations

CREATE TABLE users (id INT PRIMARY KEY);Create a new table
ALTER TABLE users ADD email VARCHAR(100);Add column to table
ALTER TABLE users DROP COLUMN email;Remove column from table
DROP TABLE users;Delete a table
TRUNCATE TABLE users;Remove all data from a table

Data Manipulation

INSERT INTO users VALUES (1, 'John');Insert a new row
UPDATE users SET name='Jane' WHERE id=1;Update existing rows
DELETE FROM users WHERE id=1;Delete rows
SELECT * FROM users WHERE age > 18;Retrieve data
SELECT * FROM users ORDER BY name ASC;Sort query results
SELECT dept, COUNT(*) FROM users GROUP BY dept;Group query results

Joins

SELECT * FROM orders o JOIN users u ON o.user_id = u.id;Inner join
SELECT * FROM users u LEFT JOIN orders o ON u.id = o.user_id;Left join
SELECT * FROM orders o RIGHT JOIN users u ON o.user_id = u.id;Right join
SELECT * FROM users u FULL OUTER JOIN orders o ON u.id = o.user_id;Full outer join

Transactions

START TRANSACTION;Begin a new transaction
COMMIT;Commit the current transaction
ROLLBACK;Roll back the current transaction
SET autocommit = 0;Disable autocommit mode
SET autocommit = 1;Enable autocommit mode

Indexes

CREATE INDEX idx_name ON users (name);Create a simple index on a column
CREATE UNIQUE INDEX idx_email ON users (email);Create a unique index to enforce uniqueness on a column
CREATE INDEX idx_multi_col ON users (name, email);Create a composite index on multiple columns
CREATE INDEX idx_partial ON users (name(10), email(15));Create an index with length limits for columns to save storage
ALTER TABLE users DROP INDEX idx_name;Remove an index using ALTER TABLE (compatible with all versions)
SHOW INDEX FROM users;Show all indexes for a table
CREATE FULLTEXT INDEX idx_content ON articles (content);Create a FULLTEXT index for text search (requires InnoDB or MyISAM)
OPTIMIZE TABLE users;Rebuilds and optimizes indexes after heavy changes to table data
EXPLAIN SELECT * FROM users WHERE name = 'John';Analyze query performance and check index usage

Step-by-step walkthroughs by task

The cheat sheet above is the one-line syntax. For the cases where the syntax is not enough — picking between three correct methods, dealing with edge cases like NULL values or huge tables, or migrating between MySQL versions — there is a deeper walkthrough for every common task. Linked by the search intent that brings people here.

Finding and removing duplicate rows

Duplicate rows are almost always the result of a missing UNIQUE constraint, and they show up in customer lists, webhook event logs, mail queues, anywhere data gets imported repeatedly. There are three correct ways to identify them and three correct ways to remove them.

  • How to find duplicate rows in MySQL — GROUP BY ... HAVING COUNT(*) > 1, ROW_NUMBER() window function (MySQL 8.0+), and the self-join pattern that works on every MySQL version. With NULL handling, case- and whitespace-insensitive matching, and the index that makes it fast.
  • How to delete duplicate rows in MySQL — Delete extras while keeping one per group, using DELETE with a self-join, DELETE with a ROW_NUMBER() CTE, or the safer temp-table swap for huge tables. Includes the dry-run-first pattern and adding the UNIQUE constraint afterwards.

Schema changes on a live table

ALTER TABLE is the workhorse, and on a production table that is being read and written constantly, the version matters. MySQL 8.0.12 introduced ALGORITHM=INSTANT for column additions, which finishes in milliseconds even on tables with hundreds of millions of rows.

  • How to add a column to a MySQL table — ALTER TABLE ADD COLUMN with DEFAULT values, the three-step pattern for NOT NULL on a table that already has rows, AFTER positioning, and ALGORITHM=INSTANT for online changes that do not lock.
  • How to change a MySQL column type — Widening INT to BIGINT, growing a VARCHAR, narrowing safely, and converting between text and numeric columns without losing data. With MODIFY vs CHANGE and a rollback strategy.

Exporting and importing data

Two of the most-asked operational questions: how do I get this data out of MySQL into a file, and how do I get a CSV from somewhere else loaded back in.

  • How to export a MySQL table to CSV — SELECT INTO OUTFILE, mysqldump --tab, the mysql client piped to a file (for managed databases where you do not have the FILE privilege), and a Python-based pipeline that correctly handles commas and quotes inside the data.
  • How to import a CSV file into MySQL — LOAD DATA INFILE, LOAD DATA LOCAL INFILE for client-side files, mysqlimport for batch jobs. With header-row handling, character-encoding fixes, and column-list mapping when the CSV does not match the table.

Joining and comparing across tables

JOINs are where most non-trivial queries live. The right join type for your question matters more than syntax fluency.

Server administration

The two things you Google for at 3 a.m. when the server is acting up.

Formatting datetime columns

  • How to format a MySQL DATETIME in a SELECT — DATE_FORMAT with the specifiers you actually use (%Y-%m-%d %H:%i:%s, %W, %M %D, %Y, etc.), common presets for ISO 8601, RFC 2822, and human-readable output, plus CONVERT_TZ for timezone-correct dashboards.

MySQL version compatibility quick reference

Some commands and features behave differently across MySQL versions. The headline differences are window functions and common table expressions (8.0), instant column additions (8.0.12), and the authentication plugin change in 8.4.

VersionGA releaseWindow functionsCTEs (WITH)ALGORITHM=INSTANTDefault auth pluginVECTOR type
MySQL 5.5Dec 2010NoNoNomysql_native_passwordNo
MySQL 5.6Feb 2013NoNoNomysql_native_passwordNo
MySQL 5.7Oct 2015NoNoNomysql_native_passwordNo
MySQL 8.0Apr 2018YesYes8.0.12+caching_sha2_passwordNo
MySQL 8.4 LTSApr 2024YesYesYescaching_sha2_passwordNo
MySQL 9.0+ InnovationJul 2024YesYesYescaching_sha2_passwordYes

If a SQL pattern in this cheat sheet uses ROW_NUMBER(), RANK(), WITH ... AS, or ALGORITHM=INSTANT, the minimum version is 8.0. The VECTOR data type and the matching DISTANCE() function for similarity search are 9.0+ only (innovation track, not yet in an LTS release). For applying that in practice, see How to Add Semantic Search to a MySQL App. Everything else works on 5.5 and later.

Modern MySQL syntax worth knowing

A few patterns that are easy to miss because they post-date the 5.7 documentation most people learned from:

  • Common Table Expressions (CTEs) since 8.0. WITH active AS (SELECT * FROM users WHERE status = 'active') SELECT COUNT(*) FROM active;. Recursive CTEs (WITH RECURSIVE) traverse hierarchies (employee/manager, category trees) without chained self-joins.
  • JSON path expressions since 5.7, with more operators in 8.0. SELECT payload->>'$.user.email' FROM events; extracts a JSON value as text. The -> operator returns a JSON value; ->> returns it unquoted as text. JSON_TABLE() (8.0+) turns a JSON array into a relational rowset for joining.
  • ALGORITHM=INSTANT for column additions since 8.0.12. ALTER TABLE users ADD COLUMN last_seen DATETIME, ALGORITHM=INSTANT; finishes in milliseconds even on tables with hundreds of millions of rows because it only updates metadata. Walked through in detail in How to Add a Column to a MySQL Table.
  • The VECTOR type and DISTANCE() function since 9.0. CREATE TABLE docs (id INT PRIMARY KEY, embedding VECTOR(1536)); stores an OpenAI-shaped embedding; SELECT id FROM docs ORDER BY DISTANCE(embedding, ?, 'COSINE') LIMIT 10; runs the nearest-neighbour query. Full walkthrough at How to Add Semantic Search to a MySQL App.
  • mysqldump --single-transaction --routines --events as the right default. The flag combination dumps a consistent snapshot of InnoDB data without holding write locks, plus the stored procedures, functions, and event scheduler entries that a bare mysqldump skips. Detail in How to Export or Back Up All MySQL Databases.

Common MySQL error codes and quick fixes

These are the eight error codes that account for most "MySQL is broken" questions. Each one has a single common cause and a single common fix.

Error codeMessageMost common causeQuick fix
1045Access denied for userWrong password, wrong host in user grant, or expired passwordVerify with SELECT user, host FROM mysql.user; check IDENTIFIED BY or reset the password
1062Duplicate entry for keyAn INSERT violated a UNIQUE constraintUse INSERT ... ON DUPLICATE KEY UPDATE or pre-check with SELECT
1146Table doesn't existWrong database selected with USE, or table was droppedSHOW TABLES and SHOW DATABASES to confirm
1205Lock wait timeout exceededAnother transaction holding the row lock for too longFind with SHOW PROCESSLIST and SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; kill the blocking transaction
1364Field doesn't have a default valueNOT NULL column inserted without value, in strict SQL modeProvide a value, add a DEFAULT, or relax the column to nullable
1452Cannot add or update child row: foreign key constraint failsReferenced row missing in parent tableInsert the parent row first or fix the foreign-key value
1366Incorrect integer valueString being inserted into an INT column, with strict mode onCast in the query or clean the source data
2002Can't connect to local MySQL server through socketmysqld is not running, or the socket path is wrongCheck systemctl status mysql; verify socket= in my.cnf matches

For the full list of MySQL error codes and their server-side detail, the official reference is the MySQL server error message reference.

Frequently asked questions

External MySQL references

For anything not on this page, the canonical sources are the official Oracle-maintained docs and the curated landing pages:

TagsMySQLSQLDatabaseCheat SheetProgrammingDevelopmentRDBMSSQL CommandsDatabase Administration
Share
Ishan Karunaratne

Ishan Karunaratne

Tech Architect · Software Engineer · AI/DevOps

Tech architect and software engineer with 20+ years building software, Linux systems, and DevOps infrastructure, and lately working AI into the stack. Currently Chief Technology Officer at a healthcare tech startup, which is where most of these field notes come from.

More by Ishan →LinkedIn
Keep reading

Related posts

Regex Cheat Sheet including regex symbols, ranges, grouping, assertions, syntax tables, examples, matches, and compatibility tables. Definitive Regular Expressions Quick Reference!
Regex

Regex Cheat Sheet

Regex Cheat Sheet including regex symbols, ranges, grouping, assertions, syntax tables, examples, matches, and compatibility tables. Definitive Regular Expressions Quick Reference!

Elasticsearch 9.x cheat sheet: index and document operations, Query DSL, aggregations, vector / kNN search, ESQL, cluster management, and common mistakes.
Database

Elasticsearch Cheat Sheet

Practitioner reference for Elasticsearch 9.x: index and document operations, Query DSL, aggregations, vector / kNN search, ESQL, cluster management, version compatibility notes, and the gotchas that bite first-time operators.