Mysql主从复制

xw大约 6 分钟MysqlMysql

概述

数据复制的意义：

横向扩展（Scale-Out）：指在多个从库之间进行读负载均衡，以提高读性能。所有数据变更在主库上执行，把之前在主库上的读负载剥离出来，以承载更多的写请求，另外，如果读负载越来越大，可以通过扩展从库来提高读性能
数据安全性：形成多个备份库，减少在极端场景丢失造成损失，对于主库来说，从库有多个，所以如果在从库上执行备份，对只读应用的可用性影响就要小很多（从库的复制机制本身也支持断点续传）。也就是说，在执行备份操作时，选择使用从库而不使用主库是一个更好的替代方案，这样可以尽量减少对主库性能以及数据安全性的影响
为其他数据源、异构系统提供实时数据
故障切换

常用的复制模式有一主多从、MHA+多节点集群 MHA Manage、MHA node。

搭建

先搭建好两台Mysql，版本保持一致且内网可通信

在主节点执行，为slave申请同步账号，192.168.159.131为slave的ip

grant replication slave on *.* to 'slave'@'192.168.159.131' identified by '123456';
FLUSH PRIVILEGES;

mysql的配置文件/etc/my.cnf，增加以下配置

# 开启binlog
log-bin=mysql-bin
server-id=100
# 需要同步的数据库，如果不配置则同步全部数据库
binlog-do-db=xxx
# binlog日志保留的天数，清除超过20天的日志
# 防止日志文件过大，导致磁盘空间不足
expire-logs-days=20

重启master
```
service mysql restart
```
slave配置：在/etc/my.cnf配置文件，增加以下配置
```
server-id=101
```

进入到slave mysql后，再输入以下命令：

CHANGE MASTER TO 
MASTER_HOST='172.168.1.2',//主机IP
MASTER_USER='slave',//之前创建的用户账号
MASTER_PASSWORD='123456',//之前创建的用户密码
MASTER_LOG_FILE='mysql-bin.000001',//master主机的binlog日志名称
MASTER_LOG_POS=100,//binlog日志偏移量
master_port=3306;//端口

启动slave服务
```
start slave;
show slave status;
```
连接master主机节点，往主机节点创建表检验、插入数据，检查slave节点是否有master插入的数据

原理

Master将数据改变记录写入到binlog文件中，Master binlog dump线程将二进制文件内容推送给slave库,除非是新建连接。
Slave通过I/O线程将Master推送的数据写入中继日志(relay log)，中继日志充当缓冲区。
Slave SQL线程将中继日志的数据执行，完成同步。

Binlog复制模式

基于row的复制模式
- 使用row格式的二进制日志时，主库会将产生的事件（一组事件）写入二进制日志，以事件来表示数据的变更。将这些表示数据变更的事件复制到从库，然后在从库中应用这些事件，把主库数据同步到从库，这称为基于row（行）的复制，简称为RBR
- 关键行为：将ROW变更转成Binlog二进制日志在从库重放
基于statement的复制模式
- 使用statement格式的二进制日志时，主库会将SQL语句文本写入二进制日志。在主库上执行的SQL语句，然后将主库的SQL变更在从库重放，这称为基于statement（语句）的复制，简称为SBR
- 关键行为：SQL转成Binlog二进制日志在从库重放
mixed复制模式
- 默认采用基于SQL的复制，一旦发现基于SQL的无法精确的复制时，就会采用基于行的复制

优缺点

row
- 优势：
  - 可以正确复制所有数据的变更，这是最安全的复制格式
- 劣势：
  - 生成更多的二进制日志数据，因为基于row的复制会将每行数据的变更都写入二进制日志。利用二进制日志进行备份和恢复的时间也会更长。此外，二进制日志的文件锁也会因为需要更长的时间来写入数据而被持有更久的时间，这可能会影响数据库的并发能
  - 无法直接看到从库中执行的语句，但是可以使用mysqlbinlog工具
statement
- 优势：
  - 写入日志文件的数据较少。当更新或删除操作涉及多行时，可以大大减少存储空间，在利用二进制日志备份与恢复数据时也可以快速完成。
  - 日志文件中包含所有的数据变更的原始语句，可用于数据库审计。
- 劣势：
  - 一些执行结果不确定的DML语句，不能使用基于statement的复制，否则可能会造成主从库的数据不一致
  - DML语句中，使用不带ORDER BY的LIMIT子句时，由于在主从库之间执行的排序结果可能不同，所以执行结果是不确定的
  - 使用statement格式的日志时，一些内置的函数无法正确复制

GTID复制

上述的复制模式中，复制拓扑的初始化配置和变更、复制的高可用切换等操作都需要找到正确的二进制日志文件和位置，否则就无法正确复制。GTID（Global Transaction Identifier，全局事务标识符），即基于GTID实现的复制，指的是基于事务的复制。

开启GTID复制：

gtid_mode=on    (必选)
enforce-gtid-consistency=1  （必选）
log_bin=mysql-bin           （可选）    #高可用切换，最好开启该功能
log-slave-updates=1     （可选）       #高可用切换，最好打开该功能

GTID工作原理
- master更新数据时，会在事务前产生GTID，一同记录到binlog日志中
- slave端的i/o 线程将变更的binlog，写入到本地的relay log中
- sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录
- 如果有记录，说明该GTID的事务已经执行，slave会忽略
- 如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog
- 在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描
GTID优势
- 一个事务对应一个唯一ID，一个GTID在一个服务器上只会执行一次;
- GTID是用来代替传统复制的方法，GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置;
- 减少手工干预和降低服务故障时间，当主机挂了之后通过软件从众多的备机中提升一台备机为主机;
GTID的劣势
- 不支持非事务引擎;
- 不支持create table ... select 语句复制(主库直接报错);(原理: 会生成两个sql, 一个是DDL创建表SQL, 一个是insert into 插入数据的 sql; 由于DDL会导致自动提交, 所以这个sql至少需要两个GTID, 但是GTID模式下, 只能给这个sql生成一个GTID)
- 不允许一个SQL同时更新一个事务引擎表和非事务引擎表
- 在一个复制组中，必须要求统一开启GTID或者是关闭GTID

Mysql主从复制

# 概述

# 搭建

# 原理

# Binlog复制模式

# 优缺点

# GTID复制

概述

搭建

原理

Binlog复制模式

优缺点

GTID复制