mysql同步

最新推荐文章于 2022-05-19 14:44:01 发布

GodDevin

最新推荐文章于 2022-05-19 14:44:01 发布

阅读量498

点赞数

分类专栏： linux基础

本文链接：https://blog.csdn.net/GodDevin/article/details/80057859

版权

linux基础专栏收录该内容

64 篇文章 0 订阅

订阅专栏

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解析的问题

GodDevin

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql同步

1.主从复制[root@server2 ~]# tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar mysql-community-client.x86_64 0:5.7.17-1.el6 mysql-community-common.x86_64 0:5.7.17-1....
复制链接

扫一扫

专栏目录