Slave服务器 io线程以redhat身份(密码认证成功)去拷贝master服务器的二进制日志(记载了master做了些什么,比如新建数据库等),到自己的relay-log中,slave的sql线程会去读取拷贝来的二进制日志,查看master做了什么,然后再进行相同的动作,达到数据同步:
多线程并行复制5.6就支持,(slave的sql多线程),5.7基于数据库(有几个数据库则有几个线程)
1.主从复制
一.数据库的安装
1.下载安装
实验环境 :master–server1–172.25.66.1
slave–server2–172.25.66.2
以下操作再server1上:
[root@server1 ~]# tax xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
[root@server1 ~]# ls ##解压出这9个包
mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
mysql-community-client-5.7.17-1.el6.x86_64.rpm
mysql-community-common-5.7.17-1.el6.x86_64.rpm
mysql-community-devel-5.7.17-1.el6.x86_64.rpm
mysql-community-embedded-5.7.17-1.el6.x86_64.rpm
mysql-community-embedded-devel-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm
mysql-community-server-5.7.17-1.el6.x86_64.rpm
mysql-community-test-5.7.17-1.el6.x86_64.rpm
[root@server1 ~]# yum install mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm -y ##安装这几个
2.启动数据库,初始化
[root@server1 ~]# /etc/init.d/mysqld start ##启动
Initializing MySQL database: [ OK ]
Installing validate password plugin: [ OK ]
Starting mysqld: [ OK ]
[root@server1 ~]# grep "temporary password" /var/log/mysqld.log ##过滤临时密码
[root@server1 ~]# mysql_secure_installation ##初始化设置
设置新密码,这里设置为Westos+007(数据库密码要求很高)
[root@server1 ~]# mysql -p ##登陆检测一下
3.slave数据库的安装同master
二.主从同步的基本配置
1.主(以下称master)的配置
[root@server1 ~]# vim /etc/my.cnf
29 server-id=1##服务器标识,正整数,2的32次方减1
30 log-bin=mysql-bin##二进制日志
完成后保存退出,重启数据库:
[root@server1 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
进入数据库
mysql> show master status\G;##查看master状态
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> grant REPLICATION SLAVE ON *.* to redhat@'172.25.66.%' identified by 'Westos+007';授权slave对任何数据库的任何表操作,以redhat身份,从172.25.66.0/24网段登陆,密码‘Westos+007‘
记录file和position值
mysql> show master status\G; ##再次查看master状态
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 449
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
2.从(以下称slave)的配置
[root@server2 ~]# vim /etc/my.cnf
server-id=2 ##不能重复
进入数据库
[root@server2 ~]# mysql -pWestos+007
mysql> show slave status\G;
Empty set (0.00 sec)
mysql> change master to master_host='172.25.66.1',master_user='redhat',master_password='Westos+007';##和master建立认证联系
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql> start slave;##开启slave
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;##查看状态
IO线程和sql线程开启 (主要查看这两项)
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
slave io线程 以redhat身份拷贝(多线程)master的log-bin 到自己的relay-log,slave的sql线程(单线程)读取relay-log,照着master的事务再做一阿遍)
实验:
master新建个数据库westos,slave会自动同步
master:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
mysql> create database westos;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| westos |
+--------------------+
slave:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| westos |
+--------------------+
5 rows in set (0.00 sec)
目录/var/lib/mysql下是二进制文件的保存目录
2.gtid主从复制
Gtid全称global transaction identifiers全局事务标志
一个事务对应唯一的一个id(mysql的语句),一个gtid在一个服务器(master)上只会也只能执行一次,在从库上(slave)也只会出现一次
Gtid由uuid+tid组成,uuid是mysql实例的唯一标识,tid代表了该实例(理解为语句,执行的动作)上已经提交的事务数量,并且会随着事务提交单调递增:
1.master
配置文件
[root@server1 ~]# vim /etc/my.cnf
29 server-id=1
30 log-bin=mysql-bin
31
32 gtid_mode=ON
33 enforce-gtid-consistency=true
[root@server2 ~]# /etc/init.d/mysqld restart
slave配置
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
[root@server2 ~]# vim /etc/my.cnf
29 server-id=2
30 gtid_mode=ON
31 enforce-gtid-consistency=true
[root@server2 ~]# /etc/init.d/mysqld restart
实验:
master再westos库中新建表linux;
master
[root@server1 ~]# mysql -pWestos+007
mysql> create table westos.linux(
-> username varchar(15) not null,
-> password varchar(15) not null
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> desc westos.linux;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(15) | NO | | NULL | |
| password | varchar(15) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
slave
[root@server2 ~]# mysql -pWestos+007
mysql> use westos;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+------------------+
| Tables_in_westos |
+------------------+
| linux |
+------------------+
mysql> desc westos.linux;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(15) | NO | | NULL | |
| password | varchar(15) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
四.slave并行复制
并行复制就是多个事务同时进行复制,但并行复制存在salve端sql线程运行时,master不断写入,会出现问题,所有有锁表机制
1.并行复制可以优化把/var/lib/mysql/relay-log.info优化为 mysql 库中的表,此时此文件将不存在
[root@server2 ~]# cat /var/lib/mysql/relay-log.info
7
./server2-relay-bin.000007
1651
mysql-bin.000004
1438
0
0
1
2.编辑slave配置文件
[root@server2 ~]# vim /etc/my.cnf
29 server-id=2
30 gtid_mode=ON
31 enforce-gtid-consistency=true
32
33 slave-parallel-type=LOGICAL_CLOCK##锁表
34 slave-parallel-workers=16##最大线程16
35 ##下边的是优化项
36 master_info_repository=TABLE##(优化)将数据存进表中(内存)不存入磁盘中(提速)
37 relay_log_info_repository=TABLE
38 relay_log_recovery=ON
[root@server2 ~]# /etc/init.d/mysqld restart
3.进入slave数据库:
mysql> show processlist;##查看线程16个
4.slave查看优化项
mysql> use mysql;
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info | ##这三个
| slave_relay_log_info | ##这三个
| slave_worker_info | ##这三个
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
4.半同步
在异步复制的情况下,mysql master server 会将自己的二进制日志通过copy线程传输给slave以后,master会自动返回数据给客户端,而不管slave上是否接收到了这个日志。
半同步,当master把日志传给slave时,确保slave收到了日志,才会返回数据给客户端。
1.master加载模块
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';##master加载模块
mysql> show plugins;##最后有rpl_semi_sync_master | ACTIVE
mysql> show variables like '%rpl_semi%';##查看加载模块信息
mysql> set global rpl_semi_sync_master_enabled=1;##开启加载的master模块 ,global全局
mysql> show variables like '%rpl_semi%';
2.slave端
mysql> install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so'; ##加载slave模块
mysql> set global rpl_semi_sync_slave_enabled=1; ##开启slave模块
mysql> show variables like '%rpl_semi%'; ##查看信息
mysql> STOP SLAVE IO_THREAD; ##关闭slaveIO线程
mysql> START SLAVE IO_THREAD; ##开启slaveIO线程
查看slave状态
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
实验:master在westos中的user_tb中添加信息,等待slave响应,因为等待,所以返回客户端时间较长;但下边的好像不是很长,这是由于之前并行实验有16个线程的缘故(不做演示了)
配置slave的/etc/my.cnf 注释并行复制配置参数,重启服务
[root@server2 ~]# vim /etc/my.cnf
server-id=2
gtid_mode=ON
enforce-gtid-consistency=true
#slave-parallel-type=LOGICAL_CLOCK
#slave-parallel-workers=16
#
#master_info_repository=TABLE
#relay_log_info_repository=TABLE
#relay_log_recovery=ON
[root@server2 ~]# /etc/init.d/mysqld restart
matser再次插入会发现返回客户端时间较长(不做演示了)
关闭slave的半同步,master进行事务返回客户端时间又短了
mysql> set global rpl_semi_sync_slave_enabled=0;
5单点.多个salve,a -> b -> c
在上边实验的基础上在增加slave2(server3)
slave(server2)授权以下操作在server2上
实验前开启gtid
[root@server2 ~]# vim /etc/my.cnf
server-id=2
gtid_mode=ON
enforce-gtid-consistency=true
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
[root@server2 ~]# /etc/init.d/mysqld restart
[root@server2 ~]# mysql -pWestos+007
mysql> grant REPLICATION SLAVE on *.* to redhat@'172.25.66.%' identified by 'Westos+007';
mysql> flush privileges;
quit退出后
备份数据库
[root@server2 ~]# mysqldump -p westos >/mnt/westos.sql
slave(host2) 编辑配置文件
[root@server2 ~]# vim /etc/my.cnf
40 log-slave-updates=ON
41 log-bin=mysql-bin
[root@server2 ~]# /etc/init.d/mysqld restart
将备份的数据库传给slave2(serever3)
[root@server2 ~]# scp /mnt/westos.sql server3:/mnt/
slave2(server3)以下操作在server3
[root@server3 ~]# vim /etc/my.cnf
29 server-id=3
30 gtid_mode=ON
31 enforce-gtid-consistency=true
[root@server3 ~]# /etc/init.d/mysqld restart
创建数据库,导入数据库,进入数据库
[root@server3 ~]# mysqladmin -p create westos;
[root@server3 ~]# mysql -p westos </mnt/westos.sql
进入数据库后,指定master为slave(server2),指定认证身份密码,等信息;
mysql> change master to master_host='172.25.66.1',master_user='redhat',master_password='Westos+007'
mysql> start slave;
mysql> show slave status\G;
查看serveert3的slave状态io线程和sql线程开启,则成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
server1(master)进行一条事务,server2和server3同步
server1
mysql> insert into westos.linux value ('www','222');
server2
mysql> select * from westos.linux;
server3
mysql> select * from westos.linux;
6.数据库读写分离
分区分表
mysql中间件https://blog.csdn.net/javacodekit/article/details/76559112
这里以mysql-proxy为例说明,类似的中间件还有Atlas(官网:https://github.com/Qihoo360/Atlas/blob/master/README_ZH.md)
Mysql router(官网:http://dev.mysql.com/doc/mysql-router/en/)
Mycat(官网:http://www.mycat.org.cn/)
Cobar(官网:https://github.com/alibaba/cobar/wiki)
Amoeba(官网:http://docs.hexnova.com/amoeba/)
等
1.下载,解压mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@server3 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/##解压到/usr/local下
[root@server3 ~]# mv /usr/local/mysql-proxy-0.8.5-linux-el6-x86-64bit/ /usr/local/mysql-proxy##简化名字
2.找到它的启动脚本
/usr/local/bin/mysql-proxy
[root@server3 ~]# cd /usr/local/mysql-proxy/bin/
[root@server3 bin]# ls
mysql-binlog-dump mysql-myisam-dump mysql-proxy
3.在/usr/local/mysql-proxy新建配置目录conf,进入新建的目录内,编写配置文件mysql-proxy.conf
[root@server3 conf]# /usr/local/mysql-proxy/bin/mysql-proxy --help-proxy##获得帮助说明信息
Usage:
mysql-proxy [OPTION...] - MySQL Proxy
proxy-module
-P, --proxy-address=<host:port> listening address:port of the proxy-server (default: :4040)
-r, --proxy-read-only-backend-addresses=<host:port> address:port of the remote slave-server (default: not set)
-b, --proxy-backend-addresses=<host:port> address:port of the remote backend-servers (default: 127.0.0.1:3306)
--proxy-skip-profiling disables profiling of queries (default: enabled)
--proxy-fix-bug-25371 fix bug #25371 (mysqld > 5.1.12) for older libmysql versions
-s, --proxy-lua-script=<file> filename of the lua script (default: not set)
--no-proxy don't start the proxy-module (default: enabled)
--proxy-pool-no-change-user don't use CHANGE_USER to reset the connection coming from the pool (default: enabled)
--proxy-connect-timeout connect timeout in seconds (default: 2.0 seconds)
--proxy-read-timeout read timeout in seconds (default: 8 hours)
--proxy-write-timeout write timeout in seconds (default: 8 hours)
[root@server3 bin]# cd /usr/local/mysql-proxy/
[root@server3 mysql-proxy]# mkdir conf
[root@server3 mysql-proxy]# cd conf/
[root@server3 conf]# vim mysql-proxy.conf##配置文件中不能有空格,否则会报错
1 [mysql-proxy]
2 user=root ##root身份运行
3 proxy-address=172.25.66.3:3306 ###mysql中间代理件
4 proxy-backend-addresses=172.25.66.1:3306 ##主
5 proxy-read-only-backend-addresses=172.25.66.2:3306 ##读
6 proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua ##lua脚本
7 log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log ##日志文件(手动新建logs)
8 plugins=proxy
9 log-level=debug ##日志级别
10 keepalive=true ##持续连接
11 daemon=true ##后台运行
[root@server3 conf]# cd ..
[root@server3 mysql-proxy]# pwd
/usr/local/mysql-proxy
[root@server3 mysql-proxy]# mkdir logs
[root@server3 mysql-proxy]# ls
bin conf include lib libexec licenses logs share
修改下lua脚本,方便实验效果
[root@server3 mysql-proxy]# cd /usr/local/mysql-proxy/share/doc/mysql-proxy
[root@server3 mysql-proxy]# vim rw-splitting.lua
40 min_idle_connections = 1,
41 max_idle_connections = 2,
启动msql-proxy
[root@server3 mysql-proxy]# /usr/local/mysql-proxy/bin/mysql-proxy ##卡住的话打入后台crtl+z
2018-06-05 09:39:50: (critical) plugin proxy 0.8.5 started
^Z
[1]+ Stopped /usr/local/mysql-proxy/bin/mysql-proxy
[root@server3 mysql-proxy]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf##会报错
2018-06-05 09:40:27: (critical) mysql-proxy-cli.c:326: loading config from '/usr/local/mysql-proxy/conf/mysql-proxy.conf' failed: permissions of /usr/local/mysql-proxy/conf/mysql-proxy.conf aren't secure (0660 or stricter required)
2018-06-05 09:40:27: (message) Initiating shutdown, requested from mysql-proxy-cli.c:328
2018-06-05 09:40:27: (message) shutting down normally, exit code is: 1
##解决方法
[root@server3 mysql-proxy]# chmod 0660 /usr/local/mysql-proxy/conf/mysql-proxy.conf
[root@server3 mysql-proxy]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
关闭代理的mysql服务3306端口空出来
[root@server3 mysql-proxy]# /etc/init.d/mysqld stop
[root@server3 mysql-proxy]# netstat -antlp | grep 3306
tcp 0 0 172.25.66.3:3306 0.0.0.0:* LISTEN 2349/mysql-proxy
[root@server3 mysql-proxy]# ps aux ##看着两条
root 2348 0.0 0.1 37124 840 ? S 09:52 0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-fil
root 2349 0.0 0.2 39228 1456 ? S 09:52 0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-fil
master授权
[root@server1]# mysql -pWestos+007
mysql> grant all on *.* to root@'%' identified by 'Westos+007';
物理机登陆代理server3
插入数据
[root@foundation66 ~]# mysql -h172.25.66.3 -P3306 -pWestos+007
MySQL [(none)]> insert into westos.linux value('zzz','231');
MySQL [(none)]> select * from westos.linux;
+----------+----------+
| username | password |
+----------+----------+
| qwe | 123 |
| asd | 111 |
| as | 222 |
| www | 222 |
| zzz | 231 |
+----------+----------+
servre1和server2都可以看到
这说明master(server1)和slave(server2)都有读的功能
将srevre1(master)关闭,物理机连接代理server3发现写不进去数据
[root@server1 ~]# /etc/init.d/mysqld stop
物理机
MySQL [(none)]> insert into westos.linux value('aaa','421');
ERROR 2013 (HY000): Lost connection to MySQL server during query
把server1(master)数据库开启,关闭slave(server2),物理机插入数据,会发现写入成功,再到server1(master上查看,发现写进去了),这说明master有读和写的功能,而slave只有读的功能
[root@server1 ~]# /etc/init.d/mysqld start
[root@server2 ~]# /etc/init.d/mysqld stop
物理机
MySQL [(none)]> insert into westos.linux value('bbb','321');
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> select * from westos.linux;
+----------+----------+
| username | password |
+----------+----------+
| qwe | 123 |
| asd | 111 |
| as | 222 |
| www | 222 |
| zzz | 231 |
| aaa | 421 |
| bbb | 321 |
+----------+----------+
7 rows in set (0.00 sec)
[root@server1 ~]# mysql -pWestos+007
mysql> select * from westos.linux;
+----------+----------+
| username | password |
+----------+----------+
| qwe | 123 |
| asd | 111 |
| as | 222 |
| www | 222 |
| zzz | 231 |
| aaa | 421 |
| bbb | 321 |
+----------+----------+
当开启slave时,数据同步,slave(serever2也会有记录)
[root@server2 ~]# /etc/init.d/mysqld start
[root@server2 ~]# mysql -pWestos+007
mysql> select * from westos.linux;
+----------+----------+
| username | password |
+----------+----------+
| qwe | 123 |
| asd | 111 |
| as | 222 |
| www | 222 |
| zzz | 231 |
| aaa | 421 |
| bbb | 321 |
+----------+----------+
说明
物理机连接server3(proxy),servere3此时指向master
[root@server3 mysql-proxy]# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 2349 root 10u IPv4 12704 0t0 TCP server3:mysql (LISTEN)
mysql-pro 2349 root 11u IPv4 12836 0t0 TCP server3:mysql->172.25.66.100:49144 (ESTABLISHED)
mysql-pro 2349 root 12u IPv4 12837 0t0 TCP server3:33366->server1:mysql (ESTABLISHED)
物理机再开一个终端,连接serever3(proxy),server3指向matser
[root@server3 mysql-proxy]# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 2349 root 10u IPv4 12704 0t0 TCP server3:mysql (LISTEN)
mysql-pro 2349 root 11u IPv4 12836 0t0 TCP server3:mysql->172.25.66.100:49144 (ESTABLISHED)
mysql-pro 2349 root 12u IPv4 12837 0t0 TCP server3:33366->server1:mysql (ESTABLISHED)
mysql-pro 2349 root 13u IPv4 13065 0t0 TCP server3:mysql->172.25.66.100:49152 (ESTABLISHED)
mysql-pro 2349 root 14u IPv4 13066 0t0 TCP server3:33368->server1:mysql (ESTABLISHED)
物理机再开一个终端,连接server3(proxy),server3指向slave (还记得lua脚本的参数吗,连接proxy的最大客户数量为2,超出这个server3转向slave)
[root@server3 mysql-proxy]# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 2349 root 10u IPv4 12704 0t0 TCP server3:mysql (LISTEN)
mysql-pro 2349 root 11u IPv4 12836 0t0 TCP server3:mysql->172.25.66.100:49144 (ESTABLISHED)
mysql-pro 2349 root 12u IPv4 12837 0t0 TCP server3:33366->server1:mysql (ESTABLISHED)
mysql-pro 2349 root 13u IPv4 13065 0t0 TCP server3:mysql->172.25.66.100:49152 (ESTABLISHED)
mysql-pro 2349 root 14u IPv4 13066 0t0 TCP server3:33368->server1:mysql (ESTABLISHED)
mysql-pro 2349 root 15u IPv4 13092 0t0 TCP server3:mysql->172.25.66.100:49156 (ESTABLISHED)
mysql-pro 2349 root 16u IPv4 13093 0t0 TCP server3:33880->server2:mysql (ESTABLISHED)
7.组同步
一.环境
1.准备三台干净的mysql环境
server1 172.25.66.1
server2 172.25.66.2
server3 172.25.66.3
[root@server1 ~]# /etc/init.d/mysqld stop
[root@server1 ~]# rm -rf /var/lib/mysql/*
[root@server1 ~]# /etc/init.d/mysqld start
[root@server1 ~]# grep "temporary password" /var/log/mysqld.log
[root@server1 ~]# mysql -p
mysql> SET SQL_LOG_BIN=0; ##不记录日志信息
mysql> alter user root@localhost identified by 'Westos+007'; ##修改密码
mysql> flush privileges; ##刷新
mysql> quit
2.配置文件
server1
[root@server1 ~]# vim /etc/my.cnf
server-id=1
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=true
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="60904750-447a-11e8-893d-a088b44bd080"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.25.66.1:24901"
loose-group_replication_group_seeds= "172.25.66.1:24901,172.25.66.2:24901,172.25.66.3:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks=TRUE
loose-group_replication_ip_whitelist="172.25.66.0/24" ##白名单
[root@server1 ~]# /etc/init.d/mysqld restart ##重起
server2
server2的文件配置server-id不能和server1相同,42行要写自己的ip,此外和server1配置文件相同
[root@server2 ~]# vim /etc/my.cnf
server-id=2
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=true
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="60904750-447a-11e8-893d-a088b44bd080"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.25.66.2:24901"
loose-group_replication_group_seeds= "172.25.66.1:24901,172.25.66.2:24901,172.25.66.3:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks=TRUE
loose-group_replication_ip_whitelist="172.25.66.0/24"
[root@server2 ~]# /etc/init.d/mysqld restart
二.数据库操作
server1数据库操作
[root@server1 ~]# mysql -pWestos+007
mysql> SET SQL_LOG_BIN=0; ##不记录二进制日志
mysql> alter user root@localhost identified by 'Westos+007';
mysql> grant replication slave on *.* to repl@'%' identified by 'Westos+007';
mysql> flush privileges;
mysql> set sql_log_bin=1; ##记录二进制文件
mysql> change master to master_user='repl',master_password='Westos+007' for channel 'group_replication_recovery';
mysql> install plugin group_replication soname 'group_replication.so';
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;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | c4f8cff0-6e61-11e8-b06d-525400d36331 | server1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
server2数据库操作
[root@server2 ~]# mysql -pWestos+007
mysql> SET SQL_LOG_BIN=0; ##不记录二进制日志
mysql> alter user root@localhost identified by 'Westos+007';
mysql> grant replication slave on *.* to repl@'%' identified by 'Westos+007';
mysql> flush privileges;
mysql> set sql_log_bin=1; ##记录二进制文件
mysql> change master to master_user='repl',master_password='Westos+007' for channel 'group_replication_recovery';
mysql> install plugin group_replication soname 'group_replication.so';
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
mysql> start group_replication;
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | c00f93f7-6e60-11e8-968b-525400e96254 | server2 | 3306 | RECOVERING |
| group_replication_applier | c4f8cff0-6e61-11e8-b06d-525400d36331 | server1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
解决方法:因为之前做的主建冲突(数据名)所以host1要先清掉master的密码数据,再重新来一次
server1
mysql> stop group_replication;
mysql> reset master;
mysql> reset slave;
mysql> grant replication slave on *.* to repl@'%' identified by 'Westos+007';
mysql> change master to master_user='repl',master_password='Westos+007' for channel 'group_replication_recovery';
mysql> install plugin group_replication soname 'group_replication.so';
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;
server2清除环境重做:
[root@server2 ~]# /etc/init.d/mysqld stop
[root@server2 ~]# rm -rf /var/lib/mysql/*
[root@server2 ~]# /etc/init.d/mysqld start
[root@server2 ~]# grep 'temporary password' /var/log/mysqld.log
[root@server2 ~]# mysql -p
mysql> SET SQL_LOG_BIN=0; ##不记录二进制日志
mysql> alter user root@localhost identified by 'Westos+007';
mysql> grant replication slave on *.* to repl@'%' identified by 'Westos+007';
mysql> flush privileges;
mysql> set sql_log_bin=1; ##记录二进制文件
mysql> change master to master_user='repl',master_password='Westos+007' for channel 'group_replication_recovery';
mysql> install plugin group_replication soname 'group_replication.so';
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
mysql> start group_replication;
mysql> select * from performance_schema.replication_group_members;
再加个server3
mysql> SET SQL_LOG_BIN=0; ##不记录二进制日志
mysql> alter user root@localhost identified by 'Westos+007';
mysql> grant replication slave on *.* to repl@'%' identified by 'Westos+007';
mysql> flush privileges;
mysql> set sql_log_bin=1; ##记录二进制文件
mysql> change master to master_user='repl',master_password='Westos+007' for channel 'group_replication_recovery';
mysql> install plugin group_replication soname 'group_replication.so';
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
mysql> start group_replication;
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 4a4952ee-6e66-11e8-b7dd-525400e96254 | server2 | 3306 | ONLINE |
| group_replication_applier | b0f08ea3-6e67-11e8-a2be-525400858f07 | server3 | 3306 | ONLINE |
| group_replication_applier | c4f8cff0-6e61-11e8-b06d-525400d36331 | server1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
三台mysql服务器都没有westos数据库再server2上新建一个westos数据库
server1、server2、server3都一样
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
server3
mysql> create database westos;
server1、server2、server3都会出现
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| westos |
+--------------------+
注意彼此dns解析的问题