Beispielcode-Freigabe, die die Details des MySQL-Protokollierungssystems erklärt
- 黄舟Original
- 2017-03-25 14:11:571340Durchsuche
Dieser Artikel fasst die detaillierten Informationen zum Protokollsystem in MySQL zusammen. Es ist sehr detailliert.
Jeder, der es in großem Umfang getan hat Systeme wissen das. Die Rolle von Protokollen sollte nicht unterschätzt werden. In der mittleren und späteren Phase eines Projekts werden Optimierungs- und Upgrade-Entscheidungen häufig auf der Grundlage von Protokollen getroffen. Wenn Sie also MySQL lernen, dürfen Sie den Protokollierungsteil natürlich nicht verpassen. Die in unseren Interviews besprochenen Optimierungen werden alle aus Protokollen abgeleitet. Das systematische Studium von MySQL-Protokollen kann uns helfen, Probleme genau zu lokalisieren und unser Arbeitsniveau zu verbessern. Darüber hinaus konzentriert sich die folgende Protokollreihe auf den Betrieb und die Wartung von DBA, das systematische Verständnis der Konfiguration aller Aspekte von MySQL, das Kennenlernen von sich selbst und dem Feind und die Herstellung von MySQL zu einem praktischen Data Warehouse für Sie.
1. MySQLs Protokolltyp Typ
Standardmäßig werden alle MySQL-Protokolle im Datenbankstammverzeichnis als Dateien gespeichert:
[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
Die Protokolltypen von MySQL sind wie folgt:
1. Fehlerprotokoll (Fehler), zugehörige Informationen wie das Starten, Ausführen oder Stoppen der MySQL-Dienstinstanz.
2. Allgemeines Abfrageprotokoll (allgemein), alle SQL-Anweisungen oder MySQL-Befehle, die von der MySQL-Dienstinstanz ausgeführt werden.
3. Binärprotokoll (binär), alle in der Datenbank ausgeführten Aktualisierungsanweisungen, mit Ausnahme von Select- und Show-Anweisungen.
4. Langsames Abfrageprotokoll (langsam), SQL-Anweisungen, deren Ausführungszeit den Einstellungswert long_query_time überschreitet, oder SQL-Anweisungen, die keine Indizes verwenden.
2. MySQL-Protokollcache
Ein schnelles, stabiles und zuverlässiges System, in dem der Cache eine wichtige Rolle spielen muss. Die MySQL-Protokollverarbeitung verwendet auch einen Caching-Mechanismus. MySQL-Protokolle werden zunächst im Speicher des MySQL-Servers gespeichert. Wenn die angegebene Speicherkapazität überschritten wird, werden die Protokolle im Speicher in den externen Speicher geschrieben (oder geleert) und in Form von Datenbanktabellen dauerhaft auf der Festplatte gespeichert oder Dateien.
3. MySQL-Fehlerprotokoll (Fehlerprotokoll)
Das Fehlerprotokoll von MySQL zeichnet hauptsächlich detaillierte Informationen zu jedem Start und Stopp der MySQL-Dienstinstanz sowie zur Ausführung auf Prozess der MySQL-Instanz. Warnungen oder Fehlermeldungen, die in generiert werden. Im Gegensatz zu anderen Protokollen muss das Fehlerprotokoll von MySQL aktiviert sein und kann nicht deaktiviert werden.
Standardmäßig lautet der Name der Fehlerprotokolldatei: hostname.err. Das Fehlerprotokoll zeichnet jedoch nicht alle Fehlerinformationen auf. Es werden nur kritische Fehler (kritisch) aufgezeichnet, die während der Ausführung der MySQL-Dienstinstanz auftreten.
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. Allgemeines MySQL-Abfrageprotokoll (allgemeines Protokoll)
Das allgemeine MySQL-Abfrageprotokoll zeichnet alle Vorgänge der MySQL-Dienstinstanz auf, z. B. Auswählen, Aktualisieren, Einfügen, Löschen und andere Vorgänge, unabhängig davon, ob der Vorgang erfolgreich ausgeführt wurde. Es gibt auch Informationen über die Verbindung und Trennung zwischen dem MySQL-Client und dem MySQL-Server, unabhängig davon, ob die Verbindung erfolgreich war oder fehlgeschlagen ist. Es gibt drei Parameter im Zusammenhang mit dem allgemeinen MySQL-Abfrageprotokoll.
[]()general_log mysql> show variables like 'general_log'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | general_log | OFF | +---------------+-------+ 1 row in set (0.01 sec)
Sie können das allgemeine Abfrageprotokoll aktivieren, indem Sie @@global.general_log = 1 setzen.
mysql> set @@global.general_log =1; mysql> show variables like 'general_log'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | general_log | ON | +---------------+-------+
Eine Änderung der MySQL-Variable auf diese Weise wird jedoch nur wirksam, während die aktuelle MySQL-Instanz ausgeführt wird. Sobald MySQL neu gestartet wird, wird sie auf den Standardwert zurückgesetzt Zustand. . Die Möglichkeit, es dauerhaft zu machen, besteht darin, die my.cnf-Datei von MySQL zu ändern. Fügen Sie nach der Konfigurationsdatei hinzu:
general_log = 1 general_log_fileSobald das allgemeine Abfrageprotokoll aktiviert ist, erstellt die MySQL-Dienstinstanz automatisch eine allgemeine Abfrageprotokolldatei. Der Parameter „general_log_file“ legt den physischen Speicherort fest der allgemeinen Abfrageprotokolldatei. Wie folgt:
mysql> show variables like 'general_log_file'; +------------------+-----------------------------------------------------------+ | Variable_name | Value | +------------------+-----------------------------------------------------------+ | general_log_file | /usr/local/webserver/extend_lib/mysql/data/roverliang.log | +------------------+-----------------------------------------------------------+Hinweis: Da das normale Abfrageprotokoll fast alle Vorgänge von MySQL aufzeichnet, wird bei Datenbankservern mit häufigem Datenzugriff die Aktivierung des normalen Abfrageprotokolls von MySQL erheblich reduziert Die Datenbankauslastung wird beeinträchtigt, daher wird empfohlen, das normale Abfrageprotokoll auszuschalten. Nur in besonderen Zeiträumen können Sie das normale Abfrageprotokoll vorübergehend öffnen, wenn Sie einige spezielle Abfrageprotokolle verfolgen müssen.
log_output
Der Parameter log_output legt den Inhalt des normalen Abfrageprotokolls und des langsamen Abfrageprotokolls fest, der in der Datenbanktabelle gespeichert werden soll. Sie können set @@global.log_output='table' verwenden, um normale Abfrageprotokolle und langsame Abfrageprotokolle in der allgemeinen Tabelle und der slow_log-Tabelle in der MySQL-Systemdatenbank zu speichern. Es ist erwähnenswert, dass die Speicher-Engine dieser beiden Tabellen CSV ist. Danach können Sie beim Anzeigen des neuen normalen Abfrageprotokollinhalts SQL-Anweisungen verwenden. 5. MySQL-langsames Abfrageprotokoll (langsames Protokoll)set @@global.log_output = 'table'; mysql> show variables like 'log_output'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | TABLE | +---------------+-------+Fragen im Zusammenhang mit langsamen Abfrageprotokollen Während des Interviews spricht der Interviewer sehr gern über dieses Problem. In der Vergangenheit konnte ich nur über die MySQL-Master-Slave-Architektur sprechen und MySQL in allen Aspekten optimieren, aber ich verstand nicht wirklich, wie man langsame Abfragen und damit verbundene Konfigurationen ermöglicht.
使用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
Das obige ist der detaillierte Inhalt vonBeispielcode-Freigabe, die die Details des MySQL-Protokollierungssystems erklärt. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!