2.1 MySQL安装概述
MySQL支持多种安装方式，包括二进制包安装、源码编译安装、Docker容器安装等。本章将详细介绍在不同操作系统上安装和配置MySQL的方法。
2.1.1 安装方式对比
class MySQLInstallationMethods:
def __init__(self):
self.methods = {
"二进制包安装": {
"优点": ["安装简单", "配置方便", "官方支持"],
"缺点": ["定制性较差", "版本更新依赖包管理器"],
"适用场景": "生产环境、快速部署"
},
"源码编译安装": {
"优点": ["高度定制", "性能优化", "版本控制灵活"],
"缺点": ["编译时间长", "依赖管理复杂", "技术要求高"],
"适用场景": "特殊需求、性能调优"
},
"Docker容器安装": {
"优点": ["环境隔离", "快速部署", "版本管理方便"],
"缺点": ["资源开销", "网络配置复杂"],
"适用场景": "开发测试、微服务架构"
},
"云服务安装": {
"优点": ["免运维", "高可用", "弹性扩展"],
"缺点": ["成本较高", "定制受限"],
"适用场景": "企业应用、快速上线"
}
}
def compare_methods(self):
"""对比安装方式"""
print("MySQL安装方式对比:")
print("=" * 60)
for method, details in self.methods.items():
print(f"\n{method}:")
print(f" 优点: {', '.join(details['优点'])}")
print(f" 缺点: {', '.join(details['缺点'])}")
print(f" 适用场景: {details['适用场景']}")
def recommend_method(self, scenario):
"""根据场景推荐安装方式"""
recommendations = {
"生产环境": "二进制包安装 - 稳定可靠，官方支持",
"开发测试": "Docker容器安装 - 快速部署，环境隔离",
"性能调优": "源码编译安装 - 高度定制，性能优化",
"企业应用": "云服务安装 - 免运维，高可用性"
}
return recommendations.get(scenario, "请提供具体使用场景")
# 安装方式对比
install_methods = MySQLInstallationMethods()
install_methods.compare_methods()
print("\n场景推荐:")
scenarios = ["生产环境", "开发测试", "性能调优", "企业应用"]
for scenario in scenarios:
recommendation = install_methods.recommend_method(scenario)
print(f"{scenario}: {recommendation}")
2.2 Linux系统安装
2.2.1 CentOS/RHEL安装
class CentOSMySQLInstaller:
def __init__(self):
self.version = "8.0"
self.repo_url = "https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm"
self.service_name = "mysqld"
def install_steps(self):
"""CentOS安装步骤"""
steps = [
{
"step": 1,
"description": "添加MySQL官方仓库",
"commands": [
"sudo yum install -y wget",
f"wget {self.repo_url}",
"sudo rpm -ivh mysql80-community-release-el7-3.noarch.rpm"
]
},
{
"step": 2,
"description": "安装MySQL服务器",
"commands": [
"sudo yum update -y",
"sudo yum install -y mysql-community-server"
]
},
{
"step": 3,
"description": "启动MySQL服务",
"commands": [
f"sudo systemctl start {self.service_name}",
f"sudo systemctl enable {self.service_name}",
f"sudo systemctl status {self.service_name}"
]
},
{
"step": 4,
"description": "获取临时密码",
"commands": [
"sudo grep 'temporary password' /var/log/mysqld.log"
]
},
{
"step": 5,
"description": "安全初始化",
"commands": [
"sudo mysql_secure_installation"
]
}
]
print("CentOS MySQL安装步骤:")
print("=" * 50)
for step in steps:
print(f"\n步骤 {step['step']}: {step['description']}")
for cmd in step['commands']:
print(f" $ {cmd}")
def post_installation_config(self):
"""安装后配置"""
print("\n安装后配置:")
print("=" * 30)
configs = {
"防火墙配置": [
"sudo firewall-cmd --permanent --add-port=3306/tcp",
"sudo firewall-cmd --reload"
],
"创建用户": [
"mysql -u root -p",
"CREATE USER 'admin'@'%' IDENTIFIED BY 'StrongPassword123!';",
"GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%';",
"FLUSH PRIVILEGES;"
],
"配置文件优化": [
"sudo vim /etc/my.cnf",
"# 添加配置项",
"[mysqld]",
"max_connections=1000",
"innodb_buffer_pool_size=1G"
]
}
for config_type, commands in configs.items():
print(f"\n{config_type}:")
for cmd in commands:
print(f" {cmd}")
# CentOS安装演示
centos_installer = CentOSMySQLInstaller()
centos_installer.install_steps()
centos_installer.post_installation_config()
2.2.2 Ubuntu/Debian安装
class UbuntuMySQLInstaller:
def __init__(self):
self.version = "8.0"
self.package_name = "mysql-server"
self.service_name = "mysql"
def install_steps(self):
"""Ubuntu安装步骤"""
steps = [
{
"step": 1,
"description": "更新包索引",
"commands": [
"sudo apt update",
"sudo apt upgrade -y"
]
},
{
"step": 2,
"description": "安装MySQL服务器",
"commands": [
f"sudo apt install -y {self.package_name}"
]
},
{
"step": 3,
"description": "检查服务状态",
"commands": [
f"sudo systemctl status {self.service_name}",
f"sudo systemctl enable {self.service_name}"
]
},
{
"step": 4,
"description": "安全初始化",
"commands": [
"sudo mysql_secure_installation"
]
}
]
print("Ubuntu MySQL安装步骤:")
print("=" * 50)
for step in steps:
print(f"\n步骤 {step['step']}: {step['description']}")
for cmd in step['commands']:
print(f" $ {cmd}")
def authentication_setup(self):
"""认证设置"""
print("\nUbuntu MySQL认证设置:")
print("=" * 40)
auth_steps = [
"# 登录MySQL（Ubuntu默认使用auth_socket插件）",
"sudo mysql",
"",
"# 修改root用户认证方式",
"ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourStrongPassword123!';",
"",
"# 刷新权限",
"FLUSH PRIVILEGES;",
"",
"# 退出并重新登录测试",
"EXIT;",
"mysql -u root -p"
]
for step in auth_steps:
print(step)
# Ubuntu安装演示
ubuntu_installer = UbuntuMySQLInstaller()
ubuntu_installer.install_steps()
ubuntu_installer.authentication_setup()
2.3 Windows系统安装
2.3.1 Windows安装器安装
class WindowsMySQLInstaller:
def __init__(self):
self.installer_url = "https://dev.mysql.com/downloads/installer/"
self.version = "8.0"
self.default_port = 3306
def installation_guide(self):
"""Windows安装指南"""
print("Windows MySQL安装指南:")
print("=" * 50)
steps = [
{
"step": 1,
"title": "下载安装器",
"details": [
f"访问官网: {self.installer_url}",
"选择 MySQL Installer for Windows",
"下载 mysql-installer-community-8.0.x.x.msi"
]
},
{
"step": 2,
"title": "运行安装器",
"details": [
"右键以管理员身份运行安装器",
"选择安装类型: Developer Default 或 Server only",
"检查系统要求"
]
},
{
"step": 3,
"title": "配置MySQL服务器",
"details": [
"配置类型: Development Computer",
f"端口号: {self.default_port}",
"认证方法: Use Strong Password Encryption"
]
},
{
"step": 4,
"title": "设置root密码",
"details": [
"设置强密码（至少8位，包含大小写字母、数字、特殊字符）",
"可选择添加其他用户账户"
]
},
{
"step": 5,
"title": "配置Windows服务",
"details": [
"服务名: MySQL80",
"启动类型: Automatic",
"运行账户: Network Service"
]
}
]
for step in steps:
print(f"\n步骤 {step['step']}: {step['title']}")
for detail in step['details']:
print(f" - {detail}")
def post_installation_tasks(self):
"""安装后任务"""
print("\n安装后任务:")
print("=" * 30)
tasks = {
"环境变量配置": [
"添加 C:\\Program Files\\MySQL\\MySQL Server 8.0\\bin 到 PATH",
"重启命令提示符或PowerShell"
],
"防火墙配置": [
"Windows Defender防火墙 -> 高级设置",
"入站规则 -> 新建规则 -> 端口 -> TCP -> 3306",
"允许连接"
],
"服务管理": [
"services.msc 打开服务管理器",
"找到 MySQL80 服务",
"设置启动类型为自动"
],
"连接测试": [
"打开命令提示符",
"mysql -u root -p",
"输入密码连接"
]
}
for task_type, steps in tasks.items():
print(f"\n{task_type}:")
for step in steps:
print(f" - {step}")
# Windows安装演示
windows_installer = WindowsMySQLInstaller()
windows_installer.installation_guide()
windows_installer.post_installation_tasks()
2.3.2 ZIP包安装
class WindowsZipInstaller:
def __init__(self):
self.zip_url = "https://dev.mysql.com/downloads/mysql/"
self.install_path = "C:\\mysql"
self.data_path = "C:\\mysql\\data"
def zip_installation_steps(self):
"""ZIP包安装步骤"""
print("Windows ZIP包安装步骤:")
print("=" * 50)
steps = [
{
"step": 1,
"description": "下载ZIP包",
"actions": [
f"访问 {self.zip_url}",
"下载 Windows (x86, 64-bit), ZIP Archive",
"解压到 C:\\mysql"
]
},
{
"step": 2,
"description": "创建配置文件",
"actions": [
f"在 {self.install_path} 目录创建 my.ini",
"配置基本参数"
]
},
{
"step": 3,
"description": "初始化数据目录",
"actions": [
"以管理员身份打开命令提示符",
"cd C:\\mysql\\bin",
"mysqld --initialize --console"
]
},
{
"step": 4,
"description": "安装服务",
"actions": [
"mysqld --install MySQL80",
"net start MySQL80"
]
}
]
for step in steps:
print(f"\n步骤 {step['step']}: {step['description']}")
for action in step['actions']:
print(f" - {action}")
def create_config_file(self):
"""创建配置文件"""
config_content = f"""
# MySQL配置文件 my.ini
[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir={self.install_path}
# 设置mysql数据库的数据的存放目录
datadir={self.data_path}
# 允许最大连接数
max_connections=200
# 允许连接失败的次数
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用"mysql_native_password"插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4
"""
print("\nmy.ini配置文件内容:")
print("=" * 40)
print(config_content)
# ZIP包安装演示
zip_installer = WindowsZipInstaller()
zip_installer.zip_installation_steps()
zip_installer.create_config_file()
2.4 Docker容器安装
2.4.1 Docker基础安装
class DockerMySQLInstaller:
def __init__(self):
self.image_name = "mysql:8.0"
self.container_name = "mysql-server"
self.port = 3306
self.root_password = "MyStrongPassword123!"
def basic_installation(self):
"""基础Docker安装"""
print("Docker MySQL基础安装:")
print("=" * 40)
commands = [
{
"description": "拉取MySQL镜像",
"command": f"docker pull {self.image_name}"
},
{
"description": "运行MySQL容器",
"command": f"""docker run -d \\
--name {self.container_name} \\
-p {self.port}:{self.port} \\
-e MYSQL_ROOT_PASSWORD={self.root_password} \\
{self.image_name}"""
},
{
"description": "检查容器状态",
"command": "docker ps"
},
{
"description": "连接到MySQL",
"command": f"docker exec -it {self.container_name} mysql -u root -p"
}
]
for cmd in commands:
print(f"\n{cmd['description']}:")
print(f" {cmd['command']}")
def advanced_configuration(self):
"""高级配置"""
print("\nDocker MySQL高级配置:")
print("=" * 40)
# Docker Compose配置
compose_content = f"""
version: '3.8'
services:
mysql:
image: {self.image_name}
container_name: {self.container_name}
restart: always
environment:
MYSQL_ROOT_PASSWORD: {self.root_password}
MYSQL_DATABASE: testdb
MYSQL_USER: testuser
MYSQL_PASSWORD: testpass
ports:
- "{self.port}:{self.port}"
volumes:
- mysql_data:/var/lib/mysql
- ./conf:/etc/mysql/conf.d
- ./init:/docker-entrypoint-initdb.d
command: --default-authentication-plugin=mysql_native_password
volumes:
mysql_data:
"""
print("docker-compose.yml:")
print(compose_content)
print("\n使用Docker Compose:")
compose_commands = [
"docker-compose up -d",
"docker-compose ps",
"docker-compose logs mysql",
"docker-compose down"
]
for cmd in compose_commands:
print(f" {cmd}")
def data_persistence(self):
"""数据持久化"""
print("\n数据持久化配置:")
print("=" * 30)
persistence_methods = {
"命名卷": {
"创建卷": "docker volume create mysql_data",
"使用卷": f"docker run -v mysql_data:/var/lib/mysql {self.image_name}"
},
"绑定挂载": {
"创建目录": "mkdir -p /opt/mysql/data",
"挂载目录": f"docker run -v /opt/mysql/data:/var/lib/mysql {self.image_name}"
},
"配置文件挂载": {
"创建配置目录": "mkdir -p /opt/mysql/conf",
"挂载配置": f"docker run -v /opt/mysql/conf:/etc/mysql/conf.d {self.image_name}"
}
}
for method, commands in persistence_methods.items():
print(f"\n{method}:")
for desc, cmd in commands.items():
print(f" {desc}: {cmd}")
# Docker安装演示
docker_installer = DockerMySQLInstaller()
docker_installer.basic_installation()
docker_installer.advanced_configuration()
docker_installer.data_persistence()
2.4.2 Docker集群部署
class DockerMySQLCluster:
def __init__(self):
self.master_container = "mysql-master"
self.slave_container = "mysql-slave"
self.network_name = "mysql-cluster"
def create_cluster(self):
"""创建MySQL主从集群"""
print("Docker MySQL主从集群部署:")
print("=" * 50)
# 创建网络
print("1. 创建Docker网络:")
print(f" docker network create {self.network_name}")
# 主服务器配置
print("\n2. 启动主服务器:")
master_cmd = f"""
docker run -d \\
--name {self.master_container} \\
--network {self.network_name} \\
-p 3306:3306 \\
-e MYSQL_ROOT_PASSWORD=master123 \\
-e MYSQL_REPLICATION_USER=repl \\
-e MYSQL_REPLICATION_PASSWORD=repl123 \\
-v master_data:/var/lib/mysql \\
-v ./master.cnf:/etc/mysql/conf.d/master.cnf \\
mysql:8.0
"""
print(f" {master_cmd}")
# 从服务器配置
print("\n3. 启动从服务器:")
slave_cmd = f"""
docker run -d \\
--name {self.slave_container} \\
--network {self.network_name} \\
-p 3307:3306 \\
-e MYSQL_ROOT_PASSWORD=slave123 \\
-v slave_data:/var/lib/mysql \\
-v ./slave.cnf:/etc/mysql/conf.d/slave.cnf \\
mysql:8.0
"""
print(f" {slave_cmd}")
def create_config_files(self):
"""创建配置文件"""
print("\n配置文件:")
print("=" * 20)
# 主服务器配置
master_config = """
# master.cnf
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-format=ROW
binlog-do-db=testdb
"""
# 从服务器配置
slave_config = """
# slave.cnf
[mysqld]
server-id=2
relay-log=mysql-relay-bin
log-slave-updates=1
read-only=1
"""
print("master.cnf:")
print(master_config)
print("\nslave.cnf:")
print(slave_config)
def setup_replication(self):
"""设置主从复制"""
print("\n设置主从复制:")
print("=" * 30)
replication_steps = [
{
"step": "1. 在主服务器创建复制用户",
"commands": [
f"docker exec -it {self.master_container} mysql -u root -p",
"CREATE USER 'repl'@'%' IDENTIFIED BY 'repl123';",
"GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';",
"FLUSH PRIVILEGES;",
"SHOW MASTER STATUS;"
]
},
{
"step": "2. 在从服务器配置复制",
"commands": [
f"docker exec -it {self.slave_container} mysql -u root -p",
"CHANGE MASTER TO",
" MASTER_HOST='mysql-master',",
" MASTER_USER='repl',",
" MASTER_PASSWORD='repl123',",
" MASTER_LOG_FILE='mysql-bin.000001',",
" MASTER_LOG_POS=0;",
"START SLAVE;",
"SHOW SLAVE STATUS\\G;"
]
}
]
for step in replication_steps:
print(f"\n{step['step']}:")
for cmd in step['commands']:
print(f" {cmd}")
# Docker集群演示
cluster = DockerMySQLCluster()
cluster.create_cluster()
cluster.create_config_files()
cluster.setup_replication()
2.5 配置文件详解
2.5.1 my.cnf配置结构
class MySQLConfigManager:
def __init__(self):
self.config_sections = {
"[mysqld]": "MySQL服务器配置",
"[mysql]": "MySQL客户端配置",
"[client]": "所有客户端程序配置",
"[mysqldump]": "mysqldump工具配置",
"[mysqladmin]": "mysqladmin工具配置"
}
def show_config_structure(self):
"""显示配置文件结构"""
print("MySQL配置文件结构:")
print("=" * 40)
for section, description in self.config_sections.items():
print(f"{section}: {description}")
def generate_basic_config(self):
"""生成基础配置"""
config = """
# MySQL基础配置文件
# /etc/my.cnf (Linux) 或 my.ini (Windows)
[mysqld]
# 基本设置
port = 3306
socket = /var/lib/mysql/mysql.sock
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
# 字符集设置
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
# 连接设置
max_connections = 1000
max_connect_errors = 100000
max_allowed_packet = 64M
interactive_timeout = 28800
wait_timeout = 28800
# InnoDB设置
default-storage-engine = InnoDB
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
# 日志设置
log-error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
# 二进制日志
log-bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M
[mysql]
default-character-set = utf8mb4
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set = utf8mb4
"""
print("\n基础配置文件:")
print("=" * 30)
print(config)
def performance_tuning_config(self):
"""性能调优配置"""
print("\n性能调优配置:")
print("=" * 30)
tuning_params = {
"内存相关": {
"innodb_buffer_pool_size": "设置为物理内存的70-80%",
"key_buffer_size": "MyISAM索引缓存，默认256M",
"query_cache_size": "查询缓存大小，建议64M-256M",
"tmp_table_size": "临时表大小，建议64M-256M",
"max_heap_table_size": "内存表最大大小"
},
"连接相关": {
"max_connections": "最大连接数，根据并发需求设置",
"thread_cache_size": "线程缓存大小，建议8-64",
"table_open_cache": "表缓存大小，建议2000-4000",
"open_files_limit": "打开文件数限制"
},
"InnoDB相关": {
"innodb_log_file_size": "日志文件大小，建议256M-1G",
"innodb_log_buffer_size": "日志缓冲区大小，建议16M-64M",
"innodb_flush_method": "刷新方法，Linux建议O_DIRECT",
"innodb_io_capacity": "IO容量，SSD建议2000-4000"
}
}
for category, params in tuning_params.items():
print(f"\n{category}:")
for param, description in params.items():
print(f" {param}: {description}")
# 配置管理演示
config_manager = MySQLConfigManager()
config_manager.show_config_structure()
config_manager.generate_basic_config()
config_manager.performance_tuning_config()
2.5.2 配置参数详解
class MySQLConfigParameters:
def __init__(self):
self.parameters = {
"连接参数": {
"max_connections": {
"默认值": 151,
"建议值": "根据并发需求，一般500-2000",
"说明": "允许的最大同时连接数"
},
"max_connect_errors": {
"默认值": 100,
"建议值": 100000,
"说明": "允许的最大连接错误次数"
},
"connect_timeout": {
"默认值": 10,
"建议值": "10-30",
"说明": "连接超时时间（秒）"
}
},
"缓存参数": {
"innodb_buffer_pool_size": {
"默认值": "128M",
"建议值": "物理内存的70-80%",
"说明": "InnoDB缓冲池大小"
},
"query_cache_size": {
"默认值": 0,
"建议值": "64M-256M",
"说明": "查询缓存大小"
},
"table_open_cache": {
"默认值": 2000,
"建议值": "2000-4000",
"说明": "表缓存大小"
}
},
"日志参数": {
"innodb_log_file_size": {
"默认值": "48M",
"建议值": "256M-1G",
"说明": "InnoDB日志文件大小"
},
"slow_query_log": {
"默认值": "OFF",
"建议值": "ON",
"说明": "是否启用慢查询日志"
},
"long_query_time": {
"默认值": 10,
"建议值": "1-5",
"说明": "慢查询阈值（秒）"
}
}
}
def show_parameters(self):
"""显示配置参数"""
print("MySQL重要配置参数:")
print("=" * 60)
for category, params in self.parameters.items():
print(f"\n{category}:")
for param_name, param_info in params.items():
print(f" {param_name}:")
print(f" 默认值: {param_info['默认值']}")
print(f" 建议值: {param_info['建议值']}")
print(f" 说明: {param_info['说明']}")
def calculate_innodb_buffer_pool(self, total_memory_gb):
"""计算InnoDB缓冲池大小"""
recommended_size = int(total_memory_gb * 0.75) # 75%的内存
print(f"\n内存配置建议（总内存: {total_memory_gb}GB）:")
print(f"innodb_buffer_pool_size = {recommended_size}G")
print(f"剩余内存用于操作系统和其他进程: {total_memory_gb - recommended_size}G")
return f"{recommended_size}G"
def generate_optimized_config(self, memory_gb, cpu_cores, storage_type="SSD"):
"""生成优化配置"""
buffer_pool_size = int(memory_gb * 0.75)
max_connections = min(cpu_cores * 100, 2000)
io_capacity = 2000 if storage_type == "SSD" else 200
config = f"""
# 优化配置（内存: {memory_gb}GB, CPU: {cpu_cores}核心, 存储: {storage_type}）
[mysqld]
# 连接设置
max_connections = {max_connections}
thread_cache_size = {min(cpu_cores * 2, 64)}
# 内存设置
innodb_buffer_pool_size = {buffer_pool_size}G
innodb_buffer_pool_instances = {min(buffer_pool_size, 8)}
# InnoDB设置
innodb_log_file_size = {min(buffer_pool_size * 256, 1024)}M
innodb_log_buffer_size = {min(buffer_pool_size * 16, 64)}M
innodb_io_capacity = {io_capacity}
innodb_io_capacity_max = {io_capacity * 2}
# 查询缓存
query_cache_size = {min(memory_gb * 32, 256)}M
query_cache_type = 1
# 表缓存
table_open_cache = {cpu_cores * 200}
open_files_limit = {cpu_cores * 1000}
"""
print(f"\n针对硬件的优化配置:")
print("=" * 40)
print(config)
# 配置参数演示
config_params = MySQLConfigParameters()
config_params.show_parameters()
config_params.calculate_innodb_buffer_pool(16) # 16GB内存
config_params.generate_optimized_config(16, 8, "SSD") # 16GB内存，8核CPU，SSD存储
2.6 安全配置
2.6.1 用户权限管理
class MySQLSecurityManager:
def __init__(self):
self.security_checklist = {
"用户管理": [
"删除匿名用户",
"删除test数据库",
"设置强密码策略",
"限制root用户远程登录"
],
"网络安全": [
"配置防火墙规则",
"使用SSL连接",
"限制监听地址",
"修改默认端口"
],
"文件权限": [
"设置数据目录权限",
"保护配置文件",
"限制日志文件访问"
]
}
def security_initialization(self):
"""安全初始化"""
print("MySQL安全初始化:")
print("=" * 40)
init_commands = [
{
"description": "运行安全脚本",
"command": "mysql_secure_installation",
"options": [
"设置root密码",
"删除匿名用户",
"禁止root远程登录",
"删除test数据库",
"重新加载权限表"
]
},
{
"description": "手动安全配置",
"command": "mysql -u root -p",
"sql_commands": [
"-- 删除匿名用户",
"DELETE FROM mysql.user WHERE User='';",
"",
"-- 删除test数据库",
"DROP DATABASE IF EXISTS test;",
"DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';",
"",
"-- 限制root用户只能本地登录",
"DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');",
"",
"-- 刷新权限",
"FLUSH PRIVILEGES;"
]
}
]
for cmd in init_commands:
print(f"\n{cmd['description']}:")
print(f" {cmd['command']}")
if 'options' in cmd:
for option in cmd['options']:
print(f" - {option}")
if 'sql_commands' in cmd:
for sql in cmd['sql_commands']:
print(f" {sql}")
def create_secure_users(self):
"""创建安全用户"""
print("\n创建安全用户:")
print("=" * 30)
user_examples = [
{
"role": "应用程序用户",
"username": "app_user",
"host": "192.168.1.%",
"privileges": "SELECT, INSERT, UPDATE, DELETE",
"databases": "app_db"
},
{
"role": "只读用户",
"username": "readonly_user",
"host": "%",
"privileges": "SELECT",
"databases": "*"
},
{
"role": "备份用户",
"username": "backup_user",
"host": "localhost",
"privileges": "SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER",
"databases": "*"
}
]
for user in user_examples:
print(f"\n{user['role']}:")
print(f" -- 创建用户")
print(f" CREATE USER '{user['username']}'@'{user['host']}' IDENTIFIED BY 'StrongPassword123!';")
print(f" -- 授权")
print(f" GRANT {user['privileges']} ON {user['databases']}.* TO '{user['username']}'@'{user['host']}';")
print(f" -- 刷新权限")
print(f" FLUSH PRIVILEGES;")
def ssl_configuration(self):
"""SSL配置"""
print("\nSSL安全连接配置:")
print("=" * 40)
ssl_steps = [
{
"step": "1. 生成SSL证书",
"commands": [
"# 使用MySQL自带工具生成证书",
"mysql_ssl_rsa_setup --datadir=/var/lib/mysql",
"",
"# 或手动生成证书",
"openssl genrsa 2048 > ca-key.pem",
"openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem",
"openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem",
"openssl rsa -in server-key.pem -out server-key.pem",
"openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem"
]
},
{
"step": "2. 配置MySQL使用SSL",
"commands": [
"# 在my.cnf中添加SSL配置",
"[mysqld]",
"ssl-ca=/var/lib/mysql/ca.pem",
"ssl-cert=/var/lib/mysql/server-cert.pem",
"ssl-key=/var/lib/mysql/server-key.pem",
"require_secure_transport=ON"
]
},
{
"step": "3. 创建需要SSL的用户",
"commands": [
"CREATE USER 'ssl_user'@'%' IDENTIFIED BY 'password' REQUIRE SSL;",
"GRANT SELECT ON *.* TO 'ssl_user'@'%';",
"FLUSH PRIVILEGES;"
]
},
{
"step": "4. 客户端SSL连接",
"commands": [
"mysql -u ssl_user -p --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem",
"",
"# 验证SSL连接",
"SHOW STATUS LIKE 'Ssl_cipher';"
]
}
]
for step in ssl_steps:
print(f"\n{step['step']}:")
for cmd in step['commands']:
print(f" {cmd}")
# 安全管理演示
security_manager = MySQLSecurityManager()
security_manager.security_initialization()
security_manager.create_secure_users()
security_manager.ssl_configuration()
2.6.2 防火墙和网络安全
class MySQLNetworkSecurity:
def __init__(self):
self.default_port = 3306
self.custom_port = 3307
def firewall_configuration(self):
"""防火墙配置"""
print("防火墙配置:")
print("=" * 30)
firewall_configs = {
"CentOS/RHEL (firewalld)": [
f"# 允许MySQL端口",
f"firewall-cmd --permanent --add-port={self.default_port}/tcp",
f"firewall-cmd --reload",
f"",
f"# 限制特定IP访问",
f"firewall-cmd --permanent --add-rich-rule='rule family=ipv4 source address=192.168.1.0/24 port protocol=tcp port={self.default_port} accept'",
f"firewall-cmd --reload"
],
"Ubuntu (ufw)": [
f"# 允许MySQL端口",
f"ufw allow {self.default_port}/tcp",
f"",
f"# 限制特定IP访问",
f"ufw allow from 192.168.1.0/24 to any port {self.default_port}",
f"ufw enable"
],
"iptables": [
f"# 允许MySQL端口",
f"iptables -A INPUT -p tcp --dport {self.default_port} -j ACCEPT",
f"",
f"# 限制特定网段访问",
f"iptables -A INPUT -p tcp -s 192.168.1.0/24 --dport {self.default_port} -j ACCEPT",
f"iptables -A INPUT -p tcp --dport {self.default_port} -j DROP",
f"",
f"# 保存规则",
f"iptables-save > /etc/iptables/rules.v4"
]
}
for system, commands in firewall_configs.items():
print(f"\n{system}:")
for cmd in commands:
print(f" {cmd}")
def network_binding_config(self):
"""网络绑定配置"""
print("\n网络绑定配置:")
print("=" * 30)
binding_configs = {
"只允许本地连接": {
"配置": "bind-address = 127.0.0.1",
"说明": "只允许本机连接，最安全"
},
"允许内网连接": {
"配置": "bind-address = 192.168.1.100",
"说明": "绑定到内网IP，允许内网访问"
},
"允许所有连接": {
"配置": "bind-address = 0.0.0.0",
"说明": "允许所有IP连接，需配合防火墙使用"
},
"多IP绑定": {
"配置": "bind-address = 127.0.0.1,192.168.1.100",
"说明": "绑定多个IP地址"
}
}
for config_type, config_info in binding_configs.items():
print(f"\n{config_type}:")
print(f" 配置: {config_info['配置']}")
print(f" 说明: {config_info['说明']}")
def port_security(self):
"""端口安全配置"""
print("\n端口安全配置:")
print("=" * 30)
port_configs = [
{
"策略": "修改默认端口",
"配置": f"port = {self.custom_port}",
"优点": "避免自动化扫描攻击",
"注意": "需要更新防火墙规则和客户端配置"
},
{
"策略": "端口敲门",
"配置": "使用knockd等工具",
"优点": "隐藏服务端口",
"注意": "增加管理复杂度"
},
{
"策略": "VPN访问",
"配置": "通过VPN隧道访问",
"优点": "加密传输，安全性高",
"注意": "需要VPN基础设施"
}
]
for config in port_configs:
print(f"\n{config['策略']}:")
print(f" 配置: {config['配置']}")
print(f" 优点: {config['优点']}")
print(f" 注意: {config['注意']}")
def connection_limits(self):
"""连接限制配置"""
print("\n连接限制配置:")
print("=" * 30)
limit_configs = """
# 连接数限制
max_connections = 1000 # 最大连接数
max_user_connections = 100 # 单用户最大连接数
max_connect_errors = 100000 # 最大连接错误数
# 超时设置
connect_timeout = 10 # 连接超时
interactive_timeout = 28800 # 交互超时
wait_timeout = 28800 # 等待超时
net_read_timeout = 30 # 网络读取超时
net_write_timeout = 60 # 网络写入超时
# 连接验证
validate_password.policy = MEDIUM # 密码策略
validate_password.length = 8 # 最小密码长度
validate_password.mixed_case_count = 1 # 大小写字母数量
validate_password.number_count = 1 # 数字数量
validate_password.special_char_count = 1 # 特殊字符数量
"""
print(limit_configs)
# 网络安全演示
network_security = MySQLNetworkSecurity()
network_security.firewall_configuration()
network_security.network_binding_config()
network_security.port_security()
network_security.connection_limits()
2.7 总结
本章详细介绍了MySQL的安装部署与环境配置，包括：
核心要点
- 安装方式：二进制包、源码编译、Docker容器、云服务
- 多平台支持：Linux（CentOS/Ubuntu）、Windows、Docker
- 配置管理：my.cnf/my.ini配置文件详解
- 安全配置：用户权限、SSL加密、网络安全
最佳实践
安装选择：
- 生产环境推荐二进制包安装
- 开发测试推荐Docker容器
- 特殊需求考虑源码编译
配置优化：
- 根据硬件资源调整内存参数
- 合理设置连接数和超时时间
- 启用慢查询日志监控性能
安全加固：
- 运行mysql_secure_installation
- 创建专用应用用户
- 配置防火墙规则
- 启用SSL加密连接
运维管理：
- 定期备份配置文件
- 监控系统资源使用
- 建立变更管理流程
下一章我们将学习MySQL的基本操作，包括数据库和表的创建、数据的增删改查等基础操作。
