温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

MySQL5.7 MGR安装配置

发布时间:2020-09-14 08:16:48 来源:网络 阅读:646 作者:易语随风去 栏目:MySQL数据库

MySQL5.7 MGR安装配置

一、服务器规划

mysql_mgr_01 192.168.10.223

mysql_mgr_02 192.168.10.224

mysql_mgr_03 192.168.10.225



二、配置文件

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:24901"

loose-group_replication_group_seeds= "192.168.10.223:24901,192.168.10.224:24902,192.168.10.225: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.224:24902"

loose-group_replication_group_seeds= "192.168.10.223:24901,192.168.10.224:24902,192.168.10.225: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.225:24903"

loose-group_replication_group_seeds= "192.168.10.223:24901,192.168.10.224:24902,192.168.10.225: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




三、MGR配置

1. mysql_mgr_01

[mysql_mgr_01]


#关闭SQL_LOG_BIN

SET SQL_LOG_BIN=0;


#创建组复制使用的帐号密码

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

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


FLUSH PRIVILEGES;


#开启SQL_LOG_BIN

SET SQL_LOG_BIN=1;


#安装组复制插件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';


#初始化主节点

SET GLOBAL group_replication_bootstrap_group=ON;

START GROUP_REPLICATION;

SET GLOBAL group_replication_bootstrap_group=OFF;


#查看组复制成员状态

SELECT * FROM performance_schema.replication_group_members;


#备份

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


2. mysql_mgr_02

[mysql_mgr_02]


#恢复备份


#安装组复制插件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;



3. mysql_mgr_03

[mysql_mgr_03]


#恢复备份


#安装组复制插件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;


向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI