mysql的主从复制、GTID配置、读写分离、半同步复制

最新推荐文章于 2022-09-21 16:56:11 发布

疾风飞魇

最新推荐文章于 2022-09-21 16:56:11 发布

阅读量227

点赞数

本文链接：https://blog.csdn.net/weixin_45426401/article/details/102706940

版权

server1（172.25.27.1）	mysql主机
server2（172.25.27.2）	mysql从机
server3（172.25.27.3）	mysql-proxy调度器

主从复制

主机server（172.25.27.1）：
1.下载mysql的rpm包

tar xf mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar

在这里插入图片描述
2.安装需要的包

yum install mysql-community-client-5.7.24-1.el7.x86_64.rpm mysql-community-common-5.7.24-1.el7.x86_64.rpm mysql-community-libs-5.7.24-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm mysql-community-server-5.7.24-1.el7.x86_64.rpm -y

安装后会替换mariadb相关的库文件
在这里插入图片描述
3.修改配置文件，配合官方文档看
负责在主、从服务器传输各种修改动作的媒介是主服务器的二进制变更日志，这个日志记载着需要传输给从服务器的各种修改动作。因此，主服务器必须激活二进制日志功能。从服务器必须具备足以让它连接主服务器并请求主服务器把二进制变更日志传输给它的权限

vim /etc/my.cnf
log-bin=mysql-bin
server-id=1

在这里插入图片描述
server2只写server-id=2

4.启动mysq

systemctl start mysqld

5.查看生成的密码
cat /var/log/mysqld.log | grep password

在这里插入图片描述
使用这个密码可以登陆，但不能操作
mysql -uroot -p

6.重置密码为 Westos+001

mysql_secure_installation

在这里插入图片描述

server1登陆
7.创建并授权用来做复制的用户
在Master的数据库中建立一个备份帐户：每个slave使用标准的MySQL用户名和密码连接master。进行复制操作的用户会授予REPLICATION SLAVE权限。用户名的密码都会存储在文本文件master.info中

mysql -uroot -pWestos+001
# 创建一个可登录数据库的用户
grant replication slave on *.* to repl@'172.25.27.%' identified by 'Westos+001';

#查看master状态
show master status;
在这里插入图片描述
从机server2（172.25.27.2）
安装数据库

tar xf mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar

yum install mysql-community-client-5.7.24-1.el7.x86_64.rpm mysql-community-common-5.7.24-1.el7.x86_64.rpm mysql-community-libs-5.7.24-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm mysql-community-server-5.7.24-1.el7.x86_64.rpm -y

vim /etc/my.cnf

server-id=2

systemctl start mysqld

# 重置密码
mysql_secure_installation

在物理机上尝试连接master的数据库，测试repl帐号
能登陆则说明server1配置成功

mysql -h 172.25.27.1 -urepl -pWestos+001

可以登录，但是查看不到任何信息，因为没有权限

用root用户登陆数据库
在server2上配置master信息

指定从机同步的主机，同步时使用的用户，同步的地点
change master to master_host='172.25.27.1',master_user='repl',master_password='Westos+001',master_log_file='mysql-bin.000002',master_log_pos=691;
#其中master_log_file和maMASTER_LOG_POS的值为0，因为它是日志的开始位置ster_log_pos写在server1上执行show master status看到的信息

开始复制

start slave;

在这里插入图片描述

查看主从复制状态

show slave status\G

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

有这两个参数说明配置成功
在这里插入图片描述

测试
server1

create databases westos

use westos;
create table usertb(
    -> username varchar(10) not null,
    -> password varchar(10) not null)
    -> ;

desc usertb;

在这里插入图片描述

insert into usertb values ('user1','123')

select * from usertb;

在这里插入图片描述
server2直接查看

在这里插入图片描述

基于GTID的主从复制

由于同一事务的GTID在所有节点上的值一致
我们都不需要知道GTID的具体值
‘前提：需要做好前面的binlog复制’
在传统的复制里面，当发生故障，需要主从切换，需要找到binlog和pos点，然后将主节点指向新的主节点，相对来说比较麻烦，也容易出错。在MySQL 5.6里面，不用再找binlog和pos点，我们只需要知道主节点的ip，端口，以及账号密码就行，因为复制是自动的，MySQL会通过内部机制GTID自动找点同步

从服务器连接到主服务器之后，把自己执行过的GTID(Executed_Gtid_Set)<SQL线程> 、获取到的GTID(Retrieved_Gtid_Set）<IO线程>发给主服务器，主服务器把从服务器缺少的GTID及对应的transactions发过去补全即可。当主服务器挂掉的时候，找出同步最成功的那台从服务器，直接把它提升为主即可。如果硬要指定某一台不是最新的从服务器提升为主，先change到同步最成功的那台从服务器，等把GTID全部补全了，就可以把它提升为主了

主库server1：

vim /etc/my.cnf
gtid_mode=ON
enforce-gtid-consistency=true

在这里插入图片描述

mysqlbinlog mysql-bin.000002	##可以看到之前做的操作都在里面

在这里插入图片描述
查看server1的uuid

cat auto.cnf 	##看到server1的uuid

在这里插入图片描述

systemctl restart mysqld

从库server2：

vim /etc/my.cnf

gtid_mode=ON
enforce-gtid-consistency=true

在这里插入图片描述
重启mysql

systemctl restart mysqld

查看主从复制是否正常

cd /var/lib/mysql
cat relay-log.info

在这里插入图片描述

先停止复制
在这里插入图片描述

修改master信息
在这里插入图片描述
查看状态，可以看到下面两个参数是空的

在server1上插入数据

再次在server2上查看状态

再查看gtid模式复制的起始和结束位置

mysql-proxy实现读写分离

#读写分离可以用很多软件实现：mysql-proxy 、MyCat 、Amoeba

用server3来做proxy，1和2一个读一个写
做实验之前，先做好server1和server2的gtid主从复制

server3上：

#先关闭之前的mysql，因为proxy也用3306端口
tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local
cd /usr/local/
ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy #软链接便于访问
在这里插入图片描述

cd /usr/local/mysql-proxy/bin
./mysql-proxy --help    ##查看帮助
./mysql-proxy --help-proxy      ##查看proxy的帮助
./mysql-proxy --help-all        ##查看所有帮助

cd /usr/local/mysql-proxy
mkdir conf      ##建立配置文件目录

#编辑配置文件（自己手动新建）

vim mysql-proxy.conf

[mysql-proxy]
proxy-address=0.0.0.0:3306
proxy-backend-addresses=172.25.136.1:3306
proxy-read-only-backend-addresses=172.25.136.2:3306
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid
plugins=proxy
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log
log-level=debug
keepalive=true
daemon=true

在这里插入图片描述

#创建日志目录

mkdir /usr/local/mysql-proxy/log

在这里插入图片描述
#修改lua脚本

vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

min_idle_connections = 1,       ##把原来的4和8改为1和2，默认超过4个连接才会启动读写分离，改为1个好测试
max_idle_connections = 2,

#启动mysql-proxy

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

启动报错：
2019-03-15 14:15:04: (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)
2019-03-15 14:15:04: (message) Initiating shutdown, requested from mysql-proxy-cli.c:328
2019-03-15 14:15:04: (message) shutting down normally, exit code is: 1

在这里插入图片描述

#因为配置文件权限过大

chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf

再启动，正常
在这里插入图片描述
#查看日志

cat /usr/local/mysql-proxy/log/mysql-proxy.log
看到两个节点都加进来了

2019-03-15 15:07:22: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=2521 alive
2019-03-15 15:07:22: (debug) chassis-unix-daemon.c:157: waiting for 2521
2019-03-15 15:07:22: (debug) chassis-unix-daemon.c:121: we are the child: 2521
2019-03-15 15:07:22: (critical) plugin proxy 0.8.5 started
2019-03-15 15:07:22: (debug) max open file-descriptors = 1024
2019-03-15 15:07:22: (message) proxy listening on port 0.0.0.0:3306
2019-03-15 15:07:22: (message) added read/write backend: 172.25.27.1:3306
2019-03-15 15:07:22: (message) added read-only backend: 172.25.27.2:3306

在这里插入图片描述

#在server1上授权新用户读写权限

mysql> grant insert,update,select on *.* to sfj@'%' identified by 'Westos+001';

mysql> flush privileges;

在这里插入图片描述

#在物理机上打开一个shell来连接数据库
[kiosk@foundation0 ~]$ mysql -h 172.25.27.3 -usfj -pWestos+001

#然后在server3上看到连接已经建立
[root@server3 log]# lsof -i:3306

在这里插入图片描述

#在物理机上再打开一个shell来连接数据库（第二个）
#再到server3上查看
[root@server3 log]# lsof -i:3306

‘发现两次连接都指向server1’
在这里插入图片描述

#在物理机上再打开一个shell来连接数据库（第三个）
#再到server3上查看
[root@server3 log]# lsof -i:3306
‘这次连接到了server2，说明读写分离启用’
在这里插入图片描述

#测试读写分离（此时应该关闭主从复制，否则无法验证读写分离功能）

现在到server2上关闭复制

mysql> stop slave;

此时server1中的数据
在这里插入图片描述
在物理机上插入数据
MySQL [westos]> insert into usertb values (‘user4’,‘444’);
Query OK, 1 row affected (0.01 sec)

在这里插入图片描述
在server3查看数据
只能查看到添加前的数据

在server1中查看，可以看到刚添加的数据

在server2中查看，只能查看添加前的数据
重新开启主从复制
数据同步后即可正常查看

此时server3也查看到了server2中同步的数据

在这里插入图片描述

半同步复制

异步复制（Asynchronous replication）

MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。

全同步复制（Fully synchronous replication）

指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，>所以全同步复制的性能必然会收到严重的影响。

半同步复制（Semisynchronous replication）

介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收>到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。

在2010年MySQL 5.5版本之前，一直采用的是这种异步复制的方式。主库的事务执行不会管备库的同步进度，如果备库落后，主库不幸crash，那么就会导致数据丢失。于是在MySQL在5.5中就顺其自然地引入了半同步复制，主库在应答客户端提交的事务前需要保证至少一个从库接收并写到relay log中

##对照官方文档 https://dev.mysql.com/doc/refman/5.7/en/replication-semisync-installation.html
1）正常的复制为：事务一（t1）写入binlog buffer；dumper线程通知slave有新的事务t1；binlog buffer进行checkpoint；slave的io线程接收到t1并写入到自己的的relay log；slave的sql线程写入到本地数据库。这时，master和slave都能
看到这条新的事务，即使master挂了，slave可以提升为新的master。

2）异常的复制为：事务一（t1）写入binlog buffer；dumper线程通知slave有新的事务t1；binlog buffer进行checkpoint；slave因为网络不稳定，一直没有收到t1；master挂掉，slave提升为新的master，t1丢失。

3）很大的问题是：主机和从机事务更新的不同步，就算是没有网络或者其他系统的异常，当业务并发上来时，slave因为>要顺序执行master批量事务，导致很大的延迟。

为了弥补以上几种场景的不足，MySQL从5.5开始推出了半同步复制。相比异步复制，半同步复制提高了数据完整性，因为>很明确知道，在一个事务提交成功之后，这个事务就至少会存在于两个地方。即在master的dumper线程通知slave后，增加
了一个ack（消息确认），即是否成功收到t1的标志码，也就是dumper线程除了发送t1到slave，还承担了接收slave的ack>工作。如果出现异常，没有收到ack，那么将自动降级为普通的复制，直到异常修复后又会自动变为半同步复制。

1.在主库上（server1）安装插件

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

2.查看插件是否安装

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';

3.激活插件

mysql> SET GLOBAL rpl_semi_sync_master_enabled =1;

在这里插入图片描述

4.在slave节点上（server2）也安装插件
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’;

5.激活插件
SET GLOBAL rpl_semi_sync_slave_enabled =1;
在这里插入图片描述

6.重启IO线程使半同步生效

mysql> STOP SLAVE IO_THREAD;
mysql> START SLAVE IO_THREAD;

在这里插入图片描述

7.在master节点上查看
mysql> show status like ‘%rpl%’;
看到
| Rpl_semi_sync_master_status | ON |
表示已开启
在这里插入图片描述

mysql> show variables like '%rpl%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         | 是否开启半同步
| rpl_semi_sync_master_timeout              | 10000      | 切换复制的timeout
| rpl_semi_sync_master_trace_level          | 32         | 用于开启半同步复制模式时的调试级别,默认是32
| rpl_semi_sync_master_wait_for_slave_count | 1          | 至少有N个slave接收到日志
| rpl_semi_sync_master_wait_no_slave        | ON         | 是否允许master 每个事物提交后都要等待slave的receipt信号。默认为on
| rpl_semi_sync_master_wait_point           | AFTER_SYNC | 等待的point
| rpl_stop_slave_timeout                    | 31536000   | 控制stop slave 的执行时间，在重放一个大的事务的时候,突然执行stop slave,命令 stop slave会执行很久,这个时候可能产生死锁或阻塞,严重影响性能
+-------------------------------------------+------------+

https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html #具体参数可以看官方文档
默认延迟10s

8.在slave节点上查看

mysql> show variables like '%rpl%';
+---------------------------------+----------+
| Variable_name                   | Value    |
+---------------------------------+----------+
| rpl_semi_sync_slave_enabled     | ON       |是否开启半同步
| rpl_semi_sync_slave_trace_level | 32       |用于开启半同步复制模式时的调试级别,默认是32
| rpl_stop_slave_timeout          | 31536000 |
+---------------------------------+----------+

在这里插入图片描述
9.停止slave上的io线程再测试

mysql> STOP SLAVE IO_THREAD;

10.在master上插入数据

mysql> use westos;
mysql> insert into usertb values ('user7','777');
Query OK, 1 row affected (10.01 sec)    #等待10s才成功，因为上面超时时间是10s，10s后如果没有收到slave节点的返回，就会切换到异步复制

在这里插入图片描述

查看半同步状态也是off，待同步的事务各增加了1

mysql> show status like '%rpl%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 2     | 原本是1，变成了2
| Rpl_semi_sync_master_no_tx                 | 3     | 原本是2，变成了3
| Rpl_semi_sync_master_status                | OFF   |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+

在这里插入图片描述

再次插入时就不会延迟，因为已经是异步了
mysql> insert into usertb values (‘user8’,‘888’);
Query OK, 1 row affected (0.00 sec)

在这里插入图片描述

11.slave上开启io线程

mysql> START SLAVE IO_THREAD;

查看进程
mysql> show processlist;输出结果显示了有哪些线程在运行
########################################################################
说明各列的含义和用途
id列:一个标识
user列: 显示当前用户，如果不是root，这个命令就只显示你权限范围内的sql语句
host列:显示这个语句是从哪个ip 的哪个端口上发出的。可用来追踪出问题语句的用户
db列:显示这个进程目前连接的是哪个数据库
command列:显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）
time列:此这个状态持续的时间，单位是秒
state列:显示使用当前连接的sql语句的状态

########################################################################

在这里插入图片描述

多主模式

server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE #\u5173\u95edbinlog\u6821\u9a8c
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW #\u7ec4\u590d\u5236\u4f9d\u8d56\u57fa\u4e8e\u884c\u7684\u590d\u5236

transaction_write_set_extraction=XXHASH64
group_replication_group_name=“d3a83f34-f2f6-11e9-af95-52540089708b”
group_replication_start_on_boot=off
loose-group_replication_local_address=“172.25.0.1:33061”
group_replication_group_seeds= “172.25.0.1:33061,172.25.0.2:33061,172.25.0.3:33061”
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist=“127.0.0.1,172.25.0.0/24”
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF

SET SQL_LOG_BIN=0;

CREATE USER rpl_user@’%’ IDENTIFIED BY ‘’

GRANT REPLICATION SLAVE ON . TO rpl_user@’%’;

FLUSH PRIVILEGES;

SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER=‘rpl_user’,
MASTER_PASSWORD=’'westos+001"
FOR CHANNEL ‘group_replication_recovery’;

INSTALL PLUGIN group_replication SONAME ‘group_replication.so’;
set global group_replication_allow_local_disjoint_gtids_join=on;

START GROUP_REPLICATION;

server-uuid=ee9fc2ef-f2ee-11e9-9591-525400fb8fdb

疾风飞魇

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql的主从复制、GTID配置、读写分离、半同步复制

mysql的主从同步tar xf mysql-5.7.24-1.el7.x86_64.rpm-bundle.taryum install mysql-community-client-5.7.24-1.el7.x86_64.rpm mysql-community-common-5.7.24-1.el7.x86_64.rpm mysql-community-libs-5.7.24-1.el7.x...
复制链接

扫一扫