1. Einführung in Binlog
Das Binärprotokoll des Servers zeichnet alle Hinzufügungen, Löschungen und Änderungen der Datenbank auf (vorausgesetzt, Binlog ist auf Ihrem eigenen Server aktiviert) und enthält auch die Ausführungszeit dieser Vorgänge. Um diesen binären Inhalt anzuzeigen, können wir ihn mit dem Befehl mysqlbinlog anzeigen.
Zweck 1: Master-Slave-Synchronisation
Zweck 2: Wiederherstellung der Datenbank (ich habe davon erst erfahren, nachdem eine Datenbankdatei online verloren gegangen war)
Mysqlbinlog-Befehlsverwendung: Shell> mysqlbinlog [Optionen] log_file ...
1) Beispiel für die Mysqlbinlog-Option
Zu den gängigen Optionen gehören die folgenden:
--start-datetime
Lesen Sie die angegebene Zeit aus dem Binärprotokoll, die dem Zeitstempel des lokalen Computers entspricht oder später liegt. Werte wie:="1470733768" oder="2016-08-09 5:09:28"
Beispiel:
[root@hcloud ~]# mysqlbinlog --start-datetime="2016-08-09 5:05:27" /var/lib/mysql/mysql-bin.000001 --stop-datetime
Lesen Sie die angegebene Zeit aus dem Binärprotokoll, die kleiner oder gleich dem Zeitstempel des lokalen Computers ist. Der Wert ist derselbe wie oben
--start-position
Lesen Sie als Start die angegebene Positionsereignisposition aus dem Binärprotokoll. Wert:="2698"
Beispiel:
[root@hcloud ~]# mysqlbinlog --start-position="2698" /var/lib/mysql/mysql-bin.000001 --stop-position
Lesen Sie die angegebene Positionsereignisposition aus dem Binärprotokoll als Ereignisende. Wert:="2698"
2. Umgebungsvorbereitung sowie Sicherung und Wiederherstellung
1) Überprüfen Sie nach der Installation von MySQL, ob Binlog aktiviert ist
mysql> BINÄRE PROTOKOLLE ANZEIGEN;
FEHLER 1381 (HY000): Sie verwenden keine Binärprotokollierung: Die obige Eingabeaufforderung zeigt an, dass kein Server zum Aktivieren von Binlog vorhanden ist
Ändern Sie /etc/my.cnf
Fügen Sie in der Option mysqld eine Zeile wie folgt hinzu:
log-bin=mysql-bin
Wenn kein Wert angegeben wird, verwendet log-bin standardmäßig mysqld-bin als Index und erstellt mysqld-bin.00001 usw.
Starten Sie mysqld einfach neu.
2) Binlog überprüfen
mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 106 | +------------------+-----------+ 1 row in set (0.00 sec)
3) Erstellen Sie zunächst einige Rohdaten.
mysql> create database Test_DB; Query OK, 1 row affected (0.00 sec) mysql> use Test_DB; Database changed mysql> CREATE TABLE OneTb(id INT(10) NOT NULL,name varchar(20),age INT(10)); Query OK, 0 rows affected (0.00 sec) mysql> insert into OneTb values (1,'user1',18); mysql> insert into OneTb values (2,'user2',19); insert into OneTb values (3,'user3',20);
Überprüfen Sie die Daten:
mysql> select * from OneTb; +----+-------+------+ | id | name | age | +----+-------+------+ | 1 | user1 | 18 | | 2 | user2 | 19 | | 3 | user3 | 20 | +----+-------+------+ 3 rows in set (0.00 sec)
4) Sichern und Wiederherstellen (vollständige Sicherung und Wiederherstellung)
Hier simulieren wir die tägliche Aufgabe zur vollständigen Sicherung der Datenbank.
[root@hcloud ~]# mysqldump -uroot -p Test_DB > /data/mysqlbackup/Test_DB_0809-16:50.sql Enter password:
Während der Simulation ist ein Betriebsfehler aufgetreten und die Daten wurden falsch geändert.
mysql> update OneTb set age = 15; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select * from OneTb; +----+-------+------+ | id | name | age | +----+-------+------+ | 1 | user1 | 15 | | 2 | user2 | 15 | | 3 | user3 | 15 | +----+-------+------+ 3 rows in set (0.00 sec)
Jetzt verwenden wir die traditionelle Methode zur Wiederherstellung.
[root@hcloud ~]# mysql -uroot -p Test_DB < /data/mysqlbackup/Test_DB_0809-16\:50.sql
Überprüfen Sie noch einmal:
mysql> select * from Test_DB.OneTb; +----+-------+------+ | id | name | age | +----+-------+------+ | 1 | user1 | 18 | | 2 | user2 | 19 | | 3 | user3 | 20 | +----+-------+------+ 3 rows in set (0.00 sec)
Sie können sehen, dass die Daten wiederhergestellt wurden.
5) Verwenden Sie Binlog, um die Wiederherstellung zu simulieren
Erstellen Sie mehrere Daten basierend auf der Originaltabelle.
mysql> insert into Test_DB.OneTb values(4,'user4',21),(5,'user5',22),(6,'user6',23); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from Test_DB.OneTb; +----+-------+------+ | id | name | age | +----+-------+------+ | 1 | user1 | 18 | | 2 | user2 | 19 | | 3 | user3 | 20 | | 4 | user4 | 21 | | 5 | user5 | 22 | | 6 | user6 | 23 | +----+-------+------+ 6 rows in set (0.00 sec)
Wenn wir zu diesem Zeitpunkt versehentlich die Daten ändern oder die Bibliothek löschen, was zu einem vollständigen Datenverlust führt, werden die Daten anschließend neu eingefügt, wenn wir zu diesem Zeitpunkt die neueste Sicherungsdatei Test_DB_0809-16:50.sql verwenden, um die Daten wiederherzustellen die Sicherung geht verloren.
Hinweis: Wenn Sie wirklich die aktuellste Sicherungsdatei verwenden, muss dies der letzte Ausweg sein (z. B. wird das Binlog gelöscht, die gesamte Festplatte hängt ... Es ist beängstigend, darüber nachzudenken ...).
Simulieren Sie Fehlbedienungen und ändern Sie den Benutzernamen stapelweise.
mysql> update Test_DB.OneTb set name='user10'; Query OK, 6 rows affected (0.00 sec) Rows matched: 6 Changed: 6 Warnings: 0
Nein, der vorherige Schritt war nicht rücksichtslos genug. Lassen Sie uns hier noch rücksichtsloser sein und alle Tabellen löschen
mysql> drop table Test_DB.OneTb; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 3 Current database: *** NONE *** Query OK, 0 rows affected (0.00 sec)
Da wir zu Beginn die Binlog-Option aktiviert hatten, verwendeten wir Binlog, um die Datenbank wiederherzustellen. Beginnen wir mit dem Binlog. Überprüfen Sie zunächst die Binlog-Datei. Derzeit wurde mein MySQL-Dienst seit dem Einschalten von Binlog zweimal neu gestartet, sodass es zwei Binlog-Dateien gibt (bei jedem Neustart wird eine Binlog-Datei neu generiert. Eine andere Situation ist zum Ausführen des FLUSH LOGS-Befehls wird auch eins neu erstellt;
Was in der Datei mysql-bin.index aufgezeichnet wird, ist: eine Liste aller Binärprotokolle, die seit der Aktivierung der Log-Bin-Option aufgezeichnet wurden.
Hinweis: Wenn Sie in einer tatsächlichen Produktionsumgebung auf eine Situation stoßen, in der die Datenbank wiederhergestellt werden muss, erlauben Sie Benutzern keinen Zugriff auf die Datenbank, um das Einfügen neuer Daten zu verhindern, und fahren Sie in einer Master-Slave-Umgebung die Datenbank herunter Meister-Sklave.
Verwenden Sie den Befehl mysqlbinlog, um die Binlog-Datei anzuzeigen. Schauen wir uns die neueste Datei mysql-bin.00002 an.
Am Ende ist ersichtlich, dass es sich um einen Löschvorgang handelt. Wir können es jedoch nicht vollständig wiederherstellen, da am Ende noch ein Löschvorgang erfolgt.
现在我的思路就是，先将第一个binlog 和第二个binlog 文件导出来à利用指定的position位置的方式（过滤掉删除表操作和update Test_DB.OneTb set name='user10';这条语句 ），导出2个sql 语句，最后我们将2个sql 合成一个sql，导入到数据库中即可。
我们先用mysqlbinlog命令找到update 那条语句的位置，然后指定position 将mysql-bin.00001 导出来。
[root@hcloud ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000001 …. #160809 5:09:28 server id 1 end_log_pos 2698 Query thread_id=17 exec_time=0 error_code=0 SET TIMESTAMP=1470733768/*!*/; SET @@session.foreign_key_checks=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; insert into Test_DB.OneTb values(4,'user4',21),(5,'user5',22),(6,'user6',23) /*!*/; # at 2698 #160809 5:19:49 server id 1 end_log_pos 2795 Query thread_id=17 exec_time=0 error_code=0 SET TIMESTAMP=1470734389/*!*/; update Test_DB.OneTb set name='user10' /*!*/; # at 2795 #160809 5:30:38 server id 1 end_log_pos 2814 Stop DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
从上面可以看到我们在做插入正常数据后的position 是2698，那么使用下面的命令导出sql
[root@hcloud ~]# mysqlbinlog --stop-position="2698" /var/lib/mysql/mysql-bin.000001 > Backup_1.sql
然后导出mysql-bin.00002的sql 语句（注：由于演示操作，该文件只有一个drop 表操作，所以不做处理，但是在实际环境中，由于中途可能会有重启数据库操作，那时就需要检测最新的binlog有没有业务需要的语句。）
sql 语句已经导出来了。我们可以利用该语句直接恢复所有正常的数据。
注：本次恢复没有利用到之前的完整备份，因为我是开启binlog后，然后才做的所有建库建表操作，第一个binlog文件里已经记录了所有的数据库操作，所以不需要使用之前的完整备份（另外：实际的生产环境，还是需要利用到完整备份的，因为线上环境可能会有N多个binlog文件，所以需要利用到完整备份和最新的binlog文件来结合恢复）
开始恢复前，我们将原有的Test_DB数据库也给干掉吧。毕竟我们的binlog中有创建操作
mysql> DROP DATABASE Test_DB; Query OK, 0 rows affected (0.03 sec)
恢复数据库时还可以利用在登陆mysql 后，用source 命令导入sql语句，这里暂不介绍
[root@hcloud ~]# mysql -uroot -p < Backup_1.sql
Enter password:
恢复完成后，我们检查下表的数据是否完整
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | Test_DB | | mysql | +--------------------+ 3 rows in set (0.00 sec) mysql> select * from Test_DB.OneTb; +----+-------+------+ | id | name | age | +----+-------+------+ | 1 | user1 | 18 | | 2 | user2 | 19 | | 3 | user3 | 20 | | 4 | user4 | 21 | | 5 | user5 | 22 | | 6 | user6 | 23 | +----+-------+------+ 6 rows in set (0.00 sec)
Ok完整的都恢复过来了。
三、总结
1) 恢复方式
a) 利用最新一次的完整备份加binlog 指定事件起始时间和终止时间或者position恢复数据库
b) 利用所有binlog指定事件起始位置和终止时间来合并sql文件恢复数据库（此方法要确保binlog文件的完整）
c) 利用mysqldump 使用完整恢复。（在确保最新一次的完整备份后的数据不重要，允许丢掉的情况下，直接恢复。该方法最简单、效率最高）
2) 附:官方建议的备份原则（为了能睡个好觉….嗯，是的）
a) 在mysql安装好并运行时，就始终开启 log-bin选项，该日志文件位于datadir目录下，也要确保该目录所在存储介质是安全的。
b) 定期做完整的mysql 备份。
c) 定期使用 FlUSH LOGS 或者 mysqladmin flush-logs ，该操作会关闭当前的二进制日志文件，并新建一个binlog日志文件。(和重启mysql后新建的binlog操作一样)。以备份binlog日志，利用binlog日志也可以做增量备份。
以上所述是小编给大家介绍的Linux上通过binlog文件恢复mysql数据库详细步骤，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对网站的支持！
