mysql组复制安装

2023-01-30 19:42:58

参考文档

https://blog.csdn.net/li123128/article/details/80744568

https://www.cnblogs.com/ctulzq/p/8631097.html

http://www.cnblogs.com/lemon-le/p/9241984.html

https://www.jianshu.com/p/ca1af156f656

首先安装mysql5.7

https://www.cnblogs.com/maobuji/p/8336702.html

服务器信息

序号 服务器IP mysql别名 端口
1 172.18.100.85 mgr1 3306
2 172.18.100.69 mgr2 3306
3 172.18.100.84 mgr3 3306

在三台服务器上设置/etc/hosts的映射，但是不清楚为什么要做这个映射，因为后面也没有碰到配置的地方。

172.18.100.85 mgr1

172.18.100.69 mgr2

172.18.100.84 mgr3

如果机器名都相同，可以修改机器名

hostnamectl set-hostname mgr1

配置三台mysql的配置文件vi /etc/my.cnf,标红色的部分是需要修改的配置。

#-------------------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 = mysql-bin

transaction_write_set_extraction = XXHASH64

loose-group_replication_group_name = '157be252-2b71-11e6-b8f4-00212889f856'

loose-group_replication_start_on_boot = off

loose-group_replication_bootstrap_group = off
loose-group_replication_local_address = '172.18.100.85:33061'

loose-group_replication_group_seeds ='172.18.100.85:33061,172.18.100.69:33061,172.18.100.84:33061'

loose-group_replication_single_primary_mode = off

loose-group_replication_enforce_update_everywhere_checks = on

max_connect_errors =

max_connections =

wait_timeout =

interactive_timeout =

net_read_timeout =

net_write_timeout =

table_open_cache =

table_definition_cache =

thread_cache_size =

open_files_limit =

character-set-server = utf8

collation-server = utf8_bin

skip_external_locking

performance_schema =

user = mysql

myisam_recover_options = DEFAULT

skip-name-resolve

local_infile =

lower_case_table_names = 

#--------------------innoDB------------#

innodb_buffer_pool_size = 2000M

#innodb_data_file_path = ibdata1:200M:autoextend

innodb_flush_log_at_trx_commit =

innodb_io_capacity =

innodb_lock_wait_timeout =

innodb_log_buffer_size = 8M

innodb_log_file_size = 200M

innodb_log_files_in_group =

innodb_max_dirty_pages_pct =

innodb_read_io_threads =

innodb_write_io_threads =

innodb_support_xa =

innodb_thread_concurrency =

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 =

expire_logs_days =

max_binlog_cache_size = 128M

max_binlog_size = 500M

binlog_cache_size = 64k

slow_query_log

log-slow-admin-statements

log_warnings =

long_query_time = 0.25

#---------------replicate--------------#

relay-log-index = relay3306.index

relay-log = relay3306
server-id =1

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 =

配置完成后，重新mysql

systemctl restart mysqld

登录mysql在三台机器上都安装组复制插件,可以通过查询状态来检查插件是否已经激活了

mysql -uroot -p

mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';

mysql>select plugin_name, plugin_status from information_schema.plugins where plugin_name like 'group_%';

需要先进行配置，否则会报错误

ERROR  (HY000): Can't initialize function 'group_replication'; Plugin initialization function failed.

修改linux系统配置/etc/selinux/config文件，修改后要重启系统

SELINUX=disabled
否则可能出现以下错误
ERROR  (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.

在配置过程中可以随时执行查询命令，查看集群状态

mysql>SELECT * FROM performance_schema.`replication_group_members`;


配置server1，做为主.




mysql -uroot -p

mysql>set sql_log_bin=0;
mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.18.100.%' IDENTIFIED BY 'RKYSrkys123!@#';
mysql>flush privileges;
mysql>set sql_log_bin=1;


mysql>CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='RKYSrkys123!@#' FOR CHANNEL 'group_replication_recovery';
mysql>set global group_replication_ip_whitelist="127.0.0.1/32,172.18.100.0/24";
mysql>SET GLOBAL group_replication_bootstrap_group = ON; 
mysql>START GROUP_REPLICATION;
mysql>SET GLOBAL group_replication_bootstrap_group = OFF;
mysql>SELECT * FROM performance_schema.`replication_group_members`;




1.创建并授权一个复制用户
2.FOR CHANNEL 'group_replication_recovery'是一个默认的管道，不能改名
3.创建白名单

在server2，server3上执行




mysql>CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='RKYSrkys123!@#' FOR CHANNEL 'group_replication_recovery';

mysql>set global group_replication_ip_whitelist="127.0.0.1/32,172.18.100.0/24";

mysql>set global group_replication_allow_local_disjoint_gtids_join=ON;

mysql>START GROUP_REPLICATION;
mysql>SELECT * FROM performance_schema.`replication_group_members`;




查看数据库状态


mysql组复制安装


查看数据库的主从状态




SELECT MEMBER_ID,MEMBER_HOST,MEMBER_PORT,MEMBER_STATE,IF(global_status.VARIABLE_NAME IS NOT NULL,'PRIMARY','SECONDARY') AS MEMBER_ROLE FROM performance_schema.replication_group_members  
  LEFT JOIN performance_schema.global_status ON global_status.VARIABLE_NAME = 'group_replication_primary_member' AND global_status.VARIABLE_VALUE = replication_group_members.MEMBER_ID;




mysql组复制安装


使用host那么可以查看机器名与ip的对应。


命令行获取主机的host




mysql -h127.0.0. -uroot -proot -e "SELECT * FROM performance_schema.replication_group_members WHERE MEMBER_ID = (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member')" |awk 'NR==2{print}'|awk -F" " '{print $3}'




如果登录失败，可以设置root登录权限


grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;


在任意数据库上添加用户，并创建数据库




create user 'studyuser'@'%' identified by 'Study123$%^';

flush privileges;

create database citystudy DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

grant all privileges on `citystudy`.* to 'studyuser'@'%' identified by 'Study123$%^';

flush privileges;




节点宕机重启




systemctl restart mysqld




mysql重启后，并不会自动加入节点,需要重新执行加入节点命令，主节点


问题处理


服务器长期处于RECOVERING状态（这里没有成功，后续需要跟踪）


一种是因为ip域名或者防火墙导致的连接不上问题。


还有一种是服务器加入集群时，事物就不同步，导致同步失败。 这里处理一下第二种情况：


在集群服务器上分别执行，查看每个服务器的事物状态()




mysql>select * from performance_schema.replication_group_member_stats \G ;






正常的服务器


*************************** 1. row ***************************
                      CHANNEL_NAME: group_replication_applier
                           VIEW_ID: 15452040099672487:7
                         MEMBER_ID: 24f6893f-00fc-11e9-aefc-005056ac3343
       COUNT_TRANSACTIONS_IN_QUEUE: 0
        COUNT_TRANSACTIONS_CHECKED: 1
          COUNT_CONFLICTS_DETECTED: 0
COUNT_TRANSACTIONS_ROWS_VALIDATING: 2
TRANSACTIONS_COMMITTED_ALL_MEMBERS: 157be252-2b71-11e6-b8f4-00212889f856:1-115:1000003-1000011:2000003,
24f9586e-00fc-11e9-b2e0-005056ac1e09:1-13
    LAST_CONFLICT_FREE_TRANSACTION: 157be252-2b71-11e6-b8f4-00212889f856:115


RECOVERING的服务器


*************************** 1. row ***************************
                      CHANNEL_NAME: group_replication_applier
                           VIEW_ID: 15452040099672487:7
                         MEMBER_ID: 24f9586e-00fc-11e9-b2e0-005056ac1e09
       COUNT_TRANSACTIONS_IN_QUEUE: 6
        COUNT_TRANSACTIONS_CHECKED: 0
          COUNT_CONFLICTS_DETECTED: 0
COUNT_TRANSACTIONS_ROWS_VALIDATING: 0
TRANSACTIONS_COMMITTED_ALL_MEMBERS:
    LAST_CONFLICT_FREE_TRANSACTION:




停止有问题的服务器的组同步，然后重新设置事物号，之后在重新加入集群




set global gtid_purged='157be252-2b71-11e6-b8f4-00212889f856:1-115:1000003-1000011:2000003,24f9586e-00fc-11e9-b2e0-005056ac1e09:1-13'






上一篇：富文本 SpannableString Span

下一篇：http server v0.1_http_reponse.c