CentOS 7.9 安装 MySQL 8 配置模板

2023-10-23 09:32:27 浏览数 (1)

1. 服务器主机 BIOS 关闭 NUMA

2. 系统版本：CentOS Linux release 7.9.2009 (Core)；MySQL 8.0.223. 修改系统核心参数

代码语言：javascript复制
# 编辑 /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. 修改用户进程限制

代码语言：javascript复制
# 编辑 /etc/security/limits.conf 文件，添加以下配置：

* soft sigpending 1031766
* hard sigpending 1031766
* soft memlock 4194304
* hard memlock 4194304
* soft stack 10240
* hard stack 10240

5. 关闭大页

代码语言：javascript复制
# 重启生效
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

代码语言：javascript复制
swapoff -a

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

文件系统格式化为 xfs。

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

代码语言：javascript复制
[mysqld]
# skip-log-bin
# slave_skip_errors=1032,1062
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，其它参数与主库类似。

centos innodb mysql 配置 数据

0 人点赞