MySQL Commands Reference

Overview

This chapter provides a comprehensive reference of MySQL commands organized by category. These commands are essential for database administration and management.

Command Categories

Server Management: Start
Database Operations: Create, drop, use
Table Operations: Create, drop, alter
Data Manipulation: CRUD operations
User Management: Grant, revoke, users
**Backup
Show Commands: Display information

Server Management

Start and Stop

bash

# Start MySQL server
sudo systemctl start mysql

# Stop MySQL server
sudo systemctl stop mysql

# Restart MySQL server
sudo systemctl restart mysql

# Check status
sudo systemctl status mysql

# Enable on boot
sudo systemctl enable mysql

Connection Commands

bash

# Connect to MySQL
mysql -u root -p

# Connect to specific database
mysql -u root -p database_name

# Connect to remote server
mysql -h hostname -u username -p

# Connect with specific port
mysql -P 3307 -u root -p

# Execute command without interactive mode
mysql -u root -p -e "SELECT VERSION();"

Database Commands

Create and Drop

sql

-- Create database
CREATE DATABASE database_name;

-- Create with character set
CREATE DATABASE database_name CHARACTER SET utf8mb4;

-- Drop database
DROP DATABASE database_name;

-- Drop if exists
DROP DATABASE IF EXISTS database_name;

Select Database

sql

-- Use database
USE database_name;

-- Show current database
SELECT DATABASE();

-- Show all databases
SHOW DATABASES;

Database Information

sql

-- Show database size
SELECT 
    table_schema,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
GROUP BY table_schema;

-- Show database character set
SELECT 
    schema_name,
    default_character_set_name,
    default_collation_name
FROM information_schema.schemata;

Table Commands

Create and Drop

sql

-- Create table
CREATE TABLE table_name (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

-- Create from SELECT
CREATE TABLE new_table AS SELECT * FROM old_table;

-- Drop table
DROP TABLE table_name;

-- Drop if exists
DROP TABLE IF EXISTS table_name;

-- Truncate table (remove all data)
TRUNCATE TABLE table_name;

Alter Table

sql

-- Add column
ALTER TABLE table_name ADD COLUMN new_col VARCHAR(100);

-- Drop column
ALTER TABLE table_name DROP COLUMN col_name;

-- Modify column
ALTER TABLE table_name MODIFY COLUMN col_name VARCHAR(200);

-- Rename table
RENAME TABLE old_name TO new_name;

-- Add index
ALTER TABLE table_name ADD INDEX idx_col (col_name);

Table Information

sql

-- Show table structure
DESCRIBE table_name;
DESC table_name;

-- Show all tables
SHOW TABLES;
SHOW TABLES FROM database_name;

-- Show create statement
SHOW CREATE TABLE table_name;

-- Show table status
SHOW TABLE STATUS LIKE 'table_name%';

Data Manipulation

Insert

sql

-- Insert single row
INSERT INTO table_name (col1, col2) VALUES (val1, val2);

-- Insert multiple rows
INSERT INTO table_name (col1, col2) VALUES
    (val1a, val2a),
    (val1b, val2b),
    (val1c, val2c);

-- Insert from SELECT
INSERT INTO table_name (col1, col2)
SELECT col1, col2 FROM another_table;

Select

sql

-- Select all columns
SELECT * FROM table_name;

-- Select specific columns
SELECT col1, col2 FROM table_name;

-- Select with WHERE
SELECT * FROM table_name WHERE col1 = 'value';

-- Select with ORDER BY
SELECT * FROM table_name ORDER BY col1 DESC;

-- Select with LIMIT
SELECT * FROM table_name LIMIT 10;

Update

sql

-- Update single row
UPDATE table_name SET col1 = 'value' WHERE id = 1;

-- Update multiple rows
UPDATE table_name SET col1 = 'value' WHERE col2 > 100;

-- Update multiple columns
UPDATE table_name SET col1 = 'val1', col2 = 'val2' WHERE id = 1;

-- Update from SELECT
UPDATE table_name t1
JOIN table_name2 t2 ON t1.id = t2.id
SET t1.col1 = t2.col1;

Delete

sql

-- Delete single row
DELETE FROM table_name WHERE id = 1;

-- Delete multiple rows
DELETE FROM table_name WHERE col1 = 'value';

-- Delete with LIMIT
DELETE FROM table_name WHERE col2 > 100 LIMIT 100;

-- Delete all rows
DELETE FROM table_name;

User Management

Create and Drop Users

sql

-- Create user
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

-- Create with plugin
CREATE USER 'username'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

-- Drop user
DROP USER 'username'@'localhost';

-- Drop user from all hosts
DROP USER 'username'@'%';

Grant Privileges

sql

-- Grant all privileges
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

-- Grant specific privileges
GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'username'@'localhost';

-- Grant with GRANT OPTION
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost' WITH GRANT OPTION;

-- Apply changes
FLUSH PRIVILEGES;

Revoke Privileges

sql

-- Revoke specific privilege
REVOKE INSERT ON database_name.* FROM 'username'@'localhost';

-- Revoke all privileges
REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'localhost';

-- Revoke GRANT OPTION
REVOKE GRANT OPTION ON database_name.* FROM 'username'@'localhost';

Show Grants

sql

-- Show user grants
SHOW GRANTS FOR 'username'@'localhost';

-- Show current user grants
SHOW GRANTS FOR CURRENT_USER();

-- Show all users
SELECT user, host FROM mysql.user;

Backup Commands

mysqldump / mysqldump

bash

# Export single database
mysqldump -u root -p database_name > backup.sql

# Export all databases
mysqldump -u root -p --all-databases > all_databases.sql

# Export specific tables
mysqldump -u root -p database_name table1 table2 > tables.sql

# Export with compression
mysqldump -u root -p database_name | gzip > backup.sql.gz

# Export with routines
mysqldump -u root -p --routines database_name > backup.sql

mysqlhotcopy / mysqlhotcopy

bash

# Hot copy (InnoDB only)
mysqlhotcopy --user=root --password=password \
    --quiet /path/to/backup database_name

# Copy with regex
mysqlhotcopy --regex="^database_name\." \
    /path/to/backup database_name

Restore Commands

Import SQL Files

bash

# Import from file
mysql -u root -p database_name < backup.sql

# Import with source
SOURCE /path/to/backup.sql;

-- Import with compression
gunzip < backup.sql.gz | mysql -u root -p database_name

LOAD DATA INFILE / LOAD DATA INFILE

sql

-- Import from CSV
LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Show Commands

Information Display

sql

-- Show databases
SHOW DATABASES;

-- Show tables
SHOW TABLES;
SHOW TABLES FROM database_name;

-- Show columns
SHOW COLUMNS FROM table_name;
DESCRIBE table_name;

-- Show indexes
SHOW INDEX FROM table_name;

-- Show create statement
SHOW CREATE TABLE table_name;

-- Show grants
SHOW GRANTS;
SHOW GRANTS FOR 'username'@'localhost';

-- Show variables
SHOW VARIABLES;
SHOW VARIABLES LIKE 'innodb%';

-- Show status
SHOW STATUS;
SHOW STATUS LIKE 'Threads%';

-- Show processlist
SHOW PROCESSLIST;

-- Show engines
SHOW ENGINES;

-- Show character sets
SHOW CHARACTER SET;

-- Show collations
SHOW COLLATION;

-- Show events
SHOW EVENTS;
SHOW EVENTS FROM database_name;

-- Show triggers
SHOW TRIGGERS;
SHOW TRIGGERS FROM database_name;

-- Show procedures
SHOW PROCEDURE STATUS;
SHOW PROCEDURE STATUS WHERE Db = 'database_name';

Analysis Commands

EXPLAIN / EXPLAIN

sql

-- Explain query
EXPLAIN SELECT * FROM table_name WHERE col1 = 'value';

-- Extended explain
EXPLAIN EXTENDED SELECT * FROM table_name;

-- Analyze query plan
EXPLAIN FORMAT=JSON SELECT * FROM table_name;

Performance Schema

sql

-- Use performance schema
USE performance_schema;

-- Show wait events
SELECT * FROM events_waits_current;

-- Show statement performance
SELECT * FROM events_statements_summary_by_digest;

-- Show table I/O
SELECT * FROM file_summary_by_instance;

Admin Commands

System Variables

sql

-- Show all variables
SHOW VARIABLES;

-- Show specific variable
SHOW VARIABLES LIKE 'max_connections';

-- Set global variable
SET GLOBAL max_connections = 200;

-- Set session variable
SET SESSION sql_mode = 'TRADITIONAL';

-- Set persistent variable (MySQL 8.0+)
SET PERSIST max_connections = 200;

System Status

sql

-- Show all status
SHOW STATUS;

-- Show specific status
SHOW STATUS LIKE 'Threads_connected';

-- Show engine status
SHOW ENGINE INNODB STATUS;

-- Show master status
SHOW MASTER STATUS;

-- Show slave status
SHOW SLAVE STATUS;

Optimization Commands

Table Optimization

sql

-- Optimize table
OPTIMIZE TABLE table_name;

-- Optimize multiple tables
OPTIMIZE TABLE table1, table2, table3;

-- Analyze table
ANALYZE TABLE table_name;

-- Check table
CHECK TABLE table_name;

-- Repair table (MyISAM)
REPAIR TABLE table_name;

Index Management

sql

-- Create index
CREATE INDEX idx_name ON table_name(col_name);

-- Drop index
DROP INDEX idx_name ON table_name;

-- Rebuild indexes
ALTER TABLE table_name ENGINE = InnoDB;

-- Check index usage
SHOW INDEX FROM table_name;

Maintenance Commands

Regular Maintenance

bash

# Daily backup
0 2 * * * /path/to/backup_script.sh

# Weekly optimization
0 3 * * 0 mysqlcheck -u root -p --all-databases --optimize

# Monthly analysis
0 4 * * 0 mysqlcheck -u root -p --all-databases --analyze

Check and Repair

bash

# Check all databases
mysqlcheck -u root -p --all-databases

# Check specific database
mysqlcheck -u root -p database_name

# Check and repair
mysqlcheck -u root -p --auto-repair database_name

# Analyze tables
mysqlcheck -u root -p --analyze database_name

# Optimize tables
mysqlcheck -u root -p --optimize database_name

Summary

MySQL commands include:

Server Management: Start, stop, configure
Database Operations: CRUD, backup, restore
Table Operations: Create, alter, drop, optimize
User Management: Create users, grant privileges
Show Commands: Display database information
Analysis Tools: EXPLAIN, performance monitoring
Maintenance: Backup, check, optimize

Use these commands for efficient database administration and troubleshooting.

Previous: Operators

Next: MySQL Quiz

MySQL Commands Reference ​

Overview ​

Command Categories ​

Server Management ​

Start and Stop ​

Connection Commands ​

Database Commands ​

Create and Drop ​

Select Database ​

Database Information ​

Table Commands ​

Create and Drop ​

Alter Table ​

Table Information ​

Data Manipulation ​

Insert ​

Select ​

Update ​

Delete ​

User Management ​

Create and Drop Users ​

Grant Privileges ​

Revoke Privileges ​

Show Grants ​

Backup Commands ​

mysqldump / mysqldump ​

mysqlhotcopy / mysqlhotcopy ​

Restore Commands ​

Import SQL Files ​

LOAD DATA INFILE / LOAD DATA INFILE ​

Show Commands ​

Information Display ​

Analysis Commands ​

EXPLAIN / EXPLAIN ​

Performance Schema ​

Admin Commands ​

System Variables ​

System Status ​

Optimization Commands ​

Table Optimization ​

Index Management ​

Maintenance Commands ​

Regular Maintenance ​

Check and Repair ​

Summary ​

MySQL Commands Reference

Overview

Command Categories

Server Management

Start and Stop

Connection Commands

Database Commands

Create and Drop

Select Database

Database Information

Table Commands

Create and Drop

Alter Table

Table Information

Data Manipulation

Insert

Select

Update

Delete

User Management

Create and Drop Users

Grant Privileges

Revoke Privileges

Show Grants

Backup Commands

mysqldump / mysqldump

mysqlhotcopy / mysqlhotcopy

Restore Commands

Import SQL Files

LOAD DATA INFILE / LOAD DATA INFILE

Show Commands

Information Display

Analysis Commands

EXPLAIN / EXPLAIN

Performance Schema

Admin Commands

System Variables

System Status

Optimization Commands

Table Optimization

Index Management

Maintenance Commands

Regular Maintenance

Check and Repair

Summary