CentOS 7.9 安装 MySQL 8 配置模板

1. 服务器主机 BIOS 关闭 NUMA

2. 系统版本：CentOS Linux release 7.9.2009 (Core)；MySQL 8.0.22

3. 修改系统核心参数

# 编辑 /etc/sysctl.conf 文件，添加以下参数：
fs.aio-max-nr=524288
vm.swappiness=0
net.ipv6.conf.all.disable_ipv6=1
net.core.rmem_max=33554432
net.core.wmem_max=33554432
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216
 
# 使配置生效
sysctl -p

4. 修改用户进程限制

# 编辑 /etc/security/limits.conf 文件，添加以下配置：
 
* soft sigpending 1031766
* hard sigpending 1031766
* soft memlock 4194304
* hard memlock 4194304
* soft stack 10240
* hard stack 10240

5. 关闭大页

# 重启生效
vim /etc/rc.d/rc.local
 
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
 echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
 echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
 
# 临时
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

6. 关闭swap

swapoff -a

7. 创建 MySQL 数据目录分区
按3比1分成两个分区，一个用作数据，另一个用作binlog，例如2T的硬盘：
/data1 1.5T，用作datadir
/data2 0.5T，用作binlog    

文件系统格式化为 xfs。

8. MySQL服务器配置模板
（1）主库

[mysqld]
# skip-log-bin
# slave_skip_errors=1032,1062
innodb_flush_method=O_DIRECT
bulk_insert_buffer_size=1073741824
innodb_adaptive_hash_index=0
binlog_transaction_dependency_tracking  = WRITESET
transaction_write_set_extraction        = XXHASH64
innodb_flush_log_at_trx_commit=1
 
# slave_parallel_type = LOGICAL_CLOCK
# slave_parallel_workers = 8
# slave_preserve_commit_order = 1
 
log_slave_updates=1
# read_only
# super_read_only
 
sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
max_allowed_packet=1G
explicit_defaults_for_timestamp=0
log_timestamps=SYSTEM
binlog_expire_logs_seconds=259200
lower_case_table_names=1
secure_file_priv=''
log_bin_trust_function_creators=on
character-set-server = utf8mb4
default_authentication_plugin=mysql_native_password
basedir=/home/mysql/mysql-8.0.22
datadir=/data/3306/mysqldata
socket=/data/3306/mysqldata/mysql.sock
 
wait_timeout=30
innodb_buffer_pool_size = 32G
max_connections = 1000
 
default-time-zone = '+8:00'
 
port = 3306
skip-name-resolve 
user=mysql
 
innodb_print_all_deadlocks=1
log_output='table'
slow_query_log = 1
long_query_time = 1
 
tmp_table_size = 32M
log-bin=/data/3306/dblog/mysql-bin
log-bin-index = /data/3306/dblog/mysql-bin.index 
 
tmpdir = /data/3306/tmp
 
server-id = 1113306
 
innodb_data_file_path = ibdata1:1G:autoextend
innodb_data_home_dir = /data/3306/mysqldata
 
innodb_log_buffer_size = 16M
innodb_log_file_size = 1G
innodb_log_files_in_group = 3
innodb_log_group_home_dir=/data/3306/dblog
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
 
gtid-mode = on
enforce_gtid_consistency=true
 
local_infile=0
log_error='/data/3306/mysqldata/master.err'
skip_symbolic_links=yes
 
[mysqldump]
quick
max_allowed_packet = 1G
 
[mysqld_safe]
open-files-limit = 8192

（2）从库
设置 read_only，其它参数与主库类似。