MySQL 9.7.0 二进制部署

基础环境配置

资源限制

cat >> /etc/security/limits.d/mysql.conf << 'EOF'
mysql  soft  nofile  65535
mysql  hard  nofile  65535
mysql  soft  nproc   65535
mysql  hard  nproc   65535
mysql  soft  core    unlimited
mysql  hard  core    unlimited
EOF

内核参数优化（面向 8C16G 全机）

cat > /etc/sysctl.d/99-mysql.conf << 'EOF'
# ===== 网络 =====
net.core.somaxconn             = 65535
net.core.netdev_max_backlog    = 65535
net.ipv4.tcp_max_syn_backlog   = 65535
net.ipv4.tcp_tw_reuse          = 1
net.ipv4.tcp_fin_timeout       = 15
net.ipv4.tcp_keepalive_time    = 600
net.ipv4.tcp_keepalive_intvl   = 30
net.ipv4.tcp_keepalive_probes  = 3
net.ipv4.tcp_max_tw_buckets    = 32768
net.ipv4.tcp_syncookies        = 1
net.ipv4.ip_local_port_range   = 1024 65535
net.ipv4.tcp_slow_start_after_idle = 0

# ===== 内存（16GB 主机） =====
vm.swappiness                  = 1
vm.dirty_ratio                 = 10
vm.dirty_background_ratio      = 5
vm.dirty_expire_centisecs      = 3000
vm.dirty_writeback_centisecs   = 500
vm.overcommit_memory           = 0
vm.min_free_kbytes             = 524288

# ===== 文件 =====
fs.file-max                    = 1048576
fs.aio-max-nr                  = 1048576

# ===== 内核 =====
kernel.core_uses_pid           = 1
kernel.sysrq                   = 0
EOF

sysctl --system

说明： vm.min_free_kbytes=512MB 确保 16GB 主机在多数据库实例并发时不会 OOM；vm.swappiness=1 尽量不用 swap 但留最后防线；dirty_ratio=10 防止大块脏页刷盘造成抖动。

透明大页关闭

echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

# 持久化
cat > /etc/systemd/system/disable-thp.service << 'EOF'
[Unit]
Description=Disable Transparent Huge Pages
DefaultDependencies=no
After=sysinit.target local-fs.target
Before=basic.target

[Service]
Type=oneshot
ExecStart=/bin/sh -c 'echo never > /sys/kernel/mm/transparent_hugepage/enabled && echo never > /sys/kernel/mm/transparent_hugepage/defrag'

[Install]
WantedBy=basic.target
EOF

systemctl enable disable-thp

第一步：上传解压

cd /usr/local
tar xf /root/mysql-9.7.0-linux-glibc2.28-x86_64.tar.xz
ln -sfn /usr/local/mysql-9.7.0-linux-glibc2.28-x86_64 /usr/local/mysql

第二步：创建用户和目录

groupadd -g 3310 mysql
useradd -r -u 3310 -g mysql -s /sbin/nologin -M mysql
mkdir -p /usr/local/mysql/{data,logs,tmp,run}
chown -R mysql:mysql /usr/local/mysql/data /usr/local/mysql/logs /usr/local/mysql/tmp /usr/local/mysql/run
chmod 750 /usr/local/mysql/data

第三步：安装依赖

dnf install -y libaio numactl-libs libtirpc

第四步：配置环境变量

cat > /etc/profile.d/mysql.sh << 'EOF'
export PATH=/usr/local/mysql/bin:$PATH
EOF

source /etc/profile.d/mysql.sh

第五步：写入 my.cnf

cat > /etc/my.cnf << 'EOF'
[mysqld]
user                           = mysql
basedir                        = /usr/local/mysql
datadir                        = /usr/local/mysql/data
tmpdir                         = /usr/local/mysql/tmp
socket                         = /usr/local/mysql/run/mysql.sock
pid_file                       = /usr/local/mysql/run/mysql.pid
port                           = 3306

# ----- 字符集 -----
character_set_server           = utf8mb4
collation_server               = utf8mb4_0900_ai_ci
init_connect                   = 'SET NAMES utf8mb4'

# ----- 网络 -----
back_log                       = 2048
max_connections                = 500
max_connect_errors             = 100000
wait_timeout                   = 600
interactive_timeout            = 600
skip_name_resolve              = ON

# ----- 缓冲池 -----
innodb_buffer_pool_size        = 1G
innodb_buffer_pool_instances   = 4
innodb_log_buffer_size         = 64M

# ----- Redo Log -----
innodb_redo_log_capacity       = 1024M

# ----- 线程并发 -----
innodb_thread_concurrency      = 0
innodb_read_io_threads         = 4
innodb_write_io_threads        = 4
innodb_io_capacity             = 600
innodb_io_capacity_max         = 1200

# ----- 数据刷新策略 -----
innodb_flush_method            = O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite             = ON

# ----- 线程缓存 -----
thread_cache_size              = 128
sort_buffer_size               = 2M
join_buffer_size               = 2M
read_rnd_buffer_size           = 4M
read_buffer_size               = 2M

# ----- 临时表 -----
innodb_temp_data_file_path     = ibtmp1:64M:autoextend:max:2G
tmp_table_size                 = 64M
max_heap_table_size            = 64M

# ----- Binlog -----
log_bin                        = mysql-bin
binlog_format                  = ROW
binlog_row_image               = MINIMAL
binlog_cache_size              = 1M
max_binlog_size                = 512M
binlog_expire_logs_seconds     = 604800
sync_binlog                    = 1

# ----- GTID -----
gtid_mode                      = ON
enforce_gtid_consistency       = ON
log_replica_updates            = ON

# ----- 慢查询 -----
slow_query_log                 = ON
slow_query_log_file            = /usr/local/mysql/logs/slow.log
long_query_time                = 0.5
log_queries_not_using_indexes  = ON
min_examined_row_limit         = 50

# ----- 错误日志 -----
log_error                      = /usr/local/mysql/logs/error.log
log_error_verbosity            = 2

# ----- 安全 -----
local_infile                   = OFF

# ----- 其他 -----
open_files_limit               = 65535
table_open_cache               = 4000
table_definition_cache         = 2000
explicit_defaults_for_timestamp = ON
lower_case_table_names         = 0

# ----- 性能加速 -----
innodb_adaptive_hash_index     = ON
innodb_read_ahead_threshold    = 0
innodb_change_buffering        = all
innodb_change_buffer_max_size  = 50
innodb_deadlock_detect         = ON
innodb_print_all_deadlocks     = ON

[client]
socket                         = /usr/local/mysql/run/mysql.sock
default_character_set          = utf8mb4

[mysql]
prompt                         = '\u@\h [\d]> '
EOF

第六步：初始化数据库

/usr/local/mysql/bin/mysqld --initialize-insecure \
  --user=mysql \
  --basedir=/usr/local/mysql \
  --datadir=/usr/local/mysql/data

--initialize-insecure 生成空密码 root，启动后再设密码。

注意： 如果这一步报错，检查：

• /data/mysql/data/ 是否为空（必须为空目录）

• 权限是否为 mysql:mysql

第七步：配置 Systemd 服务

cat > /etc/systemd/system/mysqld.service << 'EOF'
[Unit]
Description=MySQL 9.7.0 Server
After=network.target

[Service]
Type=simple
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
TimeoutSec=300
LimitNOFILE=65535
LimitNPROC=65535
LimitCORE=infinity
Restart=on-failure
RestartSec=10
PrivateTmp=true

[Install]
WantedBy=multi-user.target
EOF

systemctl daemon-reload
systemctl enable mysqld
systemctl start mysqld

第八步：设置 root 密码

/usr/local/mysql/bin/mysql -u root --skip-password -e "
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'root'@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

第九步：验证核心参数

mysql -u root -p -e "
  SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
  SHOW VARIABLES LIKE 'innodb_redo_log_capacity';
  SHOW VARIABLES LIKE 'character_set_server';
  SHOW VARIABLES LIKE 'innodb_flush_method';
  SHOW VARIABLES LIKE 'gtid_mode';
"

期望输出：