MySQL 主从复制的问题及解决方案

复制功能是构建 MySQL 的大规模、高性能的基础，也就是所谓的 “水平扩展” 架构。我们可以通过为服务器配置一个或多个备库。同时，复制也是高可用性、可扩展性、灾难恢复、备份以及数据仓库等工作的基础。MySQL主从基本原理，主要形式以及主从同步延迟原理 (读写分离)导致主库从库数据不一致问题的及解决方案。

泽拓昆仑Klustron的fullsync提供了严格健壮的数据同步机制，可以确保在主备节点同时断电、宕机等故障情况下，数据不丢失损坏，在性能和可靠性方面全面优于 MySQL semisync replication。详见：昆仑分布式数据库存储集群 Fullsync 机制

原文作者：CSDN博主[程序猿进阶]

关键词：MySQL、主从复制、高可用、备份恢复、Fullsync

01  

02  

1. 在主库上把数据更改记录到二进制日志（Binary Log）中（这些记录被称为二进制日志事件）。MySQL 会按事务提交的顺序而非每条语句的执行顺序来记录二进制日志。在记录二进制日志后，主库会告诉存储引擎可以提交事务了。
   
   
2. 备库将主库上的二进制日志复制到自己的中继日志（Relay Log）中。【更多细节】备库会启动一个工作线程，称为 I/O线程，I/O线程跟主库建立一个普通的客户端连接，然后在主库上启动一个特殊的二进制转储（binlog dump）线程，这个二进制转储线程会读取主库上二进制日志事件。如果该线程追赶上主库，它将进入睡眠状态，直到主库发送信号量通知其有新的事件产生时才会被唤醒，备库 I/O 线程会将接收到的事件记录到中继日志中。
3. 备库读取中继日志中的事件，将其重放到备库数据之上。

03  

• 实时灾备，用于故障切换；
• 读写分离，提供查询服务；
• 备份，避免影响业务。
  

• 主库开启binlog日志（设置log-bin参数）；
• 主从server-id不同；
• 从库服务器能连通主库。
  

04  

• 默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。
• 特别是使用电池供电缓存（Battery backed up cache）时。
• 设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。
• 日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。
• 设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。
• 而值2只会在整个操作系统挂了时才可能丢数据。
  
  

# 默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。
# 特别是使用电池供电缓存（Battery backed up cache）时。
# 设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。
# 日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。
# 设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。
# 而值2只会在整个操作系统挂了时才可能丢数据。
innodb_flush_log_at_try_commit=2

innodb_locks_unsafe_for_binlog=1

# replace into 主要作用类似insert插入操作。
# 主要的区别是replace会根据主键或者唯一索引检查数据是否存在，如果存在就先删除在更新。
REPLACE INTO table_min(col1,col2)
SELECT col1,SUM(col2)
FROM table_max
GROUP BY col1;

REPLACE INTO back.People(col1,col2)
SELECT col1,SUM(col2)
FROM main.People 
GROUP BY col1;

SELECT * INTO OUTFILE "/data/mysql/e.sql" FROM e;
# load DATA 需要有处理文件的权限， GRANT FILE ON *.* TO USER@host;
# 因为我们前面指定的分隔符是 ','，LOAD DATA 时也要指定分隔符，否则也会报错：
LOAD DATA INFILE "/data/mysql/e.sql" INTO TABLE e FIELDS TERMINATED 

# 先获取需要插入的数据集
SELECT col1,SUM(col2) FROM main.table_max GROUP BY col1;
# 在插入数据
REPLACE INTO  main.table_min(col1,col2) VALUES(?,?);

• 主库意外关闭：如果没有设置主库的 sync_binlog 选项，就可能在崩溃前没有将最后的几个二进制日志事件刷新到磁盘中。备库 I/O 也就一直处于读不到尚未写入磁盘的事件。

1. log buffer 将每秒一次地写入log file中，并且 log file的 flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。
2. 每次事务提交时 MySQL都会把 log buffer的数据写入 log file，并且 flush(刷到磁盘)中去，该模式为系统默认。
3. 每次事务提交时MySQL都会把 log buffer的数据写入 log file，但是 flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。

• 备库意外关闭：当备库关闭后重启时，会读取 master.info 文件已找到上次停止复制的位置。不幸的是，该文件并没有同步写到磁盘，文件中存储的信息可能也是错误的。备库可能会尝试重新执行一些二进制日志事件，这可能会导致唯一索引错误。唯一的办法就是忽略那些错误。Percona Toolkit 中的 pt-slave-restart 工具可以帮组完成这一点。
  如果使用的是 InnoDB 表，可以在重启后观察 MySQL 错误日志。InnoDB 在恢复过程中打印出它的恢复点的二进制日志坐标。可以使用这个值来决定备库指向主库的偏移量。Percona Toolkit 提供了一个新的特性，可以在恢复的过程中自动将这些信息提取出来，并更新 master.info 文件，从根本上使得复制能够协调好备库上的事务。

• 主库上的二进制日志损坏：除了忽略损坏的位置别无选择。可以在主库上执行 FLUSH LOGS 命令，这样主库会开启一个新的日志文件，然后在将备库指向该文件的开始位置。某些情况下可以通过 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1 来忽略一个损坏的事件。如果有多个损坏的事件，就需要重复该步骤，知道跳过所有损坏的事件。

• 备库上的中继日志损坏：如果主库上的日志是完好的，就可以通过 CHANGE MASTER TO 命令丢弃并重新获取损坏的事件。

• 二进制日志与InnoDB事务日志不同步：当主库崩溃时，InnoDB 可能将一个事务标记为提交，此时该事务可能还没有记录到二进制日志中。除非是某个备库的中继日志已经保存，否则没有任何办法恢复丢失的事务。在 MySQL5.0 版本可以设置 sync_binlog 选项来防止该问题。