面试必备----6种MySQL中的日志类型介绍

        在MySQL数据库中，常见的日志类型可以分为6种，以下对这6种日志类型进行介绍

        1、事务日志

                作用：事务日志主要用来保障RDBMS的ACID，磁盘随机IO转换为顺序IO，尽可能降低宕机造成的内存中的数据丢失

                场景：试想一种情景，一个事务在执行过程中，数据库主机宕机，事务中更改的数据在内存中已修改，但并未同步到持久性存储上

                详细解释：正常情形下，事务执行过程数据的修改在事务提交后会同步到磁盘上，但同步到磁盘上由于更改数据不可控，因此很可能IO产生在多个不连续的数据块上，这种随机IO性能很慢，因此MySQL为了保证数据库的ACID特性，就引入了事务日志，事务日志在初始化数据时会直接生成两个文件ib_logfile0、ib_logfile1,这两个文件在生成时大小就为5M(大小可设定)，其内部从磁盘上划分了5M连续的磁盘空间，用于保存事务日志，事务在执行过程中，数据的更改在未提交前都会先写到事务日志中，由于事务日志是连续的磁盘空间，因此IO时是顺序的，性能比较高，可以保证数据及时写入事务日志；事务日志包括redo和undo两种，数据库恢复重启时会重做这两种日志，从而保证了数据的一致性。

                工作模式：基于InnoDB存储引擎的MySQL之所以可以从崩溃中恢复，正是依赖于事务日志，当数据库实例宕机后，重启时MySQL会自行检查事务日志，然后依次处理；

                事务日志分为redo log和undo log两种；

                (1)、对于事务日志中未正常提交的事务，则会记录到undo log中，因为事务未正确执行完，因此必须回滚，从而保证数据一致性

                (2)、对于事务日志中已正常提交但未同步到持久化存储上时，则会记录到redo log中，因此MySQL会重新执行一遍事务，然后让数据存储到磁盘上，从而保证数据一致性

        2、二进制日志

                MySQL中的二进制日志(binary log)是一个二进制文件，主要用于记录可能引起数据库内容更改的SQL语句或数据行记录，例如新增(Insert)、更新(Update)、删除(Delete)、授权信息变更(Grant Change)等，除记录这些外，还会记录变更语句的发生时间、执行时长、操作数据等额外信息，但是它不会记录诸如Select、Show等这些不会引起数据修改的SQL语句。

                作用：主要用于主从复制架构，日志审计

                涉及常用命令：                

                show master logs                   查看二进制日志有哪些

                show master status                查看当前使用的日志文件

                show binlog events in 'mysql-bin.000004'      查看指定二进制日志的事件详细信息

                二进制日志记录类型：

                        1、基于语句(statement)      只记录SQL语句

                        2、基于行row(记录数据) 记录更改的记录数据

                        3、基于混合模式,mixed ，由存储引擎自己选择

                关联服务器变量：

                        log_bin = mysql-bin      只读变量，只能在/etc/my.cnf中添加此项并重启MySQL服务才能生效，添加进去意味着开启二进制日志，且二进制日志的前缀名称为mysql-bin

                        sql_log_bin      会话变量，用于在会话中临时关闭二进制日志；常用于维护模式下，避免数据写进二级制日志，取值可以为{ ON|OFF }，ON表示开启，OFF表示关闭

                        bin_log_format      设定二进制日志记录格式，取值可以为{ statement | row | mixed }

                        max_binlog_size      单个二进制日志文件的最大值。达到后自动滚动

                        sync_binlog      同步二进制日志到文件。一般不启用次选项，MySQL默认使用异步模式，可以提升性能

                面时问题一：sql_log_bin和log_bin的区别在哪里？

                答案：真正决定是否生效开启二进制日志的是配置文件/etc/my.cnf中的log_bin变量，存在该配置则开启，不存在则表示不开启；sql_log_bin是一个动态变量，通常在会话中修改，也可以开启或关闭二进制日志，但修改时可以只针对当前会话生效，也可以针对全局生效，需要注意的是如果修改为全局变量时，则仅针对新建立连接生效，因此一般修改此变量为全局时，必须把旧连接KILL掉，这个需谨慎

                面时问题二：既然有log_bin这个服务器变量，为什么还需要sql_log_bin？

                答案：当需要还原数据库数据时，如果你不关闭二进制日志，那么所有这些还原数据记录信息都会被记录到二进制日志中，这是没必要的，而且浪费磁盘空间；这时我们有两个方案：一种是在配置文件/etc/my.cnf中注释掉log_bin这一配置项，然后重启MySQL服务，该方案必须重启MySQL服务，会造成服务中断；另一个方案是直接在会话变量中设定全局sql_log_bin=OFF，则表示关闭二进制日志，不必重启MySQL服务，这便是sql_log_bin存在的价值和意义

        3、查询日志

            开启查询日志功能，会记录所有的查询操作，这在繁忙的数据库应用中会增加额外的IO开销，磁盘空间增长也会很快，不建议开启；通常用于DBA调试优化时临时开启

            涉及服务器变量：

                general_log                    取值可以为{ ON|OFF }，ON表示开启，OFF表示关闭

                general_log_file              取值可以自定义，这个值表示记录的查询日志文件名

         4、慢查询日志

                查询日志，所有的查询都会记录；但慢查询日志会记录执行比较慢的查询语句，究竟执行多久定义为慢，这个可以通过变量long_query_time来设定，慢查询通常会开启，便于DBA进行分析并进行性能优化

                涉及服务器变量：

                    slow_query_log                 取值可以为{ ON|OFF }，ON表示开启，OFF表示关闭

                    slow_query_log_file           取值可以自定义，表示记录慢查询日志的名称

                    long_query_time                取值为正整数，例如10，表示查询超过10秒为慢查询

            5、中继日志

                    作用：中继日志主要用于在主从复制架构中，在 slave节点上开启该日志类型，用于从master同步二进制日志数据；

                    涉及服务器变量：

                        relay_log                    取值可以自定义，表示中继日志的文件前缀名；有该配置表示开启中继日志，注释掉就表示关闭中继日志

             6、错误日志

                    记录MySQL守护进程启动和关闭过程中产生的错误信息，运行中产生的错误信息

            总结：MySQL的6种日志类型中，最重要的是事务日志和二进制日志，比如事务日志的工作方式涉及到磁盘的顺序IO和随机IO，这对于后期的性能优化是非常重要的，更好的理解底层也有助于提高自己的知识层次；而且在面时过程中也是经常被问到的，深入这两种日志类型有助于更好的理解MySQL的工作过程，希望上述的总结可以帮助大家理清思路