MySQL スケジュールバックアップタスクの簡単な分析

導入

実稼働環境では、データの損失を回避するために、通常、データベースは定期的にバックアップされます。 Linux の crontab コマンドは、データベースを定期的にバックアップするのに役立ちます。まず、crontab コマンドについて簡単に見てみましょう。やり方がわかっている場合は、次のコンテンツ「mysql バックアップ」に進んでください。
この記事の MySQL データベースは Docker コンテナにインストールされており、説明の例として使用されています。 Dockerコンテナにインストールされていない場合は、参照することもできます。

スケジュールされたタスクを管理する

スケジュールされたタスクを書き込むには、crontab -e を使用します。

0 5 * * 1 [コマンド]

最初の 5 つの数字はそれぞれ分、時間、日、月、週を表し、その後のcommandは実行コマンドです。
毎日午後8時にスケジュールされたタスクを実行する必要がある場合は、次のように記述します。

0 8 * * * [コマンド]

拡張機能:

crontab -lはスケジュールされたタスクを表示できます
crontab -rは現在のユーザーのスケジュールされたタスクをすべて削除します

MySQL バックアップ

すぐに始めましょう

ここで、私の MySQL データベースは Docker コンテナです。毎晩 8 時にスケジュールされたタスクを実行する必要がある場合は、次のように記述できます。
まず、crontab -e コマンドを実行します。

0 8 * * * docker exec mysql_container mysqldump -uroot -proot_password database_name > /var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql

mysql_containerはデータベースコンテナの名前です
mysqldumpはmysqlデータベースからデータをエクスポートするコマンドです。
-u ルートアカウントを入力します
-p ルートパスワードを入力してください
database_name バックアップするデータベースの名前
/var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql バックアップファイル、その後にファイル名の形式が続きます

要件がなく、単にバックアップしたいだけの場合は、上記のコマンドを使用してスケジュールされたバックアップを実行できます。

小さな落とし穴: MySQL をバックアップするときに、 docker exec -it mysqldump ... などのコマンドを使用してbashスクリプトを作成しました。 -i パラメータは対話的な意味を持つため、 crontabでスケジュールされたタスクを実行しても、 sqlファイルにデータは出力されませんでした。したがって、 crontabを使用して Docker コンテナを定期的にバックアップする場合は、 -iパラメータを追加しないでください。

Crontab の最適化

crontab -eに直接実行するコマンドを記述することはお勧めしません。タスクが多すぎると、ファイルが乱雑になってしまいます。
データベースバックアップコマンドをbashスクリプトに記述することをお勧めします。 crontabで次のように呼び出すだけです。次の内容の/var/backups/mysql/mysqldump.shファイルを作成します。

docker exec mysql_container mysqldump -uroot -pmypassword database_name > /var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql

次に、ファイルを現在のユーザーが実行できるようにします。

chmod 711 /var/backups/mysql/mysqldump.sh

crontab -e コマンドを実行し、次のように変更します。

0 20 * * * /var/backups/mysql/mysqldump.sh

これはより標準化されています。

MySQL バックアップの最適化

SQL ファイルは比較的大きいため、通常は圧縮されます。そうしないと、ディスク領域が大きくなりすぎてしまいます。
上記の crontab の最適化を行ったと仮定すると、mysqldump.sh スクリプトを次のように変更できます。

エクスポート mysqldump_date=$(日付 +%Y%m%d_%H%M%S) && \
docker exec mysql_container mysqldump -uroot -pmypassword database_name> /var/backups/mysql/$mysqldump_date.sql && \
/var/backups/mysql/$mysqldump_date.sql を gzip します。
/var/backups/mysql/ を見つけます -name "*.sql" -mtime +15 -exec rm -f {} \;

export 、バックアップおよび圧縮コマンドで使用するために、システム内の変数 mysqldump_date を定義します。
gzipは圧縮コマンドです。デフォルトでは、圧縮後にソースファイルは削除され、.gzファイルに圧縮されます。
コマンドfind ...は、 /var/backups/mysql/ディレクトリで、作成時間が 15 日前(-mtime +15 ) でファイル名のサフィックスが.sqlであるすべてのファイルを検索し、削除コマンド -exec rm -f {} \; を実行することを意味します。一般的な意味は、MySQL バックアップファイルは 15 日間のみ保持されるということです。 15 日以上経過した投稿をすべて削除します。

データ復旧

誤ってdrop databaseを実行した場合は、落ち着いてください。まず、データベースが削除された場所にデータベースを作成する必要があります。

>mysql データベース database_name を作成します。

次に、最新のバックアップを復元します。バックアップを復元するコマンド:

docker exec -i mysql_container mysql -uroot -proot_password database_name < /var/backups/mysql/20200619_120012.sql

バックアップファイルのデータは復元されましたが、バックアップ時点以降のデータは復元されませんでした。
たとえば、午後 8 時にスケジュールされたバックアップを実行し、午後 9 時にデータベースを削除した場合、午後 8 時から午後 9 時までの 1 時間のデータはバックアップされません。このとき、binlog ログを使用する必要があります。

バイナリログ

Binlog は、ID = 3 の行の money フィールドに 1 を追加するなど、データ変更のロジックを記録する MySQL のアーカイブログです。
まず、mysql にログインし、現在 binlog ファイルがいくつあるかを照会します。

> mysql バイナリログを表示します。
+---------------+-----------+-----------+
| ログ名 | ファイルサイズ | 暗号化 |
+---------------+-----------+-----------+
| binlog.000001 | 729 | いいえ |
| binlog.000002 | 1749 | いいえ |
| binlog.000003 | 1087 | いいえ |
+---------------+-----------+-----------+

現在書き込まれているバイナリログを表示する

mysql> マスターステータスを表示します\G;

新しい binlog ファイルを生成します。MySQL の以降のすべての操作は、新しい binlog ファイルに書き込まれます。通常、このコマンドは、データを復元するときに最初に実行されます。

mysql> ログをフラッシュする

binlog ログを表示

mysql> 'binlog.000003' の binlog イベントを表示します。

ヒント: MySQL コンテナを初期化するときに、パラメータ --binlog-rows-query-log-events=ON を追加します。または、コンテナ内の /etc/mysql/my.cnf ファイルを変更し、パラメータ binlog_rows_query_log_events=ON を追加して、mysql コンテナを再起動します。これにより、元の SQL が binlog ファイルに追加されます。

データの回復

上記の例の文章を元に戻します。

スケジュールされたバックアップは午後 8 時に実行されますが、データベースは午後 9 時に削除されます。そのため、午後 8 時から午後 9 時までの 1 時間以内のデータはバックアップされません。。

まず、mysqlコンテナに入った後、/var/lib/mysqlディレクトリに切り替えて、binlogファイルの作成日を確認します。

/var/lib/mysql に移動します
ls -l
...
-rw-r----- 1 mysql mysql 729 6月19日 15:54 binlog.000001
-rw-r----- 1 mysql mysql 1749 6月19日 18:45 binlog.000002
-rw-r----- 1 mysql mysql 1087 6月19日 20:58 binlog.000003
...

ファイルの日付から、時刻は 2020-06-21 であり、binlog.000002 ファイルの最終更新時刻は 18:45 であることがわかります。したがって、午後 8 時のバックアップには binlog.000002 のデータが含まれているはずです。
binlog.000003 の最終更新日は 20:58 なので、復元する必要があるデータは、午後 8 時の完全バックアップ + binlog.000003 の 20:00 より前のデータ - drop database コマンドが実行された時間です。

復元コマンドの形式:

mysqlbinlog [オプション] ファイル | mysql -uroot -proot_password データベース名

mysqlbinlog の共通パラメータ:

--start-datetime 開始時刻、形式 2020-06-19 18:00:00
--stop-datetime 終了時刻、上記と同じ形式
--start-positon 開始位置 (binlog ファイルを表示する必要がある)
--stop-position 終了位置、上記と同じ
...

バックアップデータと binlog データを復元する前に、MySQL にログインしてflush logs実行し、新しいbinlogログを生成することをお勧めします。これにより、復元する必要があるbinlogファイルに集中できます。
まず、binlog ログをチェックして、 drop database操作がどこで実行されたかを確認する必要があります。

mysql> 'binlog.000003' の binlog イベントを表示します。
+---------------+-----+----------------+------------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ログ名 | 位置 | イベント タイプ | サーバー ID | ログ終了位置 | 情報 |
+---------------+-----+----------------+------------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| binlog.000003 | 4 | Format_desc | 1 | 125 | サーバーバージョン: 8.0.20、Binlog バージョン: 4 |
| binlog.000003 | 125 | 前のgtids | 1 | 156 | |
| binlog.000003 | 156 | Anonymous_Gtid | 1 | 235 | @@SESSION.GTID_NEXT= 'ANONYMOUS' に設定 |
| binlog.000003 | 235 | クエリ | 1 | 318 | 開始 |
| binlog.000003 | 318 | Rows_query | 1 | 479 | # INSERT INTO `product_category` SET `name` = 'Bedding'、`create_time` = 1592707634、`update_time` = 1592707634、`lock_version` = 0 |
| binlog.000003 | 479 | Table_map | 1 | 559 | table_id: 139 (hotel_server.product_category) |
| binlog.000003 | 559 | Write_rows | 1 | 629 | table_id: 139 フラグ: STMT_END_F |
| binlog.000003 | 629 | Xid | 1 | 660 | COMMIT /* xid=2021 */ |
| binlog.000004 | 660 | Anonymous_Gtid | 1 | 739 | @@SESSION.GTID_NEXT= 'ANONYMOUS' に設定 |
| binlog.000004 | 739 | クエリ | 1 | 822 | drop database hotel_server /* xid=26 */ |
+---------------+-----+----------------+-----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------

上記のログによると、 drop database操作はEnd_log_pos= 822で実行されたため、 binlogリカバリの範囲は2020-06-19 20:00:00 - 660あることがわかります。なぜ660なのか?前回のdrop databaseトランザクションのコミットは位置 660 にあるため、コマンドは次のようになります。

mysqlbinlog --start-datetime=2020-06-19 20:00:00 --stop-position=660 /var/lib/mysql/binlog.000003 | mysql -uroot -proot_password datbase_name

範囲に位置 822 が含まれている場合は、 drop databaseコマンドが自動的に実行されます。信じられないなら試してみませんか？
上記のコマンドを実行すると、データはdrop database前の状態に復元されます。嬉しいか、そうでないか、興奮しているか、そうでないか！

要約する

MySQL のスケジュールされたバックアップは、実稼働環境では必要なタスクだからです。非常によく使われます。だからブログを書くのが待ちきれなかったんです。もちろん、同僚たちの助けにもとても感謝しています。この記事は、常に試行錯誤しながら記事を更新しているため、3日間かけて執筆されました。間違った知識ポイントを書き留めないようにしてください。もしこれがお役に立てば、ぜひフォローしてください！ありがとう。

上記は、MySQL スケジュールバックアップタスクの詳細の簡単な分析です。MySQL スケジュールバックアップタスクの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません: