Connection & Basics
mysql -h host -u user -p db | Connect to database |
SHOW DATABASES; | List databases |
USE dbname; | Select database |
SHOW TABLES; | List tables |
DESCRIBE tablename; | Show table structure |
CRUD
SELECT * FROM t WHERE id=1 LIMIT 10; | Query with filter |
INSERT INTO t (col1, col2) VALUES (v1, v2); | Insert row |
UPDATE t SET col=val WHERE id=1; | Update row |
DELETE FROM t WHERE id=1; | Delete row |
TRUNCATE TABLE t; | Empty table (no rollback) |
Indexes & Performance
EXPLAIN SELECT ... | Query execution plan |
CREATE INDEX idx ON t (col); | Create index |
SHOW INDEX FROM t; | List indexes |
ANALYZE TABLE t; | Update statistics |
SHOW PROCESSLIST; | Active queries |
KILL | Kill query |
Backup & Admin
mysqldump -u user -p db > dump.sql | Export database |
mysql -u user -p db < dump.sql | Import database |
GRANT ALL ON db.* TO 'user'@'%'; | Grant privileges |
FLUSH PRIVILEGES; | Reload privilege tables |
SHOW VARIABLES LIKE 'max_connections'; | Show config variable |