MySQL 关系型数据库完整指南

概述

MySQL 是世界上最流行的开源关系型数据库管理系统，由 Oracle 公司维护和开发。作为 LAMP/LNMP 技术栈的重要组成部分，MySQL 广泛应用于 Web 开发、企业应用和数据分析等领域。

核心特性

• 关系型数据库：基于 SQL 标准，支持 ACID 事务
• 高性能：优化的存储引擎和查询优化器
• 高可用性：主从复制、集群部署、故障转移
• 可扩展性：支持读写分离、分库分表
• 开源免费：MySQL Community Edition 完全开源

版本对比

应用场景

💡 推荐: 新项目建议使用 MySQL 8.0，具有更好的性能、安全性和功能特性。

环境准备

系统要求

硬件要求

最低配置:

• CPU: 1 核心
• 内存: 1GB
• 存储: 5GB 可用空间

推荐配置:

• CPU: 2 核心以上
• 内存: 4GB 以上
• 存储: 50GB 以上 SSD
• 网络: 稳定的网络连接

生产环境:

• CPU: 4 核心以上
• 内存: 16GB 以上
• 存储: 100GB 以上 SSD，建议 RAID
• 网络: 千兆网络

Docker 快速部署

使用 Docker 安装 MySQL 是最方便快捷的方式。如果没有安装 Docker，可以参考 Docker 基本命令。

步骤 1: 查看可用版本

方式一: 访问 Docker Hub

• 访问 MySQL 镜像库
• 查看所有可用版本

方式二: 使用命令查看

docker search mysql

步骤 2: 拉取 MySQL 镜像

拉取最新版本:

# 拉取最新版本
docker pull mysql:latest

# 拉取指定版本
docker pull mysql:8.0
docker pull mysql:5.7

🔧 版本选择建议:

• latest: 始终为最新版本，但可能不稳定
• 8.0: 推荐版本，性能和特性最优
• 5.7: 稳定版本，兼容性好

步骤 3: 验证镜像安装

检查本地镜像列表：

docker images

步骤 4: 运行 MySQL 容器

基本运行命令:

# 基本运行命令
docker run --name mysql-server \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=root \
  -d mysql:8.0

# 生产环境推荐配置
docker run --name mysql-server \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=your_secure_password \
  -e MYSQL_DATABASE=myapp \
  -e MYSQL_USER=appuser \
  -e MYSQL_PASSWORD=apppass \
  -v mysql_data:/var/lib/mysql \
  -v mysql_logs:/var/log/mysql \
  --restart=unless-stopped \
  -d mysql:8.0

参数说明:

步骤 5: 验证安装

检查容器状态:

docker ps

查看容器日志:

docker logs mysql-server

配置优化

远程访问配置

步骤 1: 进入容器

docker exec -it mysql-server bash

步骤 2: 登录 MySQL

mysql -u root -p

步骤 3: 配置远程访问

-- 授权远程访问（MySQL 8.0）
-- 先创建用户
CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED BY 'StrongPassword123!';
-- 再授权
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

-- 刷新权限
FLUSH PRIVILEGES;

-- 更新认证方式（MySQL 8.0）
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
FLUSH PRIVILEGES;

⚠️ 安全警告: 生产环境请使用强密码，并限制访问来源IP

性能优化配置

创建自定义配置文件:

# 创建配置目录
mkdir -p /docker/mysql/conf

# 创建配置文件
cat > /docker/mysql/conf/my.cnf << EOF
[mysqld]
# 基础配置
port = 3306
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
bind-address = 0.0.0.0

# 字符集配置
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 性能优化
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# 连接配置
max_connections = 1000
max_connect_errors = 1000
wait_timeout = 600
interactive_timeout = 600

# 查询缓存（MySQL 5.7 及以下版本，MySQL 8.0 已移除）
# query_cache_size = 0
# query_cache_type = OFF

# 慢查询日志
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2

[mysql]
default-character-set = utf8mb4
EOF

使用自定义配置运行:

docker run --name mysql-server \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=root \
  -v /docker/mysql/conf:/etc/mysql/conf.d \
  -v mysql_data:/var/lib/mysql \
  -d mysql:8.0

常用操作

数据库管理

连接数据库:

# 容器内连接
docker exec -it mysql-server mysql -u root -p

# 主机连接
mysql -h localhost -P 3306 -u root -p

备份数据库:

# 备份单个数据库
docker exec mysql-server mysqldump -u root -p database_name > backup.sql

# 备份所有数据库
docker exec mysql-server mysqldump -u root -p --all-databases > all_backup.sql

恢复数据库:

# 恢复数据库
docker exec -i mysql-server mysql -u root -p database_name < backup.sql

用户管理

创建用户:

-- 创建用户
CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';

-- 授权
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'newuser'@'%';
FLUSH PRIVILEGES;

修改密码:

-- 修改密码
ALTER USER 'username'@'%' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;

容器管理

常用容器操作:

# 停止容器
docker stop mysql-server

# 启动容器
docker start mysql-server

# 重启容器
docker restart mysql-server

# 删除容器
docker rm mysql-server

# 删除数据卷
docker volume rm mysql_data

性能优化

监控指标

重要监控指标:

查看数据库状态:

-- 查看进程列表
SHOW PROCESSLIST;

-- 查看数据库状态
SHOW STATUS;

-- 查看系统变量
SHOW VARIABLES;

索引优化

创建索引:

-- 创建单列索引
CREATE INDEX idx_column_name ON table_name(column_name);

-- 创建复合索引
CREATE INDEX idx_multi ON table_name(col1, col2);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_unique ON table_name(column_name);

查看索引使用情况:

-- 查看表索引
SHOW INDEX FROM table_name;

-- 分析查询执行计划
EXPLAIN SELECT * FROM table_name WHERE condition;

传统安装方式

CentOS/RHEL 安装

1. 添加 MySQL 官方软件源

# 下载 MySQL 软件源
wget https://repo.mysql.com/mysql80-community-release-el8-1.noarch.rpm

# 安装软件源
sudo rpm -Uvh mysql80-community-release-el8-1.noarch.rpm

# 验证软件源
yum repolist enabled | grep "mysql.*-community.*"

2. 安装 MySQL 服务器

# 安装 MySQL 服务器
sudo yum install -y mysql-community-server

# 启动 MySQL 服务
sudo systemctl start mysqld
sudo systemctl enable mysqld

# 查看临时密码
sudo grep 'temporary password' /var/log/mysqld.log

Ubuntu/Debian 安装

# 更新包管理器
sudo apt update

# 安装 MySQL 服务器
sudo apt install -y mysql-server

# 启动服务
sudo systemctl start mysql
sudo systemctl enable mysql

# 安全配置
sudo mysql_secure_installation

基础配置

初始化安全配置

# 运行安全配置脚本
sudo mysql_secure_installation

配置项说明：

1. 设置 root 密码: 设置强密码
2. 删除匿名用户: 建议删除
3. 禁止 root 远程登录: 生产环境建议禁止
4. 删除测试数据库: 建议删除
5. 重新加载权限表: 选择是

字符集配置

编辑配置文件:

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

添加字符集配置:

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

时区配置

-- 设置时区
SET GLOBAL time_zone = '+8:00';

-- 查看时区设置
SELECT @@global.time_zone, @@session.time_zone;

数据库管理

数据库操作

创建和删除数据库

-- 创建数据库
CREATE DATABASE myapp 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

-- 查看数据库
SHOW DATABASES;

-- 使用数据库
USE myapp;

-- 删除数据库
DROP DATABASE myapp;

表操作

-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 查看表结构
DESCRIBE users;
SHOW CREATE TABLE users;

-- 修改表结构
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users MODIFY COLUMN username VARCHAR(100);
ALTER TABLE users DROP COLUMN phone;

-- 删除表
DROP TABLE users;

SQL 操作指南

数据插入

-- 单行插入
INSERT INTO users (username, email, password_hash) 
VALUES ('john_doe', 'john@example.com', 'hashed_password');

-- 多行插入
INSERT INTO users (username, email, password_hash) VALUES
('jane_smith', 'jane@example.com', 'hashed_password1'),
('bob_wilson', 'bob@example.com', 'hashed_password2'),
('alice_brown', 'alice@example.com', 'hashed_password3');

-- 插入或更新
INSERT INTO users (username, email, password_hash) 
VALUES ('john_doe', 'john@example.com', 'new_password')
ON DUPLICATE KEY UPDATE password_hash = VALUES(password_hash);

数据查询

-- 基本查询
SELECT * FROM users;
SELECT username, email FROM users;

-- 条件查询
SELECT * FROM users WHERE username = 'john_doe';
SELECT * FROM users WHERE created_at > '2023-01-01';
SELECT * FROM users WHERE username LIKE 'john%';

-- 排序和限制
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY username ASC LIMIT 10;
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

-- 聚合查询
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users WHERE created_at > '2023-01-01';
SELECT DATE(created_at) as date, COUNT(*) as count 
FROM users 
GROUP BY DATE(created_at);

数据更新和删除

-- 更新数据
UPDATE users SET email = 'newemail@example.com' WHERE username = 'john_doe';
UPDATE users SET updated_at = NOW() WHERE id = 1;

-- 删除数据
DELETE FROM users WHERE username = 'john_doe';
DELETE FROM users WHERE created_at < '2022-01-01';

-- 清空表
TRUNCATE TABLE users;

连接查询

-- 创建关联表
CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 内连接
SELECT u.username, p.title, p.created_at
FROM users u
INNER JOIN posts p ON u.id = p.user_id;

-- 左连接
SELECT u.username, p.title
FROM users u
LEFT JOIN posts p ON u.id = p.user_id;

-- 子查询
SELECT * FROM users 
WHERE id IN (SELECT DISTINCT user_id FROM posts);

安全配置

用户和权限管理

创建专用用户

-- 创建应用用户
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';
CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPassword123!';

-- 授予数据库权限
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_user'@'%';

-- 创建只读用户
CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'ReadOnlyPass123!';
GRANT SELECT ON myapp.* TO 'readonly_user'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

查看和管理权限

-- 查看用户
SELECT User, Host FROM mysql.user;

-- 查看用户权限
SHOW GRANTS FOR 'app_user'@'localhost';

-- 撤销权限
REVOKE INSERT, UPDATE, DELETE ON myapp.* FROM 'readonly_user'@'%';

-- 删除用户
DROP USER 'old_user'@'localhost';

SSL/TLS 加密配置

-- 查看 SSL 状态
SHOW VARIABLES LIKE '%ssl%';

-- 要求用户使用 SSL 连接
CREATE USER 'secure_user'@'%' IDENTIFIED BY 'SecurePass123!' REQUIRE SSL;

-- 更新现有用户要求 SSL
ALTER USER 'app_user'@'%' REQUIRE SSL;

密码策略配置

-- 查看密码验证插件
SHOW VARIABLES LIKE 'validate_password%';

-- 设置密码策略
SET GLOBAL validate_password.policy = 'STRONG';
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

网络安全

# 配置防火墙（CentOS/RHEL）
sudo firewall-cmd --permanent --add-port=3306/tcp --source=192.168.1.0/24
sudo firewall-cmd --reload

# 配置防火墙（Ubuntu）
sudo ufw allow from 192.168.1.0/24 to any port 3306

备份与恢复

逻辑备份（mysqldump）

完整备份

# 备份单个数据库
mysqldump -u root -p --single-transaction --routines --triggers myapp > myapp_backup.sql

# 备份所有数据库
mysqldump -u root -p --single-transaction --routines --triggers --all-databases > full_backup.sql

# 备份多个数据库
mysqldump -u root -p --single-transaction --databases db1 db2 db3 > multi_db_backup.sql

增量备份（基于二进制日志）

# 启用二进制日志（在 my.cnf 中）
[mysqld]
log-bin = mysql-bin
server-id = 1

# 查看二进制日志
SHOW BINARY LOGS;

# 备份二进制日志
mysqlbinlog mysql-bin.000001 > binlog_backup.sql

自动化备份脚本

#!/bin/bash
# mysql_backup.sh

# 配置变量
DB_USER="backup_user"
DB_PASS="backup_password"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行备份
mysqldump -u$DB_USER -p$DB_PASS \
  --single-transaction \
  --routines \
  --triggers \
  --all-databases | gzip > $BACKUP_DIR/full_backup_$DATE.sql.gz

# 清理旧备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

# 记录备份日志
echo "$(date): Backup completed - full_backup_$DATE.sql.gz" >> $BACKUP_DIR/backup.log

恢复操作

从完整备份恢复

# 恢复单个数据库
mysql -u root -p myapp < myapp_backup.sql

# 恢复所有数据库
mysql -u root -p < full_backup.sql

# 恢复压缩备份
gunzip < full_backup_20230701_120000.sql.gz | mysql -u root -p

时间点恢复

# 1. 恢复到完整备份点
mysql -u root -p < full_backup.sql

# 2. 应用二进制日志到指定时间
mysqlbinlog --stop-datetime="2023-07-01 12:30:00" mysql-bin.000001 | mysql -u root -p

物理备份（Percona XtraBackup）

# 安装 Percona XtraBackup
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb
sudo apt update
sudo apt install -y percona-xtrabackup-80

# 完整备份
xtrabackup --backup --target-dir=/backup/full --user=root --password=your_password

# 增量备份
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full --user=root --password=your_password

# 恢复准备
xtrabackup --prepare --target-dir=/backup/full
xtrabackup --prepare --target-dir=/backup/full --incremental-dir=/backup/inc1

# 恢复数据
sudo systemctl stop mysql
sudo rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/full
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysql

监控运维

性能监控指标

连接和线程监控

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';

-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';

-- 查看连接历史峰值
SHOW STATUS LIKE 'Max_used_connections';

-- 查看当前进程列表
SHOW PROCESSLIST;

-- 查看正在执行的查询
SELECT * FROM information_schema.PROCESSLIST 
WHERE COMMAND != 'Sleep' AND TIME > 1;

查询性能监控

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- 查看慢查询状态
SHOW STATUS LIKE 'Slow_queries';

-- 查看查询统计
SHOW STATUS LIKE 'Com_%';

-- 查看表缓存状态
SHOW STATUS LIKE 'Table%';

存储引擎监控

-- InnoDB 状态监控
SHOW ENGINE INNODB STATUS;

-- 查看 InnoDB 缓冲池状态
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- 查看 InnoDB 锁等待（MySQL 8.0）
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

-- MySQL 5.7 及以下版本使用：
-- SELECT * FROM information_schema.INNODB_LOCKS;
-- SELECT * FROM information_schema.INNODB_LOCK_WAITS;

系统资源监控

# CPU 使用率
top -p $(pgrep mysqld)

# 内存使用
ps aux | grep mysqld
cat /proc/$(pgrep mysqld)/status | grep Vm

# 磁盘 I/O
iostat -x 1

# 数据库文件大小
sudo du -sh /var/lib/mysql/

# 表空间使用情况
SELECT 
    table_schema,
    table_name,
    ROUND(data_length/1024/1024, 2) AS 'Data Size (MB)',
    ROUND(index_length/1024/1024, 2) AS 'Index Size (MB)',
    ROUND((data_length + index_length)/1024/1024, 2) AS 'Total Size (MB)'
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY (data_length + index_length) DESC;

监控脚本

#!/bin/bash
# mysql_monitor.sh

# MySQL 连接信息
MYSQL_USER="monitor_user"
MYSQL_PASS="monitor_password"
MYSQL_HOST="localhost"

# 监控函数
check_mysql_status() {
    echo "=== MySQL Status Check $(date) ==="
    
    # 检查 MySQL 服务状态
    if systemctl is-active --quiet mysql; then
        echo "✓ MySQL service is running"
    else
        echo "✗ MySQL service is not running"
        return 1
    fi
    
    # 检查连接数
    CONNECTIONS=$(mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW STATUS LIKE 'Threads_connected';" | awk 'NR==2 {print $2}')
    MAX_CONNECTIONS=$(mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW VARIABLES LIKE 'max_connections';" | awk 'NR==2 {print $2}')
    CONNECTION_USAGE=$(echo "scale=2; $CONNECTIONS * 100 / $MAX_CONNECTIONS" | bc)
    
    echo "Current connections: $CONNECTIONS / $MAX_CONNECTIONS ($CONNECTION_USAGE%)"
    
    # 检查慢查询
    SLOW_QUERIES=$(mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW STATUS LIKE 'Slow_queries';" | awk 'NR==2 {print $2}')
    echo "Slow queries: $SLOW_QUERIES"
    
    # 检查复制状态（如果配置了）
    SLAVE_STATUS=$(mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW SLAVE STATUS\G" 2>/dev/null)
    if [ ! -z "$SLAVE_STATUS" ]; then
        echo "Replication status: $(echo "$SLAVE_STATUS" | grep "Slave_SQL_Running" | awk '{print $2}')"
    fi
}

# 执行监控
check_mysql_status

# 写入日志
check_mysql_status >> /var/log/mysql_monitor.log

最佳实践

开发最佳实践

1. 数据库设计原则

表设计:

-- 遵循第三范式
-- 主键设计：使用自增 ID 作为主键
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36) NOT NULL UNIQUE COMMENT '业务唯一标识',
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -- 索引优化
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

字段规范:

• 使用 NOT NULL 约束，避免空值问题
• 字符串字段设置合理的长度限制
• 使用 utf8mb4 字符集支持 emoji
• 添加适当的字段注释

2. SQL 编写规范

查询优化:

-- 好的写法：使用索引字段查询
SELECT id, username, email FROM users 
WHERE username = 'john_doe' 
LIMIT 1;

-- 避免：全表扫描
SELECT * FROM users WHERE SUBSTRING(username, 1, 4) = 'john';

-- 好的写法：批量操作
INSERT INTO user_logs (user_id, action, created_at) VALUES
(1, 'login', NOW()),
(2, 'logout', NOW()),
(3, 'update_profile', NOW());

-- 避免：循环单条插入

3. 索引策略

-- 单列索引
CREATE INDEX idx_user_email ON users(email);

-- 复合索引（最左前缀原则）
CREATE INDEX idx_user_status_created ON users(status, created_at);

-- 唯一索引
CREATE UNIQUE INDEX uk_user_phone ON users(phone);

-- 函数索引（MySQL 8.0+）
CREATE INDEX idx_username_lower ON users((LOWER(username)));

-- 不可见索引（MySQL 8.0+）
CREATE INDEX idx_test ON users(email) INVISIBLE;

-- 注意：MySQL 不支持部分索引（WHERE子句），这是 PostgreSQL 的特性

运维最佳实践

1. 配置优化

生产环境配置:

[mysqld]
# 基础配置
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# InnoDB 配置
innodb_buffer_pool_size = 2G    # 根据系统内存调整，建议为系统内存的 50-70%
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1

# 连接配置
max_connections = 1000
max_connect_errors = 1000
wait_timeout = 600
interactive_timeout = 600

# 查询缓存（MySQL 5.7 及以下版本，MySQL 8.0 已移除）
# query_cache_type = OFF
# query_cache_size = 0

# 慢查询日志
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = ON

# 二进制日志
log-bin = mysql-bin
binlog_format = ROW
# MySQL 8.0 使用：
binlog_expire_logs_seconds = 604800  # 7天 = 7*24*60*60秒
# MySQL 5.7 及以下使用：
# expire_logs_days = 7
max_binlog_size = 1G

# 错误日志
log-error = /var/log/mysql/error.log

2. 监控告警

关键监控指标:

#!/bin/bash
# 监控脚本示例

# 连接数监控
CONNECTION_THRESHOLD=800
CURRENT_CONNECTIONS=$(mysql -e "SHOW STATUS LIKE 'Threads_connected';" | awk 'NR==2 {print $2}')

if [ $CURRENT_CONNECTIONS -gt $CONNECTION_THRESHOLD ]; then
    echo "WARNING: High connection count: $CURRENT_CONNECTIONS"
fi

# 慢查询监控
SLOW_QUERY_THRESHOLD=100
SLOW_QUERIES=$(mysql -e "SHOW STATUS LIKE 'Slow_queries';" | awk 'NR==2 {print $2}')

if [ $SLOW_QUERIES -gt $SLOW_QUERY_THRESHOLD ]; then
    echo "WARNING: Too many slow queries: $SLOW_QUERIES"
fi

# 复制延迟监控（主从环境）
REPLICATION_LAG=$(mysql -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master" | awk '{print $2}')
if [ ! -z "$REPLICATION_LAG" ] && [ $REPLICATION_LAG -gt 60 ]; then
    echo "WARNING: Replication lag: ${REPLICATION_LAG}s"
fi

3. 安全加固

安全检查清单:

• 删除默认账户和测试数据库
• 设置强密码策略
• 启用 SSL/TLS 加密
• 配置防火墙规则
• 定期更新 MySQL 版本
• 审计日志配置
• 文件权限检查

-- 安全配置示例
-- 1. 删除匿名用户
DELETE FROM mysql.user WHERE User='';

-- 2. 删除测试数据库
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';

-- 3. 禁用远程 root 登录
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

-- 4. 刷新权限
FLUSH PRIVILEGES;

性能调优最佳实践

1. 查询优化

-- 使用 EXPLAIN 分析查询
EXPLAIN FORMAT=JSON 
SELECT u.username, p.title 
FROM users u 
JOIN posts p ON u.id = p.user_id 
WHERE u.status = 'active' 
ORDER BY p.created_at DESC 
LIMIT 10;

-- 优化 WHERE 条件顺序
SELECT * FROM orders 
WHERE status = 'completed'  -- 高选择性条件放前面
  AND created_at >= '2023-01-01'
  AND amount > 100;

2. 分区策略

-- 按时间分区
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT,
    user_id INT NOT NULL,
    amount DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, created_at)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

3. 读写分离

# Python 示例：数据库路由
class DatabaseRouter:
    def __init__(self):
        self.master = MySQLConnection(host='master-db')
        self.slaves = [
            MySQLConnection(host='slave1-db'),
            MySQLConnection(host='slave2-db')
        ]
    
    def get_connection(self, operation='read'):
        if operation == 'write':
            return self.master
        else:
            # 负载均衡选择从库
            return random.choice(self.slaves)

常见问题

问题1：容器启动失败

❓ 问题: MySQL 容器启动失败

2. 密码设置错误
3. 权限问题
4. 磁盘空间不足

解决方案:

# 检查端口占用
netstat -tlnp | grep :3306

# 查看容器日志
docker logs mysql-server

# 检查磁盘空间
df -h

# 清理无用容器
docker system prune

问题2：连接被拒绝

❓ 问题: Connection refused 或 Access denied

解决方案:

# 检查容器状态
docker ps -a

# 检查防火墙
sudo ufw status

# 重置 root 密码
docker exec -it mysql-server mysql -u root -p

-- 重置密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;

问题3：性能问题

❓ 问题: 数据库响应缓慢

优化建议:

1. 增加内存分配:

   SET GLOBAL innodb_buffer_pool_size = 2147483648; -- 2GB

2. 优化查询:

   -- 查看慢查询
   SHOW VARIABLES LIKE 'slow_query_log';
   
   -- 分析查询
   EXPLAIN EXTENDED SELECT ...;

3. 索引优化:

   -- 查看未使用的索引
   SELECT * FROM sys.schema_unused_indexes;

问题4：数据丢失

❓ 问题: 容器重启后数据丢失

解决方案:

# 使用数据卷持久化
docker run -v mysql_data:/var/lib/mysql mysql:8.0

# 定期备份
docker exec mysql-server mysqldump -u root -p --all-databases > backup.sql

# 恢复数据
docker exec -i mysql-server mysql -u root -p < backup.sql

相关文章

MySQL 基础系列

• MySQL 慢查询日志分析 - 性能调优和慢查询优化
• 数据库导论 01 - 数据库基础使用 - MySQL 基础操作
• 数据库导论 02 - 数据库操作 - SQL 查询与操作
• 数据库导论 07 - 索引 - 索引设计和优化

容器化部署

• Docker 容器引擎安装 - Docker 环境搭建
• Docker 容器管理命令 - 容器操作指南
• Docker 环境部署 - 各种服务容器化

Java 集成开发

• Spring 数据库开发 - Spring JDBC 集成
• 初识 MyBatis - ORM 框架入门
• MyBatis 核心配置 - 配置详解
• MyBatis 动态 SQL - 高级 SQL 构建
• MyBatis 关系映射 - 关联查询
• Spring + MyBatis 整合 - 框架集成

SpringBoot 集成

• SpringBoot 整合 MyBatis - 快速集成
• SpringBoot 整合 MyBatis-Plus - 增强工具

连接配置和问题解决

• JDBC 连接 MySQL 常见问题 - 连接问题排查
• MyBatis 连接 MySQL 8.0 配置 - 版本兼容性

其他数据库技术

• Redis 内存数据库 - 缓存和 NoSQL 存储
• MongoDB 文档数据库 - NoSQL 文档存储
• Cassandra 分布式数据库 - 大数据存储
• Elasticsearch 搜索引擎 - 全文搜索和分析

监控运维

• ELK Stack 日志分析 - 日志收集和分析
• SkyWalking 链路追踪 - 应用性能监控
• GrayLog 分布式日志 - 日志管理平台

中间件集成

• Nacos 服务发现 - 微服务注册中心
• Kafka 消息队列 - 分布式消息系统
• RabbitMQ 消息队列 - 可靠消息传递

总结

MySQL 作为世界上最流行的开源关系型数据库，是现代应用开发的核心基础设施。本指南提供了从基础安装到高级运维的完整知识体系：

🎯 核心价值

• 快速入门: Docker 容器化部署，一键启动 MySQL 服务
• 生产就绪: 完整的安全配置、性能优化和监控方案
• 最佳实践: 数据库设计、SQL 优化、运维规范
• 故障排除: 常见问题诊断和解决方案

🛠️ 技术要点

1. 环境部署: Docker 快速部署 + 传统安装方式
2. 基础管理: 数据库操作、用户权限、SQL 操作
3. 性能优化: 索引策略、查询优化、配置调优
4. 安全加固: 用户管理、SSL 加密、访问控制
5. 备份恢复: 逻辑备份、物理备份、时间点恢复
6. 监控运维: 性能监控、故障排查、自动化脚本

🔧 实施路径

1. 学习阶段: 从 Docker 部署开始，掌握基本操作
2. 开发阶段: 学习 SQL 编写、索引设计、与应用集成
3. 运维阶段: 掌握备份策略、性能监控、故障处理
4. 优化阶段: 深入性能调优、架构设计、高可用

💡 最佳实践建议:

• 开发环境使用 Docker 快速搭建
• 生产环境注重安全配置和监控
• 定期备份数据，建立灾难恢复机制
• 持续优化查询性能和系统配置
• 关注 MySQL 社区动态和版本更新

相关文章

数据库基础教程

• 数据库导论系列 - MySQL 基础知识体系
• 数据库基础使用 - 入门操作指南
• MySQL 慢查询日志配置与分析 - 性能优化实践