aop日志同步到elk 基于日志的数据同步

一、日志

1、redo log：

InnoDB存储引擎层的日志，又称重做日志文件，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。redo log在事务没有提交前，每一个修改操作都会记录变更后的数据，redolog是工作在物理层，它的作用主要是为了减少磁盘开销。因为磁盘操作是极为耗时的，因此，不可能每次对数据的更改都直接写入磁盘。redolog的作用就是缓存起来这些数据改动（缓存到磁盘），等缓存到达一定的数量后再统一写磁盘。为防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。所以redolog主要作用就是用来实现事务的crash-safe和提升写入的性能。

因为 Innodb是以 页为单位进行磁盘交互的，MySQL为了提高的性能，对于增、删、改这种操作都是在内存中完成的,而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了。或者一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差，因此 mysql设计了 redo log， 具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)，这里涉及到WAL即Write Ahead logging技术，关键点是先写日志，再写磁盘, MySQL有专门的后台线程负责将脏数据页刷新同步回磁盘。redo log作为异常宕机或者介质故障后的数据恢复使用。mysql如果每次更新操作都要写进磁盘，然后磁盘要找到对应记录，然后再更新写入，整个过程io成本、查找成本都很高。采用WAL技术（Write-Ahead Logging），具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

redo log 包括两部分：一个是内存中的日志缓冲( redo log buffer )，另一个是磁盘上的日志文件( redo logfile)。mysql 每执行一条语句，先将记录写入 redo log buffer，后续某个时间点再一次性将多个操作记录fsync写到 redo log file。

2、binlog

binlog属于MySQL Server层面的，又称为归档日志，是以二进制的形式记录的是这个语句的原始逻辑，binlog只会在日志提交后，一次性记录执行过的事务中的sql语句，执行的sql语句依靠binlog是没有crash-safe能力的。binlog的主要作用是记录数据库中表的更改，它只记录改变数据的sql，不改变数据的sql不会写入。binlog二进制日志是mysql-server层的，主要是做主从复制，时间点恢复使用。

3、redo log的两阶段提交：

由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写binlog ，或者采用反过来的顺序，会产生日志与数据不一致的问题，例如

先写 redo log 后写 binlog，假设在 redo log 写完， binlog 还没有写完的时候，MySQL进程异常重启，redo log写完后，仍能够把数据恢复回来。但是binlog就没有记录这条语句，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，会导致数据不一致
先写 binlog 后写 redo log 。如果在 binlog 写完之后 crash ，由于 redo log 还没写，崩溃恢复以后这个事务无效,单用binglog来恢复时就多了一个事务出来。

崩溃恢复分析：

1、如果在图中时刻 A 的地方，也就是写入 redo log 处于 prepare 阶段之后、写 binlog 之前，发生了崩溃（crash），由于此时 binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog 还没写，所以也不会传到备库。

2、如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整，在 MySQL 5.6.2 版本以后，还引入了 binlog-checksum 参数，用来验证 binlog 内容的正确性。redo log 和 binlog 它们有一个共同的数据字段XID 。崩溃恢复的时候，会按顺序扫描 redo log ：

如果碰到既有 prepare 、又有 commit 的 redo log ，就直接提交；

如果碰到只有 parepare 、而没有 commit 的 redo log ，就拿着 XID 去 binlog 找对应的事务。判断binlog是否完整，如果binlog完整，则提交事务；否则，回滚事务。这里，时刻 B 发生 crash 对应的就是binlog完整的情况，崩溃恢复过程中事务会被提交。

4、binlog和redo log的区别：

作用上： Binlog 是 Server层的实现，会记录所有与MySQL数据库改动有关的日志记录，Binlog的主要作用就是用来做数据恢复和主从复制。Redolog 是InnoDB的实现，主要作用就是用来实现事务的crash-safe和提升写入的性能。

存储上： redo log是固定大小的。比如可以配置为一组 4 个文件，每个文件的大小是 1GB，总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，binlog 是可以追加写入的。“追加写”是指binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。如果binlog太大，可以设置binlog日志保留时长expire_logs_days自动删除。

实现上： binlog是server层实现的，所有引擎都能使用；而InnoDB存储引擎的重做日志redolog只记录有关该存储引擎本身的事务日志。

5、undo log

undo log 是事务开始之前，将当前版本生成undo log，undo log 存储的是逻辑格式的日志，保存了事务发生之前的上一个版本的数据，可以用于回滚，是事务原子性的保证。

6、relay log

relay log 该日志存在于备库中， relay log 和 binary log 日志类似， 记录了数据库的变化，由一系列文件组成, relay log 记录了主节点发来的数据库变化信息，主库的dump线程从binlog读取日志并发送到Slave的IO线程。并且由 slave的I/O thread 写入。之后 SQL thread 在备节点slave上执行 relay log 文件里的事件操作记录。作用是保证主从同步。relay-log的结构和binlog非常相似，只不过他多了一个master.info和relay-log.info的文件。

master.info记录了上一次读取到master同步过来的binlog的位置，以及连接master和启动复制必须的所有信息。

relay-log.info记录了文件复制的进度，下一个事件从什么位置开始，由sql线程负责更新。

7、WAL日志的读写: 使用WAL时的读操作。WAL中可能包含了未写入到数据库文件中的最新值，如果读最新值就需要从WAL中读取，如果WAL中未读到，从数据库读到的就是最新的数据。写入到WAL文件中的操作记录并不一定会立刻应用到数据库文件上，这个过程是异步的。
具体说来，当执行一条sql时，过程如下：

• Innodb引擎会把数据先插入redo log(也是写入磁盘，顺序写入，比较快)当中，并更新内存(db buffer)，这个时候更新就算完成了。
• 此时，内存(db buffer)中的数据和磁盘数据(data file)对应的数据不同，我们认为内存中的数据是脏数据(即：脏页)
• db buffer再选择合适的时机将数据持久化到data file中。这种顺序可以保证在需要故障恢复时恢复最后的修改操作
• 先持久化日志的策略叫做Write Ahead Log，即预写日志。

8、crash safe: crash-safe主要体现在事务执行过程中突然奔溃，重启后能保证事务完整性。redo log 和 binlog 有一个很大的区别就是，一个是循环写，一个是追加写。也就是说 redo log是固定大小的日志文件， 只会记录未刷盘的日志，已经刷入磁盘的数据都会从 redo log 这个有限大小的日志文件里删除。binlog 是追加日志，保存的是全量的日志。当数据库 crash 后，想要恢复未刷盘但已经写入 redo log 和 binlog 的数据到内存时，binlog 是无法恢复的。虽然 binlog 拥有全量的日志，但没有一个标志让 innoDB 判断哪些数据已经刷盘，哪些数据还没有。

checkpoint 是当前要擦除的位置，擦除记录前需要先把对应的数据落盘（更新内存页，等待刷脏页）。write pos 到 checkpoint 之间的部分可以用来记录新的操作，如果 write pos 和 checkpoint 相遇，说明 redolog 已满，这个时候数据库停止进行数据库更新语句的执行，转而进行 redo log 日志同步到磁盘中。checkpoint 到 write pos 之间的部分等待落盘（先更新内存页，然后等待刷脏页）。有了 redo log 日志，那么在数据库进行异常重启的时候，可以根据 redo log 日志进行恢复，也就达到了 crash-safe。

---

二、日志同步方式

MySQL的主从复制 主要有以下热备方式。

1、异步复制（默认）：

• 逻辑上：MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时master上已经提交的事务可能并没有传到从库上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。
• 技术上：主库将事务 Binlog 事件写入到 Binlog 文件中，此时主库只会通知一下 Dump 线程发送这些新的 Binlog，然后主库就会继续处理提交操作，而此时不会保证这些 Binlog 传到任何一个从库节点上。MySQL会在binlog落盘之后会立即将新增的binlog发送给订阅者以尽可能的降低主从延迟。

2、全同步复制

• 逻辑上：指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。
• 技术上：当主库提交事务之后，所有的从库节点必须收到、APPLY并且提交这些事务，然后主库线程才能继续做后续操作。但缺点是，主库完成一个事务的时间会被拉长，性能降低。

3、半同步复制

• 逻辑上：是介于全同步复制与全异步复制之间的一种，主库只需要等待至少一个从库节点收到并且 Flush Binlog 到 Relay Log 文件即可，主库不需要等待所有从库给主库反馈。同时，这里只是一个收到的反馈，而不是已经完全完成并且提交的反馈，如此，节省了很多时间。半同步复制，在Master提交时，会首先将该事务的redo log刷入磁盘，然后将事务的binlog刷入磁盘(这里其实还涉及到两阶段提交的问题)，然后进入innodb commit流程，这个过程完成后，等待Slave发送的ACK消息，等到Slave的响应后，标记事务为提交状态(其他用户可以看到该事务的更新)，Master才成功返回给用户。
• 技术上：介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。如果半同步在主库端是开启了的，并且至少有一个半同步复制的从库节点，那么此时主库的事务线程在提交时会被阻塞并等待，结果有两种可能：要么至少有一个从库节点通知它已经收到了这个事务的binlog事件，要么一直等待到超时直至到我们配置的某一个时间点为止，而此时，半同步复制将自行关闭，转换为异步复制模式。

4、 无损复制（增强半同步）

半同步复制保证了事务成功提交后，至少有两份日志记录，一份在主库的BINLOG日志上，另一份在至少一个从库的中继日志Relay Log上，从而更进一步保证了数据的完整性。

在传统的半同步复制中，主库写数据到BINLOG，且执行Commit操作后，会一直等待从库的ACK，即从库写入Relay Log后，并将数据落盘，返回给主库消息，通知主库可以返回前端应用操作成功，这样会出现一个问题，就是实际上主库已经将该事务Commit到了事务引擎层，应用已经可以可以看到数据发生了变化，只是在等待返回而已，如果此时主库宕机，有可能从库还没能写入Relay Log，就会发生主从库不一致。增强半同步复制就是为了解决这个问题，做了微调，即主库写数据到BINLOG后，就开始等待从库的应答ACK，直到至少一个从库写入Relay Log后，并将数据落盘，然后返回给主库消息，通知主库可以执行Commit操作，然后主库开始提交到事务引擎层，应用此时可以看到数据发生了变化。增强半同步复制的大致流程如下图所示。

---

三、innodb 事务同步流程

缓冲池是InnoDB存储引擎非常重要的组件,它是在内存当中,当我们想要查询、更新数据库一条数据的时候,它先看缓冲池中是否有该数据,如果没有从磁盘加载到缓冲池中,并且对这条数据加独占锁。当热点数据随机分布的时候，如果我们对这些热数据进行缓存，就可以避免时间代价比较大的磁盘寻址，这无疑会提高数据库的性能，如果没有缓存，从磁盘随机寻址和应用进行交互，那个时间可想而知。相对于随机IO寻址，顺序IO就快的多，缓存对于顺序IO的意义不大。直接更新磁盘上的数据是磁盘随机读写,磁盘随机读写的性能是最差的,这样会导致我们的MySQL服务器无法承载高并发场景, 顺序写入磁盘文件（日志）的性能要远高于随机读写。随机IO添加缓存有更大的收益：

1. 顺序I/O一般只需扫描一次数据
2. 顺序I/O比随机I/O快
3. 随机I/O通常只要查找特定的行、但I/O的粒度是页级的、其中大部分是浪费的、而顺序I/O所读取的数据、通常发生在想要的数据块上的所有行。

InnoDB里面有专门的后台线程把Buffer Pool的脏数据写入到磁盘的DB数据文件中，每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。刷盘(db file)是随机I/O, 而记录日志是顺序I/O （append连续写的），顺序I/O效率更高，本质上是数据集中存储和分散存储的区别。因此先把日志修改通过顺序io写入日志文件，日志在保证了内存数据的安全性的情况下，可以延迟刷盘随机IO的时机，进而提升系统吞吐。

双写缓冲区：

原理： 双写缓冲区是一个位于系统表空间的存储区域。在写入时，InnoDB先把从缓冲池中的得到的page写入系统表空间的双写缓冲区。之后，再把page写到.ibd数据文件中相应的位置。如果在写page的过程中发生意外崩溃，InnoDB在稍后的恢复过程中在doublewrite buffer中找到完好的page副本用于恢复。在正常的情况下, MySQL写数据page时，会写两遍到磁盘上，第一遍是写到doublewrite buffer，第二遍是从doublewrite buffer写到真正的数据文件中。如果发生了极端情况（断电），InnoDB再次启动后，发现了一个page数据已经损坏，那么此时就可以从doublewrite buffer中进行数据恢复了。

优点： 用于避免刷新脏页的时候只写入一半造成数据混乱。有人会认为系统恢复后，MySQL可以根据redo log进行恢复，而MySQL在恢复的过程中是检查page的checksum，checksum就是page的最后事务号，发生partial page write问题时，page已经损坏，找不到该page中的事务号，就无法恢复。

doublewrite是在一个连续的存储空间, 所以硬盘在写数据的时候是顺序写，而不是随机写，这样性能更高。InnoDB 的数据页大小往往和系统的数据页大小不一致，有可能 InnoDB 为 16k，系统的为 4k，InnoDB 刷新一个数据页，系统要刷新 4 个，这就意味着在系统宕机的时候有可能只刷新了一半的数据页。而计算机硬件和操作系统，在极端情况下（比如断电）往往并不能保证这一操作的原子性，16K的数据，写入4K时，发生了系统断电或系统崩溃，只有一部分写是成功的，这种情况下就是partial page write（部分页写入）问题。这时page数据出现不一样的情形，从而形成一个"断裂"的page，使数据产生混乱。这个时候InnoDB对这种块错误是无 能为力的。双写缓冲技术，会先把要刷新的数据写入共享表空间，然后再刷新到对应系统页，如果在这个过程中系统崩溃，InnoDB 就可以从共享表空间获取到要刷新的数据，然后重新执行。

1、读数据： 在数据库中读取页的操作，首先将从磁盘读取的页存放在缓冲池中。下一次再读取数据页时，先判断该数据页是否在缓冲池中，如果在缓冲池中，会直接读取该数据页，否则，读取磁盘上的页。

2、改数据：

3、插入数据： 和上图相比减少了 检测数据是否在缓冲区中的步骤，直接在buffer pool中插入数据。

4、删除数据：MySQL在删除一条记录的时候，只是把这行记录标记为删除，但不会回收磁盘空间，被标记删除的位置可以被复用；所以说【delete】命令只是把记录的位置或者数据页标记为【可复用】，但磁盘大小不会改变。这些可复用而没有使用的空间，看起来像个【空洞】。

---

四、mysql如何大批量插入数据

1、循环插入这个也是最普通的方式，如果数据量不是很大，可以使用，但是每次都要消耗连接数据库的资源。

大致思维如下

for``($i=1;$i<=100;$i++){`` ``
$sql = ``'insert...............'``;
//querysql``}
foreach($arr ``as` `$``key` `=> $value){
$sql = ``'insert...............'``;`` ``//querysql
}
while($i <= 100){
$sql = ``'insert...............'``;
`` ``//querysql`` 
   ``$i++``
}

2、存储过程： 存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，是sql语句和控制语句的预编译集合。以一个名称存储并作为一个单元处理, 存储在数据库中，经过第一次编译后调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。把需要的命令预编译好,存到数据库。只在第一次的时候需要编译，存储以后使用的时候客户端直接（传参）调用。

A、首先需要DELIMITER 语句来修改定界符
B、然后创建存储过程【CREATE PROCUDURE】过程体中可以用 in 和 out 传参。
C、通过CALL来调用

• 增强sql语句的功能性和灵活性
• 实现较快的执行速度
• 减少网络流量【因为往数据库传递的参数少了。所以请求少了】

delimiter $$$
create procedure zqtest()
begin
declare i int default 0;
set i=0;
start transaction;
while i<80000 do
 //your insert sql 
set i=i+1;
end while;
commit;
end
$$$
delimiter;
call zqtest();

3、LOAD DATA LOCAL INFILE: MySQL的LOAD DATAINFILE语句用于高速地从一个文本文件中读取行，并装入一个表中。mysql自带的一种批量插入方式且效率更高，通过LOAD DATA LOCAL INFILE实现大批量插入。MySQL使用LOAD DATA LOCAL INFILE从文件中导入数据比insert语句要快，MySQL文档上说要快20倍左右。但是这个方法有个缺点，就是导入数据之前，必须要有文件，也就是说从文件中导入。这样就需要去写文件，以及文件删除等维护。某些情况下，比如数据源并发的话，还会出现写文件并发问题，很难处理。当需要进行大批量数据插入的时候，可以优先考虑LOAD DATA LOCAL INFILE实现方式。

如果你指定关键词low_priority，那么MySQL将会等到没有其他人读这个表的时候，才把插入数据。如果指定local关键词，则表明从客户主机读文件。如果local没指定，文件必须位于服务器上。可以使用如下的命令：
load data local infile “/home/mark/data sql” into table Orders;

上满足基本需求，100万数据问题不大，要不数据实在太大也涉及分库分表了，或者使用队列插入了。