MySQL 5.7 / 8.x 安装与初始化

MySQL 5.7（CentOS 7 / yum）

清理已有环境

# 查看系统版本
cat /etc/centos-release

# 删除内置的 MariaDB 组件
rpm -qa | grep -i mariadb
rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64

# 如果之前装过 MySQL，先卸载并删除旧配置
rpm -qa | grep -i mysql
rm -rf /etc/my.cnf

安装官方仓库

rpm -ivh http://repo.mysql.com/mysql57-community-release-el7.rpm
ls /etc/yum.repos.d | grep mysql
# 安装后会在 /etc/yum.repos.d 生成 mysql-community.repo 和 mysql-community-source.repo

yum list | grep mysql-community

处理 GPG 密钥过期

不导入新密钥会报 NOKEY ... key ID 3a79bd29，无法安装（参见 MySQL Bug #106188）：

rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

典型错误信息：

warning: ... mysql-community-libs-compat-5.7.38-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
The GPG keys listed for the "MySQL 5.7 Community Server" repository are already installed but they are not correct for this package.

安装与卸载

yum -y install mysql-community-client mysql-community-common mysql-community-devel \
  mysql-community-libs mysql-community-libs-compat mysql-community-server mysql-community-test

yum -y remove mysql-community-client mysql-community-common mysql-community-devel \
  mysql-community-libs mysql-community-libs-compat mysql-community-server mysql-community-test

目录与权限

mkdir /data/mysql
chown mysql:mysql /data/mysql -R
chown mysql:mysql /data/logs/mysqld.log

my.cnf（数据目录放到 /data/mysql，端口 2668）

[mysqld]
port=2668
socket=/data/mysql/mysql.sock
datadir=/data/mysql
pid-file=/data/mysql/mysqld.pid
log-error=/data/logs/mysqld.log
lower_case_table_names=1
character_set_server=utf8mb4
collation_server=utf8mb4_general_ci
innodb_file_per_table=1
skip_name_resolve=1
slow_query_log=1
slow_query_log_file=mysql-slow.log
symbolic-links=0
explicit_defaults_for_timestamp=1
server_id=1
sync_binlog=1
innodb_flush_log_at_trx_commit=1
log_bin=mysql-bin
log_bin_index=mysql-bin.index
binlog_format=row
default_authentication_plugin=mysql_native_password

[client]
socket=/data/mysql/mysql.sock

默认路径版本（/var/lib/mysql，端口 3306）同理，把 datadir、socket、pid-file、log-error 换成系统默认路径即可。

启动与初始化

systemctl start mysqld
systemctl status mysqld
systemctl enable mysqld

ps aux | grep mysqld
ss -tunlp | grep mysqld
tail -100 /var/log/mysqld.log

# 查看 root 初始密码
grep "password" /var/log/mysqld.log

# 安全向导
mysql_secure_installation

修改密码与开放远程

mysql -uroot -p

-- 修改密码安全策略为低（只校验长度，至少 8 位）
set global validate_password_policy=0;
ALTER USER 'root'@'localhost' IDENTIFIED BY '****';

-- 授予远程管理权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '****';
FLUSH PRIVILEGES;

use mysql;
select user, host from user where user='root';
update user set host='%' where user='root';
flush privileges;

CREATE USER 'root'@'%' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;

忘记 root 密码

# /etc/my.cnf 中临时加入
[mysqld]
skip-grant-tables

service mysqld restart
mysql -u root -p        # 直接回车免密登录

flush privileges;
alter user 'root'@'localhost' identified by '你的新密码';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '你的新密码';

启动报 Could not open file

禁用 SELinux 后通常不再出现该错误：

sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config
setenforce 0

MySQL 8.3（CentOS 9 / RHEL 9，dnf）

sudo dnf install https://repo.mysql.com//mysql80-community-release-el9-1.noarch.rpm
dnf provides mysql-server
dnf install mysql-server
sudo systemctl enable --now mysqld
systemctl status mysqld

# 查看临时密码
sudo grep 'temporary password' /var/log/mysqld.log
mysql_secure_installation
mysql -u root -p

MySQL 8 完整配置模板

[mysqld]
server-id=1                    # 每个 MySQL 服务需唯一
port=3306
datadir=/var/lib/mysql
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/lib/mysql/mysql.sock
user=mysql
bind-address=0.0.0.0           # 允许访问的 IP 网段
# skip-grant-tables            # 跳过密码登录
# skip-external-locking        # MyISAM 相关，多服务器连同一库时建议注释
# skip_name_resolve=1          # 只用 IP 校验客户端，不用主机名
# transaction_isolation=READ-COMMITTED

character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
# init_connect='SET NAMES utf8mb4'
lower_case_table_names=1       # 1 表示 SQL 不区分大小写

max_connections=400
max_connect_errors=1000
explicit_defaults_for_timestamp=true
max_allowed_packet=128M        # 有 BLOB 对象建议放宽

interactive_timeout=1800       # wait_timeout 需同时配置才生效
wait_timeout=1800

tmp_table_size=134217728       # 内部内存临时表上限，128M
max_heap_table_size=134217728

# query_cache_size = 0         # 大多数情况关闭查询缓存
# query_cache_type = 0

log-error=/var/log/mysqld.log

slow_query_log=1
slow_query_log_file=/var/log/mysqld_slow.log
log_queries_not_using_indexes=1
log_throttle_queries_not_using_indexes=5   # 每分钟记录未用索引 SQL 的条数
log_slow_slave_statements=1
long_query_time=8              # 慢查询阈值（秒）
min_examined_row_limit=100     # 检索行数达到此值才记为慢查询

# expire_logs_days=5           # 8.0 已废弃
binlog_expire_logs_seconds=604800

初始化并启动：

mysqld --initialize
systemctl start mysqld
systemctl status mysqld