ITPub博客

首页 > 数据库 > MySQL > MySQL MGR单主模式详细搭建过程

MySQL MGR单主模式详细搭建过程

原创 MySQL 作者:ywxj_001 时间:2019-08-21 14:55:04 0 删除 编辑

环境:

节点1:192.168.157.128   CentOS 7.6
数据库:mysql-5.7.27-linux-glibc2.12-x86_64
节点2:192.168.157.129   CentOS 7.6
数据库:mysql-5.7.27-linux-glibc2.12-x86_64
节点3:192.168.157.130   CentOS 7.6

数据库:mysql-5.7.27-linux-glibc2.12-x86_64


1、安装MySQL

在三台db服务器上面设置/etc/hosts映射,如下:

 

192.168.157.128  mgr1
192.168.157.129  mgr2
192.168.157.130  mgr3

 

安装的数据库服务器:
IP地址:                             端口号:数据目录:      Server-id:
192.168.157.128(mgr1) 3306 /opt/mysql/data 20      
192.168.157.129(mgr2) 3306 /opt/mysql/data 21
192.168.157.130(mgr3) 3306 /opt/mysql/data 22
安装过程略。。。。。


配置my.cnf:

注意每个节点的server_id、loose-group_replication_local_address、loose-group_replication_group_seeds都配置成自己的相应的参数


配置my.cnf:
[client]
port = 3306
socket = /opt/mysql/tmp/mysql.sock
[mysqld]
port = 3306
socket = /opt/mysql/tmp/mysql.sock
back_log = 80
basedir = /opt/mysql
tmpdir = /tmp
datadir = /opt/mysql/data
pid-file=/opt/mysql/tmp/mysqld.pid
#-------------------gobal variables------------#
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
log-bin = /opt/mysql/log/mysql-bin
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856'
loose-group_replication_start_on_boot = off
loose-group_replication_local_address = '192.168.157.128:33061'
loose-group_replication_group_seeds ='192.168.157.128:33061,192.168.157.129:33061,192.168.157.130:33061'
loose-group_replication_bootstrap_group = off
loose-group_replication_single_primary_mode = true
loose-group_replication_enforce_update_everywhere_checks = false
max_connect_errors = 20000
max_connections = 2000
wait_timeout = 3600
interactive_timeout = 3600
net_read_timeout = 3600
net_write_timeout = 3600
table_open_cache = 1024
table_definition_cache = 1024
thread_cache_size = 512
open_files_limit = 10000
character-set-server = utf8
collation-server = utf8_bin
skip_external_locking
performance_schema = 1
user = mysql
myisam_recover_options = DEFAULT
skip-name-resolve
local_infile = 0
lower_case_table_names = 0
#--------------------innoDB------------#
innodb_buffer_pool_size = 2000M
innodb_data_file_path = ibdata1:1000M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_io_capacity = 600
innodb_lock_wait_timeout = 120
innodb_log_buffer_size = 8M
innodb_log_file_size = 2000M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 85
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_support_xa = 1
innodb_thread_concurrency = 32
innodb_file_per_table
innodb_rollback_on_timeout
#------------session variables-------#
join_buffer_size = 8M
key_buffer_size = 256M
bulk_insert_buffer_size = 8M
max_heap_table_size = 96M
tmp_table_size = 96M
read_buffer_size = 8M
sort_buffer_size = 2M
max_allowed_packet = 64M
read_rnd_buffer_size = 32M
#------------MySQL Log----------------#
log-bin = my3306-bin
binlog_format = row
sync_binlog = 1
expire_logs_days = 15
#max_binlog_cache_size = 128M
#max_binlog_size = 500M
binlog_cache_size = 64k
slow_query_log
log-slow-admin-statements
log_warnings = 1
long_query_time = 0.25
#---------------replicate--------------#
relay-log = /opt/mysql/log/mysql-relay-bin
relay-log-index = relay3306.index
relay-log = relay3306
server-id = 20
init_slave = 'set sql_mode=STRICT_ALL_TABLES'
log-slave-updates
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192
log-error = /opt/mysql/log/mysql.err


2、创建MGR

在mgr1/mgr2/mgr3上建立复制账号:
mysql>
set sql_log_bin=0;
grant replication slave on *.* to 'repl'@'%' identified by '123456';
flush privileges;
set sql_log_bin=1;


安装group replication插件
在mgr1、mgr2、mgr3上依次安装group replication插件
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';  


配置group replication参数
确保binlog_format是row格式。
mysql> show variables like 'binlog_format';                                              
+---------------+-------+  
| Variable_name | Value |  
+---------------+-------+  
| binlog_format | ROW   |  
+---------------+-------+  
1 row in set (0.00 sec)  


配置文件配置:
(1)  mgr1上的my.cnf配置:
server-id=20
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = "ce9be252-2b71-11e6-b8f4-00212844f856"                                                                                                                                                      
loose-group_replication_start_on_boot = off
loose-group_replication_local_address = "192.168.157.128:33061"
loose-group_replication_group_seeds = "192.168.157.128:33061,192.168.157.129:33061,192.168.157.130:33061"
loose-group_replication_bootstrap_group = off
loose-group_replication_single_primary_mode = true
loose-group_replication_enforce_update_everywhere_checks = false

 

(2)mgr2上的my.cnf配置:
server-id=21
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = "ce9be252-2b71-11e6-b8f4-00212844f856"
loose-group_replication_start_on_boot = off
loose-group_replication_local_address = "192.168.157.129:33061"
loose-group_replication_group_seeds = "192.168.157.128:33061,192.168.157.129:33061,192.168.157.130:33061"
loose-group_replication_bootstrap_group = off
loose-group_replication_single_primary_mode = true
loose-group_replication_enforce_update_everywhere_checks = false
(3)mgr3上的my.cnf配置:
server-id=22
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = "ce9be252-2b71-11e6-b8f4-00212844f856"
loose-group_replication_start_on_boot = off
loose-group_replication_local_address = "192.168.157.130 :33061"
loose-group_replication_group_seeds = "192.168.157.128:33061,192.168.157.129:33061,192.168.157.130:33061"
loose-group_replication_bootstrap_group = off
loose-group_replication_single_primary_mode = true
loose-group_replication_enforce_update_everywhere_checks = false
配置完后,重启3个db上的mysql服务



[root@mha01 etc]# mysqladmin -uroot -p -S /opt/mysql/tmp/mysql.sock shutdown
Enter password:
2019-08-19T07:58:22.734405Z mysqld_safe mysqld from pid file /opt/mysql/tmp/mysqld.pid ended
[1]+  Done                    /opt/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf
[root@mha01 etc]# /opt/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
[1] 85384
[root@mha01 etc]# 2019-08-19T07:58:58.536554Z mysqld_safe Logging to '/opt/mysql/log/mysql.err'.
2019-08-19T07:58:58.570079Z mysqld_safe Starting mysqld daemon with databases from /opt/mysql/data


启动mgr群集
开始构建group replication集群,通常操作命令
在mgr1、mgr2、mgr3上依次执行
mysql>  CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';  
Query OK, 0 rows affected, 2 warnings (0.02 sec)  

 

Db1上建立基本主库master库:
# 设置group_replication_bootstrap_group为ON是为了标示以后加入集群的服务器以这台服务器为基准,以后加入的就不需要设置。  
mysql> SET GLOBAL group_replication_bootstrap_group = ON;  
Query OK, 0 rows affected (0.00 sec)  

   

mysql>  START GROUP_REPLICATION;  
Query OK, 0 rows affected (1.03 sec)  

   

mysql> select * from performance_schema.replication_group_members; 


Db2上启动group_replication: 
Db2上mysql命令行上执行启动:
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;

 

mysql> start group_replication;

 

mysql> select * from performance_schema.replication_group_members;


Db3上启动group_replication:
-- Db3命令行上执行:  
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;  

   

mysql> start group_replication;  


-- 再去master库mgr1上,查看group_replication成员,会有mgr3的显示,而且已经是ONLINE了  
mysql> select * from performance_schema.replication_group_members;
 
最后查看集群状态,都为ONLINE就表示OK:
mysql> select * from performance_schema.replication_group_members;  


Done.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22996654/viewspace-2654442/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
在零售、金融行业从事数据库相关工作10余年,有丰富的数据库管理的相关经验。 涉及SqlServer、Oracle、MySQL、PostgreSQL等多种数据库。 专注于各类数据库的研究。 目前在一家外资的上市零售公司担任资深DBA岗位。负责整个集团数据库的架构设计和管理。

注册时间:2010-01-19

  • 博文量
    113
  • 访问量
    90243