Linux_MySQL 上の binlog ファイルを使用して mysql データベースを復元する詳細な手順

1. ビンログの紹介

サーバーのバイナリ ログには、データベースのすべての追加、削除、変更が記録され (独自のサーバーでバイナリ ログが有効になっている場合)、これらの操作の実行時間も含まれます。これらのバイナリ内容を表示するには、mysqlbinlog コマンドを使用します。

使い方1:マスターとスレーブの同期

使用法 2: データベースの復元 (データベース ファイルがオンラインで失われた後に初めてこれについて知りました)

Mysqlbinlog コマンドの使用法：shell> mysqlbinlog [options] log_file...

1) mysqlbinlog オプションの例

一般的なオプションには以下が含まれます:

--開始日時

ローカルマシン時間以降の指定されたタイムスタンプをバイナリログから読み取ります。値: = "1470733768" または = "2016-08-09 5:09:28"

例:

リーリー

ローカルコンピュータのタイムスタンプ以下の指定された時刻をバイナリログから読み取ります。値は上記と同じです。

--開始位置

指定された位置イベント位置を開始としてバイナリログから読み取ります。値: = "2698"

例:

リーリー

指定された位置イベント位置をイベント終了としてバイナリログから読み込みます。値: = "2698"

2. 環境の準備とバックアップとリカバリ

1) mysqlをインストールした後、binlogを有効にチェックします

mysql> バイナリログを表示;

エラー 1381 (HY000): バイナリ ログを使用していません: 上記のプロンプトは、binlog を有効にするサーバーが存在しないことを示しています

/etc/my.cnfを変更します

mysqld オプションに次の行を追加します。

log-bin=mysql-bin

デフォルトでは、値が指定されていない場合、log-bin は mysqld-bin をインデックスとして使用し、mysqld-bin.00001 などを作成します。

mysqldを再起動するだけです。

2) ビンログを確認します

リーリー

3) まず生データを作成します。

リーリー

データを確認します:

リーリー

4）バックアップと復元（フルバックアップと復元）

ここでは、毎日のデータベースの完全バックアップ タスクをシミュレートします。

リーリー

シミュレーション中に操作エラーが発生し、データが不正に変更されました。

リーリー

現在は伝統的な方法で修復・修復を行っています。

リーリー

もう一度確認してください:

リーリー

データが復元されたことがわかります。

5) binlogを使用して復元をシミュレートします

元のテーブルを元にいくつかのデータを作成します。

リーリー

この時に誤ってデータを変更したり、ライブラリを削除してしまい、全てのデータが失われた場合、この時点での最新のバックアップファイルTest_DB_0809-16:50.sqlを使用してデータを復元すると、新たにデータが失われます。バックアップ後に挿入されたデータ。

注: 本当に最新のバックアップ ファイルを使用する場合は、最後の手段の状況 (バイナリログが削除される、ハードディスク全体がハングするなど...考えると恐ろしい...) の下にある必要があります。

誤操作をシミュレートし、ユーザー名を一括で変更します。

リーリー

いいえ、前の手順はそれほど厳しいものではありませんでした。ここではさらに容赦なくすべてのテーブルを削除しましょう。

リーリー

最初に binlog オプションをオンにしたため、binlog を使用してデータベースを復元しました。 binlog から始めましょう。まず binlog ファイルを確認します。現在、binlog がオンになってから mysql サービスが 2 回再起動されているため、binlog ファイルが 2 つあります (再起動されるたびに binlog ファイルが再生成されます)。 FLUSH LOGS コマンドを実行すると、再構築も行われます

;

mysql-bin.index ファイルに記録されるのは、log-bin オプションがオンになってから記録されたすべてのバイナリ ログのリストです。

注: 実際の運用環境でデータベースを復元する必要がある状況が発生した場合は、新しいデータの挿入を避けるためにユーザーにデータベースへのアクセスを許可せず、マスター/スレーブ環境ではマスター/スレーブをシャットダウンしてください。 。

mysqlbinlog コマンドを使用して binlog ファイルを表示します。最新のファイル mysql-bin.00002 を見てみましょう。

最後から削除操作があることがわかります。ただし、最後に削除操作が残っているため、完全に復元することはできません。

　　现在我的思路就是，先将第一个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数据库详细步骤，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对网站的支持！