Mysql の大きな SQL ファイルの高速リカバリソリューションの共有

序文

MySQL データベースを使用する過程では、データベースのバックアップと復元が必要になることがよくあります。最も簡単で便利な方法は、SQL データファイルをエクスポートおよびインポートしてバックアップと復元を完了することです。ただし、プロジェクトが大きくなるにつれて、データの量はますます大きくなり、復元のたびに頭痛の種になります。

最近、プロジェクトから 5GB のデータベースをダウンロードしてローカルに復元したところ、40～50 分かかりました。今後のデータ拡張を考えると、データ量が増え、復元コストも増えるので、調べてみたところ、以下の設定で復元効率を向上できることがわかりました。

1. バックアップパラメータを変更する

まず、データベースをバックアップする際のパラメータを変更して、リカバリ効率を向上させる必要があります。

mysqldump --extended-insert

mysqldump の --extended-insert パラメータはバッチ挿入を示し、複数の挿入ステートメントを 1 つのステートメントにマージします。バックアップのインポート効率は、--extended-insert をオンにしていない場合と比べて 3 ～ 4 倍異なります。

--extended-insert=false を使用してエクスポートされた sql ファイルデータは、1 行に 1 つの挿入ステートメントがあり、実行効率が非常に低いです。

--extended-insert=true でエクスポートされたテーブルは下図のようになります。非常に長い挿入文がバッチで挿入されます。

2. MYSQLクイック挿入パラメータを調整する

データベースストレージエンジンが MYISAM の場合、このパラメータを 512M または 256M に設定できます。MyISAM は特別なツリーキャッシュを使用してバッチ挿入を高速化します。

関連ドキュメント: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_bulk_insert_buffer_size

デフォルト値は8M = 8388608バイトです

挿入バッファサイズを確認する

'%bulk%' のような変数を表示します。

挿入バッファサイズを設定する（グローバル）

グローバル bulk_insert_buffer_size を 1024*1024*512 に設定します。

挿入バッファサイズを設定する（セッション）

bulk_insert_buffer_size を 1024*1024*256 に設定します。

Mysqlを再起動したときに保持される値を設定する必要がある場合は、この設定をmy.cnfに追加する必要があります。

[mysqld]
バルク挿入バッファサイズ = 256M

3. 検査項目を閉じる

Innodb エンジンの場合、一部のシステムチェックをオフにして、より高速な挿入ソリューションを実現できます。

//自動コミットをオフにする SET autocommit=0;

// ユニークチェックをオフにする set unique_checks = 0;

//外部キーチェックをオフにする SET foreign_key_checks=0;

// バックアップ時に --extended-insert パラメータを有効にする

Innodb バルクデータロードに関する関連ドキュメント: https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-bulk-data-loading.html

4. 練習

上記の最適化を行うと、MySQL のリカバリ効率が即座に向上します。上記のパラメータの最適化を行う前は、データベースの復元に毎回 40 分かかっていました。設定後は、約 16 分しかかかりません。私のデータベースファイルの容量は約 5 GB です。