该系列博文会告诉你如何从入门到进阶，从 sql 基本的使用方法，从 MySQL 执行引擎再到索引、事务等知识，一步步地学习 MySQL 相关技术的实现原理，更好地了解如何基于这些知识来优化 sql，减少 SQL 执行时间，通过执行计划对 SQL 性能进行分析，再到 MySQL 的主从复制、主备部署等内容，以便让你更完整地了解整个 MySQL 方面的技术体系，形成自己的知识框架。
# 1. MySQL 里的那些日志们
同大多数关系型数据库一样，日志文件是 MySQL 数据库的重要组成部分。MySQL 有几种不同的日志文件，通常包括错误日志文件，二进制日志，通用日志，慢查询日志，等等。这些日志可以帮助我们定位 mysqld 内部发生的事件，数据库性能故障，记录数据的变更历史，用户恢复数据库等等。本文主要描述错误日志文件。
# 1.1 MySQL 日志文件系统的组成
a、错误日志：记录启动、运行或停止 mysqld 时出现的问题。 b、通用日志：记录建立的客户端连接和执行的语句。 c、更新日志：记录更改数据的语句。该日志在 MySQL 5.1 中已不再使用。 d、二进制日志：记录所有更改数据的语句。还用于复制。 e、慢查询日志：记录所有执行时间超过 long_query_time 秒的所有查询或不使用索引的查询。 f、Innodb 日志：innodb redo log 和 undo log
缺省情况下，所有日志创建于 mysqld 数据目录中。 可以通过刷新日志，来强制 mysqld 来关闭和重新打开日志文件（或者在某些情况下切换到一个新的日志）。 当你执行一个 FLUSH LOGS 语句或执行 mysqladmin flush-logs 或 mysqladmin refresh 时，则日志被老化。 对于存在 MySQL 复制的情形下，从复制服务器将维护更多日志文件，被称为接替日志。
# 1.2 错误日志
错误日志是一个文本文件。 错误日志记录了 MySQL Server 每次启动和关闭的详细信息以及运行过程中所有较为严重的警告和错误信息。 可以用–log-error [=file_name] 选项来开启 mysql 错误日志，该选项指定 mysqld 保存错误日志文件的位置。 对于指定–log-error [=file_name] 选项而未给定 file_name 值，mysqld 使用错误日志名 host_name.err 并在数据目录中写入日志文件。 在 mysqld 正在写入错误日志到文件时，执行 FLUSH LOGS 或者 mysqladmin flush-logs 时，服务器将关闭并重新打开日志文件。 建议在 flush 之前手动重命名错误日志文件，之后 mysql 服务将使用原始文件名打开一个新文件。 以下为错误日志备份方法： shell> mv host_name.err host_name.err-old shell> mysqladmin flush-logs shell> mv host_name.err-old backup-directory
# 1.3 InnoDB 中的日志
MySQL 数据库 InnoDB 存储引擎 Log 漫游
1 – Undo Log
Undo Log 是为了实现事务的原子性，在 MySQL 数据库 InnoDB 存储引擎中，还用 Undo Log 来实现多版本并发控制 (简称：MVCC)。
- 事务的原子性 (Atomicity) 事务中的所有操作，要么全部完成，要么不做任何操作，不能只做部分操作。如果在执行的过程中发生 了错误，要回滚 (Rollback) 到事务开始前的状态，就像这个事务从来没有执行过。
- 原理 Undo Log 的原理很简单，为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方 （这个存储数据备份的地方称为 Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了 ROLLBACK 语句，系统可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态。
除了可以保证事务的原子性，Undo Log 也可以用来辅助完成事务的持久化。
- 事务的持久性 (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. 事务提交 这里有一个隐含的前提条件：‘数据都是先读到内存中，然后修改内存中的数据，最后将数据写回磁盘’。 之所以能同时保证原子性和持久化，是因为以下特点： A. 更新数据前记录 Undo log。 B. 为了保证持久性，必须将数据在事务提交前写到磁盘。只要事务成功提交，数据必然已经持久化。 C. Undo log 必须先于数据持久化到磁盘。如果在 G,H 之间系统崩溃，undo log 是完整的， 可以用来回滚事务。 D. 如果在 A-F 之间系统崩溃，因为数据没有持久化到磁盘。所以磁盘上的数据还是保持在事务开始前的状态。
缺陷：每个事务提交前将数据和 Undo Log 写入磁盘，这样会导致大量的磁盘 IO，因此性能很低。
如果能够将数据缓存一段时间，就能减少 IO 提高性能。但是这样就会丧失事务的持久性。因此引入了另外一 种机制来实现持久化，即 Redo Log.
2 – 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 log. E. 记录 B=2 到 undo log. F. 修改 B=4. G. 记录 B=4 到 redo log. H. 将 redo log 写入磁盘。 I. 事务提交 undo log 保存的是修改前的数据，并且保存到内存中，回滚的时候在读取里面的内容 (从而实现了原子性),redolog 保存的是修改后的数据 (对新数据的备份，同时也会将 redo log 备份), 在事务提交写入到磁盘，从而保证了持久性
# 1.4 慢查询日志
概述 数据库查询快慢是影响项目性能的一大因素，对于数据库，我们除了要优化 SQL，更重要的是得先找到需要优化的 SQL。如何找到低效的 SQL 是写这篇文章的主要目的。
MySQL 数据库有一个 “慢查询日志” 功能，用来记录查询时间超过某个设定值的 SQL，这将极大程度帮助我们快速定位到问题所在，以便对症下药。至于查询时间的多少才算慢，每个项目、业务都有不同的要求，传统企业的软件允许查询时间高于某个值，但是把这个标准放在互联网项目或者访问量大的网站上，估计就是一个 bug，甚至可能升级为一个功能性缺陷。
为避免误导读者，特申明本文的讨论限制在 Win 64 位 MySQL 5.6 范围内。其他平台或数据库种类及版本，我没有尝试过，不做赘述。
设置日志功能 关于慢查询日志，主要涉及到下面几个参数：
slow_query_log ：是否开启慢查询日志功能（必填） long_query_time ：超过设定值，将被视作慢查询，并记录至慢查询日志文件中（必填） log-slow-queries ：慢查询日志文件（不可填），自动在 data 创建一个 [hostname]-slow.log 文件 也就是说，只有满足以上三个条件，“慢查询功能” 才可能正确开启或关闭。
# 1.5 二进制日志
- 主从复制的基础：binlog 日志和 relaylog 日志
什么是 MySQL 主从复制 简单来说就是保证主 SQL（Master）和从 SQL（Slave）的数据是一致性的，向 Master 插入数据后，Slave 会自动从 Master 把修改的数据同步过来（有一定的延迟），通过这种方式来保证数据的一致性，就是主从复制
复制方式 MySQL5.6 开始主从复制有两种方式：基于日志（binlog）、基于 GTID（全局事务标示符）。 本文只涉及基于日志 binlog 的主从配置
复制原理 1、Master 将数据改变记录到二进制日志 (binary log) 中，也就是配置文件 log-bin 指定的文件，这些记录叫做二进制日志事件 (binary log events) 2、Slave 通过 I/O 线程读取 Master 中的 binary log events 并写入到它的中继日志 (relay log) 3、Slave 重做中继日志中的事件，把中继日志中的事件信息一条一条的在本地执行一次，完成数据在本地的存储，从而实现将改变反映到它自己的数据 (数据重放)
1、什么是 binlog binlog 是一个二进制格式的文件，用于记录用户对数据库更新的 SQL 语句信息，例如更改数据库表和更改内容的 SQL 语句都会记录到 binlog 里，但是对库表等内容的查询不会记录。
默认情况下，binlog 日志是二进制格式的，不能使用查看文本工具的命令（比如，cat，vi 等）查看，而使用 mysqlbinlog 解析查看。
2.binlog 的作用 当有数据写入到数据库时，还会同时把更新的 SQL 语句写入到对应的 binlog 文件里，这个文件就是上文说的 binlog 文件。使用 mysqldump 备份时，只是对一段时间的数据进行全备，但是如果备份后突然发现数据库服务器故障，这个时候就要用到 binlog 的日志了。
主要作用是用于数据库的主从复制及数据的增量恢复。
1. 啥是 binlog? 记录数据库增删改，不记录查询的二进制日志. 2. 作用：用于数据同步. 3、如何开启 binlog 日志功能 在 mysql 的配置文件 my.cnf 中，增加 log_bin 参数即可开启 binlog 日志，也可以通过赋值来指定 binlog 日志的文件名，实例如下：
[root@DB02 ~]# grep log_bin /etc/my.cnf log_bin = /application/mysql/logs/dadong-bin
log_bin
[root@DB02 ~]# 提示：也可以按 “log_bin = /application/mysql/logs/dadong-bin” 命名，目录要存在 为什么要刷新 binlog? 找到全备数据和 binlog 文件的恢复临界点.
# 2. 总结
mysql 数据库的 binlog 和 relay log 日志有着举足轻重的作用，并且 relay log 仅仅存在于 mysql 的 slave 库，它的作用就是记录 slave 库中的 io 进程接收的从主库传过来的 binlog, 然后等待 slave 库的 sql 进程去读取和应用，保证主从同步，但是 binlog 主库和从库（slave）都可以存在，记录对数据发生或潜在发生更改的 SQL 语句，并以二进制的形式保存在磁盘，所以可以通过 binlog 来实时备份和恢复数据库。
1、什么是 binlog binlog 是一个二进制格式的文件，用于记录用户对数据库更新的 SQL 语句信息，例如更改数据库表和更改内容的 SQL 语句都会记录到 binlog 里，但是对库表等内容的查询不会记录。
默认情况下，binlog 日志是二进制格式的，不能使用查看文本工具的命令（比如，cat，vi 等）查看，而使用 mysqlbinlog 解析查看。
2.binlog 的作用 当有数据写入到数据库时，还会同时把更新的 SQL 语句写入到对应的 binlog 文件里，这个文件就是上文说的 binlog 文件。使用 mysqldump 备份时，只是对一段时间的数据进行全备，但是如果备份后突然发现数据库服务器故障，这个时候就要用到 binlog 的日志了。
主要作用是用于数据库的主从复制及数据的增量恢复。
