第一文，MySQL集群MGR高可用（一主两从）

一个不知名的人 2021-12-19

962

先介绍下自己，从事软件开发有好多年，之前都是用到就用了，也没详细记录过，经常发现同样的错误出现的时候，还是会有点懵，真是应景了一句话，好记性不如烂笔头。好了，先不先扯淡了，进入本文主题吧。

本文使用Percona搭建MySQL5.7集群。首先，先在Percona官网下载对应的文件，下面是文件清单：

Percona-Server-server-57-5.7.34-37.1.el7.x86_64.rpm
Percona-Server-client-57-5.7.34-37.1.el7.x86_64.rpm
Percona-Server-devel-57-5.7.34-37.1.el7.x86_64.rpm
Percona-Server-shared-57-5.7.34-37.1.el7.x86_64.rpm
Percona-Server-shared-compat-57-5.7.34-37.1.el7.x86_64.rpm
percona-xtrabackup-24-2.4.21-1.el7.x86_64.rpm

准备三台Linux服务器，我这里分别是192.168.8.6(主)、192.168.8.7(从)、192.168.8.8(从)。

三台机器分别安装Percona

yum localinstall -y Percona-Server-server-57-5.7.34-37.1.el7.x86_64.rpm Percona-Server-client-57-5.7.34-37.1.el7.x86_64.rpm  Percona-Server-devel-57-5.7.34-37.1.el7.x86_64.rpm Percona-Server-shared-57-5.7.34-37.1.el7.x86_64.rpm Percona-Server-shared-compat-57-5.7.34-37.1.el7.x86_64.rpm
yum localinstall -y percona-xtrabackup-24-2.4.21-1.el7.x86_64.rpm

配置三个节点的hosts

#三台机器IP  主机名
192.168.8.6 l8-6-t-pbs
192.168.8.7 l8-7-t-pbs
192.168.8.8 l8-8-t-pbs

配置my.cnf，三个节点都要配置

下面文件是8.6节点上的，其他两台类似。标记需要替换的地方

[mysqld]
datadir=/data/mysql/mysql
socket=/data/mysql/mysql/mysql.sock
log-error=/data/logs/mysql/mysqld.log
slow_query_log_file=/data/logs/mysql/mysql_slow.log
pid-file=/data/mysql/mysql/mysqld.pid
tmpdir=/data/tmp
secure-file-priv=/data/secdata
local_infile=OFF
#general_log = 1
#general_log_file = /data/logs/mysql/mysql_general.log


extra_port=6034
extra_max_connections=20
max_connections = 4096
max_user_connections = 0
max_connect_errors = 10000
max_allowed_packet = 128M
table_open_cache=4000
table_open_cache_instances=10
table_definition_cache = 4096
table_open_cache_instances = 64
character_set_server=utf8mb4
show_compatibility_56 = on
explicit_defaults_for_timestamp = 1
event_scheduler = 1
log_timestamps=SYSTEM
default-time-zone = '+08:00'
symbolic-links=0
transaction_isolation=READ-UNCOMMITTED
skip-name-resolve
read_only=1
#lower_case_table_names=1
group_concat_max_len=10240
wait_timeout=3600
interactive_timeout=3600
query_cache_size=0


slow_query_log=on
log_output='table,file'
long_query_time=0.4
log_queries_not_using_indexes=0
log_throttle_queries_not_using_indexes=100
min_examined_row_limit=5000
log_slow_admin_statements=1
log_slow_slave_statements=1


innodb_online_alter_log_max_size=1G
innodb_buffer_pool_size=4G    ##机器内存60%
innodb_buffer_pool_instances=8
innodb_file_per_table
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_print_all_deadlocks = 1
innodb_autoinc_lock_mode = 2
innodb_log_file_size=1G 
innodb_log_buffer_size = 32M
innodb_flush_method = O_DIRECT
innodb_flush_neighbors = 0
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_max_undo_log_size=1G
innodb_undo_log_truncate = 1
innodb_purge_threads = 4   #cpus <=8 ：4      cpus >= 16: cpus/4
innodb_read_io_threads = 4 #cpus <=8 ：4      cpus >= 16: cpus/4
innodb_write_io_threads = 4 #cpus <=8 ：4      cpus >= 16: cpus/4
innodb_page_cleaners = 4  #cpus <=8 ：4      cpus >= 16: cpus/4
innodb_io_capacity=10000
innodb_io_capacity_max=20000


# perforamnce_schema settings
performance-schema-instrument='memory/%=COUNTED'
performance_schema_digests_size = 40000
performance_schema_max_table_instances = 40000
performance_schema_max_sql_text_length = 4096
performance_schema_max_digest_length = 4096


server_id=#{server_id} #需要替换的地方
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_master_timeout = 3000
#rpl_semi_sync_slave_enabled = 1
relay_log_info_repository=TABLE
slave_pending_jobs_size_max=104857600
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
slave_preserve_commit_order=1
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=0
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
relay_log=relaylog
expire_logs_days=15
binlog_format=ROW
binlog_rows_query_log_events = 1
log_bin_trust_function_creators
transaction_write_set_extraction=XXHASH64


#group_replication_exit_state_action='ABORT_SERVER'
#group_replication_compression_threshold = 131072
#group_replication_transaction_size_limit = 104857600
#group_replication_unreachable_majority_timeout = 60
#group_replication_group_name="#{group_name}" #需要替换的地方，必须是uuid   ，三个节点相同
#group_replication_start_on_boot=off
#group_replication_local_address= "192.168.8.6:13306"   #本机IP  端口不变 .当前服务器的IP
#group_replication_group_seeds='192.168.8.6:13306,192.168.8.7:13306,192.168.8.8:13306'         #三台机器IP
#group_replication_bootstrap_group=off
#group_replication_ip_whitelist="192.168.8.6,192.168.8.7,192.168.8.8"      #机器IP网段
#group_replication_flow_control_mode='DISABLED'


[client]
socket=/data/mysql/mysql/mysql.sock

创建文件，三个节点都要执行

mkdir -p /data/mysql/mysql
mkdir -p /data/logs/mysql
mkdir -p /data/tmp
mkdir -p /data/secdata
mkdir -p /data/baklog
touch /data/logs/mysql/mysqld.log
chown mysql.mysql -R /data/mysql/mysql
chown mysql.mysql -R /data/logs/mysql
chown mysql.mysql -R /data/tmp
chown mysql.mysql -R /data/secdata

启动服务

systemctl start mysqld

在mysqld.log中找到password登录

mysql -u root -p

重设root密码

mysql>alter user user() identified by "Test.123";

安装插件

mysql>install PLUGIN group_replication SONAME 'group_replication.so';

去掉my.cnf中group_replication开头的注释，然后重启mysql服务

systemctl restart mysqld

创建账号

mysql>CREATE USER 'rpl_user'@'%' IDENTIFIED by 'Test.123' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
mysql>GRANT REPLICATION CLIENT, REPLICATION SLAVE, SELECT ON *.* TO 'rpl_user'@'%';

组MGR

8.6节点(主节点)

mysql>reset master;
mysql>reset slave;
mysql>CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Bitauto.123' FOR CHANNEL 'group_replication_recovery';
mysql>SET GLOBAL group_replication_bootstrap_group=ON;
mysql>START GROUP_REPLICATION; 
mysql>SET GLOBAL group_replication_bootstrap_group=OFF;

8.7,8.8节点(从节点)

mysql>reset master;
mysql>reset slave;
mysql>CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Bitauto.123' FOR CHANNEL 'group_replication_recovery';
mysql>START GROUP_REPLICATION;

创建视图，主节点

USE sys;


DELIMITER $$


CREATE FUNCTION IFZERO(a INT, b INT)
RETURNS INT
DETERMINISTIC
RETURN IF(a = 0, b, a)$$


CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)
RETURNS INT
DETERMINISTIC
RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$


CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))
RETURNS TEXT(10000)
DETERMINISTIC
RETURN GTID_SUBTRACT(g, '')$$


CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE result BIGINT DEFAULT 0;
  DECLARE colon_pos INT;
  DECLARE next_dash_pos INT;
  DECLARE next_colon_pos INT;
  DECLARE next_comma_pos INT;
  SET gtid_set = GTID_NORMALIZE(gtid_set);
  SET colon_pos = LOCATE2(':', gtid_set, 1);
  WHILE colon_pos != LENGTH(gtid_set) + 1 DO
     SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);
     SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);
     SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);
     IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN
       SET result = result +
         SUBSTR(gtid_set, next_dash_pos + 1,
                LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -
         SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;
     ELSE
       SET result = result + 1;
     END IF;
     SET colon_pos = next_colon_pos;
  END WHILE;
  RETURN result;
END$$


CREATE FUNCTION gr_applier_queue_length()
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT
Received_transaction_set FROM performance_schema.replication_connection_status
WHERE Channel_name = 'group_replication_applier' ), (SELECT
@@global.GTID_EXECUTED) )));
END$$


CREATE FUNCTION gr_member_in_primary_partition()
RETURNS VARCHAR(3)
DETERMINISTIC
BEGIN
  RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=
((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
performance_schema.replication_group_member_stats USING(member_id));
END$$


CREATE VIEW gr_member_routing_candidate_status AS SELECT
sys.gr_member_in_primary_partition() as viable_candidate,
IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM
performance_schema.global_variables WHERE variable_name IN ('read_only',
'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,
sys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats;$$


DELIMITER ;

创建应用程序用户

GRANT ALL PRIVILEGES ON *.* TO 'cxt_test'@'%' IDENTIFIED BY 'Test.123' WITH GRANT OPTION;

到此，MGR高可用的MySQL5.7已经搭建完。后续文章会介绍在使用过程中遇到的问题，比如整个集群节点全部重启后处理方式，或者单从节点挂掉处理方式

mysql

文章转载自一个不知名的人，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

第一文，MySQL集群MGR高可用（一主两从）

评论