MySQL-日志浅析

启动日志(host.err)慢日志(host-slow.log)二进制日志(binlog/relaylog)引擎日志(redo/undolog)通用日志（host.log）启动日志记录着mysqld启动和停止,以及服务器在运行过程中发生的错误的相关信息。配置方式：在my.cnf配置文件中加入：[mysqld]log-error=/path/错误名称.err如不加入该项配置，系统会自动在msyql目录下生成以host名称命名的错误日志。查看方式：mysqlshowglobalvariableslike'log_error';该日志在服务异常、尤其断电重启之后出现问题，具有很大参考价值。启动日志(host.err)慢日志(host-slow.log)二进制日志(binlog/relaylog)引擎日志(redo/undolog)通用日志（host.log）显示MySQL服务器的普通查询日志（generalquerylog）。该日志将记录所有请求的信息，所以在现网上除非万不得已排查问题，千万不要开启。配置方式：mysqlsetglobalgeneral_log=ON;mysqlsetglobalgeneral_log_file='/var/lib/mysql/DB-test-96.log';MySQL服务器默认关闭该日志，该参数可动态配置，为全局参数。查看方式：mysqlshowglobalvariableslike'%general%';启动日志(host.err)慢日志(host-slow.log)二进制日志(binlog/relaylog)引擎日志(redo/undolog)通用日志（host.log）优化MySQL最重要的一部分工作是先确定”有问题”的查询语句。只有先找出这些查询较慢的sql查询(执行时间较长)，我们才能进一步分析原因并且优化它。MySQL为我们提供了SlowQueryLog记录功能，它能记录执行时间超过了特定时长的查询。分析SlowQueryLog有助于帮我们找到”问题”查询。配置方式：在my.cnf中添加：[mysqld]log_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)二进制日志(binlog/relaylog)引擎日志(redo/undolog)通用日志（host.log）--binlog以一种更有效的格式，并且是事务安全的方式包含更新日志中可用的所有信息。--binlog包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改。--binlog还包含关于每个更新数据库的语句的执行时间信息。它不包含没有修改任何数据的语句。如果你想要记录所有语句（例如，为了识别有问题的查询），你应使用一般查询日志。--binlog的主要目的是在恢复使能够最大可能地更新数据库，因为binlog包含备份后进行的所有更新。--binlog还用于在主复制服务器上记录所有将发送给从服务器的语句。配置方式：在my.cnf配置文件中加入：[mysqld]server-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的bin-log中。slave在复制的时候SQL进程会解析成和原来master端执行过的相同的SQL再次执行。优点：在statement模式下，首先就是解决了row模式的缺点，不需要记录每一行数据的变化，减少了bin-log日志量，节省I/O以及存储资源，提高性能。因为他只需要记录在master上所执行的语句的细节，以及执行语句时候的上下文的信息。缺点：在statement模式下，由于他是记录的执行语句，所以，为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端杯执行的时候能够得到和在master端执行时候相同的结果。另外就是，由于MySQL现在发展比较快，很多的新功能不断的加入，使MySQL的复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在statement中，目前已经发现的就有不少情况会造成MySQL的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如：sleep()函数在有些版本中就不能被正确复制，在存储过程中使用了last_insert_id()函数，可能会使slave和master上得到不一致的id等等。由于row是基于每一行来记录的变化，所以不会出现类似的问题。row日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改。优点：在row模式下，bin-log中可以不记录执行的SQL语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了。所以row的日志内容会非常清楚的记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程或function，以及trigger的调用和触发无法被正确复制的问题。缺点：在row模式下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容，比如有这样一条update语句：Mixed从官方文档中看到，之前的MySQL一直都只有基于statement的复制模式，直到5.1.5版本的MySQL才开始支持row复制。从5.0开始，MySQL的复制已经解决了大量老版本中出现的无法正确复制的问题。但是由于存储过程的出现，给MySQLReplication又带来了更大的新挑战。另外，看到官方文档说，从5.1.8版本开始，MySQL提供了除Statement和Row之外的第三种复制模式：Mixed，实际上就是前两种模式的结合。在Mixed模式下，MySQL会根据执行的每一条具体的SQL语句来区分对待记录的日志形式，也就是在statement和row之间选择一种。新版本中的statment还是和以前一样，仅仅记录执行的语句。而新版本的MySQL中对row模式也被做了优化，并不是所有的修改都会以row模式来记录，比如遇到表结构变更的时候就会以statement模式来记录，如果SQL语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。statementmixedrowREAD-UNCOMMITTEDREAD-COMMITTEDfalsetruetrueREPEATABLE-READtruetruetrueSERIERLIZED未提交读&序列化两种事物隔离级别基本不可能在现网中使用，这里暂不讨论。阿里选用：提交读+row模式语音云选用：一致性读+mixed模式利用MySQL自带同步机制，我们可以实现：主备、主主、环形、级联方式的MySQL集群，并利用开源的读写分离、心跳检查工具实现小规模负载。启动日志(host.err)慢日志(host-slow.log)二进制日志(binlog/relaylog)引擎日志(redo/undolog)通用日志（host.log）UndoLogUndoLog是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中，还用UndoLog来实现多版本并发控制(简称：MVCC)。-事务的原子性(Atomicity)事务中的所有操作，要么全部完成，要么不做任何操作，不能只做部分操作。如果在执行的过程中发生了错误，要回滚(Rollback)到事务开始前的状态，就像这个事务从来没有执行过。-原理-UndoLog的原理很简单，为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为UndoLog）。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用UndoLog中的备份将数据恢复到事务开始之前的状态。-除了可以保证事务的原子性，UndoLog也可以用来辅助完成事务的持久化。-事务的持久性(Durability)-事务一旦完成，该事务对数据库所做的所有修改都会持久的保存到数据库中。为了保证持久性，数据库系统会将修改后的数据完全的记录到持久的存储上。-用UndoLog实现原子性和持久化的事务的简化过程假设有A、B两个数据，值分别为1,2。A.事务开始.B.记录A=1到undolog.C.修改A=3.D.记录B=2到undolog.E.修改B=4.F.将undolog写到磁盘。G.将数据写到磁盘。H.事务提交-这里有一个隐含的前提条件：‘数据都是先读到内存中，然后修改内存中的数据，最后将数据写回磁盘’。UndoLog之所以能同时保证原子性和持久化，是因为以下特点：A.更新数据前记录Undolog。B.为了保证持久性，必须将数据（undolog也是数据）在事务提交前写到磁盘。只要事务成功提交，数据必然已经持久化。C.Undolog必须先于数据持久化到磁盘。如果在G,H之间系统崩溃，undolog是完整的，可以用来回滚事务。D.如果在A-F之间系统崩溃,因为数据没有持久化到磁盘。所以磁盘上的数据还是保持在事务开始前的状态。缺陷：每个事务提交前将数据和UndoLog写入磁盘，这样会导致大量的磁盘IO，因此性能很低。如果能够将数据缓存一段时间，就能减少IO提高性能。但是这样就会丧失事务的持久性。因此引入了另外一种机制来实现持久化，即RedoLog.redoLog-原理-和UndoLog相反，RedoLog记录的是新数据的备份。在事务提交前，只要将RedoLog持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是RedoLog已经持久化。系统可以根据RedoLog的内容，将所有数据恢复到最新的状态。-Undo+Redo事务的简化过程假设有A、B两个数据，值分别为1,2.A.事务开始.B.记录A=1到undolog.C.修改A=3.D.记录A=3到redolog.E.记录B=2到undolog.F.修改B=4.G.记录B=4到redolog.H.将redolog写入磁盘。I.事务提交redoLog-Undo+Redo事务的特点A.为了保证持久性，必须在事务提交前将RedoLog持久化。B.数据不需要在事务提交前写入磁盘，而是缓存在内存中。C.RedoLog保证事务的持久性。D.UndoLog保证事务的原子性。E.有一个隐含的特点，数据必须要晚于redolog写入持久存储。sync_binlog、innodb_flush_log_at_trx_commit两个参数对应的binlog和redolog协同的问题。这两个log都影响数据丢失，但是他们分别在Server和InnoDB层维护。由于一个事务可能使用两种事务引擎，所以MySQL用两段式事务提交来协调事务提交。我们先简单了解一下两段式事务提交的过程。第一阶段：首先，协调者在自身节点的日志中写入一条的日志记录，然后所有参与者发送消息prepare，询问这些参与者（包括自身），