MySQL MGR單主模式詳細搭建過程

ywxj_001發表於2019-08-21
MySql模式

環境：

節點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;  

MySQL MGR單主模式詳細搭建過程


Done.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22996654/viewspace-2654442/，如需轉載，請註明出處，否則將追究法律責任。

相關文章