首页 > 数据库 >MySQL 8.0.34 MGR部署教程

MySQL 8.0.34 MGR部署教程

时间:2023-10-19 15:22:44浏览次数:33  
标签:GPL 8.0 group replication 34 member MGR mysql NULL

基于MySQL 8.0.34 MGR部署

一、环境概述

OS IP Hostname MySQL Version DB Port MGR Port
Redhat 7.9 172.20.10.3 s1 8.0.34 3306 33061
Redhat 7.9 172.20.10.4 s2 8.0.34 3306 33061
Redhat 7.9 172.20.10.5 s3 8.0.34 3306 33061

 

 

 

 

二、在各个节点安装MySQL数据库

1)卸载自带mysql、或mariadb(各节点均要操作)

rpm -qa | grep mysql 
rpm -qa | grep mariadb
rpm -e --nodeps 软件名

2)配置hosts(各节点均要操作)

172.20.10.3 s1
172.20.10.4 s2
172.20.10.5 s3

3)关闭防火墙,并设置为不自动启动(各节点均要操作)

systemctl stop firewalld
systemctl disable firewalld

4)创建mysql组和用户(各节点均要操作)

groupadd mysql
useradd -r -g mysql -s /sbin/false mysql

5)开始安装mysql

安装前准备工作

将mysql上传至/soft路径下并解压
[root@s1 soft]# tar -xvf mysql-8.0.34-linux-glibc2.17-x86_64.tar.gz
[root@s1 soft]# mv mysql-8.0.34-linux-glibc2.17-x86_64 mysql
[root@s1 mysql]# mkdir -p mysql/{data,logs}
[root@s1 mysql]# chown -R mysql:mysql /soft/mysql
[root@s1 mysql]# chmod -R 755 /soft/mysql

编写my.cnf配置文件

[root@s1 mysql]# vim /etc/my.cnf
添加如下代码
[mysql]
socket=/tmp/mysql.sock
[mysqld]
user=mysql
port=3306
basedir=/soft/mysql
datadir=/soft/mysql/data
socket=/tmp/mysql.sock
log_timestamps=system
log_error=/soft/mysql/logs/error.log
disabled_storage_engines=MyISAM,BLACKHOLE,FEDERATED,MEMORY
sql_require_primary_key=ON
report_host=172.20.10.3
report_port=3306
server_id =330603
log_bin =/soft/mysql/logs/mysql-bin
binlog_format =ROW
gtid_mode =ON
enforce_gtid_consistency =ON
log_slave_updates =ON
master_info_repository =TABLE
relay_log_info_repository =TABLE
binlog_transaction_dependency_tracking =WRITESET
super_read_only =ON

transaction_write_set_extraction =XXHASH64
replica_parallel_type =LOGICAL_CLOCK
replica_parallel_workers =4
replica_preserve_commit_order =ON

plugin_load_add =group_replication.so
loose_group_replication_group_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose_group_replication_start_on_boot =OFF
loose_group_replication_local_address =172.20.10.3:33061
loose_group_replication_group_seeds =172.20.10.3:33061,172.20.10.4:33061,172.20.10.5:33061
loose_group_replication_bootstrap_group =OFF
loose_group_replication_recovery_get_public_key =ON

初始化数据库

[root@s1 mysql]# /soft/mysql/bin/mysqld --initialize --user=mysql --basedir=/soft/mysql --datadir=/soft/mysql/data

创建软链接并启动数据库

[root@s1 mysql]# ln -vs /soft/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@s1 mysql]# /etc/init.d/mysqld start

环境变量配置

[root@s2 mysql]# vim ~/.bash_profile 

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin:/soft/mysql/bin

export PATH
[root@s2 mysql]# source ~/.bash_profile

登陆mysql修改初始密码

初始密码存放于log_error中定义的文件里面
[root@s1 data]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.34

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter user 'root'@'localhost' identified by 'root@123';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@s1 data]# 

到这里mysql就已经安装完毕了,另外2个节点略过

节点2参数

[mysql]
socket=/tmp/mysql.sock
[mysqld]
user=mysql
port=3306
basedir=/soft/mysql
datadir=/soft/mysql/data
socket=/tmp/mysql.sock
log_timestamps=system
log_error=/soft/mysql/logs/error.log
disabled_storage_engines=MyISAM,BLACKHOLE,FEDERATED,MEMORY
sql_require_primary_key=ON
report_host=172.20.10.4
report_port=3306
server_id =330604
log_bin =/soft/mysql/logs/mysql-bin
binlog_format =ROW
gtid_mode =ON
enforce_gtid_consistency =ON
log_slave_updates =ON
master_info_repository =TABLE
relay_log_info_repository =TABLE
binlog_transaction_dependency_tracking =WRITESET
super_read_only =ON

transaction_write_set_extraction =XXHASH64
replica_parallel_type =LOGICAL_CLOCK
replica_parallel_workers =4
replica_preserve_commit_order =ON

plugin_load_add =group_replication.so
loose_group_replication_group_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose_group_replication_start_on_boot =OFF
loose_group_replication_local_address =172.20.10.4:33061
loose_group_replication_group_seeds =172.20.10.3:33061,172.20.10.4:33061,172.20.10.3:33061
loose_group_replication_bootstrap_group =OFF
loose_group_replication_recovery_get_public_key =ON

节点3参数

[mysql]
socket=/tmp/mysql.sock
[mysqld]
user=mysql
port=3306
basedir=/soft/mysql
datadir=/soft/mysql/data
socket=/tmp/mysql.sock
log_timestamps=system
log_error=/soft/mysql/logs/error.log
disabled_storage_engines=MyISAM,BLACKHOLE,FEDERATED,MEMORY
sql_require_primary_key=ON
report_host=172.20.10.5
report_port=3306
server_id =330605
log_bin =/soft/mysql/logs/mysql-bin
binlog_format =ROW
gtid_mode =ON
enforce_gtid_consistency =ON
log_slave_updates =ON
master_info_repository =TABLE
relay_log_info_repository =TABLE
binlog_transaction_dependency_tracking =WRITESET
super_read_only =ON

transaction_write_set_extraction =XXHASH64
replica_parallel_type =LOGICAL_CLOCK
replica_parallel_workers =4
replica_preserve_commit_order =ON

plugin_load_add =group_replication.so
loose_group_replication_group_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose_group_replication_start_on_boot =OFF
loose_group_replication_local_address =172.20.10.5:33061
loose_group_replication_group_seeds =172.20.10.3:33061,172.20.10.4:33061,172.20.10.5:33061
loose_group_replication_bootstrap_group =OFF
loose_group_replication_recovery_get_public_key =ON

三、配置MGR复制组

1)查看插件是否都正常加载了

mysql> show plugins;
+----------------------------------+----------+--------------------+----------------------+---------+
| Name                             | Status   | Type               | Library              | License |
+----------------------------------+----------+--------------------+----------------------+---------+
| binlog                           | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| mysql_native_password            | ACTIVE   | AUTHENTICATION     | 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     |
+----------------------------------+----------+--------------------+----------------------+---------+
49 rows in set (0.00 sec)

mysql> 

2)初始化MGR复制组

mysql> set global group_replication_bootstrap_group=on;
Query OK, 0 rows affected (0.00 sec)

mysql> start group_replication;
Query OK, 0 rows affected (1.11 sec)

mysql> set global group_replication_bootstrap_group=off;
Query OK, 0 rows affected (0.00 sec)

mysql> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members;
+--------------------------------------+-------------+-------------+--------------+-------------+
| member_id                            | member_host | member_port | member_state | member_role |
+--------------------------------------+-------------+-------------+--------------+-------------+
| 141e2b7e-6e25-11ee-9483-0800276dee47 | 172.20.10.3 |        3306 | ONLINE       | PRIMARY     |
+--------------------------------------+-------------+-------------+--------------+-------------+
1 row in set (0.00 sec)

3)创建用于复制的用户

mysql> create user 'repl'@'%' identified with 'mysql_native_password' by 'repl@123';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave,backup_admin on *.* to 'repl'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

4)配置复制通道

mysql> change master to master_user='repl',master_password='repl@123' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 5 warnings (0.01 sec)

mysql> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members;
+--------------------------------------+-------------+-------------+--------------+-------------+
| member_id                            | member_host | member_port | member_state | member_role |
+--------------------------------------+-------------+-------------+--------------+-------------+
| 141e2b7e-6e25-11ee-9483-0800276dee47 | 172.20.10.3 |        3306 | ONLINE       | PRIMARY     |
+--------------------------------------+-------------+-------------+--------------+-------------+
1 row in set (0.00 sec)

四、为MGR添加新的成员

在节点2、节点3上分别执行一下步骤,将节点加入MGR复制组内

mysql> change master to master_user='repl',master_password='repl@123' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 5 warnings (0.01 sec)

mysql> start group_replication;
Query OK, 0 rows affected (1.73 sec)

mysql> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members;
+--------------------------------------+-------------+-------------+--------------+-------------+
| member_id                            | member_host | member_port | member_state | member_role |
+--------------------------------------+-------------+-------------+--------------+-------------+
| 141e2b7e-6e25-11ee-9483-0800276dee47 | 172.20.10.3 |        3306 | ONLINE       | PRIMARY     |
| 40b9ee07-6e27-11ee-9fb7-080027f800a3 | 172.20.10.4 |        3306 | ONLINE       | SECONDARY   |
+--------------------------------------+-------------+-------------+--------------+-------------+
2 rows in set (0.00 sec)

五、测试数据同步,若前面配置无任何问题,在1节点上写入的数据,在2、3节点均可看到,自行测试(略)

六、MGR复制组模式切换

1)单主模式切换多主模式

mysql> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members;
+--------------------------------------+-------------+-------------+--------------+-------------+
| member_id                            | member_host | member_port | member_state | member_role |
+--------------------------------------+-------------+-------------+--------------+-------------+
| 141e2b7e-6e25-11ee-9483-0800276dee47 | 172.20.10.3 |        3306 | ONLINE       | PRIMARY     |
| 2f1a648d-6e28-11ee-939d-080027602436 | 172.20.10.5 |        3306 | ONLINE       | SECONDARY   |
| 40b9ee07-6e27-11ee-9fb7-080027f800a3 | 172.20.10.4 |        3306 | ONLINE       | SECONDARY   |
+--------------------------------------+-------------+-------------+--------------+-------------+
3 rows in set (0.00 sec)

mysql> select group_replication_switch_to_multi_primary_mode();
+--------------------------------------------------+
| group_replication_switch_to_multi_primary_mode() |
+--------------------------------------------------+
| Mode switched to multi-primary successfully.     |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members;
+--------------------------------------+-------------+-------------+--------------+-------------+
| member_id                            | member_host | member_port | member_state | member_role |
+--------------------------------------+-------------+-------------+--------------+-------------+
| 141e2b7e-6e25-11ee-9483-0800276dee47 | 172.20.10.3 |        3306 | ONLINE       | PRIMARY     |
| 2f1a648d-6e28-11ee-939d-080027602436 | 172.20.10.5 |        3306 | ONLINE       | PRIMARY     |
| 40b9ee07-6e27-11ee-9fb7-080027f800a3 | 172.20.10.4 |        3306 | ONLINE       | PRIMARY     |
+--------------------------------------+-------------+-------------+--------------+-------------+
3 rows in set (0.00 sec)

2)多主模式切换单主模式

mysql> select group_replication_switch_to_single_primary_mode('141e2b7e-6e25-11ee-9483-0800276dee47');
+-----------------------------------------------------------------------------------------+
| group_replication_switch_to_single_primary_mode('141e2b7e-6e25-11ee-9483-0800276dee47') |
+-----------------------------------------------------------------------------------------+
| Mode switched to single-primary successfully.                                           |
+-----------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members;
+--------------------------------------+-------------+-------------+--------------+-------------+
| member_id                            | member_host | member_port | member_state | member_role |
+--------------------------------------+-------------+-------------+--------------+-------------+
| 141e2b7e-6e25-11ee-9483-0800276dee47 | 172.20.10.3 |        3306 | ONLINE       | PRIMARY     |
| 2f1a648d-6e28-11ee-939d-080027602436 | 172.20.10.5 |        3306 | ONLINE       | SECONDARY   |
| 40b9ee07-6e27-11ee-9fb7-080027f800a3 | 172.20.10.4 |        3306 | ONLINE       | SECONDARY   |
+--------------------------------------+-------------+-------------+--------------+-------------+
3 rows in set (0.00 sec)

查看切换进度

select event_name,work_completed,work_estimated from performance_schema.events_stages_current where event_name like '%stage/group_rpl%' \G;

七、MGR复制组维护

1)监控MGR复制组

监控主要基于performance_schema中的6张表实现:
replication_group_members
replication_group_member_stats
replication_connection_status
replication_applier_status
replication_applier_status_by_coordinator
replication_applier_status_by_worker

查看组成员信息

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 141e2b7e-6e25-11ee-9483-0800276dee47 | 172.20.10.3 |        3306 | ONLINE       | PRIMARY     | 8.0.34         | XCom                       |
| group_replication_applier | 2f1a648d-6e28-11ee-939d-080027602436 | 172.20.10.5 |        3306 | ONLINE       | SECONDARY   | 8.0.34         | XCom                       |
| group_replication_applier | 40b9ee07-6e27-11ee-9fb7-080027f800a3 | 172.20.10.4 |        3306 | ONLINE       | SECONDARY   | 8.0.34         | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)

2)查看组复制的状态

mysql> select * from performance_schema.replication_group_member_stats\G;
*************************** 1. row ***************************
                              CHANNEL_NAME: group_replication_applier
                                   VIEW_ID: 16976984379765337:3
                                 MEMBER_ID: 141e2b7e-6e25-11ee-9483-0800276dee47
               COUNT_TRANSACTIONS_IN_QUEUE: 0
                COUNT_TRANSACTIONS_CHECKED: 0
                  COUNT_CONFLICTS_DETECTED: 0
        COUNT_TRANSACTIONS_ROWS_VALIDATING: 0
        TRANSACTIONS_COMMITTED_ALL_MEMBERS: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-18
            LAST_CONFLICT_FREE_TRANSACTION: 
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0
         COUNT_TRANSACTIONS_REMOTE_APPLIED: 3
         COUNT_TRANSACTIONS_LOCAL_PROPOSED: 0
         COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0
*************************** 2. row ***************************
                              CHANNEL_NAME: group_replication_applier
                                   VIEW_ID: 16976984379765337:3
                                 MEMBER_ID: 2f1a648d-6e28-11ee-939d-080027602436
               COUNT_TRANSACTIONS_IN_QUEUE: 0
                COUNT_TRANSACTIONS_CHECKED: 0
                  COUNT_CONFLICTS_DETECTED: 0
        COUNT_TRANSACTIONS_ROWS_VALIDATING: 0
        TRANSACTIONS_COMMITTED_ALL_MEMBERS: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-18
            LAST_CONFLICT_FREE_TRANSACTION: 
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0
         COUNT_TRANSACTIONS_REMOTE_APPLIED: 0
         COUNT_TRANSACTIONS_LOCAL_PROPOSED: 0
         COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0
*************************** 3. row ***************************
                              CHANNEL_NAME: group_replication_applier
                                   VIEW_ID: 16976984379765337:3
                                 MEMBER_ID: 40b9ee07-6e27-11ee-9fb7-080027f800a3
               COUNT_TRANSACTIONS_IN_QUEUE: 0
                COUNT_TRANSACTIONS_CHECKED: 0
                  COUNT_CONFLICTS_DETECTED: 0
        COUNT_TRANSACTIONS_ROWS_VALIDATING: 0
        TRANSACTIONS_COMMITTED_ALL_MEMBERS: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-18
            LAST_CONFLICT_FREE_TRANSACTION: 
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0
         COUNT_TRANSACTIONS_REMOTE_APPLIED: 1
         COUNT_TRANSACTIONS_LOCAL_PROPOSED: 0
         COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0
3 rows in set (0.00 sec)

ERROR: 
No query specified

3)查看secondary节点延迟情况

mysql> select case when min_commit_timstamp is null then 0
    -> else unix_timestamp(now(6))-unix_timestamp(min_commit_timstamp)
    -> end as seconds_behind_master
    -> from (
    -> select min(applying_transaction_original_commit_timestamp) as min_commit_timstamp
    -> from performance_schema.replication_applier_status_by_worker
    -> where applying_transaction<>'') t;
+-----------------------+
| seconds_behind_master |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (0.01 sec)

4)查看内存使用情况

mysql> select name,enabled from performance_schema.setup_instruments where name like 'memory/group_rpl%';
+-------------------------------------------------------------------+---------+
| name                                                              | enabled |
+-------------------------------------------------------------------+---------+
| memory/group_rpl/write_set_encoded                                | YES     |
| memory/group_rpl/certification_data                               | YES     |
| memory/group_rpl/certification_data_gc                            | YES     |
| memory/group_rpl/certification_info                               | YES     |
| memory/group_rpl/transaction_data                                 | YES     |
| memory/group_rpl/sql_service_command_data                         | YES     |
| memory/group_rpl/mysql_thread_queued_task                         | YES     |
| memory/group_rpl/message_service_queue                            | YES     |
| memory/group_rpl/message_service_received_message                 | YES     |
| memory/group_rpl/group_member_info                                | YES     |
| memory/group_rpl/consistent_members_that_must_prepare_transaction | YES     |
| memory/group_rpl/consistent_transactions                          | YES     |
| memory/group_rpl/consistent_transactions_prepared                 | YES     |
| memory/group_rpl/consistent_transactions_waiting                  | YES     |
| memory/group_rpl/consistent_transactions_delayed_view_change      | YES     |
| memory/group_rpl/GCS_XCom::xcom_cache                             | YES     |
| memory/group_rpl/Gcs_message_data::m_buffer                       | YES     |
+-------------------------------------------------------------------+---------+
17 rows in set (0.00 sec)

以上就是搭建MGR的相关操作

标签:GPL,8.0,group,replication,34,member,MGR,mysql,NULL
From: https://www.cnblogs.com/Roobbin/p/17774687.html

相关文章

  • P6346 题解
    题目大意如果\(\texttt{Kevin}\)想和第\(i\)个人交朋友,要么需要认识\(a_i\)个人,要么付出\(b_i\)的代价,他让你使\(\texttt{Kevin}\)与所有的人交朋友。解题思路如果想水到\(15\)分,也就是所有\(b_i\)都等于\(1\)的情况,那我们可以直接排个序,然后遍历一下每一个人,......
  • 代码训练营第八天(Python)| 344.反转字符串、541. 反转字符串II、05.替换空格、151.翻转
    344.反转字符串双指针法时间复杂度为:O(n),空间复杂度为:O(1)classSolution:defreverseString(self,s:List[str])->None:"""Donotreturnanything,modifysin-placeinstead."""left,right=0,len(s......
  • 算法训练day36 1005.134.135.
    算法训练day361005.134.135.1005.K次取反后最大化的数组和题目1005.K次取反后最大化的数组和-力扣(LeetCode)题解代码随想录(programmercarl.com)将数字按绝对值大小排序优先将绝对值最大的负数取反剩余步骤将最小非负数取反注意数组大小顺序,以及处理剩余......
  • MySQL 8.0.34 tar包安装踩坑记录
    环境概述操作系统:Centos7.9MySQL版本:8.0.34错误提示[[email protected]]#/soft/mysql/bin/mysqld--initialize--user=mysql--basedir=/soft/mysql--datadir=/soft/mysql/data/soft/mysql/bin/mysqld:/lib64/libstdc++.so.6:version`CXXABI_1.3.11'not......
  • 34 组件的生命周期
    出生————>死亡在主要的时间节点上,自动执行生命周期钩子函数mount:安装,handle:处理组件生命周期示意图虽然写这东西没什么意义,但还是花了一点时间手撕代码吧<template><div><h3>组件的生命周期</h3><p>{{msg}}</p><button@click="clickHandle">......
  • AT_abc134_d Preparing Boxes题解
    简述题意这什么破翻译,看了AtCoder的英文才看懂。给定一个长度为\(n\)序列\(a\),要求构造一个数列\(b\),使得对于任意\(i\),满足:\(1\lei\len\)将\(b\)序列下标为\(i\)的倍数的值相加使得这个总和模2等于\(a_i\)。求序列\(b\)中值为1的个数与值为1......
  • 【dp】【进制】P3464 [POI2007] WAG-Quaternary Balance 题解
    P3464显然的,先将原数变为四进制的数。由于算的是进位/不进位的代价最小值和方案数,容易想到dp。这里假定该四进制数是从高位到低位的,顺序显然是由低位到高位。令\(f_{i,0/1}\)表示第\(i\)位进/不进位的最小代价,\(g_{i,0/1}\)表示的是最小代价下的方案数。转移是简单的......
  • SP4343
    数学题。我们进行推导。设每一层选了\(s_i\)个盒子,在里面放下一层的盒子。那么我们可以得到以下式子:\[ n-s_1+s_1\timesk-s_2+s_2\timesk\cdots=f\]\[ n+s_1\times(k-1)+s_2\times(k-1)\cdots=f\]\[ n+(s_1+s_2+\cdots)\times(k-1)=f\]答案显然是\(f+s_1+s_2+\cdot......
  • 解决Matlab遇到的svmtrain (line 234) Y must be a vector or a character array.
    解决Matlab遇到的svmtrain(line234)Ymustbeavectororacharacterarray.在使用MATLAB进行SVM分类器训练时,有时会出现以下错误提示:svmtrain(line234)Ymustbeavectororacharacterarray.这个错误是由于目标变量Y的类型不正确导致的。本文将介绍如何解决这个问题......
  • ubuntu安装php7.4-8.0
    更新,加入源apt-getupdateapt-yinstallsoftware-properties-common安装存储库ppa:ondrej/php,它提供所有PHP版本add-apt-repositoryppa:ondrej/phpapt-getupdate开始安装php74apt-yinstallphp7.4php-v安装额外的php模块apt-getinstall-yphp7.4-clip......