MySQL 8.0 MGR (Group replication) highly available VIP switch script

MySQL 8.0 MGR (Group replication) highly available VIP switch script

Introduction: MGR (group replication) officially recommends using MySQL router middleware to do MGR high availability failover, but it has more than one layer of network, performance will decline, and need to maintain an additional set of middleware, the operation and maintenance cost is too high, so write a master_ip_failover script similar to MHA to achieve VIP switching.

1) the script automatically sets the current Primary and alternate Primary parameter group_replication_member_ weight value to 100 (weight 100, Secondary with a default of 50 does not switch vip)

2) the script automatically sets the current Primary and the alternate Primary parameter group_replication_ consideration to BEFORE_ON_PRIMARY_FAILOVER (meaning that when the Primary hangs, the alternate Primary provides client read and write operations only after all the transactions have been executed)

3) it is best to disable the current limiting mode set global group_replication_flow_control_mode = 'DISABLED', in production to prevent automatic triggering of current limiting during high concurrency, making the main database unwritable and causing production accidents.

* Environmental preparation:

* shell > yum install-y php-process php php-mysql

* activate the mutual trust between the monitoring and management machine and MGR SSH (you can use MHA's masterha_check_ssh script for detection)

MGR (group replication) highly available VIP switching script

Https://github.com/hcymysql/mgr_failover_vip

Operating conditions:

Modified by: hcymysql 2019-07-24

1. MySQL version 8.0

2. Single-primary mode (single main mode)

3. Native Password Authentication (5.5Compact 5.6Universe 5.7 traditional user authentication mode)

Example:

CREATE USER 'hechunyang'@'%' IDENTIFIED WITH mysql_native_password BY' 123456'

GRANT ALL ON. TO 'hechunyang'@'%' WITH GRANT OPTION

Usage:

Options:

-I interval time seconds sets the interval monitoring time under the daemon

-- daemon 1 starts the background daemon, 0 closes the background daemon

-- conf specifies the profile

-- help help

Example:

Foreground operation

Shell > php mgr_master_ip_failover.php-- conf=mgr_configure1.php

Background operation

Shell > nohup / usr/bin/php mgr_master_ip_failover.php-- conf=mgr_configure1.php-I 5-- daemon 1 > / dev/null 2 > & 1 &

Turn off background operation

Shell > php mgr_master_ip_failover.php-conf=mgr_configure1.php-daemon 0

Mgr_configure1.php is a configuration file. You can configure multiple monitoring profiles to monitor multiple MGR environments.