-
MySQL のバックアップとリカバリ 2006/09/30 土曜日 - 14:21 — yejr
MySQLのバックアップとリカバリ
著者/翻訳者: Ye Jinrong (電子メール: )、出典: http://imysql.cn 。転載する場合は、著者/翻訳者と出典を明記してください。違反者は起訴されます。
日付: 2006/10/01
この記事では、MySQL のバックアップとリカバリのメカニズム、および 2 つの主要なテーブル タイプ (MyISAM と Innodb) を含むデータ テーブルの管理方法について説明します。この記事で設計されている MySQL バージョンは 5.0.22 です。
現在 MySQL でサポートされている無料のバックアップ ツールには、mysqldump、mysqlhotcopy が含まれます。また、バックアップに SQL 構文を使用することもできます: BACKUP TABLE または SELECT INTO OUTFILE、またはバイナリ ログ (binlog) のバックアップ、またはデータ ファイルおよび関連構成ファイルの直接コピー。 MyISAM テーブルはファイルとして保存されるため、上記の方法のいくつかを使用して比較的簡単にバックアップできます。 Innodb のすべてのテーブルは同じデータ ファイル ibdata1 に保存されます (複数のファイルまたは独立したテーブル スペース ファイルである場合もあります)。これは、データ ファイルをコピーして binlog をバックアップするか、mysqldump を使用することでバックアップできます。 。
1.mysqldump
1.1 バックアップ
mysqldump は SQL レベルのバックアップ メカニズムを使用し、データ テーブルを SQL スクリプト ファイルにエクスポートします。これは、最も一般的に使用されるバックアップ方法でもあります。
次に、mysqldump の主なパラメータのいくつかについて説明します。
--互換性=名前
これは、エクスポートされたデータがどのデータベースまたは古いバージョンの MySQL サーバーと互換性があるかを mysqldump に伝えます。値には、ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options などを指定できます。複数の値を使用するには、カンマで区切ります。もちろん、完全な互換性を保証するものではありませんが、互換性を保つように努めます。
--complete-insert、-c
エクスポートされたデータは、フィールド名を含む完全な INSERT メソッドを使用します。つまり、すべての値が 1 行に書き込まれます。そうすることで挿入効率が向上しますが、max_allowed_packet パラメータの影響を受け、挿入エラーが発生する可能性があります。したがって、このパラメータは注意して使用する必要があり、少なくとも私はお勧めしません。
--default-character-set=charset
データをエクスポートするときに使用する文字セットを指定します。データ テーブルでデフォルトの latin1 文字セットが使用されていない場合は、エクスポート時にこのオプションを指定する必要があります。指定しないと、データを再度インポートした後に文字化けが発生します。
--キーを無効にする
mysqldump に /*!40000 ALTER TABLE table DISABLE KEYS */; および /*!40000 ALTER TABLE table ENABLE KEYS */; を追加するように指示します。これにより、INSERT ステートメントの速度が大幅に向上します。すべてのデータが挿入された後にインデックスが再構築されるためです。このオプションは MyISAM テーブルにのみ適しています。
--extended-insert = true|false
デフォルトでは、mysqldump は --complete-insert モードをオンにするため、これを使用したくない場合は、このオプションを使用してその値を false に設定します。
--hex-blob
16 進形式を使用してバイナリ文字列フィールドをエクスポートします。バイナリ データがある場合は、このオプションを使用する必要があります。影響を受けるフィールドの種類は、BINARY、VARBINARY、および BLOB です。
--lock-all-tables,-x
エクスポートを開始する前に、データの一貫性を確保するために、すべてのデータベースのすべてのテーブルをロックするリクエストを送信します。これはグローバル読み取りロックであり、 --single-transaction および --lock-tables オプションを使用すると自動的にオフになります。
--lock-tables
--lock-all-tables に似ていますが、データベース内のすべてのテーブルを一度にロックするのではなく、現在エクスポートされているデータ テーブルをロックします。このオプションは、MyISAM テーブルにのみ適用されます。Innodb テーブルの場合は、--single-transaction オプションを使用できます。
--no-create-info、-t
CREATE TABLE ステートメントを追加せずに、データのみをエクスポートします。
--データなし、-d
データはエクスポートされず、データベース テーブル構造のみがエクスポートされます。
--オプト
これは単なるクイック オプションであり、 --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set- を追加するのと同等です。文字セットのオプション。このオプションを使用すると、mysqldump でデータを迅速にエクスポートでき、エクスポートされたデータを迅速にインポートし直すことができます。このオプションはデフォルトで有効になっていますが、--skip-opt を使用して無効にすることができます。 --quick または --opt オプションを指定せずに mysqldump を実行すると、結果セット全体がメモリに配置されることに注意してください。大規模なデータベースをエクスポートすると、問題が発生する可能性があります。
--クイック、-q
このオプションは、大きなテーブルをエクスポートする場合に、すべてのレコードを取得してメモリにキャッシュするのではなく、サーバー クエリから取得したレコードを直接出力するように強制します。
--ルーチン、-R
ストアド プロシージャとカスタム関数をエクスポートします。
--単一トランザクション
このオプションは、データをエクスポートする前に BEGIN SQL ステートメントを送信します。BEGIN はアプリケーションをブロックせず、エクスポート中にデータベースの一貫した状態を保証します。 InnoDB や BDB などのトランザクション テーブルでのみ機能します。
LOCK TABLES を使用すると保留中のトランザクションが暗黙的にコミットされるため、このオプションと --lock-tables オプションは相互に排他的です。
大きなテーブルをエクスポートするには、 --quick オプションを組み合わせて使用する必要があります。
--トリガー
トリガーもエクスポートします。このオプションはデフォルトで有効になっています。無効にするには --skip-triggers を使用してください。
他のパラメータの詳細については、マニュアルを参照してください。私は通常、MyISAM テーブルをバックアップするために次の SQL を使用します。
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --extended-insert=false
--triggers -R --hex-blob -x db_name > db_name.sql
Innodb テーブルをバックアップするには、次の SQL を使用します。
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --extended-insert=false
--triggers -R --hex-blob --single-transaction db_name > db_name.sql
さらに、オンライン バックアップを実装する場合は、次のように --master-data パラメーターを使用することもできます。
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --master-data=1
--single-transaction --flush-logs db_name > db_name.sql
最初にロック テーブルのみを要求し、次にバイナリ ログを更新し、エクスポートされたファイルに CHANGE MASTER ステートメントを追加して、現在のバックアップのバイナリ ログの場所を指定します。このファイルをスレーブに復元する場合は、次のコマンドを使用できます。このようにしてください。
1.2 リストア mysqldump でバックアップしたファイルは直接インポートできる SQL スクリプトです。 データをインポートするには 2 つの方法があります。
たとえば、mysql クライアントを直接使用します。
/usr/local/mysql/bin/mysql -uyejr -pyejr db_name < db_name.sql
SOURCE 構文の使用は、実際には標準 SQL 構文ではなく、mysql クライアントによって提供される関数です。次に例を示します。
ソース /tmp/db_name.sql;
ここではファイルの絶対パスを指定する必要があり、mysqld を実行しているユーザー (たとえば、nobody) が読み取り権限を持っているファイルである必要があります。
2.mysqlhotcopy
2.1 バックアップ
mysqlhotcopy は、もともと Tim Bunce によって書かれた PERL プログラムです。 LOCK TABLES、FLUSH TABLES、および cp または scp を使用してデータベースを迅速にバックアップします。これはデータベースまたは単一テーブルをバックアップする最も速い方法ですが、データベース ファイル (データ テーブル定義ファイル、データ ファイル、インデックス ファイルを含む) が配置されているマシン上でのみ実行できます。 mysqlhotcopy は MyISAM のバックアップにのみ使用でき、Unix 系および NetWare システム上でのみ実行されます。
mysqlhotcopy は、一度に複数のデータベースをコピーすることをサポートし、正規表現もサポートします。以下にいくつかの例を示します。
root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
db_name /tmp (データベースディレクトリ db_name を変更します)
/tmp にコピー
下）
root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
db_name_1 ... db_name_n /tmp
root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
db_name./regex/ /tmp
詳細な使用法についてはマニュアルを参照するか、次のコマンドを呼び出して mysqlhotcopy のヘルプを表示してください。
perldoc /usr/local/mysql/bin/mysqlhotcopy
mysqlhotcopy を使用する場合は、SELECT および RELOAD (FLUSH TABLES を実行する) 権限が必要であり、datadir/db_name ディレクトリを読み取る権限も必要であることに注意してください。
2.2 復元
Mysqlhotcopy を使用すると、データベース ディレクトリ全体を mysqld で指定された datadir (ここでは /usr/local/mysql/data/) に直接コピーできます。同時に、権限の問題にも注意する必要があります。次の例のように:
root#cp -rf db_name /usr/local/mysql/data/
root#chown -R nothing:nobody /usr/local/mysql/data/ (db_name ディレクトリの所有者を mysqld に変更します)
実行中のユーザー)
3. SQL構文のバックアップ
3.1 バックアップ
BACKUP TABLE 構文は、実際には mysqlhotcopy の動作原理と似ており、どちらもテーブルをロックしてからデータ ファイルをコピーします。オンライン バックアップを実現できますが、その効果は理想的ではないため、お勧めできません。テーブル構造ファイルとデータ ファイルのみがコピーされ、インデックス ファイルは同時にコピーされないため、リカバリは遅くなります。
例：
BACK TABLE tbl_name TO '/tmp/db_name/';
この SQL を実行するには FILE 権限が必要であり、ディレクトリ /tmp/db_name/ は mysqld ユーザーによって書き込み可能である必要があることに注意してください。セキュリティの問題を避けるために、エクスポートされたファイルは既存のファイルを上書きできません。
SELECT INTO OUTFILE は、データを通常のテキスト ファイルにエクスポートします。このデータの処理を容易にするためにフィールド間隔をカスタマイズできます。
例：
SELECT * INTO OUTFILE '/tmp/db_name/tbl_name.txt' FROM tbl_name;
この SQL を実行するには FILE 権限が必要であり、ファイル /tmp/db_name/tbl_name.txt は mysqld ユーザーによって書き込み可能である必要があることに注意してください。セキュリティの問題を避けるために、エクスポートされたファイルは既存のファイルを上書きできません。
3.2 BACKUP TABLE メソッドを使用してバックアップされたファイルを復元するには、RESTORE TABLE ステートメントを実行してデータ テーブルを復元します。
例：
'/tmp/db_name/' からテーブルを復元します。
権限の要件は上記と同様です。
SELECT INTO OUTFILE メソッドを使用してバックアップされたファイルの場合、LOAD DATA INFILE ステートメントを実行してデータ テーブルを復元できます。
例：
データ INFILE '/tmp/db_name/tbl_name.txt' をテーブル tbl_name にロードします。
権限の要件は上記と同様です。データをインポートする前に、データ テーブルがすでに存在している必要があります。データの重複が心配な場合は、REPLACE キーワードを追加して既存のレコードを置き換えたり、IGNORE キーワードを使用して既存のレコードを無視したりできます。
4. バイナリログ（binlog）を有効にする
binlog を使用する方法は比較的柔軟で、心配と労力を節約でき、増分バックアップもサポートできます。
binlog が有効になっている場合は、Mysqld を再起動する必要があります。まず、mysqld を閉じ、my.cnf を開いて、次の行を追加します。
サーバーID=1
ログビン = ビンログ
ログビンインデックス = binlog.index
次に、mysqld を起動します。操作中に Binlog.000001 と binlog.index が生成されます。前者のファイルはデータに対するすべての更新操作を記録する mysqld であり、後者のファイルはすべての binlog のインデックスであり、簡単には削除できません。 binlog についてはマニュアルを参照してください。
バックアップが必要な場合は、まず SQL ステートメントを実行して mysqld による現在の binlog への書き込みを終了させてから、ファイルを直接バックアップすることで、増分バックアップの目的を達成できます。
ログをフラッシュする; レプリケーション システムでスレーブ サーバーをバックアップする場合は、master.info ファイルとrelay-log.info ファイルもバックアップする必要があります。
バックアップされた binlog ファイルは、MySQL が提供する次のようなツール mysqlbinlog を使用して表示できます。
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001
このツールを使用すると、指定したデータベース内のすべての SQL ステートメントを表示でき、時間範囲を制限することもできます。詳細についてはマニュアルを参照してください。
復元するときは、次のようなステートメントを使用できます。
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001 | mysql -uyejr -pyejr db_name
mysqlbinlog によって出力された SQL ステートメントを入力として直接使用して実行します。
アイドル状態のマシンがある場合は、この方法を使用してバックアップすることもできます。スレーブ マシンのパフォーマンス要件が比較的低いため、増分バックアップを低コストで実現でき、データ クエリの負荷の一部を共有できます。
5. データ ファイルの直接バックアップ 以前の方法と比較して、データ ファイルのバックアップは最も直接的で高速かつ便利です。欠点は、増分バックアップが基本的に不可能であることです。データの一貫性を確保するには、ファイルをバックアップする前に次の SQL ステートメントを実行する必要があります。
読み取りロック付きテーブルのフラッシュ。つまり、メモリ内のすべてのデータをディスクにフラッシュし、コピー プロセス中に新しいデータが書き込まれないようにデータ テーブルをロックします。この方法でバックアップされたデータの復元も非常に簡単で、元のデータベース ディレクトリにコピーして戻すだけです。
Innodb タイプのテーブルの場合は、そのログ ファイル、つまり ib_logfile* ファイルもバックアップする必要があることに注意してください。 Innodb テーブルが破損した場合、これらのログ ファイルを頼りに回復できるためです。
6. バックアップ戦略 中レベルの業務量を持つシステムの場合、バックアップ戦略は次のように決定できます。初回の完全バックアップ、1 日に 1 回の増分バックアップ、1 週間に 1 回の完全バックアップなどです。重要で負荷の高いシステムの場合は、1 日に 1 回の完全バックアップ、1 時間に 1 回の増分バックアップ、またはさらに頻繁なバックアップが必要になる場合があります。オンライン ビジネスに影響を与えずにオンライン バックアップと増分バックアップを実現するには、マスター/スレーブ レプリケーション メカニズム (レプリケーション) を使用してスレーブ マシン上でバックアップを作成するのが最善の方法です。
7. データのメンテナンスと災害復旧 DBA (私はまだです、笑) として、最も重要なタスクの 1 つは、データ テーブルを安全、安定、高速に使用できるようにすることです。したがって、データテーブルを定期的にメンテナンスする必要があります。次の SQL ステートメントが役立ちます。
CHECK TABLE または REPAIR TABLE、MyISAM テーブルのチェックまたは保守
OPTIMIZE TABLE、MyISAM テーブルを最適化します。
ANALYZE TABLE、MyISAM テーブルを分析する もちろん、上記のコマンドはすべて myisamchk ツールを使用して実行できますが、ここでは詳しく説明しません。
次のステートメントを実行することで、Innodb テーブルをデフラグし、インデックス作成速度を向上させることができます。
ALTER TABLE tbl_name ENGINE = Innodb;
これは実際には NULL 操作ですが、実際にはフラグメントを再配置します。
一般的に使用される MyISAM テーブルは、上記の方法を使用して復元できます。インデックスが壊れている場合は、myisamchk ツールを使用してインデックスを再構築できます。 Innodb テーブルの場合、すべてのテーブルが 1 つのテーブル スペースに格納されるため、これはそれほど単純ではありません。ただし、Innodb にはファジー チェックポイントと呼ばれるチェック機構があり、ログ ファイルが保存されていれば、ログ ファイルに基づいてエラーを修復できます。 my.cnf ファイルに次のパラメータを追加すると、mysqld の起動時にログ ファイルが自動的にチェックされるようになります。
innodb_force_recovery = 4
このパラメータの詳細については、マニュアルを参照してください。
8. データのバックアップを要約し、適切なバックアップ戦略を決定します。これは、DBA の仕事のほんの一部ですが、最初はすべてが困難です。