Get the App
SLTechnology News&Howtos  ›  Database  › 

MySQL5.7 MGR installation configuration

Shulou Source: shulou.com Published: 2022-06-01 03:55:43 10月03日 Update

MySQL5.7 MGR installation configuration

First, server planning

Mysql_mgr_01 192.168.10.223

Mysql_mgr_02 192.168.10.224

Mysql_mgr_03 192.168.10.225

II. Configuration file

1. Mysql_mgr_01

[root@mysql_mgr_01 tmp] # cat / etc/my.cnf

[mysqld]

Sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Server-id=330601

Port = 3306

Log_bin = / data/mysql/binlog/mysql_mgr01-bin

# expire_logs_days=10

Explicit_defaults_for_timestamp=true

Datadir=/data/mysql/data

Socket=/data/mysql/run/mysql.sock

Skip-external-locking

Pid-file=/data/mysql/run/mysql.pid

Innodb_file_per_table = on

# skip_name_resolve = on

Character-set-server = utf8mb4

Character-set-client-handshake = FALSE

Collation-server = utf8mb4_unicode_ci

Init_connect='SET NAMES utf8mb4'

Slow_query_log = on

Long_query_time = 2

Slow_query_log_file = / data/mysql/slow_logs/mysql-slow.log

Binlog_rows_query_log_events=ON

Relay-log=/data/mysql/relaylog/mysql_mgr_01-relay-bin

#

# Replication configuration parameters

#

Gtid_mode=ON

Enforce_gtid_consistency=ON

Master_info_repository=TABLE

Relay_log_info_repository=TABLE

Binlog_checksum=NONE

Log_slave_updates=ON

Binlog_format=ROW

#

# Group Replication configuration

#

Transaction_write_set_extraction=XXHASH64

Loose-group_replication_group_name= "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

Loose-group_replication_start_on_boot=off

Loose-group_replication_local_address= "192.168.10.223 purge 24901"

Loose-group_replication_group_seeds= "192.168.10.223 purl 24901192.168.10.224 purl 24902192.168.10.225purl 24903"

Loose-group_replication_bootstrap_group=off

Group_replication_member_weight = 100

Symbolic-links=0

[mysqld_safe]

Log-error=/data/mysql/logs/mysql.log

Pid-file=/data/mysql/run/mysql.pid

[mysql]

Default-character-set = utf8mb4

Socket=/data/mysql/run/mysql.sock

[client]

# default-character-set = utf8mb4

Socket=/data/mysql/run/mysql.sock

2. Mysql_mgr_02

[root@mysql_mgr_02 tmp] # cat / etc/my.cnf

[mysqld]

Sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Server-id=330602

Port = 3306

Log_bin = / data/mysql/binlog/mysql_mgr02-bin

# expire_logs_days=10

Explicit_defaults_for_timestamp=true

Datadir=/data/mysql/data

Socket=/data/mysql/run/mysql.sock

Skip-external-locking

Pid-file=/data/mysql/run/mysql.pid

Innodb_file_per_table = on

# skip_name_resolve = on

Relay-log=/data/mysql/relaylog/mysql_mgr_02-relay-bin

Character-set-server = utf8mb4

Character-set-client-handshake = FALSE

Collation-server = utf8mb4_unicode_ci

Init_connect='SET NAMES utf8mb4'

Slow_query_log = on

Long_query_time = 2

Slow_query_log_file = / data/mysql/slow_logs/mysql-slow.log

Symbolic-links=0

#

# Replication configuration parameters

#

Gtid_mode=ON

Enforce_gtid_consistency=ON

Master_info_repository=TABLE

Relay_log_info_repository=TABLE

Binlog_checksum=NONE

Log_slave_updates=ON

Binlog_format=ROW

#

# Group Replication configuration

#

Transaction_write_set_extraction=XXHASH64

Loose-group_replication_group_name= "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

Loose-group_replication_start_on_boot=off

Loose-group_replication_local_address= "192.168.10.224purl 24902"

Loose-group_replication_group_seeds= "192.168.10.223 purl 24901192.168.10.224 purl 24902192.168.10.225purl 24903"

Loose-group_replication_bootstrap_group=off

Group_replication_member_weight = 80

[mysqld_safe]

Log-error=/data/mysql/logs/mysql.log

Pid-file=/data/mysql/run/mysql.pid

[mysql]

Default-character-set = utf8mb4

Socket=/data/mysql/run/mysql.sock

[client]

# default-character-set = utf8mb4

Socket=/data/mysql/run/mysql.sock

3. Mysql_mgr_03

[root@mysql_mgr_03 tmp] # cat / etc/my.cnf

[mysqld]

Sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Server-id=330603

Port = 3306

Log_bin = / data/mysql/binlog/mysql_mgr03-bin

# expire_logs_days=10

Explicit_defaults_for_timestamp=true

Datadir=/data/mysql/data

Socket=/data/mysql/run/mysql.sock

Skip-external-locking

Pid-file=/data/mysql/run/mysql.pid

Innodb_file_per_table = on

# skip_name_resolve = on

Relay-log=/data/mysql/relaylog/mysql_mgr_03-relay-bin

Character-set-server = utf8mb4

Character-set-client-handshake = FALSE

Collation-server = utf8mb4_unicode_ci

Init_connect='SET NAMES utf8mb4'

Slow_query_log = on

Long_query_time = 2

Slow_query_log_file = / data/mysql/slow_logs/mysql-slow.log

Symbolic-links=0

#

# Replication configuration parameters

#

Gtid_mode=ON

Enforce_gtid_consistency=ON

Master_info_repository=TABLE

Relay_log_info_repository=TABLE

Binlog_checksum=NONE

Log_slave_updates=ON

Binlog_format=ROW

#

# Group Replication configuration

#

Transaction_write_set_extraction=XXHASH64

Loose-group_replication_group_name= "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

Loose-group_replication_start_on_boot=off

Loose-group_replication_local_address= "192.168.10.225purl 24903"

Loose-group_replication_group_seeds= "192.168.10.223 purl 24901192.168.10.224 purl 24902192.168.10.225purl 24903"

Loose-group_replication_bootstrap_group=off

Group_replication_member_weight = 90

[mysqld_safe]

Log-error=/data/mysql/logs/mysql.log

Pid-file=/data/mysql/run/mysql.pid

[mysql]

Default-character-set = utf8mb4

Socket=/data/mysql/run/mysql.sock

[client]

# default-character-set = utf8mb4

Socket=/data/mysql/run/mysql.sock

III. MGR configuration

1. Mysql_mgr_01

[mysql_mgr_01]

# turn off SQL_LOG_BIN

SET SQL_LOG_BIN=0

# account password used to create group replication

CREATE USER rpl_user@'%' IDENTIFIED BY 'password'

GRANT REPLICATION SLAVE ON *. * TO rpl_user@'%'

FLUSH PRIVILEGES

# enable SQL_LOG_BIN

SET SQL_LOG_BIN=1

# installation group replication plug-in group_replication.so

INSTALL PLUGIN group_replication SONAME 'group_replication.so'

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery'

# initialize the primary node

SET GLOBAL group_replication_bootstrap_group=ON

START GROUP_REPLICATION

SET GLOBAL group_replication_bootstrap_group=OFF

# View the status of group replication members

SELECT * FROM performance_schema.replication_group_members

# backup

Mysqldump-uroot-p-- all-databases-- master-data=2-- single-transaction > / tmp/all.sql

2. Mysql_mgr_02

[mysql_mgr_02]

# restore backup

# installation group replication plug-in group_replication.so

INSTALL PLUGIN group_replication SONAME 'group_replication.so'

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery'

# start group replication

START GROUP_REPLICATION

3. Mysql_mgr_03

[mysql_mgr_03]

# restore backup

# installation group replication plug-in group_replication.so

INSTALL PLUGIN group_replication SONAME 'group_replication.so'

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery'

# start group replication

START GROUP_REPLICATION

Tags: Configuration backup plug-ins passwords members files servers status nodes accounts services planning Apple Docker Huawei Linux macOS MariaDB Microsoft MySQL NVidia OPPO Reno NVidia Redmi Linux MySQL Shulou Information

Related Articles