MySQL - Administration

Modern MySQL Administration Fundamentals

MySQL administration involves managing the health, security, and performance of your database server. This has evolved from manual server management to using modern command-line tools, containerization, and cloud services. This tutorial covers the fundamental administrative tasks for a modern MySQL setup.

Server Lifecycle Management

How you manage the MySQL server process depends on your operating system and environment.

Linux (using systemd)

On most modern Linux distributions, you’ll use systemctl to manage the MySQL service (the service name might be mysql or mysqld).

# Start the MySQL server
sudo systemctl start mysqld

# Stop the MySQL server
sudo systemctl stop mysqld

# Restart the server
sudo systemctl restart mysqld

# Check the server's status (very useful for debugging)
sudo systemctl status mysqld

macOS (using Homebrew)

If you installed MySQL via Homebrew, you can use brew services.

# Start the MySQL server and have it launch at login
brew services start mysql

# Stop the MySQL server
brew services stop mysql

# Restart the server
brew services restart mysql

Docker (The Modern, Portable Approach)

Using Docker provides a consistent, isolated environment for development and deployment. You manage the server through the Docker container lifecycle.

# Run a new MySQL container in the background
docker run --name my-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d -p 3306:3306 mysql:latest

# Stop the container
docker stop my-mysql

# Start the container again
docker start my-mysql

# View logs for debugging
docker logs my-mysql

Secure User and Access Control Management

Proper user management is critical for security. The guiding principle is the principle of least privilege: only grant users the permissions they absolutely need.

Creating Users and Granting Privileges

Always create dedicated users for your applications instead of using the root user. The process is a two-step: create the user, then grant privileges.

-- Step 1: Create a new user. 'app_user' can connect only from localhost.
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'a-very-strong-password';

-- Step 2: Grant specific privileges on a specific database.
-- This user can only perform CRUD operations on tables in the 'my_app_db' database.
GRANT SELECT, INSERT, UPDATE, DELETE ON my_app_db.* TO 'app_user'@'localhost';

-- It's a good practice to reload privileges after manual grant table edits, though
-- GRANT/CREATE USER statements usually handle this automatically.
FLUSH PRIVILEGES;

Updating Passwords

The modern way to change a user’s password is with ALTER USER.

ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'new-even-stronger-password';

Using Roles (MySQL 8.0+)

Roles simplify permission management. You can group permissions into a role and then assign that role to multiple users.

-- Create roles for read-only and read-write access
CREATE ROLE 'app_readonly', 'app_readwrite';

-- Grant permissions to the roles
GRANT SELECT ON my_app_db.* TO 'app_readonly';
GRANT SELECT, INSERT, UPDATE, DELETE ON my_app_db.* TO 'app_readwrite';

-- Create a user and grant them the readonly role
CREATE USER 'analyst'@'localhost' IDENTIFIED BY 'secure_password';
GRANT 'app_readonly' TO 'analyst'@'localhost';

-- Activate the role for the user's session
SET DEFAULT ROLE 'app_readonly' TO 'analyst'@'localhost';

Essential Administrative Commands

These commands are your day-to-day tools for inspecting the state of your database.

• USE database_name; — Select a database to work with.
• SHOW DATABASES; — List all databases you have permission to see.
• SHOW TABLES; — List all tables in the currently selected database.
• DESCRIBE table_name; — Show the structure of a table (columns, types, etc.).
• SHOW PROCESSLIST; — Display all currently running queries and connections. Crucial for diagnosing performance issues.
• SHOW VARIABLES LIKE 'variable_name%'; — Check the value of server configuration variables.
• SHOW ENGINE INNODB STATUS; — Provides detailed information about the InnoDB storage engine, including deadlocks and transaction history.

Understanding Configuration Files

MySQL’s behavior is controlled by a configuration file, typically named my.cnf. Its location varies by OS. You can find its location by running mysql --help | grep 'Default options'.

A minimal, modern configuration file might look like this:

[mysqld]
# General settings
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

# Important for security and consistency
sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

# InnoDB performance tuning
innodb_buffer_pool_size = 1G # Adjust based on your server's RAM (e.g., 70-80% of available RAM)
innodb_log_file_size = 256M

# Logging
log-error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2

Always back up your my.cnf file before making changes, and only change one setting at a time to observe its effect. Restart the MySQL server for changes to take effect.