Getting Started with MySQL Commands
The first thing most people mess up when they're starting with MySQL is connecting to the server with the wrong character set. You log in, run a query, and suddenly your stored text looks like garbage. The fix is simple enough: add --default-character-set=utf8mb4 to your connection command. It prevents a lot of headaches later on, especially if you're dealing with multilingual data. I've been working with MySQL for over a decade, and I still keep a condensed reference open in another tab. Here's what actually matters in practice, stripped down to what you'll use day to day. Log into the server:
mysql -u root -p This prompts for a password. If you need to connect to a specific database right away, append the database name: mysql -u username -p databasename
Show all available databases: SHOW DATABASES; Select a specific database:
USE mydatabase; Show tables in the current database: SHOW TABLES;
Check which database you're currently using: SELECT DATABASE();
Creating and Managing Databases
Create a new database with UTF-8 support: CREATE DATABASE newdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; Dropping a database is permanent. There's no undo. I once accidentally ran a DROP on a staging server because I had forgotten to USE the correct database first. Always double-check with SELECT DATABASE() before issuing destructive commands.
Delete a database: DROP DATABASE IF EXISTS olddb; Alter a database's character set:
Get the Full Details
ALTER DATABASE existingdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Table Operations
Create a basic table: CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(100) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); See the structure of a table:
DESCRIBE users; Add a column to an existing table: ALTER TABLE users ADD COLUMN phone VARCHAR(20);
Remove a column: ALTER TABLE users DROP COLUMN phone; Rename a table:
RENAME TABLE users TO customers; Delete a table entirely: DROP TABLE IF EXISTS temp_data;
TRUNCATE TABLE temp_data; The difference between DROP and TRUNCATE matters. DROP removes the table definition along with the data. TRUNCATE wipes the rows but keeps the structure intact. It's also faster because it doesn't log individual row deletions.
CRUD Operations
Insert a single row: INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com'); Insert multiple rows at once:
INSERT INTO users (username, email) VALUES ('jane_doe', 'jane@example.com'), ('bob_smith', 'bob@example.com'); Select all columns from a table: SELECT * FROM users;
Select specific columns with a condition: SELECT username, email FROM users WHERE created_at > '2025-01-01'; Update records:
UPDATE users SET email = 'newemail@example.com' WHERE username = 'john_doe'; Delete records: DELETE FROM users WHERE username = 'john_doe';
Always include a WHERE clause with DELETE and UPDATE. Without it, you modify every row in the table. I lost a few hours of work early in my career because I ran an UPDATE without one. Never again.
Indexing and Performance
Create an index: CREATE INDEX idx_email ON users (email); Create a unique index:
CREATE UNIQUE INDEX idx_username ON users (username); Drop an index: DROP INDEX idx_email ON users;
Show indexes on a table: SHOW INDEX FROM users; Here's something most beginners miss: adding indexes speeds up reads but slows down writes. Every INSERT, UPDATE, and DELETE has to update the index too. On a high-traffic write workload, too many indexes can tank performance. I worked on a system where dropping three redundant indexes on a table cut average write latency by nearly 40 percent. Measure before you add, and remove what you don't need.
Data Export and Import
Export a database from the command line: mysqldump -u root -p mydatabase > backup.sql Export a single table:
mysqldump -u root -p mydatabase tablename > table_backup.sql Import a database from a dump file: mysql -u root -p mydatabase backup.sql
Export to CSV for spreadsheet work: SELECT * FROM users INTO OUTFILE '/tmp/users.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; Note that INTO OUTFILE writes to the server's filesystem, not yours. The server process needs permission to write to that path. If you get an error about file access, check the MySQL error log and the file permissions on the target directory.
Server Administration
Show server status: SHOW STATUS; Show current variables and settings:
SHOW VARIABLES; Check running processes: SHOW PROCESSLIST;
Kill a specific process: KILL 1234; Check disk usage per database:
SELECT table_schema AS database_name, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb FROM information_schema.tables GROUP BY table_schema; When I'm troubleshooting a slow query, the first thing I check is SHOW PROCESSLIST. It tells me what's actually running right now, how long it's been running, and whether there are connections stacking up. Most performance problems are obvious from that view alone.
Common Pitfalls
One issue that catches people off guard involves the difference between DELETE and TRUNCATE regarding auto-increment counters. DELETE removes rows one at a time and preserves the auto-increment value. TRUNCATE resets it to 1. If your application relies on predictable ID sequences, this can cause unexpected gaps or duplicates. Another thing: MySQL handles case sensitivity in table and database names differently depending on your operating system. On Linux, table names are case-sensitive by default. On Windows, they're not. If you're developing on a Mac or Windows and deploying to Linux, queries that work locally will fail in production. Always use consistent casing and check your lower_case_table_names setting. Using SELECT * in production code is another habit that causes trouble. It pulls every column, which means more data over the network, less chance for the query optimizer to use covering indexes, and broken applications when someone adds or reorders columns. Be explicit about which columns you need.
Useful One-Liners
Check MySQL version: SELECT VERSION(); Show current user:
SELECT USER(); Count rows in a table: SELECT COUNT(*) FROM users;
Find duplicate values: SELECT email, COUNT(*) AS cnt FROM users GROUP BY email HAVING cnt > 1; Get table row count estimates from metadata:
SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = 'mydatabase'; This last one gives approximate counts. It's fast because it reads from metadata rather than scanning the table, but it's not exact. For accurate counts on large tables, you'd need an actual SELECT COUNT(*), which can be expensive.
Security Basics
Create a new user: CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'securepassword'; Grant privileges:
GRANT SELECT, INSERT, UPDATE ON mydatabase.* TO 'newuser'@'localhost'; Grant all privileges (use cautiously): GRANT ALL PRIVILEGES ON mydatabase.* TO 'newuser'@'localhost';
Apply privilege changes: FLUSH PRIVILEGES; Revoke privileges:
REVOKE INSERT ON mydatabase.* FROM 'newuser'@'localhost'; Remove a user: DROP USER 'newuser'@'localhost';
Never give a user ALL privileges unless absolutely necessary. The principle of least privilege exists for a reason. A compromised application connecting with an overly broad database account is one of the fastest ways to lose data. For a printable reference, I usually generate a PDF from a curated Google Doc and keep it bookmarked. Searching through documentation pages while debugging isn't efficient. A quick lookup file saves time during incidents.
