MySQL CLI: From Connection to Maintenance

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 -p

This 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 -p

Once 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:

GRANT ALL PRIVILEGES ON mydb.* TO app_user@localhost IDENTIFIED BY 'strongpass123';

This creates app_user (if it doesn’t exist), 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_database

Typing 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_table enabled 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 -p

Run 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 mydb

This targets just mydb instead of everything.

A Useful Maintenance Combo

Here’s a three-step that keeps MySQL happy:

# First, check for table corruption
mysqlcheck --check --all-databases -u root -p

# Then repair anything broken
mysqlcheck --auto-repair --all-databases -u root -p

# Finally, optimize storage
mysqlcheck --optimize --all-databases -u root -p

Or combine them:

mysqlcheck --check --repair --optimize --all-databases -u root -p

Same 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.