Shulou(Shulou.com)06/01 Report--

This article mainly gives you a brief introduction to MySQL5.7 installation and configuration MGR tutorials. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on MySQL5.7 installation and configuration MGR tutorials can bring you some practical help.

1. CVM 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

This is the end of the MySQL5.7 installation and configuration MGR tutorial. If you want to know about other related issues, you can continue to follow our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.