Kenhuang Academy
  • English

      • 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

      # 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

      # 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

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

      -- Use database
USE database_name;

-- Show current database
SELECT DATABASE();

-- Show all databases
SHOW DATABASES;

      Database Information

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

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

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

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

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

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

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

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

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

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

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

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

      # 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

      # 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

      # 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

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

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

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

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

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

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

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

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

      # 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

      # 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