MySQL Administration

Overview

MySQL administration involves managing the MySQL server, databases, users, and security. This chapter covers essential administrative tasks for maintaining a healthy MySQL environment.

Administrative Responsibilities

• Server configuration and monitoring
• User management and security
• Database backup and recovery
• Performance optimization
• Access control

Server Administration

Starting and Stopping MySQL

# Linux (systemd)
sudo systemctl start mysql
sudo systemctl stop mysql
sudo systemctl restart mysql
sudo systemctl status mysql
sudo systemctl enable mysql  # Enable on boot

# Linux (init.d)
sudo service mysql start
sudo service mysql stop
sudo service mysql restart

# macOS (Homebrew)
brew services start mysql@8.0
brew services stop mysql@8.0
brew services restart mysql@8.0

# Windows (command line)
net start MySQL
net stop MySQL

# As service
mysqld --install
mysqld --remove

Server Configuration

MySQL configuration file locations:

| Platform |----------------|----------------| | Linux (Ubuntu/Debian) | /etc/mysql/mysql.conf.d/mysqld.cnf | | Linux (CentOS/RHEL) | /etc/my.cnf | | macOS | /usr/local/etc/my.cnf | | Windows | C:\ProgramData\MySQL\MySQL Server 8.0\my.ini |

Configuration File Structure

[mysqld]
# Server settings
port = 3306
bind-address = 127.0.0.1
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock

# Character set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Performance settings
max_connections = 200
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# Security
local_infile = 0
symbolic_links = 0

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

Runtime Configuration

-- Show all variables
SHOW VARIABLES;

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

-- Set variable at runtime
SET GLOBAL max_connections = 300;
SET SESSION sort_buffer_size = 256K;

-- Persist changes (MySQL 8.0+)
SET PERSIST max_connections = 300;
SET PERSIST_ONLY innodb_buffer_pool_size = 2G;

-- View persisted variables
SELECT * FROM performance_schema.persisted_variables;

Server Status

-- Show server status
SHOW STATUS;

-- Connection information
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Connections';
SHOW STATUS LIKE 'Uptime';

-- Query cache
SHOW STATUS LIKE 'Qcache%';

-- Table locks
SHOW STATUS LIKE 'Table_locks%';

-- Temporary tables
SHOW STATUS LIKE 'Created_tmp%';

-- Slow queries
SHOW GLOBAL STATUS LIKE 'Slow_queries';

User Management

Creating Users

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

-- Create user with specific host
CREATE USER 'username'@'%' IDENTIFIED BY 'password123';
CREATE USER 'username'@'192.168.1.%' IDENTIFIED BY 'password123';

-- Create user without password (not recommended)
CREATE USER 'username'@'localhost';

-- Create user with authentication plugin
CREATE USER 'username'@'localhost' 
    IDENTIFIED WITH mysql_native_password BY 'password123';

-- Create user with resource limits
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password123'
    WITH MAX_QUERIES_PER_HOUR 100
         MAX_UPDATES_PER_HOUR 50
         MAX_CONNECTIONS_PER_HOUR 10
         MAX_USER_CONNECTIONS 5;

Granting Privileges

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

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

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

-- Grant replication privileges
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';

-- Grant backup privileges
GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* 
    TO 'backup_user'@'localhost';

-- Grant process privilege
GRANT PROCESS ON *.* TO 'admin_user'@'localhost';

Revoking Privileges

-- Revoke specific privileges
REVOKE INSERT, UPDATE, DELETE 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 *.* FROM 'username'@'localhost';

Managing Users

-- Show all users
SELECT User, Host FROM mysql.user;

-- Show user privileges
SHOW GRANTS FOR 'username'@'localhost';
SHOW GRANTS FOR CURRENT_USER();

-- Change user password
ALTER USER 'username'@'localhost' IDENTIFIED BY 'NewPassword123!';
SET PASSWORD FOR 'username'@'localhost' = 'NewPassword123!';

-- Rename user
RENAME USER 'old_username'@'localhost' TO 'new_username'@'localhost';

-- Lock/unlock user
ALTER USER 'username'@'localhost' ACCOUNT LOCK;
ALTER USER 'username'@'localhost' ACCOUNT UNLOCK;

-- Expire password
ALTER USER 'username'@'localhost' PASSWORD EXPIRE;

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

Role-Based Access Control

-- Create role
CREATE ROLE 'app_read', 'app_write', 'app_admin';

-- Grant privileges to role
GRANT SELECT ON database_name.* TO 'app_read';
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'app_write';
GRANT ALL ON database_name.* TO 'app_admin';

-- Assign role to user
CREATE USER 'developer'@'localhost' IDENTIFIED BY 'password123';
GRANT 'app_read', 'app_write' TO 'developer'@'localhost';

-- Set default role
SET DEFAULT ROLE ALL TO 'developer'@'localhost';

-- Activate role
SET ROLE 'app_write';
SELECT CURRENT_ROLE();

-- View assigned roles
SELECT * FROM information_schema.applicable_roles
WHERE GRANTEE = '''developer''@''localhost''';

Security Management

Secure Installation

# Run MySQL secure installation
sudo mysql_secure_installation

This script performs:

1. Set root password
2. Remove anonymous users
3. Disallow root login remotely
4. Remove test database
5. Reload privilege tables

SSL

# Generate SSL certificates
mysql_ssl_rsa_setup --datadir=/var/lib/mysql

-- Check SSL status
SHOW VARIABLES LIKE 'have_ssl';
SHOW VARIABLES LIKE 'have_openssl';

-- Require SSL for user
CREATE USER 'secure_user'@'localhost' 
    IDENTIFIED BY 'password123' 
    REQUIRE SSL;

-- Require specific cipher
CREATE USER 'secure_user'@'%' 
    IDENTIFIED BY 'password123' 
    REQUIRE CIPHER 'DHE-RSA-AES256-SHA';

Firewall Configuration

# Linux (ufw)
sudo ufw allow 3306/tcp
sudo ufw status

# Linux (firewalld)
sudo firewall-cmd --permanent --add-service=mysql
sudo firewall-cmd --reload

MySQL Enterprise Firewall

-- Enable firewall
INSTALL PLUGIN mysql_firewall SONAME 'mysql_firewall.so';

-- Create firewall rule
SET GLOBAL mysql_firewall_mode = ON;
SET GLOBAL mysql_firewall_trace = ON;

-- Create whitelist rule
CALL mysql_firewall_add_rule_context('mysql', 'SELECT * FROM users WHERE id = ?');

-- Check firewall status
SELECT * FROM mysql.firewall_whitelist;

Backup and Recovery

Logical Backups

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

# Backup multiple databases
mysqldump -u root -p --databases db1 db2 > backup.sql

# Backup all databases
mysqldump -u root -p --all-databases > full_backup.sql

# Backup with options
mysqldump -u root -p \
    --databases myapp \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    > myapp_backup.sql

# Backup structure only (no data)
mysqldump -u root -p --no-data database_name > structure.sql

# Backup data only (no structure)
mysqldump -u root -p --no-create-info database_name > data.sql

# Compressed backup
mysqldump -u root -p database_name | gzip > backup.sql.gz

Physical Backups

# Copy data files (when MySQL is stopped)
sudo systemctl stop mysql
sudo cp -r /var/lib/mysql /backup/mysql/
sudo systemctl start mysql

# Using MySQL Enterprise Backup
mysqlbackup --backup-dir=/backup/ backup
mysqlbackup --backup-dir=/backup/ copy-back

Recovery

# Restore from SQL dump
mysql -u root -p database_name < backup.sql

# Restore from compressed backup
gunzip < backup.sql.gz | mysql -u root -p

# Restore to different database
mysql -u root -p new_database < backup.sql

# Restore all databases
mysql -u root -p < full_backup.sql

# Using source command in MySQL
mysql -u root -p
SOURCE /path/to/backup.sql;

Point-in-Time Recovery

# Backup binlog position
mysqldump -u root -p --single-transaction --master-data=2 > backup.sql

# Restore full backup
mysql -u root -p < full_backup.sql

# Apply binlog to point in time
mysqlbinlog --stop-datetime="2024-01-15 10:00:00" /var/log/mysql/binlog.* | mysql -u root -p

Performance Monitoring

Performance Schema

-- Enable performance schema (enabled by default)
SHOW VARIABLES LIKE 'performance_schema';

-- View available instruments
SELECT * FROM performance_schema.setup_instruments LIMIT 10;

-- Check table I/O
SELECT * FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA = 'myapp';

-- Check index usage
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'myapp';

-- Check statements
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY COUNT_STAR DESC LIMIT 10;

Slow Query Log

-- Enable slow query log
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;  -- 2 seconds
SET GLOBAL log_queries_not_using_indexes = ON;

-- Slow query log file location
SHOW VARIABLES LIKE 'slow_query_log_file';

-- Analyze slow queries
mysqldumpslow -s t /var/log/mysql/slow.log
mysqldumpslow -s c /var/log/mysql/slow.log  -- most common

System Variables

-- Key performance variables
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';  -- Buffer pool size
SHOW VARIABLES LIKE 'innodb_log_file_size';     -- Log file size
SHOW VARIABLES LIKE 'max_connections';          -- Max connections
SHOW VARIABLES LIKE 'query_cache_type';         -- Query cache
SHOW VARIABLES LIKE 'thread_cache_size';        -- Thread cache

-- Key status variables
SHOW STATUS LIKE 'Threads_created';             -- Threads created
SHOW STATUS LIKE 'Questions';                   -- Total queries
SHOW STATUS LIKE 'Slow_queries';                -- Slow queries
SHOW STATUS LIKE 'Innodb_buffer_pool_wait_free'; -- Buffer waits

Database Health Checks

-- Check table status
SHOW TABLE STATUS FROM database_name;

-- Check for corrupted tables
CHECK TABLE table_name;

-- Repair table (MyISAM)
REPAIR TABLE table_name;

-- Optimize table
OPTIMIZE TABLE table_name;

-- Analyze table
ANALYZE TABLE table_name;

-- Check disk usage
SELECT 
    table_schema,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;

Database Maintenance

Regular Maintenance Tasks

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

-- Analyze tables for better query plans
ANALYZE TABLE table_name;

-- Check all tables in database
CHECK TABLE table_name EXTENDED;

-- Repair MyISAM tables
REPAIR TABLE table_name QUICK;

Scheduled Maintenance

Create maintenance script:

#!/bin/bash
# mysql_maintenance.sh

DATE=$(date +%Y%m%d)
LOGFILE="/var/log/mysql_maintenance.log"

echo "Starting MySQL maintenance at $(date)" >> $LOGFILE

# Optimize all databases
for db in $(mysql -u root -p -e "SHOW DATABASES" | grep -v Database | grep -v information_schema); do
    echo "Optimizing $db..." >> $LOGFILE
    mysql -u root -p -e "OPTIMIZE TABLE \`$db\`" >> $LOGFILE 2>&1
done

echo "Maintenance completed at $(date)" >> $LOGFILE

Add to cron:

cron：

# Run maintenance daily at 3 AM
0 3 * * * /path/to/mysql_maintenance.sh

Log Management

Error Log

-- Error log location
SHOW VARIABLES LIKE 'log_error';

-- Check error log
sudo tail -f /var/log/mysql/error.log

General Query Log

-- Enable general query log
SET GLOBAL general_log = ON;
SET GLOBAL general_log_file = '/var/log/mysql/general.log';

-- Disable when not needed
SET GLOBAL general_log = OFF;

Binary Log

-- Enable binary logging
SHOW VARIABLES LIKE 'log_bin';

-- Binary log format
SHOW VARIABLES LIKE 'binlog_format';

-- View binary log files
SHOW BINARY LOGS;

-- View current binary log position
SHOW MASTER STATUS;

-- View binary log events
SHOW BINLOG EVENTS IN 'mysql-bin.000001';

Replication Setup

Master Configuration

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
sync_binlog = 1

-- Create replication user
CREATE USER 'replica_user'@'%' IDENTIFIED BY 'replica_password';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';

-- Get master status
SHOW MASTER STATUS;

Slave Configuration

[mysqld]
server-id = 2
relay-log = /var/log/mysql/mysql-relay.log
read_only = ON

-- Configure replication
CHANGE MASTER TO
    MASTER_HOST = 'master_host',
    MASTER_USER = 'replica_user',
    MASTER_PASSWORD = 'replica_password',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 123;

-- Start replication
START SLAVE;

-- Check replication status
SHOW SLAVE STATUS\G

Summary

MySQL administration encompasses:

• Server Management: Start
• User Management: Create, grant privileges, security
• **Backup
• Monitoring: Performance schema, slow queries
• Maintenance: Optimization, health checks
• Replication: Master-slave setup

---

Previous: Installation

Next: Connection