MySQLの増分バックアップ-InnoDBおよびMyIsamデータベースのポイントインタイムバックアップとリカバリ

大規模な本番データベースでは、増分バックアップを実行することが重要な要件です。安全な増分バックアップがなければ、信頼できる本番データベースがあることを自覚することはできません。緊急時にデータベースを回復するには、十分なデータが必要だからです。インターネットで検索したところ、アプリケーションが両方のデータベースエンジンを同時に使用している場合、混合環境でMyISAMとInnodBの完全増分バックアップを実行できるツールが見つかりませんでした（おそらく私はGoogleとインターネットのエキスパートサーチャーではありません）。そこで、これを作成することにしましたが、時間を無駄にせず、他のオープンソースソリューションのメリットを享受するために、この機能を-automysqlbackup-スクリプトに追加することを選択しました。これは、シンプルで広く使用される完全バックアップに最適なスクリプトです。

メカニズム

automysqlbackupのPost-およびPre機能を使用して、増分バックアップを実行します。完全バックアップを開始する前に、mysql-backup-preは、バックアップの実行中に変更を回避するためにbinlogをフリーズする必要があるため、バックアッププロセス中にデータベース全体をロックするクエリを実行します。 binlogの名前と位置は、バックアップ中に変更されない場合があります。バイナリログの位置は、後続の増分バックアッププロセスで非常に重要であり、次の増分バックアップを開始するための開始点として使用されます。完全バックアップが完了すると、mysql-backup-postはデータベースロックを削除します。

ロッククエリ：READLOCKを使用したFLUSHTABLES; SELECT SLEEP（86400）

ロッククエリの検索：mysql -u [username] -p [pass] -e "show processlist" | grep "SELECT SLEEP（86400）" | awk'{print $ 1}'

要件

パッケージをインストールしてmysql.confを更新するためのroot権限
mysql-community-clientパッケージ
インストールautomysqlbackupおよびmysql-incremental

インストール

ディストリビューション用のmysql-community-clientパッケージをインストールします。

注：MySQLのインストール後、「mysqlshow」コマンドが必要です。

automysqlbackupをインストールします：

https://sourceforge.net/projects/automysqlbackup/からパッケージをダウンロードします

 tar -xzf [PathYouSavedTarFile] -C / tmp /

 cd / tmp /

 ./ install.sh

automysqlbackupのインストール中に、automysqlbackup.confとそのバイナリのパスについて尋ねられます。変更せずにデフォルトのままにすることができます。

 rm /etc/automysqlbackup/myserver.conf

mysql-incrementalをインストールします。https：//sourceforge.net/projects/mysqlincrementalbackup/からパッケージをダウンロードします

 cd / tmp 
 wget http://downloads.sourceforge.net/project/mysqlincrementalbackup/mysql-incremental.tar.gz 
 tar xfz mysql-incremental.tar.gz

 cp mysql-incremental / etc / automysqlbackup /

 chmod 755 / etc / automysqlbackup / mysql-incremental

 cp mysql-backup-post / etc / automysqlbackup /

 chmod 755 / etc / automysqlbackup / mysql-backup-post

 cp mysql-backup-pre / etc / automysqlbackup /

 chmod 755 / etc / automysqlbackup / mysql-backup-pre

automysqlbackup.confを更新します：

以下のパラメータを見つけ、コメントを外して変更します：

 CONFIG_mysql_dump_username='Mysqlユーザー名。 Lockを取得する権限が必要です'CONFIG_mysql_dump_password='Password'CONFIG_backup_dir='完全バックアップと増分バックアップを保存するバックアップディレクトリ'CONFIG_db_names=（'databaseName1''databaseName2'）CONFIG_db_month_names =（'databaseName1''databaseName2'）CONFIG_mysql_dump_master_data =2 CONFIG_prebackup ="/ etc / automysqlbackup / mysql-backup-pre" CONFIG_postbackup ="/ etc / automysqlbackup / mysql-backup-post"

my.cnfを更新します：

MySQL構成ファイルを編集します：

 nano /etc/mysql/my.cnf

1-BinLog形式

STATEMENT形式にはいくつかの制限があるため、ROWベースの形式を設定することをお勧めします。詳細については、このハウツーの「トラブルシューティング」セクションを参照してください。「select@@binlog_format;」を実行すると、バイナリログ形式の種類を確認できます。クエリ。 logbin形式を変更するには、binlog_format=ROWをmysql.confまたはmy.cnfに追加する必要があります。

2- binlog_do_db

バイナリログで、関連する変更を行う予定のデータベースを指定する必要があります。データベースを指定しない場合、データベースの変更はバイナリログに記録されることに注意してください。この場合、STATEMENT形式を選択した場合、増分バックアップファイルとbinlogファイルから復元するときに問題が発生する可能性があります。このオプションにデータベースを追加できます：

 binlog_do_db =DATABASENAME1binlog_do_db =DATABASENAME2

3-expire_logs_days

バイナリログファイルをより長く保持するには、このパラメータをより高い値に増やすことができます。私の推薦は60日です。したがって、「expire_logs_days=60」に追加または変更する必要があります。

4-ログビン

バイナリログが保存されるディレクトリ。古いMySQLバージョンでは、mysql-incremenetalが正しいパスを見つけられない場合があります。したがって、mysql-incrementalの実行後にこれに関するエラーが発生した場合は、mysql-incrementalスクリプトを更新し、バイナリログパスを設定する必要があります。

5-log_slave_updates

スレーブサーバーでmysql-incrementalバックアップを設定する場合は、このオプションを有効にする必要があります。通常、スレーブは、マスターサーバーから受信した更新を、自身のバイナリログに記録しません。このオプションは、SQLスレッドによって実行された更新を独自のバイナリログに記録するようにスレーブに指示します。 http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_log-slave-updates

automysqlbackupを実行します

automysqlbackupを手動で実行して、指定したデータベースから少なくとも1つの完全バックアップを作成します。

 automysqlbackup

コマンドを正常に実行した後、/ [BackupDirInAutomysqlbackup] / status / backup_infoファイルで、毎日のバックアップに関する新たに追加された情報を確認してください。エラーの詳細については、/ var / log/Backup_Post_Pre_logを確認してください。バックアップファイルは、ディレクトリ/ [BackupDirInAutomysqlbackup] / daily /[DatabaseName]/に保存されます。

mysql-incrementalを実行

ここでmysql-incrementalを手動で実行して、少なくとも1時間ごとのバックアップを作成します。

 mysql-incremental

エラーが発生した場合、詳細はファイル「/ var / log/Backup_Incremental_Log」に記録されます。増分バックアップファイルは、ディレクトリ/ [BackupDirInAutomysqlbackup] /IncrementalBackup/に保存されます。

ルートcrontabを編集

mysql-incrementalを1時間以上スケジュールできます。フルバックアップの合計時間をbackup_statusから確認し、その値に基づいて正確なスケジュール時間を設定できます。もちろん、mysql-incremental backupには、開始前に実行中の完全バックアップを見つけるメカニズムがあるため、増分バックアップと完全バックアップの競合について心配する必要はありません。

 crontab -e

 5 00 * * * root / usr / local / bin / automysqlbackup25 * * * * root / etc / automysqlbackup / mysql-incremental

データベースの復元

特定の時間（ポイントインタイムリカバリ）まで復元するには、最初に1日1回の完全バックアップを復元してから、順次関連する増分バックアップファイルを復元する必要があります。さらに明確にするために、testDBデータベースを回復する手順は次のとおりです。サンプルシナリオでは、2015年5月1日午前2時までのデータを回復する予定です。 / backupをメインのバックアップディレクトリとして設定し、testDBをターゲットデータベースとして設定しました：

 1- mysql -u root -p DatabaseName  重要な注意事項とトラブルシューティング 
 MySQLは、バイナリログに対してさまざまな形式をサポートしています。一部のMysqlバージョンは、binlog形式として「ステートメントベース」を使用しますが、このタイプのbinlogにはいくつかの制限があり、増分バックアップ手順で使用する場合は注意が必要です。 mysqlがステートメントベース形式に設定されている場合、データベースに基づいて正しくフィルタリングすることはできません。 'USEまたは\u'を設定してデータベースを変更してから、binlog-do-dbに含まれていない別のデータベースを更新すると、ステートメントはbinlogファイルに記録され、望ましくない状態になります。また、特定のデータベースに基づいて復元するときに問題が発生します。また、binlog-do-dbに含まれていない別のデータベースに変更し、binlog-do-dbに含まれているデータベースを更新すると、ステートメントはログに記録されません。 binlogファイル。 binlog-do-dbにデータベースを追加する目的は、データベースに基づいてフィルタリングすることですが、期待どおりに機能しません。クエリを実行する前にUSEまたは\uが実行されない場合、mysqlbinlogは1つのデータベースに関連する「更新クエリ」を抽出できません。この問題については、以下のシナリオで詳しく説明します。
 データベース：-binlog-person（テーブル）-binlog2-person（テーブル）binlog-do-db =binlog2（このデータベースの変更のみがbinlogファイルに記録されると想定されています）--------シナリオ1 --------- \ u binlog2insert into person（data）values（ '17'）---> loged in binlog * desired state * insert into binlog.person（data）values（ '25'）; --->ログインしたbinlog（ターゲットデータベースは'binlog'）*望ましくない状態*--------シナリオ2---------\ u binloginsert into person（data）values（'17 '）--->はbinlogにログインしていません*望ましい状態* binlog2.person（データ）値（'25'）に挿入します。 ---> binlogにログインしていません（ターゲットデータベースは'binlog2'です）* binlog2データベースが変更され始めたため、望ましくない状態*です。この変更を行いたいのですが、logbinファイルにはログインしません------ --シナリオ3---------USEまたは\uステートメントなしでデータベースに接続した場合、データベースのすべての更新がログに記録されますが、mysqlbinlogは特定のデータベースに基づいてフィルタリングできません。これは、増分バックアップの目的にとって望ましい状態ではありません。更新クエリを実行する前にUSEまたは\uを使用することは、非常に重要です。 mysqlbinlogは、binlogファイルのUSEステートメントに基づいて更新クエリを検出するためです。
 言及された問題の回避策 
 1）各ユーザーが更新するデータベース（アプリケーションユーザー）に1つしかアクセスできないようにデータベース上のユーザーを定義し、データベースに接続するときにデータベースの名前を指定する必要があります。もちろん、ほとんどのアプリケーションには、データベースの資格情報と名前が設定されている構成ファイルがあります。その場合、データベースへのクロスアクセスはなく、「\USE」または「\u」の使用に関する懸念はありません。 "。
 
 2）行ベースのbinlog形式を使用する場合、上記の問題はすべて解消されます。言い換えれば、行ベースの形式は、binlogのはるかに適切な方法です。 https://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html 
 ログファイル 
 ログに十分な情報が見つかるように、すべてをログファイルに記録しようとしました：
 
 /var/log/Backup_Post_Pre_log 
 / var / log / Backup_Incremental_Log 
 / [SpecifiedBackupDirInAutomysqlbackup.conf] / status / backup_info 
 
 ファイル「backup_info」には、バックアップに関する詳細情報とバックアップがいつ終了したかが含まれています（TimesはUnix Time形式です）。これには、binlogの名前と、バックアップが開始された時点の位置、バックアップのタイプ、最後の完全バックアップ以降のバックアップの数、およびバックアップの期間が含まれます。
 
 サンプルbackup_info：
  1431043501、mysql-bin.000026,120、Daily、2015-05-08,0,241431044701、mysql-bin.000026,120、Hourly、2015-05-08,1,1 
 
 さまざまな値の説明は次のとおりです。
  1日）1431043501：バックアップが終了した時刻を示します。バックアップが実行されたサーバーでdate--date@ 1431043501コマンドを実行して、人間が読める形式で表示できます。 2番目）Mysql-bin.000026：このファイルへのバックアップが実行されたバイナリログ名を示します。 3番目）120：バイナリログのこの位置までのバックアップが実行されたbinlogの位置を示します。 4日）毎日/毎時：バックアップのタイプを示します。毎日はautomysqlbackupスクリプトによる完全バックアップを意味し、毎時はmysql-incrementalスクリプトによって実行されます。 5日）2015-05-08：バックアップが行われた日付。この日付は、増分バックアップ用のディレクトリの作成に使用され、1時間ごとのバックアップの復元のベースとしても使用されます。復元手順では、最初に完全バックアップが復元され、次に他の増分バックアップが復元されます。 6番目）0：前回の完全バックアップからのバックアップ数を示します。 0はバックアップがいっぱいであることを意味し、その他は1時間ごとを意味します。この番号は、手順を復元する際に非常に重要です。 7日）24：バックアップ期間（秒）。
 バグレポート 
 https://sourceforge.net/projects/mysqlincrementalbackupでバグを報告したり、提案やレビューを提供したりできます。