MySQL-日志浅析

1、启动日志(host.err)慢日志(host-slow.log)二进制日志(binlog/relaylog)引擎日志(redo/undolog)通用日志（host.log） 启动日志记录着mysqld启动和停止,以及服务器在运行过程中发生的错误的相关信息。配置方式：在f配置文件中加入：mysqldlog-error = /path/错误名称.err如不加入该项配置，系统会自动在msyql目录下生成以host名称命名的错误日志。查看方式：mysql show global variables like log_error;该日志在服务异常、尤其断电重启之后出现问题，具有很大参考价值。启动日志(h

2、ost.err)慢日志(host-slow.log)二进制日志(binlog/relaylog)引擎日志(redo/undolog)通用日志（host.log） 显示MySQL 服务器的普通查询日志（general query log）。该日志将记录所有请求的信息，所以在现网上除非万不得已排查问题，千万不要开启。配置方式：mysql set global general_log = ON;mysql set global general_log_file = /var/lib/mysql/DB-test-96.log;MySQL服务器默认关闭该日志，该参数可动态配置，为全局参数。查看方式：my

3、sql show global variables like %general%;启动日志(host.err)慢日志(host-slow.log)二进制日志(binlog/relaylog)引擎日志(redo/undolog)通用日志（host.log） 优化MySQL最重要的一部分工作是先确定”有问题”的查询语句。只有先找出这些查询较慢的sql查询(执行时间较长)，我们才能进一步分析原因并且优化它。MySQL为我们提供了Slow Query Log记录功能，它能记录执行时间超过了特定时长的查询。分析Slow Query Log有助于帮我们找到”问题”查询。配置方式：在f中添加：mysqldl

4、og_slow_queries = ONlong_query_time = 1log-queries-not-using-indexes该参数可动态配置，为全局参数。该日志记录了慢日志发生时间、连接用户、IP、执行时间、锁定时间、最终发送行数、总计扫描行数、SQL语句等相关信息可通过mysqldumpslow工具进行汇总、排序，以便找出耗时最高、请求次数最多的慢查询日志除了mysql自带mysqldumpslow工具外，还有很多第三方优秀的慢日志分析工具，如：mysqlsla、MyProfi、mysql-log-filter启动日志(host.err)慢日志(host-slow.log)二进制

5、日志(binlog/relaylog)引擎日志(redo/undolog)通用日志（host.log）-binlog 以一种更有效的格式，并且是事务安全的方式包含更新日志中可用的所有信息。-binlog 包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改。-binlog 还包含关于每个更新数据库的语句的执行时间信息。它不包含没有修改任何数据的语句。如果你想要记录所有语句（例如，为了识别有问题的查询），你应使用一般查询日志。-binlog 的主要目的是在恢复使能够最大可能地更新数据库，因为 binlog 包含备

6、份后进行的所有更新。-binlog 还用于在主复制服务器上记录所有将发送给从服务器的语句。配置方式：在f配置文件中加入：mysqldserver-id = 96log-bin = mysql-binbinlog-do-db= *辅助配置log-bin=log-slave-updatesexpire_logs_days = 3sync_binlog = 0binlog_cache_size = 4Mreplicate-do-db=*replicate-ignore-db=*辅助配置选项与其他配置有依赖关系、后续将陆续介绍statement 每一条会修改数据的 SQL 都会记录到 master 的

7、 bin-log 中。slave 在复制的时候 SQL 进程会解析成和原来 master 端执行过的相同的 SQL 再次执行。优点优点：在 statement 模式下，首先就是解决了row 模式的缺点，不需要记录每一行数据的变化，减少了 bin-log 日志量，节省 I/O 以及存储资源，提高性能。因为他只需要记录在 master 上所执行的语句的细节，以及执行语句时候的上下文的信息。缺点缺点：在 statement 模式下，由于他是记录的执行语句，所以，为了让这些语句在 slave 端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在 sla

8、ve 端杯执行的时候能够得到和在 master 端执行时候相同的结果。另外就是，由于 MySQL 现在发展比较快，很多的新功能不断的加入，使 MySQL 的复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug 也就越容易出现。在 statement 中，目前已经发现的就有不少情况会造成 MySQL 的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如：sleep() 函数在有些版本中就不能被正确复制，在存储过程中使用了 last_insert_id() 函数，可能会使 slave 和 master 上得到不一致的 id 等等。由于 row 是基于每一行来

9、记录的变化，所以不会出现类似的问题。row 日志中会记录成每一行数据被修改的形式，然后在 slave 端再对相同的数据进行修改。优点优点：在 row 模式下，bin-log 中可以不记录执行的 SQL 语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了。所以 row 的日志内容会非常清楚的记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程或 function ，以及 trigger 的调用和触发无法被正确复制的问题。缺点缺点：在 row 模式下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容，比如

10、有这样一条 update 语句：Mixed 从官方文档中看到，之前的 MySQL 一直都只有基于 statement 的复制模式，直到 5.1.5 版本的 MySQL 才开始支持 row 复制。 从 5.0 开始，MySQL 的复制已经解决了大量老版本中出现的无法正确复制的问题。但是由于存储过程的出现，给 MySQL Replication 又带来了更大的新挑战。另外，看到官方文档说，从 5.1.8 版本开始，MySQL 提供了除 Statement 和 Row 之外的第三种复制模式：Mixed，实际上就是前两种模式的结合。 在 Mixed 模式下，MySQL 会根据执行的每一条具体的 SQL

11、 语句来区分对待记录的日志形式，也就是在 statement 和 row 之间选择一种。新版本中的 statment 还是和以前一样，仅仅记录执行的语句。而新版本的 MySQL 中对 row 模式也被做了优化，并不是所有的修改都会以 row 模式来记录，比如遇到表结构变更的时候就会以 statement 模式来记录，如果 SQL 语句确实就是 update 或者 delete 等修改数据的语句，那么还是会记录所有行的变更。statementmixedrowREAD-UNCOMMITTEDREAD-COMMITTEDfalsetruetrueREPEATABLE-READtruetruetrue

12、SERIERLIZED未提交读 & 序列化 两种事物隔离级别基本不可能在现网中使用，这里暂不讨论。阿 里选用：提交读+row模式语音云选用：一致性读+mixed模式利用MySQL自带同步机制，我们可以实现：主备、主主、环形、级联方式的MySQL集群，并利用开源的读写分离、心跳检查工具实现小规模负载。启动日志(host.err)慢日志(host-slow.log)二进制日志(binlog/relaylog)引擎日志(redo/undolog)通用日志（host.log）Undo Log Undo Log 是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中，还用Undo Log来实现

13、多版本并发控制(简称：MVCC)。-事务事务的原子性的原子性(Atomicity)事务中的所有操作，要么全部完成，要么不做任何操作，不能只做部分操作。如果在执行的过程中发生了错误，要回滚(Rollback)到事务开始前的状态，就像这个事务从来没有执行过。-原理原理-Undo Log的原理很简单，为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了 ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。-除了可以保证事务的原子性，Undo Log也可以

14、用来辅助完成事务的持久化。-事务事务的持久性的持久性(Durability)-事务一旦完成，该事务对数据库所做的所有修改都会持久的保存到数据库中。为了保证持久性，数据库系统会将修改后的数据完全的记录到持久的存储上。-用用Undo Log实现原子性和持久化的事务的简化过程实现原子性和持久化的事务的简化过程 假设有A、B两个数据，值分别为1,2。 A.事务开始. B.记录A=1到undo log. C.修改A=3. D.记录B=2到undo log. E.修改B=4. F.将undo log写到磁盘。 G.将数据写到磁盘。 H.事务提交-这里有一个隐含的前提条件：数据都是先读到内存中，然后修改内存

15、中的数据，最后将数据写回磁盘。Undo Log之所以能同时保证原子性和持久化，是因为以下特点： A. 更新数据前记录Undo log。 B. 为了保证持久性，必须将数据（undolog也是数据）在事务提交前写到磁盘。只要事务成功提交，数据必然已经持久化。 C. Undo log必须先于数据持久化到磁盘。如果在G,H之间系统崩溃，undo log是完整的，可以用来回滚事务。 D. 如果在A-F之间系统崩溃,因为数据没有持久化到磁盘。所以磁盘上的数据还是保持在事务开始前的状态。缺陷：缺陷：每个事务提交前将数据和Undo Log写入磁盘，这样会导致大量的磁盘IO，因此性能很低。如果能够将数据缓存一段

16、时间，就能减少IO提高性能。但是这样就会丧失事务的持久性。因此引入了另外一种机制来实现持久化，即Redo Log.redo Log-原理原理-和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可， 不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是Redo Log已经持久化。系统可以根据 Redo Log的内容，将所有数据恢复到最新的状态。- Undo + Redo事务的简化过程事务的简化过程 假设有A、B两个数据，值分别为1,2. A.事务开始. B.记录A=1到undo log. C.修改A=3. D.记录A=3到redo l

17、og. E.记录B=2到undo log. F.修改B=4. G.记录B=4到redo log. H.将redo log写入磁盘。 I.事务提交redo Log-Undo + Redo事务的特点事务的特点 A. 为了保证持久性，必须在事务提交前将Redo Log持久化。 B. 数据不需要在事务提交前写入磁盘，而是缓存在内存中。 C. Redo Log 保证事务的持久性。 D. Undo Log 保证事务的原子性。 E. 有一个隐含的特点，数据必须要晚于redo log写入持久存储。sync_binlog、innodb_flush_log_at_trx_commit两个参数对应的 binlog和

18、redolog协同的问题。这两个log都影响数据丢失，但是他们分别在Server 和InnoDB层维护。由于一个事务可能使用两种事务引擎，所以MySQL用两段式事务提交来协调事务提交。我们先简单了解一下两段式事务提交的过程。第一阶段第一阶段：首先，协调者在自身节点的日志中写入一条的日志记录，然后所有参与者发送消息prepare ，询问这些参与者（包括自身），是否能够提交这个事务；参与者在接受到这个prepare 消息以后，会根据自身的情况，进行事务的预处理，如果参与者能够提交该事务，则会将日志写入磁盘，并返回给协调者一个ready 信息，同时自身进入预提交状态状态；如果不能提交该事务，则记录日志，并返回一个not commit 信息给协调者，同时撤销在自身上所做的数据修改；第二阶段第二阶段：协调者会收集所有参与者的意见，如果收到参与者发来的not commit 信息，则标识着该事务不能提交，协调者会将Abort 记录到日志中，并向所有参与者发送一个Abort T信息，让所有参与者撤销在自身上所有的预操作；如果协调者收到所有参与者发来prepare 信息，那么协调者会