# 卸载系统自带的 MariaDB 及旧版 MySQL
sudo dnf remove -y mariadb-server mariadb mariadb-libs \
  mysql-server mysql mysql-libs \
  community-mysql-server community-mysql
​
# 清理残留配置（可选）
sudo rm -rf /var/lib/mysql
sudo rm -f /etc/my.cnf /etc/my.cnf.d/*

# 下载 MySQL 8.4 Yum 仓库 RPM（EL9）
sudo dnf install -y https://dev.mysql.com/get/mysql84-community-release-el9-1.noarch.rpm

sudo dnf repolist enabled | grep mysql

# 查看所有可用的 MySQL 子仓库
sudo dnf repolist all | grep mysql
​
# 禁用 8.4，启用 8.0
sudo dnf config-manager --disable mysql-8.4-lts-community
sudo dnf config-manager --enable mysql80-community
​
# 验证当前启用的版本
sudo dnf repolist enabled | grep mysql

sudo dnf install -y mysql-community-server

# 启动 MySQL 守护进程
sudo systemctl start mysqld
​
# 设置开机自动启动
sudo systemctl enable mysqld
​
# 验证运行状态
sudo systemctl status mysqld

# 从日志中提取临时密码
sudo grep 'temporary password' /var/log/mysqld.log

sudo mysql_secure_installation

# 登录 MySQL
mysql -u root -p
​
# 查看版本
mysql> SELECT VERSION();
​
# 查看当前用户
mysql> SELECT USER(), CURRENT_USER();
​
# 查看数据库列表
mysql> SHOW DATABASES;
​
# 退出
mysql> EXIT;

# 查看 MySQL 服务版本（无需登录）
mysqld --version
​
# 查看安装的 MySQL 包
rpm -qa | grep mysql-community

# 列出所有可用版本
dnf list mysql-community-server --showduplicates | sort -r
​
# 安装指定版本（替换 VERSION_STRING）
sudo dnf install -y mysql-community-server-VERSION_STRING

-- 查看当前密码策略
SHOW VARIABLES LIKE 'validate_password%';
​
-- 降低密码策略为 LOW（仅检查长度）
SET GLOBAL validate_password.policy = LOW;
SET GLOBAL validate_password.length = 6;

-- 创建用户并授权（替换实际用户名、密码和权限）
CREATE USER 'appuser'@'%' IDENTIFIED BY 'StrongP@ssw0rd!';
GRANT ALL PRIVILEGES ON mydb.* TO 'appuser'@'%';
FLUSH PRIVILEGES;
​
-- 如需 root 远程访问（不推荐生产环境）
-- UPDATE mysql.user SET Host='%' WHERE User='root';
-- FLUSH PRIVILEGES;

-- 查看当前默认认证插件
SHOW VARIABLES LIKE 'default_authentication_plugin';
​
-- 为特定用户使用旧版认证（兼容旧客户端）
ALTER USER 'appuser'@'%' IDENTIFIED WITH mysql_native_password BY 'StrongP@ssw0rd!';

# 放行 MySQL 默认端口 3306
sudo firewall-cmd --permanent --add-port=3306/tcp
sudo firewall-cmd --reload
​
# 验证规则
sudo firewall-cmd --list-ports

# 查看 SELinux 状态
getenforce
​
# 允许 MySQL 使用非标准端口（如修改了默认端口）
sudo semanage port -a -t mysqld_port_t -p tcp 3307
​
# 如果遇到 SELinux 阻止问题，查看审计日志
sudo ausearch -m AVC -ts recent | grep mysql

# 更新 Homebrew
brew update
​
# 安装 MySQL 8.4（默认最新 LTS）
brew install mysql
​
# 如需安装 8.0（注意：8.0 将于 2026-04 EOL）
# brew install mysql@8.0

# 使用 brew services 管理（推荐，支持开机自启）
brew services start mysql
​
# 或手动启动（不设置开机自启）
mysql.server start

# 首次安装后 root 密码为空，执行安全初始化
mysql_secure_installation

mysql --version
mysql -u root -p -e "SELECT VERSION();"

# 查看服务状态
brew services list | grep mysql
​
# 停止服务
brew services stop mysql
​
# 重启服务
brew services restart mysql
​
# 卸载
brew uninstall mysql
rm -rf /opt/homebrew/var/mysql  # 删除数据目录（Apple Silicon 路径）

# 添加到 ~/.zshrc（macOS 默认 zsh）
export PATH="/usr/local/mysql/bin:$PATH"
source ~/.zshrc

mysql_secure_installation

docker pull --platform linux/arm64 mysql:8.4

# 添加 MySQL 到 PATH（如果未自动添加）
$env:PATH += ";C:\Program Files\MySQL\MySQL Server 8.4\bin"
​
# 验证版本
mysql --version
​
# 登录测试
mysql -u root -p

# 初始化数据目录（生成临时 root 密码）
cd D:\mysql-8.4\bin
.\mysqld --initialize --console
​
# 注册 Windows 服务
.\mysqld --install MySQL84 --defaults-file="D:\mysql-8.4\my.ini"
​
# 启动服务
net start MySQL84
​
# 安全初始化
mysql_secure_installation

# 查看服务状态
Get-Service MySQL*
​
# 停止/启动
Stop-Service MySQL84
Start-Service MySQL84

[mysqld]
# === 基础设置 ===
port                    = 3306
bind-address            = 0.0.0.0
socket                  = /var/lib/mysql/mysql.sock
pid-file                = /var/run/mysqld/mysqld.pid
datadir                 = /var/lib/mysql
default-storage-engine  = InnoDB
character-set-server    = utf8mb4
collation-server        = utf8mb4_0900_ai_ci
skip-name-resolve       = ON
lower-case-table-names  = 0

# === 连接设置 ===
max_connections         = 500
max_connect_errors      = 1000
wait_timeout            = 600
interactive_timeout     = 1800
connect_timeout         = 10

# === InnoDB 引擎（核心调优）===
innodb_buffer_pool_size       = 4G
innodb_buffer_pool_instances  = 4
innodb_log_file_size          = 512M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method           = O_DIRECT
innodb_file_per_table         = ON
innodb_io_capacity            = 2000
innodb_io_capacity_max        = 4000
innodb_read_io_threads        = 4
innodb_write_io_threads       = 4

# === 日志设置 ===
log-error               = /var/log/mysqld.log
slow_query_log          = ON
slow_query_log_file     = /var/log/mysql-slow.log
long_query_time         = 2
log_queries_not_using_indexes = ON

# === 二进制日志（主从复制 / 数据恢复）===
server-id               = 1
log-bin                 = mysql-bin
binlog_format           = ROW
binlog_expire_logs_seconds = 604800
max_binlog_size         = 256M
sync_binlog             = 1

# === 临时表与排序 ===
tmp_table_size          = 64M
max_heap_table_size     = 64M
sort_buffer_size        = 4M
join_buffer_size        = 4M
read_buffer_size        = 2M
read_rnd_buffer_size    = 8M

# === 安全设置 ===
local-infile            = OFF
symbolic-links          = OFF
sql_mode                = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

[client]
default-character-set   = utf8mb4
socket                  = /var/lib/mysql/mysql.sock

[mysql]
default-character-set   = utf8mb4
prompt                  = (\u@\h) [\d]>

# 编辑配置文件
sudo vim /etc/my.cnf
​
# 检查配置语法（MySQL 8.4）
mysqld --validate-config
​
# 重启 MySQL 使配置生效
sudo systemctl restart mysqld
​
# 验证关键参数
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'character_set_server';"

# 停止 MySQL
sudo systemctl stop mysqld
​
# 以跳过权限模式启动
sudo mysqld --skip-grant-tables --user=mysql &
​
# 无密码登录
mysql -u root
​
# 重置密码
mysql> FLUSH PRIVILEGES;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewP@ssw0rd!';
mysql> EXIT;
​
# 正常重启
sudo kill $(pgrep mysqld)
sudo systemctl start mysqld

# 查看详细错误
sudo journalctl -xeu mysqld.service
​
# 查看 MySQL 错误日志
sudo tail -100 /var/log/mysqld.log
​
# 常见原因及解决
# 1. 数据目录权限不正确
sudo chown -R mysql:mysql /var/lib/mysql
sudo chmod 750 /var/lib/mysql
​
# 2. my.cnf 配置错误
mysqld --validate-config
​
# 3. 端口被占用
ss -tlnp | grep 3306

# 彻底卸载 MariaDB
sudo dnf remove -y mariadb* mysql-libs
​
# 清理缓存后重新安装
sudo dnf clean all
sudo dnf install -y mysql-community-server

-- 检查各级字符集
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
​
-- 修改已有数据库字符集
ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
​
-- 修改已有表字符集
ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW PROCESSLIST;
​
-- 临时增加（运行时生效）
SET GLOBAL max_connections = 1000;
​
-- 永久修改（写入 my.cnf）
-- max_connections = 1000
​
-- 排查：找出空闲连接并终止
SELECT * FROM information_schema.processlist WHERE Command = 'Sleep' AND Time > 600;

# 查看 MySQL 数据目录磁盘占用
du -sh /var/lib/mysql/*
​
# 清理过期 binlog
mysql -u root -p -e "PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;"
​
# 设置自动过期（my.cnf）
# binlog_expire_logs_seconds = 604800  (7天)
​
# 如需迁移数据目录
# 1. 停止 MySQL
# 2. 移动 /var/lib/mysql 到新路径
# 3. 修改 my.cnf 中 datadir
# 4. 更新 SELinux 上下文
# 5. 重启 MySQL

# 查看错误日志
cat /opt/homebrew/var/mysql/*.err
​
# 通常是数据目录版本不兼容，执行升级
mysql_upgrade -u root -p
​
# 或使用 mysqld 内置升级（8.0.16+）
mysqld --upgrade=AUTO
​
# 如果仍然失败，备份数据后重新初始化
brew services stop mysql
mv /opt/homebrew/var/mysql /opt/homebrew/var/mysql.bak
mysqld --initialize
brew services start mysql

# 停止服务
sudo systemctl stop mysqld
sudo systemctl disable mysqld
​
# 卸载所有 MySQL 包
sudo dnf remove -y mysql-community-*
​
# 删除数据、日志和配置
sudo rm -rf /var/lib/mysql
sudo rm -rf /var/log/mysqld.log
sudo rm -f /etc/my.cnf /etc/my.cnf.d/*
sudo rm -f /etc/yum.repos.d/mysql-community*
​
# 删除 MySQL 用户和组（可选）
sudo userdel -r mysql

brew services stop mysql
brew uninstall mysql
rm -rf /opt/homebrew/var/mysql
rm -f /opt/homebrew/etc/my.cnf

sudo rm -rf /usr/local/mysql*
sudo rm -f /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
sudo rm -rf /Library/PreferencePanes/MySQL.prefPane