MySQL はバックアップ データの一貫性をどのように保証しますか?

はじめに

データ セキュリティのために、データベースは定期的にバックアップする必要があります。これは誰もが知っています。しかし、データベースをバックアップするとき、最も恐れるのは書き込み操作です。 Brother Song は簡単な例を挙げています 例を見てみましょう:

データベースのバックアップ期間中にユーザーが注文すると、次の問題が発生する可能性があります:

• 在庫表の控除在庫。

• #在庫テーブルをバックアップします。

#注文テーブルのデータをバックアップします。

• #注文テーブルに注文を追加します。

• ユーザー テーブルはアカウント残高を差し引きます。

• ユーザー テーブルをバックアップします。

• 上記のロジックに従うと、バックアップ ファイル内の注文テーブルのレコードが欠落します。このバックアップ ファイルを使用してデータを復元すると、1 つのレコードが欠落し、データの不整合が発生します。

この問題を解決するために、MySQL にはさまざまな解決策が用意されていますので、それらを 1 つずつ説明し、メリットとデメリットを分析してみましょう。

1. 完全なデータベース読み取り専用

この問題を解決するために考えられる最も簡単な方法は、データベースのバックアップ中にデータベースを読み取り専用にし、書き込みできないように設定することです。データベース全体を読み取り専用に設定する方法も非常に簡単です。まず、次の SQL を実行して、対応する変数の値を確認します。 ##

show variables like 'read_only';

デフォルトでは

read_only が OFF、つまりクローズ状態になっていることがわかりますが、まずこれを ON に変更して、次の SQL を実行します。

set global read_only=1;

1 は ON、0 は OFF を意味し、実行結果は次のようになります:

This

read_only は有効ではありません。スーパー ユーザーなので、設定が完了したら、このセッションを終了し、スーパー権限のないユーザーを作成し、新しいユーザーでログインし、ログインします。成功したら、挿入 SQL を実行します。結果は次のようになります。

ご覧のとおり、このエラー メッセージは、現在の MySQL が読み取り専用 (クエリのみ可能) であり、現在の SQL を実行できないことを示しています。

読み取り専用属性を使用すると、バックアップ中のデータの不整合を心配する必要がありません。

But

read_only

通常、MySQL インスタンスがマスター ライブラリであるかスレーブ ライブラリであるかを識別するためにこれを使用します:

read_only=0。これは、インスタンスはマスター ライブラリです。データベース管理者の DBA は、メイン ライブラリが書き込み可能で利用可能かどうかを判断するために、時々インスタンスに非ビジネス データを書き込むことがあります。これは、メイン ライブラリ インスタンスが生きているかどうかを検出する一般的な方法です。

• read_only=1。インスタンスがスレーブ ライブラリであることを示します。通常、スレーブ ライブラリを定期的に探索する場合、「select 1;」などのステートメントの実行など、一部の読み取り操作のみが実行されます。

• したがって、

  read_only

  属性はバックアップには適していません。
read_only

属性が使用されると、ライブラリ全体が次のように設定されます。 readonly その後、クライアントで例外が発生した場合、データベースは読み取り専用状態のままとなり、ライブラリ全体が長時間書き込みできない状態になるため、リスクが高くなります。

したがって、このソリューションは適格ではありません。 2. グローバル ロック

グローバル ロックは、その名前が示すように、ライブラリ全体をロックします。ロックされたライブラリは追加、削除、変更できず、読み取りのみが可能です。

次に、グローバル ロックの使用方法を見てみましょう。 MySQL には、グローバル読み取りロックを増やすメソッドが用意されており、そのコマンドは

flush tables with read lock

(FTWRL) です。ライブラリ全体を読み取り専用状態にする必要がある場合、このコマンドを使用すると、他のスレッドによる追加、削除、変更などの操作がブロックされます。

図からわかるように、

flush tables with read lock;

コマンドを使用してテーブルをロックし、unlock tables を使用します。

コマンドはロック解除操作を完了できます (セッションが切断されたときにも自動的にロック解除されます)。

最初のセクションのソリューションと比較すると、FTWRL はある程度の進歩を遂げています。つまり、FTWRL コマンドの実行後にクライアントが異常に切断された場合、MySQL は自動的にグローバル ロックを解放し、ライブラリ全体を更新できるようになります。読み取り専用ステータスのままではなく、通常のステータスになります。 ＃＃＃＃＃＃しかし！ ！ ！ グローバル ロックを追加すると、バックアップ期間中はデータベース全体が読み取り専用状態になるため、ビジネスを停止できるのはデータベースのバックアップ期間中のみになります。

したがって、この方法は最良の解決策ではありません。

3. トランザクション

お友達が、以前ソング兄弟があなたと共有したデータベースの分離レベルをまだ覚えているかどうかはわかりません。4 つの分離レベルの 1 つは

repeatable です。 read ( REPEATABLE READ)

。これは MySQL のデフォルトの分離レベルでもあります。

ユーザーがこの分離レベルで別のトランザクションで同じ SELECT ステートメントを複数回実行した場合、結果は常に同じになります。 (トランザクションの実行によって生じるデータの変更は外部からは確認できないため)。

换言之，在 InnoDB 这种支持事务的存储引擎中，那么我们就可以在备份数据库之前先开启事务，此时会先创建一致性视图，然后整个事务执行期间都在用这个一致性视图，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作，并且这些更新操作不会被当前事务看到。

在可重复读的隔离级别下，即使其他事务更新了表数据，也不会影响备份数据库的事务读取结果，这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据。

具体操作也很简单，使用 mysqldump 备份数据库的时候，加上 -–single-transaction 参数即可。

为了看到 -–single-transaction 参数的作用，我们可以先开启 general_log，general_log 即 General Query Log，它记录了 MySQL 服务器的操作。当客户端连接、断开连接、接收到客户端的 SQL 语句时，会向 general_log 中写入日志，开启 general_log 会损失一定的性能，但是在开发、测试环境下开启日志，可以帮忙我们加快排查出现的问题。

通过如下查询我们可以看到，默认情况下 general_log 并没有开启：

我们可以通过修改配置文件 my.cnf(Linux)/my.ini（Windows），在 mysqld 下面增加或修改（如已存在配置项）general_log 的值为1，修改后重启 MySQL 服务即可生效。

也可以通过在 MySQL 终端执行 set global general_log = ON 来开启 general log，此方法可以不用重启 MySQL。

开启之后，默认日志的目录是 mysql 的 data 目录，文件名默认为 主机名.log。

接下来，我们先来执行一个不带 -–single-transaction 参数的备份，如下：

mysqldump -h localhost -uroot -p123 test08 > test08.sql

大家注意默认的 general_log 的位置。

接下来我们再来加上 -–single-transaction 参数看看：

mysqldump -h localhost -uroot -p123 --single-transaction test08 > test08.sql

大家看我蓝色选中的部分，可以看到，确实先开启了事务，然后才开始备份的，对比不加 -–single-transaction 参数的日志，多了开启事务这一部分。

以上がMySQL はバックアップ データの一貫性をどのように保証しますか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。