网站导航
> 文档中心 > MySQL MGR安装部署

MySQL MGR安装部署

网友: 文档中心 2022-05-22 22:26:03

部署环境:

模式 单主模式
主节点(8.0.22) 192.168.1.240
备节点(8.0.22) 192.168.1.241
备节点(8.0.22) 192.168.1.242

MGR配置以及限制:

  • 数据库版本MySQL 5.7.17及以上。

  • InnoDB存储引擎:数据必须存储在 InnoDB事务存储引擎中。

  • disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

  • 主键:MGR里面每个表都必须定义主键或者非空唯一键。

  • 网络:8.0.14之前只能使用IPV4，在8.0.14之后支持IPV6。

  • 网络性能:MGR性能和稳定性受到网络延迟和网络带宽的影响,需要千兆带宽以上

  • 唯一服务器标识符server_id:MGR每个节点的server_id都必须唯一，不能出现相同的server_id

  • 开启binlog:MGR通过binlog进行数据同步复制。

  • 开启log-slave-updates:组成员需要记录加入时从其捐助者处收到并通过复制应用程序应用的事务，并记录他们从组中接收并应用的所有事务。这使组复制能够通过从现有组成员的二进制日志进行状态转移来执行分布式恢复。

  • 二进制日志行格式:设置--binlog-format=row。MGR依靠row格式在组中的服务器之间一致地传播更改。它依靠基于行的基础结构来提取必要的信息，以检测在组中的不同服务器中同时执行的事务之间的冲突。从MySQL 8.0.19起，该 REQUIRE_ROW_FORMAT设置会自动添加到组复制的通道中，以在应用事务时强制使用基于行的复制。

  • 关闭二进制日志校验binlog-checksum=NONE（适用于MySQL 8.0.20以及低版本）:MGR无法使用校验，不支持二进制日志中的校验。从MySQL 8.0.21开始，组复制支持校验和，因此组成员可以使用默认设置。

  • 开启gtid:设置gtid_mode=ON和 enforce_gtid_consistency=ON。组复制使用全局事务标识符来精确跟踪已在每个节点提交了哪些事务，从而能够推断出哪些服务器已执行了可能与其他位置已提交的事务发生冲突的事务。

  • 复制元数据存储TABLE格式：设置master_info_repository=TABLE 和relay_log_info_repository=TABLE，复制应用程序需要将复制元数据写入 mysql.slave_master_info和mysql.slave_relay_log_info系统表，以确保组复制插件具有一致的可恢复性和对复制元数据的事务管理。从MySQL 8.0.23开始，不需要调整这些变量，只允许使用默认值table。

  • 事务writeset算法transaction-write-set-extraction=XXHASH64：需要使用算法对事务每行的主键进行计算，并记录到writeset，用于检测冲突以及并行复制。

  • binlog_transaction_dependency_tracking=WRITESET_SESSION：可以提高组成员的性能，具体取决于组的工作量。

  • 默认表加密default-table-encryption:在所有组成员上设置 为相同的值。只要所有成员的设置都相同，可以启用（ON）或禁用（OFF默认值）默认模式和表空间加密。

  • 关闭表名大小写lower-case-table-names:在所有组成员上设置为相同的值1

  • 多线程组复制应用:可以将组复制成员配置为多线程副本，从而使事务可以并行应用。通过设置slave_parallel_type=LOGICAL_CLOCK以及slave_parallel_workers为非0 在成员上启用多线程应用程序，最多可以指定1024个并行应用程序线程。

  • 设置slave_preserve_commit_order=1可确保并行事务的最终提交与原始事务的顺序相同，这是组复制所需的，组复制依赖于所有参与成员以相同顺序接收和应用已提交事务来保证一致性。

  • MGR在并发事务认证中不考虑gap lock，建议隔离级别使用read-commited。

  • MGR认证过程不考虑table lock(unlock/lock table)以及named lock（get_lock）。

  • MGR多主模式不支持SERIALIZABLE Isolation Level。

  • MGR多主模式不支持跨节点的并行DDL,DML。

  • MGR多主模式不支持"Foreign Keys with Cascading Constraints",建议多主模式开启一致性检测group_replication_enforce_update_everywhere_checks=ON。

  • 不支持MySQL Enterprise Audit and MySQL Enterprise Firewall ，由于使用了MyISAM表。

  • MGR多主可能产生死锁，对于select .. for update由于锁是不共享给其他组成员的，可能导致死锁问题的发生。

  • MGR组复制不支持replication filter。

  • MGR组复制成员最大9个。

  • MGR事务大小限制:如果单个事务导致消息内容大到无法在 5 秒窗口内通过网络在组成员之间复制消息，则成员可能会被怀疑失败，然后被驱逐，因为他们正忙于处理 交易。由于内存分配问题，大型事务也可能导致系统变慢。为避免这些问题，请使用以下缓解措施：

    1 对应用的事务进行控制，拆分大事务，避免大事务的执行。

    2 使用变量 group_replication_transaction_size_limit 指定组接受的最大事务大小。在 MySQL 5.7.37 及之前的版本中，此系统变量默认为零，但从 MySQL 5.7.38 开始以及 MySQL 8.0 ，它默认最大事务大小 150000000 字节（约 143 MB）。超过此限制的事务将回滚，并且不会发送到组复制的组通信系统 (GCS) 以分发给组。根据您需要组容忍的最大消息大小调整此变量的值，处理事务所花费的时间与其大小成正比。

    需要注意的是:

    当从 MySQL 5.7.37 或更早版本升级到 MySQL 5.7.38 或更高版本时，如果您的 Group Replication 服务器之前执行的事务大于新的默认限制或是允许 group_replication_transaction_size_limit 默认为0不限制，则这些事务在升级到新的默认值后将执行失败。您必须指定适当的大小限制，以允许您需要组容忍的最大消息大小（这是推荐的解决方案）或者指定零设置以恢复以前的行为。

    3 使用系统变量 group_replication_compression_threshold 指定应用压缩的消息大小。此系统变量默认为 1000000 字节 (1 MB)，因此自动压缩大于1MB的大消息。压缩由 组复制的 Group Communication System (GCS) 在收到 group_replication_transaction_size_limit 的设置允许但超过 group_replication_compression_threshold 设置的消息时执行。如果将系统变量值设置为零，则禁用压缩，使用LZ4 算法，可以提高30%-40%的带宽性能。

    4 如果停用消息压缩并且未指定最大事务大小，则复制组成员上的apply thread可以处理的消息大小上限是该成员的 slave_max_allowed_packet 系统变量的值，该变量默认值和最大值均为 1073741824 字节 (1 GB)。超过此限制的消息在接收成员尝试处理它时会失败。组成员可以发起并尝试传输到组的消息的大小上限为 4294967295 字节（大约 4 GB）。这是组复制（XCom，Paxos ）的组通信引擎接受的数据包大小的硬限制，它在 GCS 处理完消息后接收消息。当发起成员尝试广播时，超出此限制的消息会失败。

安装部署:

    初始化240，241，242节点，使用以下配置my.cnf，注意每个节点的server_id要不一样,report_host要换成对于节点的IP

[mysqld]user = mysqlport = 3306server_id = 240basedir = /usr/local/mysqldatadir = /opt/mysql/datalog-error=/opt/mysql/log/error.logsocket = /opt/mysql/data/mysql.sockpid-file = /opt/mysql/data/mysqld.pidcharacter_set_server = utf8mb4log_timestamps = SYSTEMlower_case_table_names = 1max_allowed_packet = 32Mskip_name_resolve = 1skip_slave_start = 1max_connections = 2000max_connect_errors=1000000interactive_timeout =3600wait_timeout =3600explicit_defaults_for_timestamp = 1transaction_isolation =READ-COMMITTEDdisabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"# BINLOG #log_bin =/opt/mysql/log/binloglog_bin_index=/opt/mysql/log/binlog.indexbinlog_format = rowbinlog_transaction_dependency_tracking=WRITESET_SESSION binlog_expire_logs_seconds=86400log_slave_updates = 1binlog_rows_query_log_events=1master_info_repository = TABLEmax_binlog_size = 1024Mrelay_log = mysql-relay-binrelay_log_info_repository = TABLErelay_log_recovery = 1binlog_gtid_simple_recovery=1sync_binlog = 1table_open_cache = 4096open_files_limit = 65535thread_cache_size = 768transaction_write_set_extraction=XXHASH64# GTID #gtid_mode = ONenforce_gtid_consistency = 1# INNODB #default_storage_engine = InnoDBinnodb_buffer_pool_size = 1G innodb_buffer_pool_instances = 1innodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown = 1innodb_buffer_pool_dump_pct=40innodb_lru_scan_depth=2048innodb_lock_wait_timeout=5innodb_io_capacity=1000innodb_io_capacity_max=2000innodb_data_file_path = ibdata1:1G:autoextendinnodb_file_per_table = 1innodb_flush_log_at_trx_commit=1innodb_flush_method = O_DIRECTinnodb_log_file_size=2048Minnodb_log_files_in_group=2innodb_log_buffer_size=16Minnodb_max_dirty_pages_pct = 75innodb_thread_concurrency = 0innodb_print_all_deadlocks = 1innodb_stats_on_metadata = 0innodb_read_io_threads = 8innodb_write_io_threads = 8innodb_purge_threads = 4innodb_sort_buffer_size = 16Minnodb_strict_mode = 1innodb_page_cleaners = 8innodb_undo_directory=/opt/mysql/datainnodb_undo_log_truncate=1innodb_rollback_segments=128innodb_max_undo_log_size=2GBinnodb_purge_rseg_truncate_frequency=128innodb_open_files=65535innodb_autoinc_lock_mode=2# CACHE #key_buffer_size = 16Mtmp_table_size = 32Mmax_heap_table_size = 32Mjoin_buffer_size = 16Mread_buffer_size = 16Mread_rnd_buffer_size = 16Msort_buffer_size = 16Mbinlog_cache_size = 4Mmax_binlog_cache_size=2g# SLOW LOG #slow_query_log = 1slow_query_log_file =/opt/mysql/log/slow-query.loglog_slow_admin_statements = 1log_slow_slave_statements = 1log_queries_not_using_indexes=1long_query_time = 1#  slave #slave_parallel_type=LOGICAL_CLOCK slave_parallel_workers=4slave_preserve_commit_order=on#  host #report_host=192.168.2.240report_port=3306[mysql]socket = /opt/mysql/data/mysql.sockprompt="\u@\p \R:\m:\s [\d]>"[client]socket = /opt/mysql/data/mysql.sock[mysqldump]max_allowed_packet = 128M

执行初始化

#每个节点执行/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/opt/mysql/data

启动数据库

#每个节点执行mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --datadir=/opt/mysql/data &

创建同步用户

#每个节点执行SET SQL_LOG_BIN=0;CREATE USER rpl_user@'%' IDENTIFIED  BY 'rpl_user';GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%';GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';FLUSH PRIVILEGES;SET SQL_LOG_BIN=1;#8.0的认证方式caching_sha2_password可能导致连接不上的问题，可以修改成mysql_native_passwordALTER USER 'rpl_user'@'%' IDENTIFIED WITH mysql_native_password BY 'rpl_user';

权限说明

REPLICATION SLAVE, which is required for making a distributed recovery connection to a donor to retrieve data.CONNECTION_ADMIN, which ensures that Group Replication connections are not terminated if one of the servers involved is placed in offline mode.BACKUP_ADMIN, if the servers in the replication group are set up to support cloning (see Section 18.5.4.2, “Cloning for Distributed Recovery”). This privilege is required for a member to act as the donor in a cloning operation for distributed recovery.GROUP_REPLICATION_STREAM, if the MySQL communication stack is in use for the replication group (see Section 18.6.1, “Communication Stack for Connection Security Management”). This privilege is required for the user account to be able to establish and maintain connections for Group Replication using the MySQL communication stack.

安装MGR插件

INSTALL PLUGIN group_replication SONAME 'group_replication.so';

确认插件激活

show plugins| group_replication | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |+---------------------------------+----------+--------------------+----------------------+---------+

配置MGR参数

#MGR默认为单主模式group_replication_single_primary_mode=on---配置group replication 组的名字，组名必须是一个uuid#可以用select uuid()随机生成一个set global group_replication_group_name='aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';---设置成员白名单（即哪些成员可以加进来）set global group_replication_ip_allowlist='192.168.0.0/16'---设置成员的本地地址#MGR每个成员需要设置一个单独的端口(33061)进行通信#主节点一set global group_replication_local_address='192.168.2.240:33061'#备节点二set global group_replication_local_address='192.168.2.241:33061'#备节点三set global group_replication_local_address='192.168.2.242:33061'---设置组内成员的地址set global group_replication_group_seeds='192.168.2.240:33061,192.168.2.241:33061,192.168.2.242:33061'---指示在启动时MGR不自动启动操作。这在设置组复制时很重要，因为它确保您可以在手动启动插件之前配置服务器。#默认为on#配置成员后，可以设置 group_replication_start_on_boot 为on，在服务器引导时自动启动Group Replicationset global group_replication_start_on_boot =off---初始化参数#只有第一个成员初始化组需要设置为on，即在主节点一执行就行#开启group replication之后要把参数group_replication_bootstrap关了set global group_replication_bootstrap_group=on

配置同步管道

#每个节点都执行CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_user' FOR CHANNEL 'group_replication_recovery';

开启MGR

#在每个节点执行#默认不进行用户验证START GROUP_REPLICATION;#在8.0.21之后设置用户验证#START GROUP_REPLICATION USER='rpl_user', PASSWORD='password';

主节点一关闭bootstrap

set global group_replication_bootstrap_group=off

查看成员状态

SELECT * FROM performance_schema.replication_group_members;+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+| CHANNEL_NAME| MEMBER_ID| MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+| group_replication_applier | b87decaf-d9b2-11ec-91e6-000c29ed797a | 192.168.2.240 | 3306 | ONLINE| PRIMARY     | 8.0.22  || group_replication_applier | e37983d6-d9b2-11ec-8a0a-000c29a6e7be | 192.168.2.241 | 3306 | ONLINE| SECONDARY   | 8.0.22  || group_replication_applier | e3d12004-d9b2-11ec-a818-000c29d9d34c | 192.168.2.242 | 3306 | ONLINE| SECONDARY   | 8.0.22  |+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+

最后把MGR的参数添加到参数文件/etc/my.cnf

# mgr #plugin_load_add='group_replication.so'group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"group_replication_ip_allowlist='192.168.0.0/16'group_replication_local_address='192.168.2.240:33061'group_replication_group_seeds='192.168.2.240:33061,192.168.2.241:33061,192.168.2.242:33061'group_replication_start_on_boot =offgroup_replication_bootstrap_group=off

测试同步:

查看连接状态

select * from performance_schema.replication_connection_status;

创建测试表

create database test;use test;create table test(id bigint primary key);#建表记得添加主键，不然表无法DML操作，会出现错误ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin.insert into test values(11);

在备节点可以查到数据

root@mysql.sock 19:12:  [(none)]>show global variables like '%report_host%';+---------------+---------------+| Variable_name | Value  |+---------------+---------------+| report_host   | 192.168.2.242 |+---------------+---------------+1 row in set (0.01 sec)root@mysql.sock 19:12:  [(none)]>select * from test.test;+----+| id |+----+| 11 |+----+1 row in set (0.00 sec)

测试主从切换:

kill掉主节点240

[root@ol7db1 log]# ps -ef |grep -i mysqldroot      23602  16198  0 18:45 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --datadir=/opt/mysql/datamysql     24839  23602  0 18:45 pts/0    00:00:16 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/opt/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/opt/mysql/log/error.log --open-files-limit=65535 --pid-file=/opt/mysql/data/mysqld.pid --socket=/opt/mysql/data/mysql.sock --port=3306root      26682  19921  0 19:13 pts/2    00:00:00 grep --color=auto -i mysqld[root@ol7db1 log]# kill -9 23602[root@ol7db1 log]# kill -9 24839

241节点被选为主节点

2022-05-22T19:00:28.979798+08:00 0 [System] [MY-011490] [Repl] Plugin group_replication reported: 'This server was declared online within the replication group.'2022-05-22T19:14:18.086308+08:00 0 [Warning] [MY-011493] [Repl] Plugin group_replication reported: 'Member with address 192.168.2.240:3306 has become unreachable.'2022-05-22T19:14:34.939084+08:00 0 [Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: 192.168.2.240:3306'2022-05-22T19:14:34.939127+08:00 0 [System] [MY-011500] [Repl] Plugin group_replication reported: 'Primary server with address 192.168.2.240:3306 left the group. Electing new Primary.'2022-05-22T19:14:35.940510+08:00 0 [System] [MY-011507] [Repl] Plugin group_replication reported: 'A new primary with address 192.168.2.241:3306 was elected. The new primary will execute all previous group transactions before allowing writes.'2022-05-22T19:14:35.941538+08:00 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to 192.168.2.241:3306, 192.168.2.242:3306 on view 16532164872631617:8.'2022-05-22T19:14:35.943714+08:00 99 [System] [MY-011566] [Repl] Plugin group_replication reported: 'Setting super_read_only=OFF.'2022-05-22T19:14:35.945501+08:00 99 [System] [MY-011510] [Repl] Plugin group_replication reported: 'This server is working as primary member.'
root@mysql.sock 19:15:  [(none)]>SELECT * FROM performance_schema.replication_group_members;+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+| CHANNEL_NAME| MEMBER_ID| MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+| group_replication_applier | e37983d6-d9b2-11ec-8a0a-000c29a6e7be | 192.168.2.241 | 3306 | ONLINE| PRIMARY     | 8.0.22  || group_replication_applier | e3d12004-d9b2-11ec-a818-000c29d9d34c | 192.168.2.242 | 3306 | ONLINE| SECONDARY   | 8.0.22  |

恢复节点240

---记得启动之后要start group_replication---节点240会重进行加入MGR集群，恢复成为备节点+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+| CHANNEL_NAME| MEMBER_ID| MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+| group_replication_applier | b87decaf-d9b2-11ec-91e6-000c29ed797a | 192.168.2.240 | 3306 | ONLINE| SECONDARY   | 8.0.22  || group_replication_applier | e37983d6-d9b2-11ec-8a0a-000c29a6e7be | 192.168.2.241 | 3306 | ONLINE| PRIMARY     | 8.0.22  || group_replication_applier | e3d12004-d9b2-11ec-a818-000c29d9d34c | 192.168.2.242 | 3306 | ONLINE| SECONDARY   | 8.0.22  |+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+

风车动漫

网络标签：

相关问题