MySQL 主从同步

一、MySQL 主从同步介绍

主从复制是指一台 MySQL 服务器充当主数据库服务器，另外一台或者多台充当从数据库服务器，主数据库服务器的数据自动复制到从数据库服务器之中。

1. 1 MySQL 支持的复制类型

• 基于语句的复制：在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选着基于行的复制。

• 基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持。

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

1.2 MySQL 主从同步原理

• Master 节点将数据改变记录到二进制日志（Bianry log）中，也就是 Masert 节点的配置文件 my.cnf 中log-bin 指定的文件，这些记录叫做二进制日志事件（binary log events）；

• Slave 节点通过 I/O 线程读取 Master 中的 binary log events 并写入到它的中继日志（relay binlog）中；

• Slave 通过 SQL Thread 线程读取relay binlogs的记录，重做中继日志中的事件，把中继日志中的事件信息一条一条的在本地执行一次，完成数据在本地的存储，从而实现将改变反映到它自己的数据(数据重放)。

1.3 主从复制解决的问题

MySQL复制技术有以下一些特点：

  (1)数据分布 (Data distribution )

  (2)负载平衡(load balancing)

  (3)备份(Backups) 

  (4)高可用性和容错行 High availability and failover

1.4 异步复制和半同步复制

• 异步复制

MySQL本身支持单向的、异步的复制。异步复制意味着在把数据从一台机器拷贝到另一台机器时有一个延时，最重要的是这意味着当应用系统的事务在主服务器上提交并确认时数据并不能在同一时刻拷贝或应用到从服务器上。

• 同步复制

同步复制可以定义为数据在同一时刻被提交到一台或多台机器，通常这是通过众所周知的“两阶段提交”做到的。同步复制虽然能保证数据的一致性，但是也造成了性能的下降。使用MyISAM或者InnoDB存储引擎的MySQL本身并不支持同步复制，然而有些技术，例如分布式复制块设备（简称DRBD），可以在下层的文件系统提供同步复制，允许第二个MySQL服务器在主服务器丢失的情况下接管（使用第二服务器的复本）。

• 半同步复制

MYSQL 5.5开始，支持半自动复制。之前版本的MySQL Replication都是异步（asynchronous）的，主库在执行完一些事务后，是不会管备库的进度的。如果备库不幸落后，而更不幸的是主库此时又出现Crash（例如宕机），这时备库中的数据就是不完整的。简而言之，在主库发生故障的时候，我们无法使用备库来继续提供数据一致的服务了。Semisynchronous Replication(半同步复制)则一定程度上保证提交的事务已经传给了至少一个备库。Semi synchronous中，仅仅保证事务的已经传递到备库上，但是并不确保已经在备库上执行完成了。

     此外，还有一种情况会导致主备数据不一致。在某个session中，主库上提交一个事务后，会等待事务传递给至少一个备库，如果在这个等待过程中主库Crash，那么也可能备库和主库不一致，这是很致命的。如果主备网络故障或者备库挂了，主库在事务提交后等待10秒（rpl_semi_sync_master_timeout的默认值）后，就会继续。这时，主库就会变回原来的异步状态。

MySQL在加载并开启Semi-sync插件后，每一个事务需等待备库接收日志后才返回给客户端。如果做的是小事务，两台主机的延迟又较小，则Semi-sync可以实现在性能很小损失的情况下的零数据丢失。

默认情况下MySQL的复制是异步的，Master上所有的更新操作写入Binlog之后并不确保所有的更新都被复制到 Slave之上。异步操作虽然效率高，但是在Master/Slave 出现问题的时候，存在很高数据不同步的风险，甚至可能丢失数据。

     MySQL5.5引入半同步复制功能的目的是为了保证在master出问题的时候，至少有一台Slave的数据是完整的。在超时的情况下也可以临时转入异步复制，保障业务的正常使用，直到一台 slave 追赶上之后，继续切换到半同步模式。

 

二、主从配置

主从配置的基本步骤：

1. Master 配置

• 启用二进制日志

• 配置一个唯一的server id

• 创建具有复制权限的用户

1. Slave 配置

• 启用中继日志

• 配置一个唯一的server id

• 连接主服务器，并开始复制数据

• 启动数据复制

2.1 Master 配置

1. 修改配置文件

# 启用二进制日志：格式 + 二进制日志前缀名
binlog-format=ROW
log-bin=mysql-master-bin
# 设置 server id：必须唯一
server-id=127
# slave更新时是否记录到日志中；  
# 让slave扮演其它slave的master。此时，slave把#SQL线程执行的事件写进行自己的二
# 进制日志(binary log)，然后，它的slave可以获取这些事件并执行它
log-slave-updates=true
# 开启半同步
# 先安装插件: install plugin rpl_semi_sync_master soname 
# 'semisync_master.so'; 
# 查看状态:   show variables like 'rpl_%';
rpl_semi_sync_master_enabled=ON
# 需要同步的二进制数据库名；
binlog-do-db=tuling
# 不同步的二进制数据库名,如果不设置可以将其注释掉;
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=personalsite
binlog-ignore-db=test
# 二进制日志文件校验,当主从数据库版本不一致的时候需要关闭改校验
# binlog_checksum =none；

1. 创建用户用于主从同步的数据库：

grant replication slave,super,reload on *.* to slave1@192.168.17.201 identified by '123456';

 

1. 查看主节点状态

mysql> show master status;

# 在主库上查看已连接的slave主机

mysql> show slave hosts;

# 查看所有binlog日志

mysql> show binary logs;

# 查看所有binlog 事件

mysql> show binlog events in 'mysql-bin.000003';

2.2 Slave 配置

1. 修改配置文件 my.cnf

server-id = 2
# 二进制文件
log-bin=mysql-slave-bin
# 需要同步的数据库
binlog-do-db=tuling 
# 无需同步的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=personalsite
binlog-ignore-db=test

1. 连接主数据库

mysql>change master to master_host='192.168.0.101', master_user='slave1', master_password='123456' ,MASTER_AUTO_POSITION = 2887;

1. Slave 相关操作

#启动slave
mysql>start slave;
mysql>stop slave;
#查看 slave 状态
show slave status\G;