MySQL ログ システムの詳細を説明するサンプル コードの共有

この記事は、MySQL のログ システムの詳細情報をまとめて紹介しています。必要な方は参照してください。

ログの役割は次のとおりです。過小評価しないでください。多くの場合、プロジェクトの中盤以降の段階では、ログに基づいて最適化とアップグレードの決定が行われます。したがって、MySQL を学習するときは、もちろんログの部分を見逃すことはできません。私たちのインタビューで議論された最適化はすべてログから得られます。 mysql ログを体系的に調査することは、問題を正確に特定し、作業レベルを向上させるのに役立ちます。さらに、次の一連のログは DBA の運用と保守に焦点を当て、MySQL のあらゆる側面の構成を体系的に理解し、自分自身と敵を知り、MySQL を便利なデータ ウェアハウスにすることを目的としています。

1. MySQL のログ タイプtype

デフォルトでは、すべての MySQL ログはファイルの形式でデータベースのルート ディレクトリに保存されます:

[root@roverliang data]# pwd
/usr/local/webserver/extend_lib/mysql/data
[root@roverliang data]# ls
auto.cnf ibdata1 ib_logfile0 ib_logfile1 mysql mytest performance_schema roverliang roverliang.err roverliang.pid test

MySQL のログ タイプは次のとおりです:

1エラー ログ (エラー)、MySQL サービス インスタンスの起動、実行中または停止、その他の関連情報。
2. 一般的なクエリ ログ (一般)、MySQL サービス インスタンスによって実行されるすべての SQL ステートメントまたは MySQL コマンド。
3. バイナリ ログ (バイナリ)、select ステートメントと show ステートメントを除く、データベース上で実行されたすべての update ステートメント。
4. スロークエリログ（slow）、実行時間がlong_query_time設定値を超えるSQL文、またはインデックスを使用しないSQL文。

2. MySQL ログ キャッシュ

キャッシュが重要な役割を果たす必要がある高速、安定性、信頼性の高いシステム。 MySQL ログ処理では、キャッシュ メカニズムも使用されます。 MySQL ログは、最初は MySQL サーバーのメモリに保存され、指定されたストレージ容量を超えると、メモリ内のログは外部メモリに書き込まれ (またはフラッシュされ)、データベース テーブルの形式でハードディスクに永続的に保存されます。またはファイル。

3. MySQL エラー ログ (エラー ログ)

MySQL のエラー ログには、主に MySQL サービス インスタンスの各開始と停止に関する詳細情報、および MySQL インスタンスの実行中に生成される警告や エラー メッセージ が記録されます。 。他のログとは異なり、MySQL のエラー ログはオンにする必要があり、オフにすることはできません。

デフォルトでは、エラー ログ ファイル名は hostname.err です。 ただし、エラー ログにはすべてのエラー情報が記録されるわけではありません。MySQL サービス インスタンスの実行中に発生した重大なエラー (重大) のみが記録されます。

mysql> show variables like 'log_error'\G
*************************** 1. row ***************************
Variable_name: log_error
Value: /usr/local/webserver/extend_lib/mysql/data/roverliang.err
1 row in set (0.02 sec)

4. MySQL 一般クエリ ログ (一般ログ)

MySQL 一般クエリ ログは、操作が成功したかどうかに関係なく、選択、更新、挿入、削除などの MySQL サービス インスタンスのすべての操作を記録します。実行されました。また、MySQL クライアントと MySQL サーバー間の接続と切断 (接続が成功したか失敗したか) に関する情報もあります。 MySQL の一般クエリ ログに関連するパラメータは 3 つあります。

[]()general_log

mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log  | OFF  |
+---------------+-------+
1 row in set (0.01 sec)

@@global.general_log = 1 を設定すると、一般的なクエリ ログをオンにできます。

mysql> set @@global.general_log =1;
mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log  | ON  |
+---------------+-------+

しかし、この方法での MySQL の 変数の変更は、現在の MySQL インスタンスが実行されている間のみ有効になり、MySQL が再起動されると、デフォルトの 状態 に復元されます。これを永続的にする方法は、mysql の my.cnf ファイルを変更することです。 設定ファイルの後に追加します:

general_log = 1 
general_log_file

一般クエリ ログがオンになると、MySQL サービス インスタンスは一般クエリ ログ ファイルを自動的に作成します。general_log_file パラメータは、一般クエリ ログ ファイルの物理的な場所を設定します。以下の通り:

mysql> show variables like 'general_log_file';
+------------------+-----------------------------------------------------------+
| Variable_name  | Value                           |
+------------------+-----------------------------------------------------------+
| general_log_file | /usr/local/webserver/extend_lib/mysql/data/roverliang.log |
+------------------+-----------------------------------------------------------+

注: 通常のクエリ ログには MySQL のほぼすべての操作が記録されるため、データ アクセスが頻繁なデータベース サーバーの場合、MySQL の通常のクエリ ログをオンにするとデータベースのパフォーマンスが大幅に低下するため、通常のクエリログをオフにします。特別な期間中にのみ、特別なクエリ ログを追跡する必要がある場合は、通常のクエリ ログを一時的に開くことができます。

log_output

log_output パラメーターは、データベース テーブルに保存される通常のクエリ ログとスロー クエリ ログの内容を設定します。 set @@global.log_output='table' を使用すると、通常のクエリ ログとスロー クエリ ログを mysql システム データベースの一般テーブルと throw_log テーブルに保存できます。これら 2 つのテーブルのストレージ エンジンは CSV であり、新しい通常のクエリ ログの内容を表示するときに SQL ステートメントを使用できることに注目してください。

set @@global.log_output = 'table';
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output  | TABLE |
+---------------+-------+

5. MySQL スロー クエリ ログ (スロー ログ)

関連する質問: 面接官は面接中にこの問題について話すのがとても好きです。以前は、MySQL のマスター/スレーブ アーキテクチャについて話し、あらゆる側面から MySQL を最適化することしかできませんでしたが、遅いクエリと関連する構成を有効にする方法についてはよく理解していませんでした。

使用MySQL慢查询日志可以有效的跟踪 执行时间过长 或者 没有使用索引的查询语句。这种包括select 语句，update语句，delete语句，以及insert语句，为优化查询提供帮助。与普通查询日志不同的另一个区别在于，慢查询日志只包含成功执行过的查询语句。与MySQL慢查询日志有关的参数有5个。

1、slow_query_log

slow_query_log 设置慢查询日志是否开启。

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF  |
+----------------+-------+

2、slow_query_log_file

慢查询日志一旦开启，MySQL实例将自动创建慢查询日志文件。slowquerylog_file 所指定的文件，存放慢查询日志内容。 修改方法和上文所示的一致。直接到my.cnf 文件中进行编辑。

3、long_query_time

long_query_time 设置了慢查询的时间阈值。默认阈值是10s。

4、log_quries_not_using_indexes

log_quries_not_using_indexes 是否将不使用索引的查询语句记录到慢查询日志中，无论查询速度有多快。

mysql> set @@global.log_queries_not_using_indexes=1;

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name         | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON  |
+-------------------------------+-------+

5、log_output

设置了普通查询日志以及慢查询日志的输出形式，值有两个file、table;

六、MySQL慢查询日志查看

log_output参数可以设置慢查询日志的输出形式。默认为FILE,可以设置为TABLE;

mysql> desc mysql.slow_log;
+----------------+---------------------+
| Field     | Type        |
+----------------+---------------------+
| start_time   | timestamp      |
| user_host   | mediumtext     |
| query_time   | time        |
| lock_time   | time        |
| rows_sent   | int(11)       |
| rows_examined | int(11)       |
| db       | varchar(512)    |
| last_insert_id | int(11)       |
| insert_id   | int(11)       |
| server_id   | int(10) unsigned  |
| sql_text    | mediumtext     |
| thread_id   | bigint(21) unsigned |
+----------------+---------------------+

其中: lock_time表示该SQL执行时被锁阻塞的时间。 rows_send表示执行SQL后返回的内容行数。 rows_examined表示该SQL执行时实际扫描的记录条数。

但是使用TABLE来存储慢查询日志并不常见，业务量较大的情况下，对于系统的主服务会有影响。我们可以使用FILE 的方式来进行日志存储。安装MySQL的时候在MySQL的bin目录下已经默认安装了mysqldumpslow.pl工具来进行慢查询的日志分析。 window下使用这个工具，可能需要折腾些配置，这不在本文的介绍范围内，学系统服务，还是移步linux吧。 linux下的命令以及工具都可以使用 命令本身 + --help 的选项来查看帮助文档。

-s 表示按照何种方式排序

子选项: c、t、l、r

c : SQL执行的次数
t : 执行时间
l : 锁等待时间
r : 返回数据条数
at、al、ar 是对应 t l r 的平均值。 -t :表示返回前 N 条记录。

-g： grep 缩写。包含模糊匹配

常用法如下：

//返回访问次数最多的20条SQL语句
./mysqldumpslow -s c -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log

//返回return记录数最多的20条SQL语句
./mysqldumpslow -s r -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log

//返回含有like的SQL语句
./mysqldumpslow -g 'like' 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log

七、二进制日志（binary）

二进制日志和前面提到的几种日志不同，二进制不能直接通过cat或者less 文本查看器查看。需要借助专业的工具。二进制日志主要记录数据库的变化情况，因此可以用作主从库的同步。内容主要包括数据库所有的更新操作，use语句、insert语句、delete语句、update语句、create语句、alter语句、drop语句。用一句更简洁易懂的话概括就是：所有涉及数据变动的操作，都要记录进二进制日志中。

启动二进制日志 使用 show variables like 'log_bin'\G 来查看二进制日志是否开启。

mysql> show variables like 'log_bin'\G
*************************** 1. row ***************************
Variable_name: log_bin
    Value: OFF
1 row in set (0.00 sec)

mysql> set @@global.log_bin=1;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable
mysql>

看到log_bin 默认是不开启的，并且是个只读的变量，需要在my.cnf中配置，然后重启MySQL。 service mysql restart 重启MySQL后，在data目录会发现生成了一个1.000001的文件。实际上每次MySQL重启，在目录下都会生成一个这样的文件，文件名依次递增。此外，MySQL还会在该目录下创建一个二进制日志的索引文件，可以通过命令show variables like 'log_bin_index'\G来查看索引文件的位置，然后使用cat命令看下。会发现，里面记录着二进制文件的相对路径。

查看二进制日志 可以使用MySQL 中自带的工具。具体位置在mysql的bin目录下。 mysqlbinlog命令的常用选项：

-s 以精简的方式显示日志内容
-v 以详细的方式显示日志内容
-d=数据库名 只显示指定数据库的日志内容
-o=n 忽略日志中前n行MySQL命令
-r=file 将指定内容写入指定文件

--start-datetime
显示指定时间范围内的日志内容
--stop-datetime

--start-position
显示指定位置间隔内的日志内容
--stop-position

获取当前使用的二进制日志文件

mysql> show master status;
+----------+----------+--------------+------------------+-------------------+
| File   | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------+----------+--------------+------------------+-------------------+
| 1.000002 |   120 |       |         |          |
+----------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

使用二进制日志恢复数据

语法很简单：

mysqlbinlog -s 1.000001 | mysql -h 192.168.1.188 -u root -p

mysqlbinlog 之后可以跟 --start-datetime 、--stop-datetime 、start-position 、stop-position 等参数。

--start-datetime 、--stop-datetime 这两个参数可以基于时间点进行数据恢复；

start-position 、stop-position 可以进行操作点更加细化的进行数据恢复；

MySQL二进制日志相关参数

mysql> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name              | Value        |
+-----------------------------------------+----------------------+
| binlog_cache_size            | 32768        |
| binlog_checksum             | CRC32        |
| binlog_direct_non_transactional_updates | OFF         |
| binlog_error_action           | IGNORE_ERROR     |
| binlog_format              | STATEMENT      |
| binlog_gtid_simple_recovery       | OFF         |
| binlog_max_flush_queue_time       | 0          |
| binlog_order_commits          | ON          |
| binlog_row_image            | FULL         |
| binlog_rows_query_log_events      | OFF         |
| binlog_stmt_cache_size         | 32768        |
| binlogging_impossible_mode       | IGNORE_ERROR     |
| innodb_api_enable_binlog        | OFF         |
| innodb_locks_unsafe_for_binlog     | OFF         |
| max_binlog_cache_size          | 18446744073709547520 |
| max_binlog_size             | 1073741824      |
| max_binlog_stmt_cache_size       | 18446744073709547520 |
| simplified_binlog_gtid_recovery     | OFF         |
| sync_binlog               | 0          |
+-----------------------------------------+----------------------+

max_binlog_size

maxbinlogsize 单个二进制日志文件的大小。如果超过该值，则生成新的文件，后缀名+1；

binlog_cache_size

binlogcachesize 内存中存放二进制日志的缓存大小

sync_binlog

sync_binlog 缓存中写入几次二进制日志，开始同步刷新到外存（硬盘）中。

log_slave_updates

logslvaeupdates 用于主从复制

二进制日志的清理

原则上要先将准备清理的日志通过物理备份的方式备份到其他存储设备上，永久留存。 然后推荐使用下面两种风险较小的清理方式：

第一种：

purge master logs before '2017-02-16 00:00:00';

第二种：

直接在MySQL的配置文件my.cnf 中设置expire_logs_days参数来设置二进制文件的过期天数，过期的二进制文件将会被自动删除。建议在删除前另启一个周期计划任务，定期去备份二进制任务。免得一些数据若干天才发现出现了差错，而二进制日志被自动删除了。

expire_logs_days=90

八 、InnoDB 事务日志

MySQL会最大程度的利用缓存，从而提高数据的访问效率。那么换一句话来说，任何高性能的系统都必须利用到缓存，从各个层面来讲，缓存都发挥了巨大的作用。再上升到一个高度提炼一下：缓存和队列是实现高性能的必走之路。那么对于数据库来说这个却是个很棘手的问题，要保证数据更高效率的读取和存储，所以要利用到缓存。但是要保证数据的一致性，则必须保证所有的数据都必须准确无误的存储到数据库中，及时发生意外，也要保证数据可恢复。我们知道InnoDB是一个事务安全的存储引擎，而一致性是事务ACID中的一个重要特性。InnoDB存储引擎主要是通过InnoDB事务日志实现数据一致性的，InnoDB事务日志包括重做（redo）日志，以及回滚（undo）日志。

InnoDB事务日志与前面提高的日志不同，InnoDB事务日志由InnoDB存储引擎自行维护，而且内容不能被数据库管理员读取。

重做日志（redo）

重做日志主要是记录已经全部完成的事务,即执行了commit的日志，在默认情况下重做日志的值记录在iblogfile0 以及iblogfile1重做日志中。

[root@roverliang data]# pwd
/usr/local/webserver/mysql/data
[root@roverliang data]# ls ib*
ibdata1 ib_logfile0 ib_logfile1

回滚日志（undo）

回滚日志主要记录已经部分完成并且写入硬盘的未完成事务，默认情况情况下，回滚日志的信息记录在表空间文件，共享表空间文件ibdata1或者独享表空间未见ibd中。

由上图我们可以知道，回滚日志默认是记录在ibdta1中的。我的mysql系统版本为：5.6.24.

Checkpoint机制

MySQL服务器崩溃后，重新启动MySQL服务时，由于重做日志（redo）与回滚日志（undo）日志的存在，InnoDB通过回滚日志（undo）日志将所有已部分完成并写入硬盘的未完成事务进行回滚操作（rollback）。然后将重做日志（undo）日志中的事务全部重新执行一遍即可恢复所有的数据。但是数据量过大，为了缩短恢复的时间InnoDB引入了Checkpoint 机制。

脏页（dirty page）

当事务需要修改某条记录是，InnoDB首先将该数据所在的数据块从外存中读取到硬盘中，事务提交后，InnoDB修改数据页中的记录，这时缓存的数据页已经和外存中的数据块已经不一样了，此时缓存中的数据页称为脏页（dirty page）,脏页刷新到外存中，变为干净页（clean page）。

备注：一个内存页默认为4K，或者4K的倍数。你可以把内存想象成一本可以擦洗的书，每次MySQL读数据的时候，向内存申请几张干净的书页，然后书写上去。当数据刷新到硬盘之后，这些数据页马上被擦除，供其他程序使用。

日志序列号（log sequence number）

日志序列号（LSN）是日志空间中每条日志的结束点，用字节偏移量表示，在Checkpoin和恢复时使用。

Checkpoint 机制原理 假设在某个时间点，所有的脏页（dirty page）都被刷新到了硬盘上，这个时间点之前的所有重做日志（redo）就不需要重做了.系统就就下这个时间点重做日志的结尾位置作为Checkpoint,Checkpoint之前的重做日志也就不需要再重做了，可以放心的删除掉。为了更好的利用重做日志（redo）的空间,InnoDb采用轮循的策略使用重做日志空间，因此InnoDB的重做日志文件至少为2个。通过Checkpoint 机制，通过重做日志（redo）将数据库崩溃时已经完成但还没有来得及将缓存中已经修改但还未完全写入外存的事务进行重做（undo）操作，即可保证数据的一致性，也可以缩短恢复时间。

InnoDB重做日志(redo)的参数

innodb_log_buffer_size: 设置了重做日志缓存的大小。
innodb_log_files_in_group : 设置了日志文件组中重做日志（redo）日志的数量。
innodb_log_file_size: 设置了重做日志文件的大小，文件越大，恢复起来耗时越长。
innodb_mirrored_log_groups: 重做日志镜像文件组数量，只能设置为1.
innodb_log_group_home_dir： 设置日志文件组存放的目录，默认在数据库根目录下。

InnoDB回滚日志（undo）的参数

innodb_undo_directory: 设置回滚日志存放的目录。
innodb_undo_logs :设置回滚日志的回滚段大小，默认为128k
innodb_undo_tablespace:设置了回滚日志由多少个回滚日志文件组成，默认为0.
Warning 特别注意：安装MySQL后需要在my.cnf中设置回滚日志的参数，如果创建数据库以后再设置回滚日志的参数，MySQL就会报错，并且回滚日志建好后，就不能再次修改或者增加。

九 、日志文件备份

备份的时候可以使用flush logs，关闭当前的所有日志文件，然后产生新的日志文件。关闭日志文件后，可以采用物理方式备份。 另外flush logs可以添加具体的日志类型：

flush error logs
flush general logs
flush binary logs
flush slow logs

以上がMySQL ログ システムの詳細を説明するサンプル コードの共有の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。