MySQL Commands Cheat Sheet for Developers (8.4 & 9.7 LTS) | RustcodeWeb

MySQL Commands Cheat Sheet for Developers

Q: Which MySQL version should I use in 2027?

MySQL 8.0 reached end of life in April 2026. MySQL 8.4 LTS is a conservative choice for production, and 9.7 LTS is the newer long-term support line. The commands in this guide are standard SQL for these releases, and version-specific differences are noted where they matter. Run SELECT VERSION(); to see what your server uses.

Q: Do I need FLUSH PRIVILEGES after GRANT or CREATE USER?

No. MySQL applies account changes made with CREATE USER, GRANT, REVOKE and ALTER USER immediately. FLUSH PRIVILEGES is only needed if you edit the grant tables directly, which is not recommended.

Q: Why should I avoid GRANT ALL PRIVILEGES ON *.*?

It gives the account control over every database on the server, including system schemas. If the credentials leak or the application has a bug, the damage is much larger. Grant privileges on a single database or table instead.

Q: Can I rename a database in MySQL?

Not directly, because there is no RENAME DATABASE statement. Create a new database and restore a dump into it, or move each table with RENAME TABLE old_db.table_name TO new_db.table_name.

Q: Should I use BOOLEAN or TINYINT(1)?

BOOLEAN is an alias for TINYINT(1), so they behave the same. Use BOOLEAN for readability, and avoid display widths on other integer types because they are deprecated.

Q: How should I store user passwords?

Never store them in plain text. Hash them in your application with bcrypt or Argon2 and store the result in a VARCHAR(255) column. Database account passwords are a separate concern and belong in a secrets manager.

Beginner to intermediateCheat sheetMySQL 8.4 and 9.7 LTS

The commands you use every day for logging in, managing users, working with databases and tables, querying data, and backing up safely, updated for current MySQL releases.

How to use this sheet

Scan the table of contents, jump to the task you need, and copy the example. Replace the sample names with your own, and read the notes under each group before you run anything destructive.

8.4 and 9.7Current LTS releases

13Topic sections

SQL + CLIQueries and terminal commands

SafeLeast privilege and backups

1. Before you begin: versions and conventions

Every example below uses the same sample names, so you can read the sheet top to bottom or jump to the command you need.

Release	Status in November 2026	What to do
MySQL 8.0	Reached end of life in April 2026.	Plan an upgrade to 8.4 LTS or 9.7 LTS.
MySQL 8.4 LTS	Long-term support release, supported for several more years.	A safe default for production systems.
MySQL 9.7 LTS	Newer long-term support line released in April 2026.	Test your application before migrating.
Innovation releases	Short-lived releases for new features.	Use for evaluation, not long-lived production.

Check exact patch versions and support dates on the official MySQL site before you plan an upgrade. Run SELECT VERSION(); to see what your server uses.

Sample names

Database shop, user app_user, tables customers and orders. Replace them with your own.

Two kinds of blocks

bash blocks run in your terminal. sql blocks run inside the mysql> prompt.

Safe by default

Examples use IF EXISTS, least privilege and transactions wherever they reduce risk.

Test first. Run destructive commands such as DROP, TRUNCATE, DELETE and REVOKE on a test copy, and take a backup before you touch production data.

2. Connect and use the MySQL client

Start here: log in, check who and where you are, and run commands from files.

Log in

Prompts for the password so it never lands in your shell history.

mysql -u root -p

Connect to a host, port and database

Use this for remote servers or when you want to start inside a database.

mysql -u app_user -p -h 127.0.0.1 -P 3306 shop

Run one statement without opening the prompt

Handy in scripts and quick checks.

mysql -u root -p -e "SHOW DATABASES;"

Check the server version and current user

Confirm what you are connected to before you change anything.

SELECT VERSION();
SELECT CURRENT_USER();
SELECT DATABASE();

Run a SQL file

From the shell, or from inside the client with source.

mysql -u root -p shop < script.sql

source /path/to/script.sql;

Exit

Any of these closes the client.

exit;

Handy client shortcuts

Shortcut	What it does
`\G`	Ends a statement and prints rows vertically, useful for wide tables.
`\s`	Shows connection and server status.
`\c`	Cancels the statement you are typing.
`help`	Lists client commands.
`\q`	Quits the client.

Avoid -pYourPassword. Passwords typed on the command line can appear in shell history and process lists. Leave the value out so MySQL prompts you.

3. Users, roles and privileges

Give each application its own account with only the access it needs. Avoid using root outside administration.

Create a user

IF NOT EXISTS makes the command safe to re-run.

CREATE USER IF NOT EXISTS 'app_user'@'localhost'
  IDENTIFIED BY 'Use-a-long-random-password';

Change a password

Use ALTER USER for password changes.

ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'New-long-random-password';

List users

Needs access to the mysql system schema.

SELECT User, Host FROM mysql.user;

Show grants

Check what a user can do, or what you can do.

SHOW GRANTS FOR 'app_user'@'localhost';
SHOW GRANTS;

Grant least privilege on one database

The right default for most applications.

GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app_user'@'localhost';

Grant full control of one database

For a trusted owner or migration user. Still limited to a single database.

GRANT ALL PRIVILEGES ON shop.* TO 'app_user'@'localhost';

Remove specific privileges, or all of them

Revoke only what you need to take away.

REVOKE INSERT, UPDATE ON shop.* FROM 'app_user'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app_user'@'localhost';

Drop a user

IF EXISTS avoids an error when the account is already gone.

DROP USER IF EXISTS 'app_user'@'localhost';

Use roles for shared permissions

Roles let you manage permissions for groups of users in one place.

CREATE ROLE IF NOT EXISTS 'report_reader';
GRANT SELECT ON shop.* TO 'report_reader';
GRANT 'report_reader' TO 'analyst'@'localhost';
SET DEFAULT ROLE 'report_reader' TO 'analyst'@'localhost';

Avoid GRANT ALL PRIVILEGES ON *.*. It gives an account control over every database, including system schemas. Reserve it for a small number of administrator accounts, and never use it for an application.

Good to know: FLUSH PRIVILEGES; is not needed after CREATE USER, GRANT or REVOKE. MySQL 8.4 disables the legacy mysql_native_password plugin by default and 9.0 removed it, so use the default caching_sha2_password and update old clients instead.

4. Database operations

Create, select and remove databases, with an explicit character set.

Show databases

Lists every database your account can see.

SHOW DATABASES;

Create a database

Specify utf8mb4 so emoji and all languages store correctly.

CREATE DATABASE IF NOT EXISTS shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

Select a database and check which one is active

Run this before table commands, or you will see "No database selected".

USE shop;
SELECT DATABASE();

Inspect how a database was created

Shows its character set and collation.

SHOW CREATE DATABASE shop;

Delete a database

Permanent. Back up first.

DROP DATABASE IF EXISTS shop;

Renaming: MySQL has no RENAME DATABASE. Create the new database, then dump and restore into it, or move tables with RENAME TABLE old_db.t TO new_db.t;.

5. Table operations

A modern table definition, plus the commands you use to inspect and change it.

Show tables, columns and the full definition

DESCRIBE is the quick view and SHOW CREATE TABLE is the exact one.

SHOW TABLES;
DESCRIBE customers;
SHOW CREATE TABLE customers\G

Create a table

Uses a hash column instead of a plain password, timestamps that maintain themselves and an explicit unique key.

CREATE TABLE IF NOT EXISTS customers (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name          VARCHAR(100)    NOT NULL,
  email         VARCHAR(255)    NOT NULL,
  password_hash VARCHAR(255)    NOT NULL,
  is_student    BOOLEAN         NOT NULL DEFAULT FALSE,
  created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
                                ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_customers_email (email)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

Change a table

Add, change, rename and remove columns with ALTER TABLE.

ALTER TABLE customers ADD COLUMN phone VARCHAR(20) NULL AFTER email;
ALTER TABLE customers MODIFY COLUMN name VARCHAR(150) NOT NULL;
ALTER TABLE customers RENAME COLUMN phone TO mobile;
ALTER TABLE customers DROP COLUMN mobile;

Rename a table

Renames atomically.

RENAME TABLE customers TO clients;

Empty or delete a table

TRUNCATE removes every row and resets AUTO_INCREMENT. Neither can be rolled back.

TRUNCATE TABLE customers;
DROP TABLE IF EXISTS customers;

Passwords: Never store plaintext passwords. Hash them in your application with bcrypt or Argon2 and store the result in a VARCHAR(255) column.

6. Insert, read, update and delete

The four everyday operations, with the safety habits that prevent bad edits.

Insert one row or many

Always name the columns so the statement survives schema changes.

INSERT INTO customers (name, email, password_hash, is_student)
VALUES ('Asha Rao', 'asha@example.com', '$2b$12$exampleHashOnly', TRUE);

INSERT INTO customers (name, email, password_hash)
VALUES ('Dev Patel', 'dev@example.com', '$2b$12$exampleHashOnly'),
       ('Mia Chen',  'mia@example.com', '$2b$12$exampleHashOnly');

Insert or update (upsert)

Updates the row when the unique key already exists. The row alias syntax replaces the deprecated VALUES() function.

INSERT INTO customers (name, email, password_hash)
VALUES ('Asha R. Rao', 'asha@example.com', '$2b$12$exampleHashOnly') AS new_row
ON DUPLICATE KEY UPDATE name = new_row.name;

Read rows

Select only the columns you need, sort them and limit the result.

SELECT id, name, email
FROM customers
WHERE is_student = TRUE
ORDER BY created_at DESC
LIMIT 10;

Update rows

Run the matching SELECT first to confirm what will change.

UPDATE customers
SET name = 'Asha R. Rao'
WHERE id = 1;

Delete rows

Keep the WHERE clause.

DELETE FROM customers WHERE id = 1;

Use a transaction for related changes

Either all statements apply or none do. Works with InnoDB tables.

START TRANSACTION;
UPDATE customers SET is_student = FALSE WHERE id = 2;
-- check the result, then:
COMMIT;   -- or ROLLBACK;

Turn on a safety net for your session

Blocks UPDATE and DELETE statements that have no key-based WHERE.

SET sql_safe_updates = 1;

Never run UPDATE or DELETE without WHERE. Without it, every row in the table changes. Write the WHERE first, then the rest of the statement.

7. Indexes, keys and constraints

Indexes make reads fast, and constraints keep your data valid.

Create a related table with a foreign key

Foreign keys keep orders tied to real customers.

CREATE TABLE IF NOT EXISTS orders (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id BIGINT UNSIGNED NOT NULL,
  total       DECIMAL(10,2)   NOT NULL,
  ordered_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_orders_customer (customer_id),
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers (id)
    ON DELETE CASCADE
) ENGINE = InnoDB;

Add, inspect and drop indexes

Index columns you filter, join and sort on.

CREATE INDEX idx_customers_name ON customers (name);
CREATE INDEX idx_customers_created ON customers (created_at);
SHOW INDEX FROM customers;
DROP INDEX idx_customers_name ON customers;

Check whether a query uses an index

EXPLAIN shows the plan, and EXPLAIN ANALYZE also runs the query and reports real timings.

EXPLAIN SELECT * FROM customers WHERE email = 'asha@example.com';
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1;

Tip: Indexes speed up reads but slow writes and use disk space. Add them for measured needs instead of indexing every column.

8. Filtering, grouping and joins

Patterns you will reuse in almost every project.

Filter with operators

Pattern matching, lists and ranges.

SELECT * FROM customers WHERE name LIKE 'A%';
SELECT * FROM customers WHERE id IN (1, 2, 3);
SELECT * FROM orders
WHERE ordered_at BETWEEN '2026-01-01' AND '2026-12-31';

Group and aggregate

HAVING filters groups after they are counted.

SELECT is_student, COUNT(*) AS total
FROM customers
GROUP BY is_student
HAVING COUNT(*) > 1;

Join tables

INNER JOIN returns only matching rows.

SELECT c.name, o.total, o.ordered_at
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id
ORDER BY o.ordered_at DESC;

Keep rows that have no match

LEFT JOIN keeps every customer, even those without orders.

SELECT c.name, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Paginate results

Use a stable ORDER BY so pages do not overlap.

SELECT id, name FROM customers ORDER BY id LIMIT 20 OFFSET 40;

9. Backup, restore and export

Run these in your terminal, not in the mysql> prompt.

Back up one database

--single-transaction takes a consistent InnoDB snapshot without locking tables.

mysqldump -u root -p --single-transaction --routines --triggers --events shop > shop_backup.sql

Back up and compress

Adds the date to the file name.

mysqldump -u root -p --single-transaction shop | gzip > shop_$(date +%F).sql.gz

Back up every database, one table, or structure only

Pick the variant that matches your need.

mysqldump -u root -p --single-transaction --all-databases > all_databases.sql
mysqldump -u root -p --single-transaction shop customers > customers.sql
mysqldump -u root -p --no-data shop > shop_schema.sql

Restore a backup

Create the database first if it does not exist.

mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS shop"
mysql -u root -p shop < shop_backup.sql
gunzip < shop_2026-11-06.sql.gz | mysql -u root -p shop

Test your restores. A backup you have never restored is only a guess. Restore into a scratch database regularly and check the data.

10. Inspect the server and find slow spots

Quick checks for when something feels slow or stuck.

See running queries and stop one

Find the Id of a stuck query, then kill it.

SHOW FULL PROCESSLIST;
KILL 123;

Check connections and limits

Useful when you see "Too many connections".

SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

Check character sets and SQL mode

Confirms the defaults your sessions use.

SHOW VARIABLES LIKE 'character_set%';
SELECT @@sql_mode;

Find the largest tables

Sizes in megabytes for one schema.

SELECT TABLE_NAME AS table_name,
       ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS size_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'shop'
ORDER BY size_mb DESC;

Refresh table statistics

Helps the optimizer choose good plans after large data changes.

ANALYZE TABLE customers;

11. Quick reference

The whole sheet on one screen. Bookmark this section.

Task	Command
Log in	`mysql -u root -p`
Show databases	`SHOW DATABASES;`
Select a database	`USE shop;`
Show tables	`SHOW TABLES;`
Describe a table	`DESCRIBE customers;`
Create a user	`CREATE USER IF NOT EXISTS 'app_user'@'localhost' IDENTIFIED BY '...';`
Grant on one database	`GRANT SELECT, INSERT ON shop.* TO 'app_user'@'localhost';`
Show grants	`SHOW GRANTS FOR 'app_user'@'localhost';`
Insert a row	`INSERT INTO customers (name, email, password_hash) VALUES (...);`
Update a row	`UPDATE customers SET name = 'New' WHERE id = 1;`
Delete a row	`DELETE FROM customers WHERE id = 1;`
Add an index	`CREATE INDEX idx_name ON customers (name);`
Explain a query	`EXPLAIN SELECT ...;`
Back up a database	`mysqldump -u root -p --single-transaction shop > shop.sql`
Restore a backup	`mysql -u root -p shop < shop.sql`
See running queries	`SHOW FULL PROCESSLIST;`

12. Common mistakes and a safety checklist

Most expensive database incidents come from a short list of habits.

Running UPDATE or DELETE without WHEREWrite the filter first and preview it with SELECT.
Using root for applicationsCreate a dedicated user per application with only the privileges it needs.
Granting privileges on *.*Grant on database.* or on specific tables instead.
Typing passwords in commandsLet -p prompt you, or use a protected option file.
Dropping or truncating without a backupThese statements cannot be rolled back. Dump the data first.
Storing plaintext passwordsStore a bcrypt or Argon2 hash created by your application.
Indexing every columnIndex what you filter, join and sort on, and confirm with EXPLAIN.

13. Official documentation

Prefer the manual for your exact version, and check the release notes before upgrading.

MySQL 8.4 Reference Manual

The official manual for the 8.4 LTS release.

GRANT Statement

Privileges, scopes and role syntax.

mysqldump

Every backup option explained.

Data Types

Choose the right column types.

EXPLAIN Statement

Read query plans and tune slow queries.

MySQL Release Support Dates

Track supported versions before upgrading.

Frequently asked questions

Which MySQL version should I use in 2027?

MySQL 8.0 reached end of life in April 2026. MySQL 8.4 LTS is a conservative choice for production, and 9.7 LTS is the newer long-term support line. The commands in this guide are standard SQL for these releases, and version-specific differences are noted where they matter. Run SELECT VERSION(); to see what your server uses.

Do I need FLUSH PRIVILEGES after GRANT or CREATE USER?

No. MySQL applies account changes made with CREATE USER, GRANT, REVOKE and ALTER USER immediately. FLUSH PRIVILEGES is only needed if you edit the grant tables directly, which is not recommended.

Why should I avoid GRANT ALL PRIVILEGES ON *.*?

It gives the account control over every database on the server, including system schemas. If the credentials leak or the application has a bug, the damage is much larger. Grant privileges on a single database or table instead.

Can I rename a database in MySQL?

Not directly, because there is no RENAME DATABASE statement. Create a new database and restore a dump into it, or move each table with RENAME TABLE old_db.table_name TO new_db.table_name.

Should I use BOOLEAN or TINYINT(1)?

BOOLEAN is an alias for TINYINT(1), so they behave the same. Use BOOLEAN for readability, and avoid display widths on other integer types because they are deprecated.

How should I store user passwords?

Never store them in plain text. Hash them in your application with bcrypt or Argon2 and store the result in a VARCHAR(255) column. Database account passwords are a separate concern and belong in a secrets manager.

Conclusion: your next steps

Knowing the commands is the easy part. Using them safely is what makes you dependable. Keep this sheet nearby, follow the safety habits and test every change on a copy first.

Key takeaways

Use MySQL 8.4 LTS or 9.7 LTS, because 8.0 is no longer supported.
Give every application its own user with least privilege, and avoid global grants.
Name columns in INSERT statements and always use WHERE with UPDATE and DELETE.
Use transactions for related changes and IF EXISTS or IF NOT EXISTS where it prevents errors.
Hash passwords in your application, and never store them in plain text.
Back up with mysqldump, then practice restoring.

Build the habit: read the statement, preview it with SELECT, run it inside a transaction when you can, and keep a backup you have actually restored.

About this cheat sheet: Examples target MySQL 8.4 and 9.7 and use sample names. Syntax and defaults can change between releases, so check the manual for your exact version before running commands on production data.