In der Kolumne „MySQL-Tutorial“ wird heute das Binlog von MySQL vorgestellt.
Grundlegendes Verständnis von Binlog 
Das Binärprotokoll von MySQL kann als das wichtigste Protokoll von MySQL bezeichnet werden. Es zeichnet alle DDL- und DML-Anweisungen (außer Datenabfrageanweisungen) in Form von Ereignissen auf und enthält auch ausgeführte Anweisungen Im Hinblick auf den Zeitaufwand ist das Binärprotokoll von MySQL transaktionssicher.
Im Allgemeinen führt das Einschalten des Binärprotokolls zu einem Leistungsverlust von etwa 1 % (siehe offizielles chinesisches Handbuch von MySQL, Version 5.1.24). Binär hat zwei wichtigste Verwendungsszenarien:
Erstens: MySQL-Replikation aktiviert Binlog auf der Master-Seite und der Master übergibt sein Binärprotokoll an Slaves, um Master-Slave-Datenkonsistenz zu erreichen.
Zweitens: Selbstverständlich werden die Daten wiederhergestellt, indem das mysqlbinlog-Tool zur Wiederherstellung der Daten verwendet wird.
Das Binärprotokoll umfasst zwei Arten von Dateien: Die Binärprotokoll-Indexdatei (das Dateinamensuffix ist .index) wird zum Aufzeichnen aller Binärdateien verwendet, und die Binärprotokolldatei (das Dateinamensuffix ist .index). .00000*) zeichnet alle DDL- und DML-Anweisungsereignisse der Datenbank (außer Datenabfrageanweisungen) auf.
1. Binlog aktivieren:
Öffnen Sie die MySQL-Konfigurationsdatei im vi-Editor
# vi /usr/local/mysql/etc/my.cnf Im [mysqld]-Block
# vi /usr/local/mysql/etc/my.cnf
在[mysqld] 区块
设置/添加 log-bin=mysql-bin 确认是打开状态(值 mysql-bin 是日志的基本名或前缀名)；
重启mysqld服务使配置生效
# pkill mysqld
# /usr/local/mysql/bin/mysqld_safe --user=mysql & Einstellungen /Add log-bin=mysql-bin Stellen Sie sicher, dass es geöffnet ist (der Wert mysql-bin ist der Basisname oder das Präfix des Protokolls);
Starten Sie den MySQL-Dienst neu, damit die Konfiguration wirksam wird
# pkill mysqld
# /usr/local/mysql/bin/mysqld_safe --user=mysql &
Zweitens können Sie sich auch beim MySQL-Server anmelden und prüfen, ob die Binärdatei log hat das Wort: Variable, die über die MySQL-Variablenkonfigurationstabelle aktiviert ist. Master-Protokolle anzeigen;
2. Zeigen Sie den Master-Status an, d ;
3. Aktualisieren Sie das Protokoll und generieren Sie von nun an eine neue nummerierte Binlog-Protokolldatei.
mysql> ; Durch Hinzufügen der Option -F beim Sichern von Daten mit mysqldump wird auch das Binlog-Protokoll aktualisiert.
mysql> eines Binlog-Protokolls:
1. Verwenden Sie die eigene Anzeigebefehlsmethode von mysqlbinlog:
Hinweis: Binlog ist eine Binärdatei und der normale Dateibetrachter cat More vi kann nicht geöffnet werden, und Sie müssen zum Anzeigen den integrierten Befehl mysqlbinlog verwenden das binlog-Protokoll und die Datenbankdatei im selben Verzeichnis (meine Umgebungskonfiguration und Installation besteht darin, /usr/local/mysql/data zu wählen)
Wenn bei Verwendung des mysqlbinlog-Befehls in Versionen unter 5 ein Fehler gemeldet wird, fügen Sie hinzu die Option „--no-defaults“
# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000013个In der folgenden Analyse:
# /usr/local/mysql/bin/mysql -uroot -p123456
mysql> show variables like 'log_%';
+----------------------------------------+---------------------------------------+
| Variable_name | Value |
+----------------------------------------+---------------------------------------+
| log_bin | ON | ------> ON表示已经开启binlog日志
| log_bin_basename | /usr/local/mysql/data/mysql-bin |
| log_bin_index | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_error | /usr/local/mysql/data/martin.err |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_throttle_queries_not_using_indexes | 0 |
| log_warnings | 1 |
+----------------------------------------+---------------------------------------+Nach dem Login kopieren
e
Hinweis: Server ID 1-Datenbank-Host-Servicenummer;
END_LOG_POS 665 POS-Punkt Es gibt viel Inhalt und es ist nicht einfach, POS-Punktinformationen zu unterscheiden und anzuzeigen:
mysql> log_name'] [FROM pos] [LIMIT [offset,] row_count];
Optionsanalyse: Geben Sie in 'log_name' den Binlog-Dateinamen an (nicht angegeben ist die erste Binlog-Datei)
from POS gibt an, welcher POS-Startpunkt beginnt ab welchem POS-Startpunkt beginnt (nicht angegeben ist der erste POS der gesamten Datei POS POS Startanzahl)
Limit [Office,] Matting (nicht angegeben 0)
row_count Gesamtzahl (nicht angegeben sind alle Zeilen)
Senden Teil der Abfrageergebnisse:
*************************** 20. row ***************************
Log_name: mysql-bin.000021 ----------------------------------------------> 查询的binlog日志文件名
Pos: 11197 ----------------------------------------------------------> pos起始点:
Event_type: Query ----------------------------------------------------------> 事件类型：Query
Server_id: 1 --------------------------------------------------------------> 标识是由哪台服务器执行的
End_log_pos: 11308 ----------------------------------------------------------> pos结束点:11308(即：下行的pos起始点)
Info: use `zyyshop`; INSERT INTO `team2` VALUES (0,345,'asdf8er5') ---> 执行的sql语句
*************************** 21. row ***************************
Log_name: mysql-bin.000021
Pos: 11308 ----------------------------------------------------------> pos起始点:11308(即：上行的pos结束点)
Event_type: Query
Server_id: 1
End_log_pos: 11417
Info: use `zyyshop`; /*!40000 ALTER TABLE `team2` ENABLE KEYS */
*************************** 22. row ***************************
Log_name: mysql-bin.000021
Pos: 11417
Event_type: Query
Server_id: 1
End_log_pos: 11510
Info: use `zyyshop`; DROP TABLE IF EXISTS `type`Nach dem Login kopieren
这条语句可以将指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos点的起始偏移，查询条数；
A.查询第一个(最早)的binlog日志：
mysql> show binlog events\G;
B.指定查询 mysql-bin.000021 这个文件：
mysql> show binlog events in 'mysql-bin.000021'\G;
C.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起：
mysql> show binlog events in 'mysql-bin.000021' from 8224\G;
D.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，查询10条
mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10\G;
E.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，偏移2行，查询10条
mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10\G;
五、恢复binlog日志实验(zyyshop是数据库)
1.假设现在是凌晨4:00，我的计划任务开始执行一次完整的数据库备份：
将zyyshop数据库备份到 /root/BAK.zyyshop.sql 文件中：
# /usr/local/mysql/bin/mysqldump -uroot -p123456 -lF --log-error=/root/myDump.err -B zyyshop > /root/BAK.zyyshop.sql
......
大约过了若干分钟，备份完成了，我不用担心数据丢失了，因为我有备份了，嘎嘎~~~
由于我使用了-F选项，当备份工作刚开始时系统会刷新log日志，产生新的binlog日志来记录备份之后的数据库“增删改”操作，查看一下：
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000023 | 120 | | |
+------------------+----------+--------------+------------------+Nach dem Login kopieren
也就是说， mysql-bin.000023 是用来记录4:00之后对数据库的所有“增删改”操作。
2.早9:00上班了，业务的需求会对数据库进行各种“增删改”操作~~~~~~~
@ 比如：创建一个学生表并插入、修改了数据等等：
CREATE TABLE IF NOT EXISTS `tt` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(16) NOT NULL,
`sex` enum('m','w') NOT NULL DEFAULT 'm',
`age` tinyint(3) unsigned NOT NULL,
`classid` char(6) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;Nach dem Login kopieren
导入实验数据
mysql> insert into zyyshop.tt(`name`,`sex`,`age`,`classid`) values('yiyi','w',20,'cls1'),('xiaoer','m',22,'cls3'),('zhangsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6');
Nach dem Login kopieren
查看数据
mysql> select * from zyyshop.tt;
+----+----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+----------+-----+-----+---------+
| 1 | yiyi | w | 20 | cls1 |
| 2 | xiaoer | m | 22 | cls3 |
| 3 | zhangsan | w | 21 | cls5 |
| 4 | lisi | m | 20 | cls4 |
| 5 | wangwu | w | 26 | cls6 |
+----+----------+-----+-----+---------+Nach dem Login kopieren
中午时分又执行了修改数据操作
mysql> update zyyshop.tt set name='李四' where id=4;
mysql> update zyyshop.tt set name='小二' where id=2;Nach dem Login kopieren
修改后的结果：
mysql> select * from zyyshop.tt;
+----+----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+----------+-----+-----+---------+
| 1 | yiyi | w | 20 | cls1 |
| 2 | 小二 | m | 22 | cls3 |
| 3 | zhangsan | w | 21 | cls5 |
| 4 | 李四 | m | 20 | cls4 |
| 5 | wangwu | w | 26 | cls6 |
+----+----------+-----+-----+---------+Nach dem Login kopieren
假设此时是下午18:00，莫名地执行了一条悲催的SQL语句，整个数据库都没了：
mysql> drop database zyyshop;
3.此刻杯具了，别慌！先仔细查看最后一个binlog日志，并记录下关键的pos点，到底是哪个pos点的操作导致了数据库的破坏(通常在最后几步)；
备份一下最后一个binlog日志文件：
# ll /usr/local/mysql/data | grep mysql-bin
# cp -v /usr/local/mysql/data/mysql-bin.000023 /root/Nach dem Login kopieren
此时执行一次刷新日志索引操作，重新开始新的binlog日志记录文件，理论说 mysql-bin.000023 这个文件不会再有后续写入了(便于我们分析原因及查找pos点)，以后所有数据库操作都会写入到下一个日志文件；
mysql> flush logs;
mysql> show master status;Nach dem Login kopieren
4.读取binlog日志，分析问题
方式一：使用mysqlbinlog读取binlog日志：
# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000023
方式二：登录服务器，并查看(推荐)：
mysql> show binlog events in 'mysql-bin.000023';
以下为末尾片段：
+------------------+------+------------+-----------+-------------+------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+------------+-----------+-------------+------------------------------------------------------------+
| mysql-bin.000023 | 922 | Xid | 1 | 953 | COMMIT /* xid=3820 */ |
| mysql-bin.000023 | 953 | Query | 1 | 1038 | BEGIN |
| mysql-bin.000023 | 1038 | Query | 1 | 1164 | use `zyyshop`; update zyyshop.tt set name='李四' where id=4|
| mysql-bin.000023 | 1164 | Xid | 1 | 1195 | COMMIT /* xid=3822 */ |
| mysql-bin.000023 | 1195 | Query | 1 | 1280 | BEGIN |
| mysql-bin.000023 | 1280 | Query | 1 | 1406 | use `zyyshop`; update zyyshop.tt set name='小二' where id=2|
| mysql-bin.000023 | 1406 | Xid | 1 | 1437 | COMMIT /* xid=3823 */ |
| mysql-bin.000023 | 1437 | Query | 1 | 1538 | drop database zyyshop |
+------------------+------+------------+-----------+-------------+------------------------------------------------------------+Nach dem Login kopieren
通过分析，造成数据库破坏的pos点区间是介于 1437--1538 之间，只要恢复到1437前就可。
5.现在把凌晨备份的数据恢复：
# /usr/local/mysql/bin/mysql -uroot -p123456 -v < /root/BAK.zyyshop.sql;
注: 至此截至当日凌晨(4:00)前的备份数据都恢复了。
但今天一整天(4:00--18:00)的数据肿么办呢？就得从前文提到的 mysql-bin.000023 新日志做文章了......
6.从binlog日志恢复数据
恢复语法格式：
# mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名
常用选项：
--start-position=953 起始pos点
--stop-position=1437 结束pos点
--start-datetime="2013-11-29 13:18:54" 起始时间点
--stop-datetime="2013-11-29 13:21:53" 结束时间点
--database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志)
不常用选项：
-u --user=name Connect to the remote server as username.连接到远程主机的用户名
-p --password[=name] Password to connect to remote server.连接到远程主机的密码
-h --host=name Get the binlog from server.从远程主机上获取binlog日志
--read-from-remote-server Read binary logs from a MySQL server.从某个MySQL服务器上读取binlog日志
小结：实际是将读出的binlog日志内容，通过管道符传递给mysql命令。这些命令、文件尽量写成绝对路径；
A.完全恢复(本例不靠谱，因为最后那条 drop database zyyshop 也在日志里，必须想办法把这条破坏语句排除掉，做部分恢复)
# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000021 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshop
Nach dem Login kopieren
B.指定pos结束点恢复(部分恢复)：
@ --stop-position=953 pos结束点
注：此pos结束点介于“导入实验数据”与更新“name='李四'”之间，这样可以恢复到更改“name='李四'”之前的“导入测试数据”
# /usr/local/mysql/bin/mysqlbinlog --stop-position=953 --database=zyyshop /usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshop
Nach dem Login kopieren
在另一终端登录查看结果(成功恢复了)：
mysql> select * from zyyshop.tt;
+----+----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+----------+-----+-----+---------+
| 1 | yiyi | w | 20 | cls1 |
| 2 | xiaoer | m | 22 | cls3 |
| 3 | zhangsan | w | 21 | cls5 |
| 4 | lisi | m | 20 | cls4 |
| 5 | wangwu | w | 26 | cls6 |
+----+----------+-----+-----+---------+Nach dem Login kopieren
C.指定pso点区间恢复(部分恢复)：
更新 name='李四' 这条数据，日志区间是Pos[1038] --> End_log_pos[1164]，按事务区间是：Pos[953] --> End_log_pos[1195]；
更新 name='小二' 这条数据，日志区间是Pos[1280] --> End_log_pos[1406]，按事务区间是：Pos[1195] --> End_log_pos[1437]；
c1.单独恢复 name='李四' 这步操作，可这样：
# /usr/local/mysql/bin/mysqlbinlog --start-position=1038 --stop-position=1164 --database=zyyshop /usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshop
Nach dem Login kopieren
也可以按事务区间单独恢复，如下：
# /usr/local/mysql/bin/mysqlbinlog --start-position=953 --stop-position=1195 --database=zyyshop /usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshop
Nach dem Login kopieren
c2.单独恢复 name='小二' 这步操作，可这样：
# /usr/local/mysql/bin/mysqlbinlog --start-position=1280 --stop-position=1406 --database=zyyshop /usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshop
Nach dem Login kopieren
也可以按事务区间单独恢复，如下：
# /usr/local/mysql/bin/mysqlbinlog --start-position=1195 --stop-position=1437 --database=zyyshop /usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshop
Nach dem Login kopieren
c3.将 name='李四'、name='小二' 多步操作一起恢复，需要按事务区间，可这样：
# /usr/local/mysql/bin/mysqlbinlog --start-position=953 --stop-position=1437 --database=zyyshop /usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshop
Nach dem Login kopieren
D.在另一终端登录查看目前结果(两名称也恢复了)：
mysql> select * from zyyshop.tt;
+----+----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+----------+-----+-----+---------+
| 1 | yiyi | w | 20 | cls1 |
| 2 | 小二 | m | 22 | cls3 |
| 3 | zhangsan | w | 21 | cls5 |
| 4 | 李四 | m | 20 | cls4 |
| 5 | wangwu | w | 26 | cls6 |
+----+----------+-----+-----+---------+Nach dem Login kopieren
E.也可指定时间区间恢复(部分恢复)：除了用pos点的办法进行恢复，也可以通过指定时间区间进行恢复，按时间恢复需要用mysqlbinlog命令读取binlog日志内容，找时间节点。
比如，我把刚恢复的tt表删除掉，再用时间区间点恢复
mysql> drop table tt;
@ --start-datetime="2013-11-29 13:18:54" 起始时间点
@ --stop-datetime="2013-11-29 13:21:53" 结束时间点
# /usr/local/mysql/bin/mysqlbinlog --start-datetime="2013-11-29 13:18:54" --stop-datetime="2013-11-29 13:21:53" --database=zyyshop /usr/local/mysql/data/mysql-bin.000021 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshopNach dem Login kopieren
总结：所谓恢复，就是让mysql将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已。
更多相关免费学习推荐：mysql教程(视频)
Das obige ist der detaillierte Inhalt vonVerstehen Sie das Binlog-Protokoll von MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!
