CentOS 79 安装 MySQL 8 配置模板

1. 服务器主机 BIOS 关闭 NUMA

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

3. 修改系统核心参数

# 编辑 /etc/sysctl.conf 文件添加以下参数fs.aio-max-nr524288vm.swappiness0net.ipv6.conf.all.disable_ipv61net.core.rmem_max33554432net.core.wmem_max33554432net.ipv4.tcp_rmem  4096 87380 16777216net.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.localif test -f /sys/kernel/mm/transparent_hugepage/enabled; then echo never > /sys/kernel/mm/transparent_hugepage/enabledfiif test -f /sys/kernel/mm/transparent_hugepage/defrag; then echo never > /sys/kernel/mm/transparent_hugepage/defragfi# 临时echo never > /sys/kernel/mm/transparent_hugepage/enabledecho never > /sys/kernel/mm/transparent_hugepage/defrag

6. 关闭swap

swapoff -a

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

文件系统格式化为 xfs。

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

[mysqld]# skip-log-"># slave_skip_errors1032,1062bulk_insert_buffer_size1073741824innodb_adaptive_hash_index0">log_transaction_dependency_tracking   WRITESETtransaction_write_set_extraction         XXHASH64innodb_flush_log_at_trx_commit1# slave_parallel_type  LOGICAL_CLOCK# slave_parallel_workers  8# slave_preserve_commit_order  1log_slave_updates1# read_only# super_read_onlysql_modeSTRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIONmax_allowed_packet1Gexplicit_defaults_for_timestamp0log_timestampsSYSTEM">log_expire_logs_seconds259200lower_case_table_names1secure_file_privlog_">_trust_function_creatorsoncharacter-set-server  utf8mb4default_authentication_pluginmysql_native_passwordbasedir/home/mysql/mysql-8.0.22datadir/data/3306/mysqldatasocket/data/3306/mysqldata/mysql.sockwait_timeout30innodb_buffer_pool_size  32Gmax_connections  1000default-time-zone  8:00port  3306skip-name-resolve usermysqlinnodb_print_all_deadlocks1log_outputtableslow_query_log  1long_query_time  1tmp_table_size  32Mlog-">/data/3306/dblog/mysql-">log-">-index  /data/3306/dblog/mysql-">.index tmpdir  /data/3306/tmpserver-id  1113306innodb_data_file_path  ibdata1:1G:autoextendinnodb_data_home_dir  /data/3306/mysqldatainnodb_log_buffer_size  16Minnodb_log_file_size  1Ginnodb_log_files_in_group  3innodb_log_group_home_dir/data/3306/dbloginnodb_max_dirty_pages_pct  90innodb_lock_wait_timeout  120gtid-mode  onenforce_gtid_consistencytruelocal_infile0log_error/data/3306/mysqldata/master.errskip_symbolic_linksyes[mysqldump]quickmax_allowed_packet  1G[mysqld_safe]open-files-limit  8192

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