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

环境说明

1.1 版本说明

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

服务器规划

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

配置目录规划

各软件的安装目录如下：

基础配置

2.1 关闭selinux和防火墙

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

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

2.2 配置IP域名映射

编辑hosts文件：

cat /etc/hosts127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain192.168.137.86 node1192.168.137.87 node2192.168.137.88 node3

2.3 更改I/O调度模式

执行以下命令：

echo deadline > /sys/block/sda/queue/schedulercat /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.confecho "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/grubgrub2-mkconfig -o /etc/grub2.cfgrebootcat /proc/cmdlinedmesg | grep -i numa

部署MySQL

3.1 创建MySQL用户组和用户

执行以下命令：

groupadd mysqluseradd -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 -ptar 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/mysqlserverln -s /usr/local/mysql/mysql-router-8.0.20-el7-x86_64/ /usr/local/mysql/mysqlrouterexport PATH=/usr/local/mysql/mysqlserver/bin:/usr/local/mysql/mysqlrouter/bin:$PATHecho 'export PATH=/usr/local/mysql/mysqlserver/bin:/usr/local/mysql/mysqlrouter/bin:$PATH' > /etc/profilesource /etc/profile

3.4 修改配置文件

编辑my.cnf：

[client]port    = 3306socket  = /data/mysql/data/mysql.sock[mysql]prompt="\u@mysql \R:\m:\s [\d]>\s "no-auto-rehash[mysqld]user=mysqlport=3306dataadir=/data/mysql/datatmpdir=/data/mysql/tmpsocket=/data/mysql/data/mysql.sockinnodb_doublewrite=1character-set-server=utf8mb4skip_name_resolve=1default_time_zone='+8:00'open_files_limit=65535back_log=1024max_connections=256max_user_connections=64max_connect_errors=10000autocommit=1table_open_cache=1024table_definition_cache=1024table_open_cache_instances=4thread_stack=512Kexternal-locking=FALSEmax_allowed_packet=32Msort_buffer_size=4Mjoin_buffer_size=4Minnodb_sort_buffer_size=64Mthread_cache_size=384interactive_timeout=600wait_timeout=600tmp_table_size=32Mmax_heap_table_size=32Mslow_query_log=1log_timestamps=SYSTEMslow_query_log_file=/data/mysql/logs/slow.loglog-error=/data/mysql/logs/error.loglong_query_time=0.1log_queries_not_using_indexes=1log_throttle_queries_not_using_indexes=60min_examined_row_limit=0log_slow_admin_statements=1log_slow_slave_statements=1server-id=3306log-bin=/data/mysql/binlogbinlogsync_binlog=1binlog_cache_size=4Mmax_binlog_cache_size=2Gmax_binlog_size=1Gauto_increment_offset=1auto_increment_increment=1expire_logs_days=7master_info_repository=TABLErelay_log_info_repository=TABLEgtid_mode=onenforce_gtid_consistency=1log_slave_updates=1slave-rows-search-algorithms='INDEX_SCAN,HASH_SCAN'binlog_format=rowrelay_log=/data/mysql/binlog/relayrelay_log_recovery=1relay-log-purge=1key_buffer_size=32Mread_buffer_size=8Mread_rnd_buffer_size=4Mbulk_insert_buffer_size=64Mmyisam_sort_buffer_size=128Mmyisam_max_sort_file_size=10Gmyisam_repair_threads=1lock_wait_timeout=3600explicit_defaults_for_timestamp=1innodb_thread_concurrency=0innodb_sync_spin_loops=100innodb_spin_wait_delay=30transaction_isolation=READ-COMMITTEDinnodb_buffer_pool_size=2048Minnodb_buffer_pool_instances=2innodb_buffer_pool_load_at_startup=1innodb_buffer_pool_dump_at_shutdown=1innodb_log_group_home_dir=/data/mysql/redoinnodb_data_file_path=ibdata1:1G:autoextendinnodb_temp_data_file_path=ibtmp1:500M:autoextendinnodb_flush_log_at_trx_commit=1innodb_log_buffer_size=32Minnodb_log_file_size=2Ginnodb_log_files_in_group=2innodb_max_undo_log_size=4Ginnodb_undo_directory=/data/mysql/datainnodb_undo_tablespaces=2innodb_io_capacity=4000innodb_io_capacity_max=8000innodb_flush_sync=0innodb_flush_neighbors=0innodb_write_io_threads=8innodb_read_io_threads=8innodb_purge_threads=4innodb_page_cleaners=4innodb_open_files=65535innodb_max_dirty_pages_pct=50innodb_flush_method=O_DIRECTinnodb_lru_scan_depth=4000innodb_checksum_algorithm=crc32innodb_lock_wait_timeout=10innodb_rollback_on_timeout=1innodb_print_all_deadlocks=1innodb_file_per_table=1innodb_online_alter_log_max_size=4Ginnodb_stats_on_metadata=0innodb_undo_log_truncate=1slave_preserve_commit_order=1log_error_verbosity=3innodb_print_ddl_logs=1binlog_expire_logs_seconds=604800innodb_status_file=1innodb_status_output=0innodb_status_output_locks=0performance_schema=1performance_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/