MySQL5.7 installation and configuration MGR tutorial
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.
