MySQL数据库主从复制与读写分离（图文详解！）

目录

前言

一：MySQL数据库主从复制与读写分离

1、什么是读写分离？

2、为什么要读写分离呢？

3、什么时候要读写分离？

 4、主从复制与读写分离

 5、mysql支持的复制类型

（1）STATEMENT

（2）ROW

（3）MIXED

6、主从复制的工作过程

7、MySQL 读写分离原理

8、目前较为常见的 MySQL 读写分离分为两种

 （1）基于程序代码内部实现

（2）基于中间代理层实现

二、MySQL主从复制和读写分离实验部署

​1.搭建 MySQL 主从复制

 （1）Mysql主从服务器时间同步

 （2）Mysql从服务器设置

 2、主服务器的mysql配置

 3、从服务器的mysql配置

（1）MySQL主从复制延迟

4、验证主从复制效果

三：MySQL主从复制的同步模式

1、MySQL主从复制的同步模式的分类

（1）异步复制（Asynchronous replication）

（2）全同步复制（Fully synchronous replication）

（3）半同步复制（Semisynchronous replication）

2、同步模式的配置

（1）主数据库配置

（2）从数据库配置

（3）查看半同步是否在运行

（4）参数说明

四：MySQL 读写分离

1、MySQL读写分离原理

2、常见的MySQL读写分离分为以下两种

（1）基于程序代码内部实现

（2）基于中间代理层实现

五：搭建 MySQL 读写分离

1、安装 Java 环境

2、 安装 Amoeba软件

 3、配置 Amoeba读写分离，两个 Slave 读负载均衡

（1）先在Master、Slave1、Slave2 的mysql上开放权限给 Amoeba 访问

（2） 再回到amoeba服务器配置amoeba服务

（2.1）修改amoeba配置文件

 （2.2）修改数据库配置文件

 4、测试读写分离

（1）在客户端服务器上进行测试

 （2）在主服务器

 （3）在两台从服务器

 六：Mycat

1、mycat简介

2、应用场景

3、Mycat长期路线图

4、 mycat安装目录结构说明

5、Mycat的常用配置文件

6、 Mycat日志

七：mycat 实现读写分离

1.环境准备

2.初始化环境

 ​​​​​​​3.部署主从复制

4、安装mycat（192.168.59.114）

5.修改 mycat 配置文件

/apps/mycat/conf/server.xml

6.修改 mycat 配置文件

/apps/mycat/conf/schema.xml

7. 主服务器上授权

8.重启mycat服务，客户机连接mycat

9.客户端测试读写分离

八：MySQL主从复制延迟

1、主从复制出现延迟怎么判断

2、延迟的原因

3、减少主从复制延迟方法

4、如何监控mysql主从之间的延迟

 九：主从复制不一致时怎么解决

1、方法一：忽略错误后，继续同步

2、方式二：重新做主从，完全同步

---

前言

在实际的生产环境中，如果对mysql数据库的读和写都在一台数据库服务器中操作，无论是在安全性、高可用性，还是高并发等各个方面都是不能满足实际需求的。因此，一般通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。
所以主从复制、读写分离就是为了数据库能支持更大的并发。
随着业务量的扩展、如果是单机部署的MySQL，会导致I/O频率过高。采用主从复制、读写分离可以提高数据库的可用性。

一：MySQL数据库主从复制与读写分离

1、什么是读写分离？

读写分离，基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

2、为什么要读写分离呢？

因为数据库的“写”（写10000条数据可能要3分钟）操作是比较耗时的。
但是数据库的“读”（读10000条数据可能只要5秒钟）。
所以读写分离，解决的是，数据库的写入，影响了查询的效率。

3、什么时候要读写分离？

 数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用。利用数据库主从同步，再通过读写分离可以分担数据库压力，提高性能。

 4、主从复制与读写分离

在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于rsync，但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份。

 5、mysql支持的复制类型

（1）STATEMENT

基于语句的复制。在服务器上执行sql语句，在从服务器上执行同样的语句，mysql默认采用基于语句的复制，执行效率高。

（2）ROW

基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍。

（3）MIXED

混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

6、主从复制的工作过程

（1）mysql主服务器数据更新，将数据写入二进制文件日志保存

（2）mysql从服务器开启IO线程向主服务器请求二进制日志事件/记录

（3）主服务器为每个IO线程开启dump线程向从服务器发送二进制日志事件

（4）从服务器保存二进制日志事件到中继日志，在5.7版本中开启半同步复制的情况下，主服务器会用ack collector线程接收从服务器反馈的确认情况

（5）从服务器开启SQL线程读取中继日志中的二进制事件并解析成sql语句进行重发/逐一执行

注：
●中继日志通常会位于 OS 缓存中，所以中继日志的开销很小。
●复制过程有一个很重要的限制，即复制在 Slave上是串行化的，也就是说 Master上的并行更新操作不能在 Slave上并行操作。 

7、MySQL 读写分离原理

读写分离就是只在主服务器上写，只在从服务器上读。基本的原理是让主数据库处理事务性操作，而从数据库处理 select 查询。数据库复制被用来把主数据库上事务性操作导致的变更同步到集群中的从数据库。

8、目前较为常见的 MySQL 读写分离分为两种

 （1）基于程序代码内部实现

在代码中根据 select、insert 进行路由分类，这类方法也是目前生产环境应用最广泛的。
优点是性能较好，因为在程序代码中实现，不需要增加额外的设备为硬件开支；缺点是需要开发人员来实现，运维人员无从下手。
但是并不是所有的应用都适合在程序代码中实现读写分离，像一些大型复杂的Java应用，如果在程序代码中实现读写分离对代码改动就较大。

（2）基于中间代理层实现

代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库，有以下代表性程序。
（1）MySQL-Proxy。MySQL-Proxy 为 MySQL 开源项目，通过其自带的 lua 脚本进行SQL 判断。
（2）Atlas。是由奇虎360的Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。360内部使用Atlas运行的mysql业务，每天承载的读写请求数达几十亿条。支持事物以及存储过程。
（3）Amoeba。由陈思儒开发，作者曾就职于阿里巴巴。该程序由Java语言进行开发，阿里巴巴将其用于生产环境。但是它不支持事务和存储过程。
（4）Mycat。是一款流行的基于Java语言编写的数据库中间件，是一个实现了MySql协议的服务器，其核心功能是分库分表。配合数据库的主从模式还可以实现读写分离。
 

由于使用MySQL Proxy 需要写大量的Lua脚本，这些Lua并不是现成的，而是需要自己去写。这对于并不熟悉MySQL Proxy 内置变量和MySQL Protocol 的人来说是非常困难的。
Amoeba是一个非常容易使用、可移植性非常强的软件。因此它在生产环境中被广泛应用于数据库的代理层。
 

二、MySQL主从复制和读写分离实验部署

案例拓扑图

实验思路
第一步：客户端client访问代理服务器amoeba
第二步：代理服务器读写判断
写操作：写入到主服务器
第三步：主服务器将增删改写入自己二进制日志
第四步：从服务器将主服务器的二进制日志同步至自己中继日志
第五步：从服务器重放中继日志到数据库中
读操作：直接访问从服务器
最终结果：降低负载，起到负载均衡作用

环境准备

 1.搭建 MySQL 主从复制

 （1）Mysql主从服务器时间同步

 Master服务器：192.168.2.4

yum -y install ntp

vim /etc/ntp.conf
##---------末尾添加---------
server 127.127.2.0          #设置本地是时钟源，注意修改网段
fudge 127.127.2.0 stratum 8 #设置时间层级为8（限制在15内）

service ntpd start

 

 命令模式输入G跳转到结尾，输入o在末尾添加以下两个字段

 （2）Mysql从服务器设置

Slave1服务器：192.168.2.5
Slave2服务器：192.168.2.6

yum -y install ntp ntpdate

service ntpd start

/usr/sbin/ntpdate 192.168.2.4  #进行时间同步，指向Master服务器IP

crontab -e
*/30 * * * * /usr/sbin/ntpdate 192.168.2.4

两个slave配置相同 

 2、主服务器的mysql配置

vim /etc/my.cnf
server-id=11
log-bin=mysql-bin						#添加，主服务器开启二进制日志
binlog_format=mixed
	
#选配项	
expire_logs_days=7						#设置二进制日志文件过期时间，默认值为0，表示logs不过期
max_binlog_size=500M					#设置二进制日志限制大小，如果超出给定值，日志就会发生滚动，默认值是1GB
skip_slave_start=1						#阻止从库崩溃后自动启动复制，崩溃后再自动复制可能会导致数据不一致的

#"双1设置"，数据写入最安全
innodb_flush_logs_at_trx_commit=1		#redo log（事务日志）的刷盘策略，每次事务提交时MySQL都会把事务日志缓存区的数据写入日志文件中，并且刷新到磁盘中，该模式为系统默认
sync_binlog=1							#在进行每1次事务提交（写入二进制日志）以后，Mysql将执行一次fsync的磁盘同步指令，将缓冲区数据刷新到磁盘
---------------------------------------------------------------
#"双1设置"适合数据安全性要求非常高，而且磁盘IO写能力足够支持的业务，比如订单、交易、充值、支付消费系统。"双1模式"下，当磁盘IO无法满足业务需求时，比如11.11活动的压力。推荐一下性能较快的设置，并使用带蓄电池后备电源，防止系统断电异常。
innodb_flush_logs_at_trx_commit=2		#每次事务提交时MySQL都会把日志缓存区的数据写入日志文件中，但是并不会同时刷新到磁盘上。该模式下，MySQL会每秒执行一次刷新磁盘操作
sync_binlog=500							#在进行500次事务提交以后，Mysql将执行一次fsync的磁盘同步指令，将缓冲区数据刷新到磁盘
---------------------------------------------------------------

systemctl restart mysqld

mysql -u root -pabc123
GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.2.%' IDENTIFIED BY '123456';			#给从服务器授权
FLUSH PRIVILEGES;

show master status;

#File 列显示日志名，Position 列显示偏移量

 3、从服务器的mysql配置

vim /etc/my.cnf
server-id = 22								#修改，注意id与Master的不同，两个Slave的id也要不同
relay-log=relay-log-bin						#开启中继日志，从主服务器上同步日志文件记录到本地
relay-log-index=relay-log-bin.index			#定义中继日志文件的位置和名称，一般和relay-log在同一目录

#选配项
innodb_buffer_pool_size=2048M		#用于缓存数据和索引的内存大小，让更多数据读写内存中完成，减少磁盘操作，可设置为服务器总可用内存的 70-80%
sync_binlog=0						#MySQL不做任何强制性的磁盘刷新指令，而是依赖操作系统来刷新数据到磁盘
innodb_flush_log_at_trx_commit=2	#每次事务log buffer会写入log file，但一秒一次刷新到磁盘
log-slave-updates=0					#slave 从 master 复制的数据会写入二进制日志文件里，从库做为其他从库的主库时设置为 1
relay_log_recovery=1				#当 slave 从库宕机后，假如 relay-log 损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的 relay-log， 并且重新从 master 上获取日志，这样就保证了 relay-log 的完整性。默认情况下该功能是关闭的，将 relay_log_recovery 的值设置为 1 时， 可在 slave 从库上开启该功能，建议开启。


systemctl restart mysqld

mysql -u root -pabc123
CHANGE master to master_host='192.168.80.10',master_user='myslave',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=339;					#配置同步，注意 master_log_file 和 master_log_pos 的值要与Master查询的一致

start slave;						#启动同步，如有报错执行 reset slave;
show slave status\G					#查看 Slave 状态
//确保 IO 和 SQL 线程都是 Yes，代表同步正常。
Slave_IO_Running: Yes				#负责与主机的io通信
Slave_SQL_Running: Yes				#负责自己的slave mysql进程

#一般 Slave_IO_Running: No 的可能性：
1、网络不通
2、my.cnf配置有问题
3、密码、file文件名、pos偏移量不对
4、防火墙没有关闭

 

（1）MySQL主从复制延迟

1、master服务器高并发，形成大量事务
2、网络延迟
3、主从硬件设备导致
cpu主频、内存io、硬盘io
4、是同步复制、而不是异步复制
从库优化Mysql参数。比如增大innodb_buffer_pool_size，让更多操作在Mysql内存中完成，减少磁盘操作。
从库使用高性能主机。包括cpu强悍、内存加大。避免使用虚拟云主机，使用物理主机，这样提升了i/o方面性。
从库使用SSD磁盘
网络优化，避免跨机房实现同步

4、验证主从复制效果

主服务器上进入执行 create database db_test;

去从服务器上查看 show databases;

三：MySQL主从复制的同步模式

1、MySQL主从复制的同步模式的分类

（1）异步复制（Asynchronous replication）

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

（2）全同步复制（Fully synchronous replication）

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

（3）半同步复制（Semisynchronous replication）

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

2、同步模式的配置

（1）主数据库配置

vim /etc/my.cnf				#在 [mysqld] 区域添加下面内容
......
plugin-load=rpl_semi_sync_master=semisync_master.so			#加载mysql半同步复制的插件
rpl_semi_sync_master_enabled=ON								#或者设置为"1"，即开启半同步复制功能
rpl-semi-sync-master-timeout=1000							#超时时间为1000ms，即1s

systemctl restart mysqld

（2）从数据库配置

vim /etc/my.cnf	
......
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=ON

systemctl restart mysqld

（3）查看半同步是否在运行

#主数据库执行
show status like 'Rpl_semi_sync_master_status';
show variables like 'rpl_semi_sync_master_timeout';

#从数据库执行（此时可能还是OFF状态，需要在下一步重启IO线程后，从库半同步状态才会为ON）
show status like 'Rpl_semi_sync_slave_status';

#重启从数据库上的IO线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

#在主库查询半同步状态
show status like '%Rpl_semi%';	

（4）参数说明

Rpl_semi_sync_master_clients              		#半同步复制客户端的个数
Rpl_semi_sync_master_net_avg_wait_time    		#平均等待时间（默认毫秒）
Rpl_semi_sync_master_net_wait_time        		#总共等待时间
Rpl_semi_sync_master_net_waits            		#等待次数
Rpl_semi_sync_master_no_times             		#关闭半同步复制的次数
Rpl_semi_sync_master_no_tx                		#表示没有成功接收slave提交的次数
Rpl_semi_sync_master_status               		#表示当前是异步模式还是半同步模式，on为半同步
Rpl_semi_sync_master_timefunc_failures    		#调用时间函数失败的次数
Rpl_semi_sync_master_tx_avg_wait_time     		#事物的平均传输时间
Rpl_semi_sync_master_tx_wait_time         		#事物的总共传输时间
Rpl_semi_sync_master_tx_waits             		#事物等待次数
Rpl_semi_sync_master_wait_pos_backtraverse		#可以理解为"后来的先到了，而先来的还没有到的次数"
Rpl_semi_sync_master_wait_sessions        		#当前有多少个session因为slave的回复而造成等待
Rpl_semi_sync_master_yes_tx               		#成功接受到slave事物回复的次数

• 当半同步复制发生超时（由rpl_semi_sync_master_timeout参数控制，默认为10000ms，即10s），会暂时关闭半同步复制，转而使用异步复制，也就是会自动降为异步工作。
• 当 master dump 线程发送完一个事务的所有事件之后，如果在 rpl_semi_sync_master_timeout 内，收到了从库的响应， 则主从又重新恢复为半同步复制。

注：
1) 在一主多从的架构中，如果要开启半同步复制，并不要求所有的从都是半同步复制。
2) MySQL 5.7极大的提升了半同步复制的性能。
5.6 版本的半同步复制，dump thread 承担了两份不同且又十分频繁的任务：传送binlog 给 slave ，还需要等待 slave 反馈信息，而且这两个任务是串行的，dump thread 必须等待 slave 返回之后才会传送下一个 events 事务。dump thread 已然成为整个半同步提高性能的瓶颈。在高并发业务场景下，这样的机制会影响数据库整体的系统吞吐量(TPS)。
5.7 版本的半同步复制中，独立出一个 ack collector thread ，专门用于接收 slave 的反馈信息。这样 master 上有两个线程独立工作，可以同时发送 binlog 到 slave ，和接收 slave 的反馈。

四：MySQL 读写分离

1、MySQL读写分离原理

读写分离就是只在主服务器上写，只在从服务器上读。基本的原理是让主数据库处理事务性操作，而从数据库处理select查询。数据库复制被用来把主数据库上事务性操作导致的变更同步到集群中的从数据库。

2、常见的MySQL读写分离分为以下两种

（1）基于程序代码内部实现

在代码中根据select、insert进行路由分类，这类方法也是目前生产环境应用最广泛的。

优点是性能较好，因为在程序代码中实现，不需要增加额外的设备为硬件开支；缺点是需要开发人员来实现，运维人员无从下手。

但是并不是所有的应用都适合在程序代码中实现读写分离，像一些大型复杂的Java应用，如果在程序代码中实现读写分离对代码改动就较大。

（2）基于中间代理层实现

代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库，有以下代表性程序。

(1)MySQL-Proxy。MySQL-Proxy为MySQL开源项目，通过其自带的lua脚本进行SQL判断。

(2)Atlas。是由奇虎360的Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy

0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。360内部使用Atlas运行的mysql业务，每天承载的读写请求数达几十亿条。支持事物以及存储过程。

(3)Amoeba。由陈思儒开发，作者曾就职于阿里巴巴。该程序由Java语言进行开发，阿里巴巴将其用于生产环境。但是它不支持事务和存储过程。

(4)Mycat。是一款流行的基于Java语言编写的数据库中间件，是一个实现了MySq1协议的服务器，其核心功能是分库分表。配合数据库的主从模式还可以实现读写分离。

由于使用MySQL Proxy需要写大量的Lua脚本，这些Lua并不是现成的，而是需要自己去写。这对于并不熟悉MySQL Proxy内置变量和MySQL Protocol的人来说是非常困难的。Amoeba是一个非常容易使用、可移植性非常强的软件。因此它在生产环境中被广泛应用于数据库的代理层。

五：搭建 MySQL 读写分离

1、安装 Java 环境

 因为 Amoeba 基于是 jdk1.5 开发的，所以官方推荐使用 jdk1.5 或 1.6 版本，高版本不建议使用。
将jdk-6u14-linux-x64.bin 和 amoeba-mysql-binary-2.2.0.tar.gz.0 上传到/opt目录下。

cd /opt/
cp jdk-6u14-linux-x64.bin /usr/local/

cd /usr/local/
chmod +x jdk-6u14-linux-x64.bin
./jdk-6u14-linux-x64.bin
按空格到最后一行
按yes，按enter

mv jdk1.6.0_14/ /usr/local/jdk1.6

vim /etc/profile
export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin

source /etc/profile
java -version

 ​​​​​​​

 

2、 安装 Amoeba软件

mkdir /usr/local/amoeba
tar zxvf /opt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
chmod -R 755 /usr/local/amoeba/
/usr/local/amoeba/bin/amoeba
//如显示amoeba start|stop 说明安装成功

 3、配置 Amoeba读写分离，两个 Slave 读负载均衡

Master服务器：192.168.231.4
Slave1服务器：192.168.231.5
Slave2服务器：192.168.231.6

（1）先在Master、Slave1、Slave2 的mysql上开放权限给 Amoeba 访问

grant all on *.* to 'test'@'192.168.231.%' identified by 'abc123';

（2） 再回到amoeba服务器配置amoeba服务

Amoeba服务器：192.168.231.3

 （2.1）修改amoeba配置文件

cd /usr/local/amoeba/conf/

cp amoeba.xml amoeba.xml.bak
vim amoeba.xml           #修改amoeba配置文件
#---------30修改------------------------------
<property name="user">amoeba</property>
#---------32修改------------------------------
<property name="password">123456</property>
#---------115修改-----------------------------
<property name="defaultPool">master</property>
#---------117去掉注释–------------------------
<property name="writePool">master</property>
<property name="readPool">slaves</property>

 （2.2）修改数据库配置文件

cp dbServers.xml dbServers.xml.bak

vim dbServers.xml
#---------23注释掉--------------------------------------
作用：默认进入test库 以防mysql中没有test库时，会报错
<!-- mysql schema
<property name="schema">test</property>
-->
#---------26修改-----------------------------------------
<!-- mysql user -->
<property name="user">test</property>
#---------28-30去掉注释----------------------------------
<property name="password">abc123</property>
#---------45修改，设置主服务器的名Master------------------
<dbServer name="master"  parent="abstractServer">
#---------48修改，设置主服务器的地址----------------------
<property name="ipAddress">192.168.231.4</property>
#---------52修改，设置从服务器的名slave1-----------------
<dbServer name="slave1"  parent="abstractServer">
#---------55修改，设置从服务器1的地址---------------------
<property name="ipAddress">192.168.231.5</property>
#---------58复制上面6行粘贴，设置从服务器2的名slave2和地址---
<dbServer name="slave2"  parent="abstractServer">
<property name="ipAddress">192.168.231.6</property>
#---------修改后的65或66修改-------------------------------------
<dbServer name="slaves" virtual="true">
#---------71修改----------------------------------------
<property name="poolNames">slave1,slave2</property>

/usr/local/amoeba/bin/amoeba start&	 #启动Amoeba软件，按ctrl+c 返回
netstat -anpt | grep java		   	 #查看8066端口是否开启，默认端口为TCP 8066

 ​​​​​​​

 

 4、测试读写分离

客户端：192.168.231.7

（1）在客户端服务器上进行测试

使用yum快速安装MySQL虚拟客户端
yum install -y mariadb-server mariadb
systemctl start mariadb.service

mysql -u amoeba -p123456 -h 192.168.80.20 -P8066		
//通过amoeba服务器代理访问mysql ，在通过客户端连接mysql后写入的数据只有主服务会记录，然后同步给从--从服务器

 （2）在主服务器

通过amoeba服务器代理访问mysql ，在通过客户端连接mysql后写入的数据只有主服务会记录，然后同步给从服务器

use test;
create table test (id int(10),name varchar(10),address varchar(20));

 （3）在两台从服务器

stop slave;			#关闭同步
use test;

在salve1上

insert into test values('1','slave1','this_is_slave1');

 在slave2上

insert into test values('2','slave2','this_is_slave2');

 在主服务器上

insert into test values('3','master','this_is_master');

 在客户端服务器上

use test;
select * from test;		//客户端会分别向slave1和slave2读取数据，显示的只有在两个从服务器上添加的数据，没有在主服务器上添加的数据

insert into test values('4','client','this_is_client');		//只有主服务器上有此数据

 

 再在两个从服务器上执行 start slave; 即可实现同步在主服务器上添加的数据

start slave;

注意：这样对导致客户端读的时候会有数据不一样的情况 

 

 六：Mycat

1、mycat简介

• 一个彻底开源的，面向企业应用开发的大数据库集群

• 支持事务、ACID、可以替代MySQL的加强版数据库

• 一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群

• 一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server

• 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品

• 一个新颖的数据库中间件产品

2、应用场景

• 单纯的读写分离，此时配置最为简单，支持读写分离，主从切换

• 分表分库，对于超过1000万的表进行分片，最大支持1000亿的单表分片

• 多租户应用，每个应用一个库，但应用程序只连接Mycat，从而不改造程序本身，实现多租户化报表系统，借助于Mycat的分表能力，处理大规模报表的统计

• 替代Hbase，分析大数据，作为海量数据实时查询的一种简单有效方案，比如100亿条频繁查询的记录需要在3秒内查询出来结果，除了基于主键的查询，还可能存在范围查询或其他属性查询，此时Mycat可能是最简单有效的选择

3、Mycat长期路线图

强化分布式数据库中间件的方面的功能，使之具备丰富的插件、强大的数据库智能优化功能、全面的系统监控能力、以及方便的数据运维工具，实现在线数据扩容、迁移等高级功能进一步挺进大数据计算领域，深度结合Spark Stream和Storm等分布式实时流引擎，能够完成快速的巨表关联、排序、分组聚合等 OLAP方向的能力，并集成一些热门常用的实时分析算法，让工程师以及DBA们更容易用Mycat实现一些高级数据分析处理功能不断强化Mycat开源社区的技术水平，吸引更多的IT技术专家，使得Mycat社区成为中国的Apache，并将Mycat推到Apache基金会，成为国内顶尖开源项目，最终能够让一部分志愿者成为专职的Mycat开发者，荣耀跟实力一起提升

4、 mycat安装目录结构说明

bin	mycat命令，启动、重启、停止等运行目录
catlet	catlet为Mycat的一个扩展功能
conf	mycat 配置信息,重点关注
lib	mycat引用的jar包，Mycat是java开发的
logs	日志文件，包括Mycat启动的日志和运行的日志
version.txt	mycat版本说明

5、Mycat的常用配置文件

​​​​​​​Mycat的配置文件都在conf目录里面，这里介绍几个常用的文件：

• server.xml ：Mycat软件本身相关的配置文件，设置账号、参数等

• schema.xml：Mycat对应的物理数据库和数据库表的配置,读写分离、高可用、分布式策略定制、节点控制

• rule.xml：Mycat分片（分库分表）规则配置文件,记录分片规则列表、使用方法等

6、 Mycat日志

Mycat的日志文件都在logs目录里面

• wrapper.log ：mycat启动日志

• mycat.log ：mycat详细工作日志

七：mycat 实现读写分离

1.环境准备

mycat服务器上不能装mysql

master服务器	192.168.59.113
slave1服务器	192.168.59.112
mycat服务器	192.168.59.114
客户机	192.168.59.118

2.初始化环境

#每台服务器上都初始化，关闭防火墙
systemctl stop firewalld
systemctl disable firewalld
setenforce 0

 ​​​​​​​​​​​​​​3.部署主从复制

主mysql服务器配置（192.168.59.113）
（1）修改配置文件
[root@localhost ~]#vim /etc/my.cnf

#服务器id不能和从服务器一样
server-id = 1
#开启二进制日志
log-bin=master-bin
#二进制日志格式
binlog_format=MIXED
#开启从服务器同步
log-slave-updates=true

server-id = 1
log-bin=master-bin
binlog_format=MIXED
log-slave-updates=true

（2）重启服务
[root@localhost ~]#systemctl restart mysqld

（3）进入数据库授权,查看二进制文件及节点号
[root@localhost ~]#mysql -uroot -p123123

#建立一个myslave用户，并且允许192.168.59.%这个网段的地址来登录，密码是123456，一会儿要在slave上使用这个账号
grant replication slave on *.* to 'myslave'@'192.168.59.%' identified by '123456';
#刷新一下
flush privileges;
#查看主服务器的bin-log日志文件名称和position点
show master status;

grant replication slave on *.* to 'myslave'@'192.168.59.%' identified by '123456';
flush privileges;
show master status;

从mysql服务器配置（192.168.59.112）
（1）#修改配置文件
[root@localhost ~]#vim /etc/my.cnf

#修改，服务器id不能和主服务器一样
server-id = 2
#添加，开启中继日志，从主服务器上同步日志文件记录到本地
relay-log=relay-log-bin
#添加，定义中继日志文件的位置和名称，一般和relay-log在同一目录
relay-log-index=slave-relay-bin.index

server-id = 2
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index

（2）重启服务
[root@localhost ~]#systemctl restart mysqld

（3）进入数据库，设置连接主库信息，并开启slave服务
[root@localhost ~]#mysql -uroot -p123123

#不知道连接主库信息格式可以用help change master to 查看，下面的master_log_file是在配置Master的时候的File字段， master_log_pos是在配置Master的Position 字段。一定要一一对应
change master to master_host='192.168.59.113',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=603;
#开启slave
start slave;
#查看从库状态信息，Slave_IO_Running和Slave_SQL_Running状态必须是yes
show slave status \G;
#Slave_IO_Running: Yes
#Slave_SQL_Running: Yes

change master to master_host='192.168.59.113',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=603;
start slave;
show slave status \G;

在master服务器上上传库文件，并在从服务器上验证主从同步 
#在主服务器上下载hellodb库文件，source后加绝对路径
[root@localhost ~]#mysql -uroot -p123123
#下载hellodb库文件
source /bak/hellodb.sql
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

#在从库中查看库文件hellodb是否已经同步
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

4、安装mycat

（1）主机上安装java（mycat基于java）
#yum安装java
[root@localhost ~]#yum install java -y
#确认安装成功
[root@localhost ~]#java -version
openjdk version "1.8.0_131"
OpenJDK Runtime Environment (build 1.8.0_131-b12)
OpenJDK 64-Bit Server VM (build 25.131-b12, mixed mode)

（2）切换至opt目录，下载mycat安装包
[root@localhost ~]#cd /opt
[root@localhost ~]#wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz

（3）创建/apps文件夹，并解压mycat包至/apps下
[root@localhost ~]#mkdir /apps
[root@localhost ~]#tar zxvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps/

（4）设置变量环境
[root@localhost ~]#echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@localhost ~]#source /etc/profile.d/mycat.sh

（5）启动mycat，查看日志文件，最后可以看到启动成功
[root@localhost ~]#mycat start
#注意内存小于2G 起不来
Starting Mycat-server...

[root@localhost ~]#tail -f /apps/mycat/logs/wrapper.log
#启动成功日志末尾会出现successfully
STATUS | wrapper  | 2021/12/09 21:04:10 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2021/12/09 21:04:10 | Launching a JVM...
INFO   | jvm 1    | 2021/12/09 21:04:11 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2021/12/09 21:04:11 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2021/12/09 21:04:11 | 
INFO   | jvm 1    | 2021/12/09 21:04:12 | MyCAT Server startup successfully. see logs in logs/mycat.log


(6)客户端连接数据库
#这里密码初始为123456   需要加端口
[root@localhost bin]#mysql -uroot -p123456 -h 192.168.59.114 -P8066

5.修改 mycat 配置文件

/apps/mycat/conf/server.xml

[root@localhost ~]#vim /apps/mycat/conf/server.xml

#去掉44行行注释，对应的在51行行末注释，删除50行行末注释，5 * 60 * 1000L; //连接空>    闲检查

#修改45行端口号为3306
45 <property name="serverPort">3306</property>

#配置Mycat的连接信息(账号密码)，在110 和111行， 可以修改，这边不修改了

####参数解释说明####
110         <user name="root" defaultAccount="true">
111                 <property name="password">123456</property>
112                 <property name="schemas">TESTDB</property>
113                 <property name="defaultSchema">TESTDB</property>

116                 <!-- 表级 DML 权限设置 -->
117                 <!-- 
118                 <privileges check="false">
119                         <schema name="TESTDB" dml="0110" >
120                                 <table name="tb01" dml="0000"></table>
121                                 <table name="tb02" dml="1111"></table>
122                         </schema>
123                 </privileges>   
124                  -->

127         <user name="user">
128                 <property name="password">user</property>
129                 <property name="schemas">TESTDB</property>
130                 <property name="readOnly">true</property>
131                 <property name="defaultSchema">TESTDB</property>

user    用户配置节点
name    逻辑用户名，客户端登录MyCAT的用户名，也就是客户端用来连接Mycat的用户名。
password     客户端登录MyCAT的密码
schemas      数据库名，这里会和schema.xml中的配置关联，可配置多个，多个用逗号分开，例如:db1,db2
privileges   配置用户针对表的增删改查的权限
readOnly mycat   逻辑库所具有的权限。true为只读，false为读写都有，默认为false

##注意
1.#server.xml文件里登录mycat的用户名和密码可以任意定义，这个账号和密码是为客户机登录mycat时使用的账号信息
2.#逻辑库名(如上面的TESTDB，也就是登录mycat后显示的库名，切换这个库之后，显示的就是代理的真实mysql数据库的表)要在schema.xml里面也定义，否则会导致mycat服务启动失败！这里只定义了一个标签，所以把多余的都注释了。如果定义多个标签，即设置多个连接mycat的用户名和密码，那么就需要在schema.xml文件中定义多个对应的库！

6.修改 mycat 配置文件

/apps/mycat/conf/schema.xml

schema.xml是最主要的配置项，此文件关联mysql读写分离策略，读写分离、分库分表策略、分片节点都是在此文件中配置的.MyCat作为中间件，它只是一个代理，本身并不进行数据存储，需要连接后端的MySQL物理服务器，此文件就是用来连接MySQL服务器的。

[root@localhost ~]#vim  /apps/mycat/conf/schema.xml
#删除所有内容，重新写入以下
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        #schema标签：数据库设置，此数据库为逻辑数据库，name与server.xml中schema对应。
        #name:逻辑数据库名，与server.xml中的schema对应；
        #checkSQLschema: 数据库前缀相关设置，这里为false；
        #sqlMaxLimit:  select时默认的limit，避免查询全表，否则可能会遇到查询量特别大的情况造成卡 死；
        #dataNode:表存储到哪些节点，多个节点用逗号分隔。节点为下文dataNode设置的name
</schema>
        <dataNode name="dn1" dataHost="localhost1" database="hellodb" />
        #dataNode标签: 定义mycat中的数据节点，也是通常说的数据分片，也就是分库相关配置
        #name: 定义数据节点的名字，与table中dataNode对应
        #datahost: 物理数据库名，与datahost中name对应，该属性用于定义该分片属于哪个数据库实例
        #database: 物理数据库中数据库名，该属性用于定义该分片属性哪个具体数据库实例上的具体库
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
        #dataHost标签： 物理数据库，真正存储数据的数据库
        #name: 物理数据库名，与dataNode中dataHost对应
        #maxCon属性指定每个读写实例连接池的最大连接。也就是说，标签内嵌套的writeHost、readHost标  签都会使用这个属性的值来实例化出连接池的最大连接数
        #minCon属性指定每个读写实例连接池的最小连接，初始化连接池的大小
        #balance: 均衡负载的方式
       
                  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                  #writeType: 写入方式
                  #dbType: 数据库类型
                  #dbDriver指定连接后端数据库使用的 Driver，目前可选的值有 native 和 JDBC。用 native 的话，因为这个值执行的是二进制的 mysql 协议，所以可以使用 mysql 和maridb。其他类型的数据库则需要使用 JDBC 驱动来支持。
                  #switchType:  “-1” 表示不自动切换； “1” 默认值，自动切换； “2” 基于 MySQL主从同步的状态决定是否切换心跳语句为 show slave status； “3” 基于 MySQL galary cluster 的切换机制（适合集群）（1.4.1）心跳语句为 show status like ‘wsrep%’.
                <heartbeat>select user()</heartbeat>
                #heartbeat: 心跳检测语句，注意语句结尾的分号要加
                <writeHost host="host1" url="192.168.59.113:3306" user="root" password="123456">
                #host：用于标识不同实例，一般 writeHost 我们使用*M1，readHost 我们用*S1。
                #url：后端实例连接地址。Native：地址：端口 JDBC：jdbc的url
                #user：后端存储实例需要的用户名字
                #password:后端存储实例需要的密码
                 <readHost host="host2" url="192.168.59.112:3306" user="root" password="123456"/>

                </writeHost>
        </dataHost>
</mycat:schema>


#schema.xml文件中有三点需要注意：balance="1"，writeType="0" ,switchType="1" 
#schema.xml中的balance的取值决定了负载均衡对非事务内的读操作的处理。balance 属性负载均衡类型，目前的取值有 4 种：
##balance="0"：不开启读写分离机制，所有读操作都发送到当前可用的writeHost上,即读请求仅            发送到writeHost上
##balance="1"：一般用此模式，读请求随机分发到当前writeHost对应的readHost和standby的writeHost上。即全部的readHost与stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1 ->S1 ， M2->S2，并且 M1 与 M2 互为主备)，正常情况下， M2,S1, S2 都参与 select 语句的负载均衡
##balance="2"：读请求随机分发到当前dataHost内所有的writeHost和readHost上。即所有读操作都随机的在writeHost、 readhost 上分发
##balance="3"：读请求随机分发到当前writeHost对应的readHost上。即所有读请求随机的分发wiriterHost 对应的 readhost 执行, writerHost 不负担读压力，注意 balance=3 只在 1.4 及其以后版本有，1.3 没有

###writeHost和readHost标签，这两个标签都指定后端数据库的相关配置给mycat，用于实例化后端连接池。唯一不同的是：writeHost指定写实例、readHost指定读实例，组着这些读写实例来满足系统的要求。在一个dataHost内可以定义多个writeHost和eadHost。但是，如果writeHost指定的后端数据库宕机，那么这个writeHost绑定的所有readHost都将不可用。另一方面，由于这个writeHost宕机系统会自动的检测到，并切换到备用的writeHost上去   
               
#PS：Mycat主从分离只是在读的时候做了处理，写入数据的时候，只会写入到writehost，需要通过mycat的主从复制将数据复制到readhost

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
        <dataNode name="dn1" dataHost="localhost1" database="hellodb" />
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="host1" url="192.168.59.113:3306" user="root" password="123456">
                 <readHost host="host2" url="192.168.59.112:3306" user="root" password="123456"/>

                </writeHost>
        </dataHost>
</mycat:schema>

7. 主服务器上授权

[root@localhost ~]#mysql -uroot -p123123
#授权
GRANT ALL ON *.* TO 'root'@'192.168.59.%' IDENTIFIED BY '123456';

#查看创建成功
use mysql;
select user,host from user;

8.重启mycat服务，客户机连接mycat

（1）在mycat服务器上，重启mycat服务,查看启动日志，文末出现successfully
 [root@localhost ~]#mycat restart  
 [root@localhost ~]#tail -f /apps/mycat/logs/wrapper.log
 INFO   | jvm 1    | 2021/12/09 21:15:40 | 
INFO   | jvm 1    | 2021/12/09 21:15:40 | MyCAT Server startup successfully. see logs in logs/mycat.log
STATUS | wrapper  | 2021/12/09 21:16:38 | TERM trapped.  Shutting down.
STATUS | wrapper  | 2021/12/09 21:16:39 | <-- Wrapper Stopped
STATUS | wrapper  | 2021/12/09 21:16:40 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2021/12/09 21:16:40 | Launching a JVM...
INFO   | jvm 1    | 2021/12/09 21:16:40 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2021/12/09 21:16:40 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2021/12/09 21:16:40 | 
INFO   | jvm 1    | 2021/12/09 21:16:41 | MyCAT Server startup successfully. see logs in logs/mycat.log

(2)查看3306端口，可以监听到主从服务器（192.168.59.113、192.168.59.112）
 [root@localhost ~]# ss -antp|grep 3306
 LISTEN     0      128         :::3306                    :::*                   users:(("java",pid=74936,fd=185))
TIME-WAIT  0      0        ::ffff:192.168.59.114:37344                ::ffff:192.168.59.112:3306               
ESTAB      0      0        ::ffff:192.168.59.114:58160                ::ffff:192.168.59.113:3306                users:(("java",pid=74936,fd=195))

 
（3）在客户机上登录mycat，这时可以不加端口直接进入数据库了
[root@localhost ~]#mysql -uroot -p123456 -h 192.168.59.114
#看是否能查到表
show databases;
use TESTDB;
show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+

#查看当前的查询来自哪台服务器，可以看到查询功能来自id为2的从服务器
select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+

9.客户端测试读写分离

（1）在主从服务器上都打开通用日志
[root@localhost ~]#mysql -uroot -p123123
#打开通用日志
set global general_log=1;
#查看通用查询日志是否开启
show variables like 'general%';
+------------------+-------------------------------------+
| Variable_name    | Value                               |
+------------------+-------------------------------------+
| general_log      | ON                                  |
| general_log_file | /usr/local/mysql/data/localhost.log |
+------------------+-------------------------------------+

set global general_log=1;
show variables like 'general%';

（2）在主从服务器上实时查看通用日志
[root@localhost ~]#tail -f /usr/local/mysql/data/localhost.log

（3）在客户机上的表中插入数据，并查看主从服务器实时日志，可以看到只有主服务器上有日志变化显示
[root@localhost ~]#mysql -uroot -p123456 -h 192.168.59.114
insert into teachers values(5,'Xiao Ming',46,'F');

（4）在客户机上select查表，并查看主从服务器实时日志，可以看到只有从服务器上有日志变化显示，从而实现了读写分离
select * from teachers;

八：MySQL主从复制延迟

1、主从复制出现延迟怎么判断

mysql> show slave status\G;

Seconds_Behind_Master:是通过比较sql_thread执行的event的timestamp和io_thread复制好的event的timestamp(简写为ts)进行比较，而得到的这么一个差值；

NULL一表示io_thread或是sql_thread有任何一个发生故障，也就是该线程的Running状态是No,而非Yes。

0 —该值为零，是我们极为渴望看到的情况，表示主从复制良好，可以认为lag不存在。

正值 一表示主从已经出现延时，数字越大表示从库落后主库越多。

负值 一几乎很少见，我只是听一些资深的DBA说见过，其实，这是一个BUG值，该参数是不支持负值的，也就是不应该出现。

2、延迟的原因

1、master服务器高并发，形成大量事务
2、网络延迟
3、主从硬件设备导致    cpu主频、内存io、硬盘io

4、是同步复制、而不是异步复制

• 从库优化Mysql参数。比如增大innodb_buffer_pool_size，让更多操作在Mysql内存中完成，减少磁盘操作。
• 从库使用高性能主机。包括cpu强悍、内存加大。避免使用虚拟云主机，使用物理主机，这样提升了i/o方面性。
• 从库使用SSD磁盘
• 网络优化，避免跨机房实现同步

3、减少主从复制延迟方法

 如果延迟比较大，就先确认以下几个因素：

1.从库硬件比主库差，导致复制延迟

2.主从复制单线程，如果主库写并发太大，来不及传送到从库就会导致延迟。更高版本的mysq可以支持多线程复制

3.慢SQL语句过多

4.网络延迟

5.master负载

主库读写压力大，导致复制延迟，架构的前端要加buffer及缓存层

6.slave负载

一般的做法是，使用多台slave来分摊读请求，再从这些slave中取一台专用的服务器

只作为备份用，不进行其他任何操作.另外，2个可以减少延迟的参数：-slave-net-timeout=seconds单位为秒默认设置为3600秒

#参数含义：当slave从主数据库读取log数据失败后，等待多久重新建立连接并获取数据-master-connect-retry=seconds单位为秒默认设置为60秒

#参数含义：当重新建立主从连接时，如果连接建立失败，间隔多久后重试通常配置以上2个参数可以减少网络问题导致的主从数据同步延迟

MySQL数据库主从同步延迟解决方案

最简单的减少slave同步延时的方案就是在架构上做优化，尽量让主库的DDL快速执行还有就是主库是写， 对数据安全性较高， 比如sync_binlog=1 ；innodb_flush_log_at_trx_commit

=1之类的设置，而slave则不需要这么高的数据安全，完全可以讲sync_binlog设置为0或者关闭binlog_innodb_flushlog也可以设置为0来提高sql的执行效率。另外就是使用比主库更好的硬件设备作为slave

4、如何监控mysql主从之间的延迟

如何监控mysq|主从之间的延迟，通常有两种方法：Seconds_Behind_Master 和 mk-heartbeat

方法1:通过监控show slave status\G命令输出的Seconds_Behind_Master参数的值来判断，是否有发生主从延时。show slave status; #需要有replcation client权限以下是show slave status\G的输出结果的几个重点参数说明：

Slave_IO_Running:该参数可作为io_thread的监控项，Yes表示io_thread的和主库连接正常并能实施复制工作，No则说明与主库通讯异常，多数情况是由主从间网络引起的问题；

Slave_SQL_Running:该参数代表sql_thread是否正常，具体就是语句是否执行通过，常会遇到主键重复或是某个表不存在。

Seconds_Behind_Master:是通过比较sql_thread执行的event的timestamp和io_thread复制好的event的timestamp(简写为ts)进行比较，而得到的这么一个差值；

NULL一表示io_thread或是sql_thread有任何一个发生故障，也就是该线程的Running状态是No,而非Yes。0-该值为零，是我们极为渴望看到的情况，表示主从复制良好，可以认为lag不存在。正值一表示主从已经出现延时，数字越大表示从库落后主库越多。负值-几乎很少见，我只是听一些资深的DBA说见过，其实，这是一个BUG值，该参数是不支持负值的，也就是不应该出现。

方法2:mk-heartbeat:Maatkit万能工具包中的一个工具，被认为可以准确判断复制延时的方法。

mk-heartbeat的实现也是借助timestmp的比较实现的，它首先需要保证主从服务器必须要保持一致，通过与相同的一个NTP server同步时钟。它需要在主库上创建一个heartbeat的表，里面至少有id与ts两个字段，id为server_id,ts就是当前的时间戳now(),该结构也会被复制到从库上，表建好以后，会在主库上以后台进程的模式去执行一行更新操作的命令，定期去向表中的插入数据，这个周期默认为1秒，同时从库也会在后台执行一个监控命令，与主库保持一致的周期去比较，复制过来记录的ts值与主库上的同一条ts值，差值为0表示无延时，差值越大表示延时的秒数越多。我们都知道复制是异步的ts不肯完全一致，所以该工具允许半秒的差距，在这之内的差异都可忽略认为无延时。这个工具就是通过实打实的复制，巧妙的借用timestamp来检查延时；

 九：主从复制不一致时怎么解决

1、方法一：忽略错误后，继续同步

该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况解决：

stop slave;
#表示跳过一步错误，后面的数字可变
set global sql_slave_skip_counter =1;
start slave;
之后再用mysql> show slave status\G查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
ok,现在主从同步状态正常了。。。

2、方式二：重新做主从，完全同步

该方法适用于主从库数据相差较大，或者要求数据完全统一的情况解决步骤如下：

1.先进入主库，进行锁表，防止数据写入使用命令：

mysql> flush tables with read lock;
注意：该处是锁定为只读状态，语句不区分大不写

2.进行数据备份

#把数据备份到mysql.bak.sq|文件

[root@server01 mysql]#mysqldump -uroot -p-hlocalhost > mysql.bak.sql

这里注意一点：数据库备份一定要定期进行，可以用shell脚本或者python脚本，都比较方便，确保数据万无一失

3.查看master状态

mysql> show master status;

| File| Position |Binlog_Do_DB |Binlog_gnore_DB|

Imysqld-bin.000001|3260|| mysql,test;information_schema|

1 row in set (0.00 sec)

4.把mysql备份文件传到从库机器，进行数据恢复

#使用scp命令

[root@server01 mysql]# scp mysql.bak.sql root@192.168.128.101:/tmp/

5.停止从库的状态

mysql> stop slave;

6.然后到从库执行mysql命令，导入数据备份

mysql> source /tmp/mysql.bak.sql

7.设置从库同步，注意该处的同步点，就是主库show master status信息里的| File] Position两项

change master to master_host ='192.168.128.100', master user ='rsync', master_port=3306, master_password=", master log_file ='mysqld-bin.000001',master_log_pos=3260;

8.重新开启从同步

mysql> start slave;

9.查看同步状态

mysql> show slave status\G查看：

Slave IO Running: Yes