You’re staring at a bash prompt wondering how to connect to MySQL, run a query, or convince your tables they need some maintenance work. Here’s the thing—the CLI is where MySQL lives. GUIs are nice when you’re debugging at 2 PM, but at 2 AM when production is having a moment, you want the terminal.
Connecting to MySQL
Let’s start with the simplest thing you’ll do a thousand times:
mysql -u root -pThis connects with the root user. The -p flag makes MySQL prompt for the password—don’t ever put the password on the command line. Your shell history is a security risk you don’t need. Hit enter, type the password (it won’t echo—trust that it’s there), and you’re in.
If you’re connecting to a remote server:
mysql -h 192.168.1.50 -u root -pOnce you’re in, you get the mysql> prompt. That’s your stage now.
The Essential Commands
These five commands will cover 80% of what you do:
SHOW DATABASES;Lists all databases. Your eyes scan this like you’re looking at a flight status board.
USE myapp_db;Switches to a specific database. Everything after this runs against myapp_db until you switch again.
SHOW TABLES;Lists all tables in the current database. If this is empty and you expected data, your 2 AM self is about to have a bad night.
DESCRIBE users;Shows the schema of the users table—columns, types, null constraints, keys. This is your “what’s in this table” cheat code.
SELECT * FROM users LIMIT 5;Actually look at data. Start small with LIMIT 5 before you query 10 million rows. Your DBA will thank you.
User Management One-Liners
MySQL is paranoid about permissions (rightfully so). Here’s how to hand people access without giving them keys to everything:
Updated: The combined GRANT…IDENTIFIED BY syntax was removed in MySQL 8.0. Use separate CREATE USER and GRANT statements. MariaDB still supports the old form.
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strongpass123';GRANT ALL PRIVILEGES ON mydb.* TO 'app_user'@'localhost';FLUSH PRIVILEGES;This creates app_user, sets the password to strongpass123, and lets them log in only from localhost. They can do anything to tables in mydb, but nowhere else.
GRANT SELECT, INSERT, UPDATE ON mydb.transactions TO readonly@'192.168.1.100' IDENTIFIED BY 'pass';More granular. This user from 192.168.1.100 can read and write to one table only. No deletes.
DROP USER 'olduser'@'localhost';Remove access completely. Use this before 2 AM becomes 3 AM.
mysqladmin -u root -p drop old_databaseTyping from your shell (not MySQL CLI), this deletes old_database. mysqladmin prompts for the password and asks “Are you sure?” before nuking it. That’s built-in safety.
Checking Table Health
Before you optimize, you need to know what’s broken:
CHECK TABLE users, products;Scans these tables for corruption. If you see “error” in the output, your lunch break is cancelled.
ANALYZE TABLE users;Updates MySQL’s internal statistics about the table. Useful before a big query to make sure the optimizer has fresh numbers.
Repair & Optimize: The One-Liner You Actually Want
Here’s where the second part of this puzzle comes in. If MySQLTuner is nagging you (“Hey, optimize your tables”), or you’ve just deleted 40% of a large table, you want to reclaim that disk space and defragment the data.
The OPTIMIZE TABLE statement reorganizes physical storage and reclaims disk space—especially useful for:
- InnoDB tables with
innodb_file_per_tableenabled after heavy deletes - MyISAM or ARCHIVE tables after deleting rows or changing variable-length columns (VARCHAR, BLOB, TEXT)
You could log in, type OPTIMIZE TABLE a hundred times for each table. Or:
mysqlcheck --auto-repair --optimize --all-databases -u root -pRun this from bash (not MySQL CLI). It:
- Checks every table in every database
- Auto-repairs anything broken
- Optimizes storage on everything
No need to loop through databases. No need to log in. It prompts for your password, runs, and you get your shell back. This is the setup you want in a cron job at 3 AM while you sleep.
You can scope it down too:
mysqlcheck --auto-repair --optimize -u root -p mydbThis targets just mydb instead of everything.
A Useful Maintenance Combo
Here’s a three-step that keeps MySQL happy:
# First, check for table corruptionmysqlcheck --check --all-databases -u root -p
# Then repair anything brokenmysqlcheck --auto-repair --all-databases -u root -p
# Finally, optimize storagemysqlcheck --optimize --all-databases -u root -pOr combine them:
mysqlcheck --check --repair --optimize --all-databases -u root -pSame thing, one command. Your server runs faster, disk space is reclaimed, and you look like you know what you’re doing.
Exit
EXIT;Or QUIT; or \q—they all work. You’re back in bash.
That’s it. Master these and you’ve got MySQL by the throat. The CLI isn’t scary once you stop thinking of it as a alternative UI and start thinking of it as the actual control panel. Because it is.
