前言
在数字化时代,企业的数据安全和业务连续性至关重要。想象一下,当关键业务数据存储在数据库中,而数据库突然出现故障,或者面临硬件故障、网络中断、自然灾害等不可预知的灾难性事件时,企业如何确保数据的完整性和业务的正常运行?这就是为什么构建企业级高可用数据库集群变得至关重要。
MySQL-MGR(MySQL Group Replication)正是解决这一问题的强有力工具。它不仅能够提供数据的一致性和高可用性,还能在故障发生时自动进行故障转移,确保业务的连续性和数据的完整性。
本文将帮助您构建一个高效、可靠的数据库集群,应对各种挑战。
MGR的介绍
MySQL Group Replication(MGR)是MySQL官方在5.7.17版本引进的一个数据库高可用与高扩展的解决方案,以插件形式提供,实现了分布式下数据的最终一致性,总结MGR特点如下:
高一致性:基于分布式paxos协议实现组复制.集群是多个MySQL Server节点共同组成的分布式集群,每个Server都有完整的副本,它是基于ROW格式的二进制日志文件和GTID特性,保证数据一致性;
高容错性:自动故障检测机制,只要不是大多数节点都宕机就可以继续工作,内置防脑裂保护机制,在主节点故障时,MGR能够自动选举新的主节点继续处理事务,保持服务的连续性;
高扩展性:节点的增加与移除会自动更新组成员信息,新节点加入后,自动从其他节点同步增量数据,直到与其他节点数据一致;
高灵活性:提供单主模式和多主模式,单主模式在主库宕机后能够自动选主,所有写入都在主节点进行,多主模式支持多节点写入。工作中优先使用单主模式。
优点:
基本无延迟,延迟比异步的小很多
支持多写模式,但是目前还不是很成熟
数据的强一致性,可以保证数据事务不丢失
事务处理流程:
事务发起:客户端向任意MGR组内节点提交事务。
事务验证:节点通过内置的事务一致性检查机制(如冲突检测、全局唯一事务ID(GTID)校验等)确保事务符合组复制的要求。
事务传播:通过复制协议模块,节点将待提交的事务以消息的形式广播到组内其他节点。每个节点接收到消息后,将其暂存到本地队列中。
共识达成:借助Paxos协议(或其变体),组内节点对事务的提交顺序进行协商并达成一致。只有当大多数节点(法定数量)同意某个事务的提交顺序时,该事务才能被确认为可执行。
事务执行:各节点按照达成一致的顺序执行事务。即使在网络分区或节点故障的情况下,只要还有足够数量的节点存活且能够相互通信,就能继续进行共识决策和事务执行,保证数据一致性。
状态报告:节点定期或在事件触发时向组内其他节点报告自己的状态,包括事务执行进度、健康状况等,以便其他节点了解整个组的全局状态
实验测试环境:
单主模式,主节点可读写,备节点只可读
1.下载安装mysql9,主节点修改配置文件如下
[mysqld]
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
##MGR配置
server_id=1 #备节点依次为2,3
gtid_mode=ON
enforce_gtid_consistency=ON
plugin_load_add='group_replication.so'
group_replication_group_name="bc946766-0c46-11ef-a8e2-0242ac110002"
group_replication_start_on_boot=off
group_replication_local_address= "172.20.0.1:33061" #备节点按备节点的ip修改
group_replication_group_seeds= "172.20.0.1:33061,172.20.0.2:33061,172.20.0.3:33061"
group_replication_bootstrap_group=off
2.初始化mysql,启动mysql
创建一个repl用户,并赋予replication slave 权限
在每个节点上执行
SET SQL_LOG_BIN=0;
CREATE USER IF NOT EXISTS 'repl'@'%' IDENTIFIED BY '$PASSWORD';
GRANT REPLICATION SLAVE, CONNECTION_ADMIN, BACKUP_ADMIN ON *.* TO 'repl'@'%';
GRANT GROUP_REPLICATION_STREAM ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE REPLICATION SOURCE TO SOURCE_USER='repl',
SOURCE_PASSWORD='$PASSWORD' FOR CHANNEL 'group_replication_recovery';
3.在3个节点安装MGR模块组件
root@opseye:[(none)]> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.02 sec)
root@opseye:[(none)]> show plugins;
+----------------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------------+----------+--------------------+----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha2_cache_cleaner | ACTIVE | AUDIT | NULL | GPL |
| daemon_keyring_proxy_plugin | ACTIVE | DAEMON | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CACHED_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SESSION_TEMP_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| TempTable | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| ndbcluster | DISABLED | STORAGE ENGINE | NULL | GPL |
| ndbinfo | DISABLED | STORAGE ENGINE | NULL | GPL |
| ndb_transid_mysql_connection_map | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| mysqlx_cache_cleaner | ACTIVE | AUDIT | NULL | GPL |
| mysqlx | ACTIVE | DAEMON | NULL | GPL |
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+----------------------------------+----------+--------------------+----------------------+---------+
set persist group_replication_single_primary_mode=on; ##多主模式设置off,单主模式设置为on
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
SET GLOBAL group_replication_bootstrap_group=ON;
start group_replication;
SET GLOBAL group_replication_bootstrap_group=OFF;
4.备节点执行如下命令
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
set persist group_replication_single_primary_mode=on;
START GROUP_REPLICATION;
5.在主节点查看MGR加入情况
[root@opseye scripts]# mysql -uroot -p"PASSWORD" -e "
SELECT * FROM performance_schema.replication_group_members;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | e346e7e5-5a24-11ef-9764-0242ac14000b | ba660f83ad88 | 3306 | ONLINE | PRIMARY | 9.0.1 | XCom |
| group_replication_applier | e38f497b-5a24-11ef-977a-0242ac14000c | 26061266b3c6 | 3306 | ONLINE | SECONDARY | 9.0.1 | XCom |
| group_replication_applier | e3be9da0-5a24-11ef-96ff-0242ac14000d | f5c6138f30f3 | 3306 | ONLINE | SECONDARY | 9.0.1 | XCom |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
6.可自行测试主节点的数据同步和主机点损坏切换。
模拟主节点挂掉,查看集群情况。
[root@opseye scripts]# mysql -uroot -p"123456" -e "
SELECT * FROM performance_schema.replication_group_members;
"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | e38f497b-5a24-11ef-977a-0242ac14000c | 26061266b3c6 | 3306 | ONLINE | PRIMARY | 9.0.1 | XCom |
| group_replication_applier | e3be9da0-5a24-11ef-96ff-0242ac14000d | f5c6138f30f3 | 3306 | ONLINE | SECONDARY | 9.0.1 | XCom |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
结束语
在构建和维护数据库集群的过程中,无论是配置、监控、故障恢复,还是性能优化,都需要专业的知识和经验。为了帮助您在这一旅程中更加顺利,我们诚挚推荐专业的IT监控运维服务。
江苏立维成立于2015年,核心团队来自一线互联网企业,有着十多年丰富的运维管理经验,专注于企业业务故障的发现和管理,是国内早批专注于企业业务安全稳定运行服务保障的公司。
点击链接进入我们的官网,了解更多详情:https://www.live400.com/