MySQL の運用および保守のバイナリ ログ

MySQL バイナリ ログには、データ変更を引き起こす、またはデータ変更を引き起こす可能性のある SQL ステートメントが保存されます。リアルタイムのリモート災害復旧バックアップ、読み取り/書き込み分離、データ復旧などの機能は、バイナリ ログを通じて実行できます。次に、Mysql バイナリ ログを見てみましょう。

bin-log ログを有効にする

Mysql はデフォルトでは bin-log ログを有効にしません。設定を自分で追加する必要があります。

log-bin=mysql-bin
binlog_format=mixed
server-id   = 1
expire_logs_days = 10

log-bin この項目を設定すると、バイナリログ機能が有効になります。 mysql-bin は bin-log ログ ファイル名です。

expire_logs_days = 10 は、過去 10 日間の bin-log ログのみが保存されることを示します。

一般に、bin-log ログは mysql インストール パス /var/

に保存されます。運用とメンテナンスのヒント: バイナリ ログ ファイルとデータベース データ ファイルを次の場所に置かないことをお勧めします。データ ファイルを保存しているハード ドライブが破損した場合は、別のハード ドライブのバイナリ ログを使用してデータを回復できます

いくつかの便利なコマンド

• flush logs: 新しい bin-log ログを生成します。

• #show master status: 最新の bin-log ログのステータスを表示します。

#マスターをリセット: すべての bin-log ファイルをクリア
mysql > マスターのステータスを表示

Mysql ログの表示ログはバイナリ ログであるため、一般的なコマンド cat または vim を使用して表示すると文字化けします。コードです。 Mysql は mysqlbinlog ツールを提供します。それを使用して表示します。

./mysqlbinlog ../var/mysql-bin.000015
……
# at 123
#200601  8:35:19 server id 1  end_log_pos 154 CRC32 0xd25b404e  Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
……

at: SQL 開始時の pos ノード
server_id: データベース ホストのサービス番号;
end_log_pos 154: SQL の最後にある pos ノード
Mysqlbinlog の共通オプションは次のとおりです:

--start-datetime : ローカル コンピュータのタイムスタンプ以降の指定時刻をバイナリ ログから読み取ります。
--stop-datetime: ローカル コンピュータのタイムスタンプ以降の指定時刻をバイナリ ログから読み取ります。タイムスタンプより大きいか、ローカル コンピュータと同じです。 時刻の値は上記と同じです。
--start-position: 指定された位置イベント位置をバイナリ ログから開始として読み取ります。
--stop-position: 指定された位置イベント位置を
-d 時点のイベントとしてバイナリ ログから読み取ります。-- Database= name: 指定されたデータベースのログ操作のみを表示します

bin ログ ログを使用してデータを回復します

SQL ファイルのエクスポート コマンド: mysqldump データベース名 [データ テーブル名 1 [データ テーブル名 2...]] > 外部ファイル ディレクトリ (.sql の使用を推奨)
sql ファイル インポートデータベース: mysql - u** -p** データベース名 < バックアップ ファイル ディレクトリ
ここでシナリオをシミュレートします: データベースは毎晩 3 時に定期的にバックアップされ、 Web サイトは翌日の半日は通常通り稼働していましたが、午後 5 時に突然、プログラマー A が DELETE 中に誤って WHERE 条件を追加しなかったため、テーブルの 1 つ内のすべてのデータが失われました。そこでシャオ・アはテクニカルディレクターのダーシェンを見つけ、データの復旧を手伝ってほしいと頼んだ。

binlog_test データベースにはユーザー テーブルが 1 つだけあります。

午前 3 時のバックアップ前のデータは次のとおりです。

+---------+----------+---------------------+
| user_id | username | add_time            |
+---------+----------+---------------------+
|       1 | gwx      | 2018-07-05 13:00:31 |
|       2 | snn      | 2018-07-05 14:00:00 |
|       3 | zy       | 2018-07-05 15:00:00 |
+---------+----------+---------------------+

午前 3 時朝、データはバックアップされました

mysqldump binlog_test -l -F > /root/sql_backup/20180706.sql
ll /root/sql_backup/
总用量 4
-rw-r--r-- 1 root root 2149 7月   6 13:42 20180706.sql
=======数据备份完成=========

Web サイトは一定期間正常に動作しており、多くのユーザーが登録しています。

INSERT INTO `user` (username) values('user1'),('user2'),('user3');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
select * from user;
+---------+----------+---------------------+
| user_id | username | add_time            |
+---------+----------+---------------------+
|       1 | gwx      | 2018-07-05 13:00:31 |
|       2 | snn      | 2018-07-05 14:00:00 |
|       3 | zy       | 2018-07-05 15:00:00 |
|       4 | user1    | 2018-07-06 15:01:18 |
|       5 | user2    | 2018-07-06 15:01:18 |
|       6 | user3    | 2018-07-06 15:01:18 |
+---------+----------+---------------------+
==============新增了3个用户user1 user2 及user3==============

午後 5 時、Little A愚かな行動を始めました

DELETE FROM user;
Query OK, 6 rows affected (0.00 sec)
=========没where条件，数据全没了===========

リトル A は、データの復元を手伝ってくれる大賢者を見つけました。大賢者が最初に昨日のデータを復元しました。データは午前 3 時に復元されました。

service nginx stop;  # 大圣先关闭了nginx，使网站用户暂时访问不了数据库
Stoping nginx...  done 
MariaDB [binlog_test]> flush logs;  #生成新的binlog日志
MariaDB [binlog_test]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |     1536 |              |                  |
+------------------+----------+--------------+------------------+
mysql -v -f binlog_test < /root/sql_backup/20180706.sql

この時点で、大賢者は昨夜の午前 3 時にデータを復元しました

MariaDB [binlog_test]> select * from user;
+---------+----------+---------------------+
| user_id | username | add_time            |
+---------+----------+---------------------+
|       1 | gwx      | 2018-07-05 13:00:31 |
|       2 | snn      | 2018-07-05 14:00:00 |
|       3 | zy       | 2018-07-05 15:00:00 |
+---------+----------+---------------------+
=============昨晚凌晨三点数据恢复完成===============

次に、午前 3 時から DELETE までのデータを復元しました

まず削除位置を見つけます。バックアップ後のログは 000002 です削除後のフラッシュ ログも 000003 になるため、000002 の削除前の位置を見つけるだけです。

# /usr/local/mariadb/bin/mysqlbinlog --stop-position=629  >
'mysql-bin.000002' >
| mysql binlog_test;

MariaDB [binlog_test]> select * from user;
+---------+----------+---------------------+
| user_id | username | add_time            |
+---------+----------+---------------------+
|       1 | gwx      | 2018-07-05 13:00:31 |
|       2 | snn      | 2018-07-05 14:00:00 |
|       3 | zy       | 2018-07-05 15:00:00 |
|       4 | user1    | 2018-07-06 15:01:18 |
|       5 | user2    | 2018-07-06 15:01:18 |
|       6 | user3    | 2018-07-06 15:01:18 |
+---------+----------+---------------------+
==============数据都回来了========================

以上がMySQL の運用および保守のバイナリ ログの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。