This page provides a comprehensive collection of essential MySQL commands for developers and database administrators. Mastering these commands is crucial for efficient database management, data manipulation, and system maintenance.

MySQL Basics and Core Operations

Get started with fundamental MySQL operations, including database export and import, viewing active processes, and managing connection limits.

# *****************************************************************************
# BASICS
# *****************************************************************************

mysqldump -h hostname -u username -p database_name -P port > file.sql # Export database
mysql -u username -p database_name < file.sql                         # Import database

SHOW PROCESSLIST; # Show you any queries that are currently running or in the queue to run

show status where `variable_name` = 'Threads_connected'; # Show all connected threads
show variables like 'max_connections'; # Show maximum number of allowed connections
SET GLOBAL max_connections = 150; ## Set new value for maximum connections (no restart needed but for permanent change update my.cnf)

GRANT ALL PRIVILEGES ON prospectwith.* TO 'power'@'localhost' WITH GRANT OPTION; # Grant all privileges on database

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; # Create user

mysql -u root -pmypassword -e "MY SQL QUERY" &&>> query.log & disown # Run SQL query in the background

Database and Table Management

Learn how to create, delete, and manage databases and tables, along with commands to view table structures and lists.

# *****************************************************************************
# Database and Table Operations
# *****************************************************************************

CREATE DATABASE database_name; # Create a new database
DROP DATABASE database_name; # Delete a database
CREATE TABLE table_name (column1 datatype, column2 datatype, ...); # Create a new table
DROP TABLE table_name; # Delete a table
SHOW TABLES; # Display all tables in the current database
DESCRIBE table_name; # Show the structure of a table

Data Manipulation Language (DML)

Execute commands to insert, update, delete, and select data from your MySQL tables efficiently.

# *****************************************************************************
# Data Manipulation
# *****************************************************************************

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); # Insert data into a table
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; # Update existing data in a table
DELETE FROM table_name WHERE condition; # Delete data from a table
SELECT column1, column2, ... FROM table_name WHERE condition; # Select data from a table

Backup and Restore Procedures

Secure your data with commands for backing up entire databases or specific tables, and restoring them when needed.

# *****************************************************************************
# Backup and Restore
# *****************************************************************************

mysqldump -u username -p database_name table1 table2 > file.sql # Backup specific tables
mysql -u username -p database_name < file.sql # Restore specific tables

User Management and Security Best Practices

Implement robust security by managing user privileges, revoking access, and resetting passwords.

# *****************************************************************************
# User Management and Security
# *****************************************************************************

REVOKE privilege_type ON database_name.table_name FROM 'username'@'hostname'; # Revoke privileges from a user
DROP USER 'username'@'hostname'; # Delete a user
ALTER USER 'username'@'hostname' IDENTIFIED BY 'newpassword'; # Reset a user's password

Performance Tuning and Maintenance

Optimize your database performance and ensure data integrity with commands for table optimization, analysis, and repair.

# *****************************************************************************
# Performance and Maintenance
# *****************************************************************************

OPTIMIZE TABLE table_name; # Optimize a table
ANALYZE TABLE table_name; # Analyze a table for key distribution and storage optimization
CHECK TABLE table_name; # Check a table for errors
REPAIR TABLE table_name; # Repair a corrupted table

Advanced Querying Techniques

Explore powerful SQL features like JOIN operations, subqueries, and data grouping for complex data retrieval.

# *****************************************************************************
# Advanced Queries
# *****************************************************************************

SELECT ... FROM table1 JOIN table2 ON table1.column = table2.column; # Perform a join operation between two tables
SELECT ... FROM (SELECT ... FROM table_name) AS subquery; # Use a subquery within another query
SELECT column, COUNT(*) FROM table_name GROUP BY column; # Group results and use aggregate functions

System Information and Logging

Gain insights into your MySQL server by querying version information, user lists, and enabling query logging.

# *****************************************************************************
# System Information
# *****************************************************************************

SELECT VERSION(); # Show the current version of MySQL
SELECT User, Host FROM mysql.user; # List all current MySQL users

# *****************************************************************************
# Miscellaneous
# *****************************************************************************

SET GLOBAL general_log = 'ON'; # Enable query logging
SHOW FULL PROCESSLIST; # Show the last queries executed in MySQL

For further learning, refer to the official MySQL Documentation and explore resources on Stack Overflow for specific query solutions.