MySQL Cheat Sheet for DBAs: 2025

22/11/2025

1 View 0

SaveSavedRemoved 0

Essential MySQL Commands: The Ultimate DBA Cheat Sheet

In the complex world of database administration, efficiency is key. Whether you’re a seasoned DBA or just starting, having a quick reference for essential MySQL commands can save you valuable time and prevent costly mistakes. This guide serves as your comprehensive cheat sheet, covering the fundamental commands for server management, data manipulation, user administration, and performance tuning.

Mastering these commands will not only streamline your daily tasks but also deepen your understanding of how MySQL operates under the hood.

Getting Started: Server Access and Basic Operations

Before you can manage a database, you need to connect to it and know how to perform basic informational queries.

Connect to a MySQL Server: This is the first step. You’ll need your username and will be prompted for a password.
```
mysql -u [username] -p
```
Check Server Version: Knowing the specific version of MySQL is crucial for compatibility and feature availability.
```
SELECT VERSION();
```
Show All Databases: Get a list of all databases on the server that you have permission to see.
```
SHOW DATABASES;
```
Exit the MySQL Prompt: To safely close your connection.
sql EXIT;

Core Database Management

These commands allow you to create, select, and remove entire databases.

Create a New Database: This command creates a new, empty database.
```
CREATE DATABASE [database_name];
```
Select a Database to Use: Before you can work with tables inside a database, you must select it. This is a critical step for all subsequent table and data operations in your session.
```
USE [database_name];
```
Delete a Database: Use this command with extreme caution. Deleting a database is an irreversible action that removes the database and all of its tables and data permanently.
sql DROP DATABASE [database_name];

Mastering Table Structure (DDL)

Data Definition Language (DDL) commands are used to define and manage the structure of your tables.

Create a New Table: Define the columns and their data types for a new table.

CREATE TABLE [table_name] (
    column1_name datatype,
    column2_name datatype,
    column3_name datatype,
    PRIMARY KEY (column1_name)
);

View Table Structure: See the columns, data types, and other information for a specific table. This is invaluable for debugging and understanding schemas.
```
DESCRIBE [table_name];
```
Modify an Existing Table: ALTER TABLE is a powerful command used to add, modify, or drop columns.
- Add a new column:
  sql ALTER TABLE [table_name] ADD [column_name] datatype;
- Modify a column’s data type:
  sql ALTER TABLE [table_name] MODIFY COLUMN [column_name] new_datatype;
Delete a Table: This permanently removes a table and all the data within it. There is no undo for this command.
sql DROP TABLE [table_name];

Data Manipulation Language (DML) Essentials

These are the commands you’ll use most frequently to interact with the data inside your tables.

Insert Data into a Table: Add a new row of data.

INSERT INTO [table_name] (column1, column2, column3) VALUES (value1, value2, value3);

Retrieve Data from a Table: The SELECT statement is used to query your data.
```
SELECT * FROM [table_name] WHERE [condition];
```
Update Existing Data: Modify records that are already in a table. Always use a WHERE clause to avoid accidentally updating every row in the table.
```
UPDATE [table_name] SET column1 = new_value1 WHERE [condition];
```
Delete Data from a Table: Remove specific rows. Just like with UPDATE, it is critical to use a WHERE clause to specify which records to delete.
sql DELETE FROM [table_name] WHERE [condition];

User Management and Security

Proper user management is the cornerstone of database security. These commands allow you to control who can access your data and what they can do.

Create a New User: Create a new user account, specifying their username and the host they can connect from (e.g., ‘localhost’, ‘%’).
```
CREATE USER '[new_user]'@'[host]' IDENTIFIED BY '[password]';
```
Grant Privileges to a User: Give a user specific permissions on a database or table. Granting only necessary permissions is a best practice.
```
GRANT ALL PRIVILEGES ON [database_name].* TO '[user]'@'[host]';
```
(You can replace ALL PRIVILEGES with specific permissions like SELECT, INSERT, UPDATE, etc.)
Revoke Privileges from a User: Remove permissions that were previously granted.
```
REVOKE ALL PRIVILEGES ON [database_name].* FROM '[user]'@'[host]';
```
Show a User’s Grants: Check the current permissions for a specific user.
```
SHOW GRANTS FOR '[user]'@'[host]';
```
Delete a User: Permanently remove a user account.
sql DROP USER '[user]'@'[host]';

Backup, Restore, and Performance

A DBA’s job isn’t just managing data—it’s also about protecting it and ensuring the system runs smoothly.

Backup a Database (mysqldump): This command-line utility creates a .sql file containing the commands needed to recreate the database.
```
mysqldump -u [username] -p [database_name] > backup_file.sql
```
Restore a Database from a Backup: Execute the SQL commands from a backup file to restore a database.
```
mysql -u [username] -p [database_name] < backup_file.sql
```
Show Active Processes: See a list of all currently running queries and connections. This is essential for diagnosing slow performance or locked resources.
```
SHOW FULL PROCESSLIST;
```
Analyze a Query’s Execution Plan: Use EXPLAIN to see how MySQL intends to execute your SELECT query. This is the most important tool for optimizing slow queries.
sql EXPLAIN SELECT * FROM [table_name] WHERE [condition];

Pro Tips for Secure and Efficient Management

Practice the Principle of Least Privilege: Never grant more permissions than a user absolutely needs. Avoid using the root user for application connections.
Automate Your Backups: Don’t rely on manual backups. Set up a scheduled task (like a cron job) to run mysqldump regularly.
Regularly Analyze Slow Queries: Use SHOW PROCESSLIST to find long-running queries and EXPLAIN to understand why they are slow. Proper indexing is often the solution.
Always Test DROP and DELETE: Before running a destructive command on production, double-check your syntax and your WHERE clauses in a development environment.

By keeping this guide handy, you can perform your DBA duties with greater confidence and precision. Bookmark this page for a quick and reliable reference for all your essential MySQL tasks.

Source: https://centlinux.com/mysql-cheat-sheet/