本文共 7841 字，大约阅读时间需要 26 分钟。

环境说明

1.1 版本说明

本文基于MySQL 8.0.20版本进行配置和部署，详细说明了环境搭建、数据库配置、集群部署等内容。

服务器规划

以下是服务器的详细信息：

服务器名称	IP 地址	端口	角色
node1	192.168.137.86	3306	Master, shell, router
node2	192.168.137.87	3306	Slave
node3	192.168.137.88	3306	Slave

配置目录规划

各软件的安装目录如下：

软件名称	安装目录
MySQL 软件	/usr/local/mysql/mysqlserver
MySQL 数据文件	/data/mysql/data
MySQL 日志文件	/data/mysql/logs
MySQL binlog日志	/data/mysql/binlog
MySQL 配置文件	/etc/my.cnf
MySQL Shell	/usr/local/mysql
MySQL Router	/usr/local/mysql/mysqlserver

基础配置

2.1 关闭selinux和防火墙

执行以下命令关闭selinux和防火墙：

systemctl stop firewalld
systemctl disable firewalld
setenforce 0
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

2.2 配置IP域名映射

编辑hosts文件：

cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain
192.168.137.86 node1
192.168.137.87 node2
192.168.137.88 node3

2.3 更改I/O调度模式

执行以下命令：

echo deadline > /sys/block/sda/queue/scheduler
cat /sys/block/sda/queue/scheduler

2.4 文件系统选择

推荐使用XFS文件系统，因其管理性能优于ext4。

2.5 操作系统调优

修改sysctl配置：

echo "vm.swappiness = 0 fs.aio-max-nr='1048576' fs.file-max='681574400' kernel.shmmax='137438953472' kernel.shmmni='4096' kernel.sem='250 32000 100 200'" > /etc/sysctl.conf
echo "kernel.shmmax='137438953472' kernel.shmmni='4096' kernel.sem='250' 32000 100 200 net.ipv4.ip_local_port_range='9000 65000' net.core.rmem_default='262144' net.core.rmem_max='4194304' net.core.wmem_default='262144' net.core.wmem_max='1048586'" >> /etc/sysctl.conf

修改limits.conf：

echo "mysql soft nproc 65536 mysql hard nofile 65536" > /etc/security/limits.conf

修改profile：

echo "mysql soft nproc 65536 mysql hard nofile 65536" > /etc/profile

2.6 关闭numa

编辑grub配置文件：

sed -i 's/GRUB_CMDLINE_LINUX.*/GRUB_CMDLINE_LINUX="crashkernel=auto rd.lvm.lv=centos/root rd.lvm.lv=centos/swap rhgb quiet numa=off"/g' /etc/default/grub
grub2-mkconfig -o /etc/grub2.cfg
reboot
cat /proc/cmdline
dmesg | grep -i numa

部署MySQL

3.1 创建MySQL用户组和用户

执行以下命令：

groupadd mysql
useradd -g mysql mysql -s /sbin/nologin

3.2 配置目录

创建必要目录并设置权限：

mkdir -pv /data/mysql/{data,logs,binlog,tmp,redo}
chown -R mysql:mysql /data/mysql/

3.3 解压安装包

安装MySQL软件：

mkdir /usr/local/mysql/program -p
tar xf mysql-8.0.20-el7-x86_64.tar -C /usr/local/mysql/program/

创建符号链接并配置环境变量：

ln -s /usr/local/mysql/mysql-8.0.20-el7-x86_64/ /usr/local/mysql/mysqlserver
ln -s /usr/local/mysql/mysql-router-8.0.20-el7-x86_64/ /usr/local/mysql/mysqlrouter
export PATH=/usr/local/mysql/mysqlserver/bin:/usr/local/mysql/mysqlrouter/bin:$PATH
echo 'export PATH=/usr/local/mysql/mysqlserver/bin:/usr/local/mysql/mysqlrouter/bin:$PATH' > /etc/profile
source /etc/profile

3.4 修改配置文件

编辑my.cnf：

[client]
port    = 3306
socket  = /data/mysql/data/mysql.sock
[mysql]
prompt="\u@mysql \R:\m:\s [\d]>\s "no-auto-rehash
[mysqld]
user=mysql
port=3306
dataadir=/data/mysql/data
tmpdir=/data/mysql/tmp
socket=/data/mysql/data/mysql.sock
innodb_doublewrite=1
character-set-server=utf8mb4
skip_name_resolve=1
default_time_zone='+8:00'
open_files_limit=65535
back_log=1024
max_connections=256
max_user_connections=64
max_connect_errors=10000
autocommit=1
table_open_cache=1024
table_definition_cache=1024
table_open_cache_instances=4
thread_stack=512K
external-locking=FALSE
max_allowed_packet=32M
sort_buffer_size=4M
join_buffer_size=4M
innodb_sort_buffer_size=64M
thread_cache_size=384
interactive_timeout=600
wait_timeout=600
tmp_table_size=32M
max_heap_table_size=32M
slow_query_log=1
log_timestamps=SYSTEM
slow_query_log_file=/data/mysql/logs/slow.log
log-error=/data/mysql/logs/error.log
long_query_time=0.1
log_queries_not_using_indexes=1
log_throttle_queries_not_using_indexes=60
min_examined_row_limit=0
log_slow_admin_statements=1
log_slow_slave_statements=1
server-id=3306
log-bin=/data/mysql/binlog
binlogsync_binlog=1
binlog_cache_size=4M
max_binlog_cache_size=2G
max_binlog_size=1G
auto_increment_offset=1
auto_increment_increment=1
expire_logs_days=7
master_info_repository=TABLE
relay_log_info_repository=TABLE
gtid_mode=on
enforce_gtid_consistency=1
log_slave_updates=1
slave-rows-search-algorithms='INDEX_SCAN,HASH_SCAN'
binlog_format=row
relay_log=/data/mysql/binlog/relay
relay_log_recovery=1
relay-log-purge=1
key_buffer_size=32M
read_buffer_size=8M
read_rnd_buffer_size=4M
bulk_insert_buffer_size=64M
myisam_sort_buffer_size=128M
myisam_max_sort_file_size=10G
myisam_repair_threads=1
lock_wait_timeout=3600
explicit_defaults_for_timestamp=1
innodb_thread_concurrency=0
innodb_sync_spin_loops=100
innodb_spin_wait_delay=30
transaction_isolation=READ-COMMITTED
innodb_buffer_pool_size=2048M
innodb_buffer_pool_instances=2
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_dump_at_shutdown=1
innodb_log_group_home_dir=/data/mysql/redo
innodb_data_file_path=ibdata1:1G:autoextend
innodb_temp_data_file_path=ibtmp1:500M:autoextend
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=2G
innodb_log_files_in_group=2
innodb_max_undo_log_size=4G
innodb_undo_directory=/data/mysql/data
innodb_undo_tablespaces=2
innodb_io_capacity=4000
innodb_io_capacity_max=8000
innodb_flush_sync=0
innodb_flush_neighbors=0
innodb_write_io_threads=8
innodb_read_io_threads=8
innodb_purge_threads=4
innodb_page_cleaners=4
innodb_open_files=65535
innodb_max_dirty_pages_pct=50
innodb_flush_method=O_DIRECT
innodb_lru_scan_depth=4000
innodb_checksum_algorithm=crc32
innodb_lock_wait_timeout=10
innodb_rollback_on_timeout=1
innodb_print_all_deadlocks=1
innodb_file_per_table=1
innodb_online_alter_log_max_size=4G
innodb_stats_on_metadata=0
innodb_undo_log_truncate=1
slave_preserve_commit_order=1
log_error_verbosity=3
innodb_print_ddl_logs=1
binlog_expire_logs_seconds=604800
innodb_status_file=1
innodb_status_output=0
innodb_status_output_locks=0
performance_schema=1
performance_schema_instrument='%memory%=on'
performance_schema_instrument='%lock%=on'
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"
 slave_parallel_type=LOGICAL_CLOCK
 slave_parallel_workers=2
 binlog_transaction_dependency_tracking=write_set_session
 binlog_checksum=NONEMaster_info_repository=TABLE
 relay_log_info_repository=TABLE
 transaction_write_set_extraction=XXHASH64loose-group-replication-group-name='e88cd6a7-8a12-4368-aeea-21d6b68b2982'
 loose-group-replication-start-on-boot=OFF
 loose-group-replication-local-address='192.168.137.86:33061'
 loose-group-replication-group-seeds='192.168.137.86:33061,192.168.137.87:33061,192.168.137.88:33061'
 loose-group-replication-bootstrap-group=OFF
 [mysqldump]
 quick-max-allowed-packet=32M

3.5 初始化

执行以下命令：

mysqld-debug --defaults-file=/etc/my.cnf --initialize-insecure

3.6 启动数据库

启动MySQL服务：

mysqld_safe --defaults-file=/etc/my.cnf

MySQL Router配置

5.1 配置

MySQL Router基于InnoDB Cluster自动配置，支持基于X Protocol的路由。

5.2 启动MySQL Router

执行以下命令：

mysqlrouter --user=mysql --bootstrap dba@192.168.137.86:3306

MySQL Shell管理

6.1 dba操作

使用MySQL Shell管理集群，支持创建、配置和监控InnoDB Cluster。

Innodb Cluster配置

4.1 创建MySQL Shell管理账号

执行以下命令：

create user 'dba'@'%' identified by 'dba';
grant all on *.* to 'dba'@'%';
flush privileges;

4.2 检查实例配置

使用dba.checkInstanceConfiguration()验证实例配置。

4.3 配置实例

使用dba.configureInstance()配置实例。

4.4 创建集群

创建集群并添加实例：

dba.createCluster('testCluster')
cluster.addInstance('dba@node2:3306')
cluster.addInstance('dba@node3:3306')

4.5 查看集群状态

查看集群状态：

cluster.status()

总结

以上步骤详细说明了在 CentOS 7.7环境下部署MySQL 8.0.20集群的完整过程，包括环境配置、数据库安装、集群部署、MySQL Router配置等内容，确保了数据库的高可用性和可扩展性。

转载地址：http://ckrwz.baihongyu.com/