mysql バックアップ戦略の実装 (フルバックアップ + 増分バックアップ)

最近、プロジェクトではデータベース データをバックアップする必要がありました。さまざまな資料を参考にして、データベース バックアップ戦略を設計しました。1 週間のデバッグと運用を経て、現在は安定した運用状態になっています。ここで私のアイデアを共有し、gredn に感謝したいと思います。

設計シナリオ

1) 月曜日から土曜日の午前 3 時に増分バックアップが実行され、mysql-bin.00000* が指定されたディレクトリにコピーされます。
2) 完全バックアップの場合は、mysqldump を使用してデータベース全体をエクスポートします。これは毎週日曜日の午前 3 時に実行され、先週残った mysq-bin.00000* を削除します。その後、mysql のバックアップ操作は bak.log ファイルに保持されます。

技術的なポイント

mysqldump、mysqlbinlog、crontab

サーバー情報

ホスト: centos7; データベース: mysql5.7

準備

binlog ログを有効にする (1) 新しいディレクトリを作成し、以下を実行します。

#mkdir /home/mysql
#cd /home/mysql
#mkdir mysql-bin. #増分ログファイルディレクトリ

（２）所属するユーザー/グループを変更する（変更しないとMySQLを再起動できません）

#chown -R mysql.mysql mysql-bin 

（3）MySQL設定ファイルを変更し、以下を実行します。

#vim /etc/my.cnf 

このうち、server-id は単一ノードの ID を表します。ここではノードが 1 つしかないため、ID はランダムに番号に割り当てられます。ここでは ID は 1 に設定されています。クラスター内に複数のノードがある場合、ID は同じにできません (バージョン 5.7 より前のバージョンでは、server-id を指定する必要はありません)。
log_bin は、binlog ログ ファイルの保存パスを指定します。ログ ファイルは mysql-bin で始まります。

（4）MySQLを再起動して以下を実行します。

#systemctl mysqld.service を再起動します

（5）ログファイルを表示する：

#cd /home/mysql/mysql-bin 

（6）データベースに入り、起動効果を確認します。

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

フルバックアップスクリプト（Mysql-FullyBak.sh）を書く

/home/mysqlディレクトリに入り、新しいディレクトリを作成します: mkdir backup
バックアップディレクトリに入り、新しい日次ディレクトリを作成します: mkdir backup
/home/mysql ディレクトリに切り替えて、次を実行します。

#vim Mysql-FullyBak.sh

パラメータの説明:
--ロックテーブル
データベース内のすべてのテーブルを一度にロックするのではなく、現在エクスポートされているデータ テーブルをロックします。このオプションは、MySQL データベース エンジンが使用されている場合、MyISAM テーブルにのみ適用されます。Innodb テーブルの場合は、–single-transaction オプションを使用できます。
--ログをフラッシュする
現在のログを終了し、新しいログ ファイルを生成します。
--マスターログを削除する
古いログをクリアしてスペースを解放します。ただし、サーバーがミラー化されたレプリケーション マスターとして構成されている場合、スレーブ サーバーがバイナリ ログの内容を完全に処理していない可能性があるため、--delete-master-logs を使用して MySQL バイナリ ログを削除するのは危険です。この場合は、PURGE MASTER LOGS を使用する方が安全です。
-素早い
このオプションは、大きなテーブルをエクスポートするときに便利です。これにより、MySQLdump は、すべてのレコードを取得してメモリにキャッシュするのではなく、サーバーにクエリを実行してレコードを直接取得し、出力します。
--単一トランザクション
このオプションは、データをエクスポートする前に BEGIN SQL ステートメントを送信します。BEGIN はアプリケーションをブロックせず、エクスポート中にデータベースの一貫性を確保できます。これは、InnoDB や BDB などのトランザクション テーブルにのみ適用されます。このオプションは --lock-tables オプションと相互に排他的です。これは、lock-tables により保留中のトランザクションが暗黙的にコミットされるためです。大きなテーブルをエクスポートするには、--quick オプションとともに使用します。
–イベント
イベントをエクスポート –master-data=2
パラメータ --master-data=[0|1|2]
0: 記録しない
1: CHANGE MASTERステートメントとして記録される
2: コメントとして記録された CHANGE MASTER ステートメント –master-data=2 オプションは、出力 SQL に完全バックアップ後の新しいログ ファイルの名前を記録します。
たとえば、将来のリカバリ時の参照用に、出力バックアップ SQL ファイルには次の内容が含まれます。
MASTERをMASTER_LOG_FILE='MySQL-bin.000002'、MASTER_LOG_POS=106に変更します。

増分バックアップ スクリプトの作成

/home/mysql ディレクトリに切り替えて、次を実行します。

#vim Mysql-DailyBak.sh 

スケジュールされたタスク用のcrontabを設定する

（1）crontabをインストールします（centos7ではデフォルトでインストール済みです）。

#yum crontabs をインストール

サービス操作手順:

#/bin/systemctl start crond.service //サービスを開始#/bin/systemctl stop crond.service //サービスをシャットダウン#/bin/systemctl restart crond.service //サービスを再起動#/bin/systemctl reload crond.service //リロード

構成：

#/bin/systemctl status crond.service //サービスステータス

自動起動を追加:

#chkconfig –level 35 crond オン

（2）コマンドラインに次のように入力します。

#crontab -e 

対応するタスクを追加し、保存してwqで終了します。

#毎週日曜日の午前3時に完全バックアップスクリプトを実行します 0 3 * * 0 /bin/bash -x /home/mysql/Mysql-FullyBak.sh >/dev/null 2>&1
#月曜日から土曜日の午前3時に増分バックアップを作成します。0 3 * * 1-6 /bin/bash -x /home/mysql/Mysql-DailyBak.sh >/dev/null 2>&1

注意: デフォルトでは、crontabはタスクの実行後にユーザーに電子メールで通知します。毎回メッセージを送信しないようにするには、/dev/null 2>&1を追加します。

（3）スケジュールされたタスクを表示する: #crontab -l

パラメータと説明:
crontab -u // ユーザーの cron サービスを設定します。通常、このコマンドを実行するときに、root ユーザーはこのパラメータを必要とします。
crontab -l // ユーザーの cron サービスの詳細を一覧表示します。
crontab -r //すべてのユーザーの cron サービスを削除します。
crontab -e // ユーザーの cron サービスを編集します。
たとえば、rootは自身のcron設定を表示できます: crontab -u root -l
たとえば、root はユーザー fred の cron 設定を削除します: crontab -u fred -r
補充:
（1）/etc/crontabファイル（vi /etc/crontab）を直接編集し、対応するタスクを追加することができます（システム全体のcrontabファイル用）。
（２）スケジュールされたタスクを実行するcrontabの記録は/var/log/cronファイルに書き込まれ、記録はアカウント番号によって区別されます。

回復作戦

リカバリ処理ではログファイルも書き込まれます。データ量が多い場合は、まず binlog 機能をオフにすることをお勧めします。
1. シナリオ: 午前 9 時にデータベースが攻撃され、データベース全体が削除されたとします。
2. 回復のアイデア:
完全な SQL ファイル、binlog ファイルおよびその位置情報に記録された CHANGE MASTER ステートメントを使用して、binlog ファイル内の増分部分を見つけます。
mysqlbinlog コマンドを使用して、上記の binlog ファイルを sql ファイルにエクスポートし、drop ステートメントを削除します。
完全バックアップ ファイルと増分 binlog ファイルを介して SQL ファイルをエクスポートすることで、完全なデータを復元できます。
3. 回復手順:

（1）まず、最新のフルバックアップファイルを解凍し、バックアップファイルディレクトリに入り、以下を実行します。

#tar -zxvf XXX.sql.tgz

（2）フルバックアップ後に新しく追加されたbinlogファイルを表示するには、次のコマンドを実行します。

#grep CHANGE XXX.sql 

図からわかるように、これは完全準備時の binlog ファイルの位置、つまり mysql-bin.000003 の 154 行目です。したがって、このファイルの前の binlog ファイル内のデータは、この完全準備 sql ファイルにすでに含まれています。

（３）mysql-bin.000003ファイルの154行目以降の情報を復元する

mysql-bin.000003 ディレクトリに移動して実行します (sysecokit はデータベース名です)。

#mysqlbinlog --start-position=154 --database=sysecokit mysql-bin.000003 | mysql -uroot -p -v sysecokit

(4) 他のbinlogファイル（mysql-bin.000003を除く）をsqlファイルにエクスポートして実行します（-dはデータベースを指定します）。

#mysqlbinlog -d sysecokit mysql-bin.00000X >00Xbin.sql 

（５）最新の00Xbin.sqlをvimで編集し、drop文を削除します。
（6）フルバックアップデータを復元するには、次のコマンドを実行します。

#mysql -uroot -p < XXX.sql

例: #mysql -uroot -p < 20180716.sql
（7）増分データを復元するには、次のコマンドを実行します（sysecoはデータベース名です）。

#mysql -uroot -p syseco<00Xbin.sql

例: #mysql -uroot -p syseco<004bin.sql
すべてが完了したので、1 週間実行した後に生成されたファイルを見てみましょう。

これで、MySQL バックアップ戦略 (フルバックアップ + 増分バックアップ) の実装に関するこの記事は終了です。MySQL バックアップ戦略に関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。