MySQL5.7 mysqldump バックアップとリカバリの実装

MySQL バックアップ

コールドバックアップ:
停止服務進行備份，即停止數據庫的寫入

ホットバックアップ:
不停止服務進行備份（在線）

MySQL の MyIsam エンジンはコールド バックアップのみをサポートしていますが、InnoDB はホット バックアップをサポートしています。その理由は次のとおりです。

InnoDB エンジンはトランザクション ストレージ エンジンです。各ステートメントはログに書き込まれ、各ステートメントにはログ内のタイム ポイントがあります。そのため、バックアップ時に、MySQL はこのログに基づいてやり直しと取り消しを実行し、バックアップ中にコミットされなかったトランザクションをロールバックし、コミットされたトランザクションをやり直すことができます。しかし、MyIsam ではこれができません。MyIsam にはログがありません。一貫性を確保するには、バックアップのためにテーブルをシャットダウンするかロックすることしかできません。

InnoDB は、データベース ディレクトリ全体を直接コピーし、mysqlhotcopy ツールを使用して物理バックアップを行うことをサポートしていません。

1. データベースディレクトリ全体を直接コピーします。MYSQL テーブルはファイルとして保存されるため、MYSQL データベースのストレージディレクトリとファイルを直接コピーしてバックアップできます。 MYSQL データベース ディレクトリの場所は必ずしも同じではありません。Windows プラットフォームでは、MYSQL5.6 がデータベースを保存するディレクトリは通常、デフォルトで ~\MySQL\MYSQL Server 5.6\data またはその他のユーザー定義ディレクトリになります。この方法は、INNODB ストレージ エンジンを使用するテーブルには適用されません。この方法でバックアップされたデータは、同じバージョンのサーバーに復元するのが最適です。異なるバージョンでは互換性がない可能性があります。復元する場合は、バックアップ ファイルを MYSQL データ ディレクトリに直接コピーして復元できます。この方法で復元する場合、バックアップ データのデータベースと復元するデータベース サーバーのメジャー バージョン番号が同じであることを確認する必要があります。さらに、この方法は MYISAM エンジンに対してのみ有効であり、InnoDB エンジンを使用するテーブルでは使用できません。復元を実行する前に、MySQL サービスをシャットダウンし、バックアップされたファイルまたはディレクトリで MySQL データ ディレクトリを上書きして、MySQL サービスを起動します。

2. クイックバックアップにはmysqlhotcopyツールを使用する
mysqlhotcopy は、もともと Tim Bunce によって作成され、提供された Perl スクリプトです。彼は、 LOCK TABLES 、 FLUSH TABLES 、および cp または scp を使用して、データベースをすばやくバックアップします。これはデータベースまたは単一のテーブルをバックアップする最も速い方法ですが、データベース ディレクトリが配置されているマシンでのみ実行でき、MyISAM タイプのテーブルのみをバックアップできます。

mysqldump バックアップの簡単な紹介

mysqldump は、-tab=dir_name オプションが使用されているかどうかに応じて、2 種類の出力ファイルを生成できます。

-tab=dir_name オプションを使用しない場合、mysqldump によって生成されるデータ ファイルは、CREATE (データベース、テーブル、ストレージ パスなど) ステートメントと INSERT (レコード) ステートメントで構成されるプレーン テキスト SQL ファイルになります。出力結果はファイルに保存され、バックアップファイルはmysqlコマンドを使用して復元できます。

-tab=dir_name オプションを使用すると、mysqldump はバックアップするデータ テーブルごとに 2 つの出力ファイルを生成します。1 つは区切りテキスト ファイルで、バックアップされたデータ テーブルの各行がテキスト行として保存され、「table name.txt」として保存されます。もう 1 つの出力ファイルは、データ テーブルの CREATE TABLE ステートメントで、「table name.sql」として保存されます。

mysqldump の構文とオプション

【コマンド】 shell> mysqldump -help

- -all-databases は、システム内のすべてのデータベースをバックアップすることを意味します。 - -databases パラメータを使用した後、少なくとも 1 つのデータベースの名前を指定する必要があります。複数のデータベース名はスペースで区切られます。

よく使われるオプション

- - テーブルの追加と削除
このオプションは、各テーブルの前に DROP TABLE IF EXISTS ステートメントを追加します。これにより、インポートするたびに最初にテーブルが存在するかどうかが確認され、存在する場合は削除されるため、MySQL データベースにインポートし直すときにエラーが発生しないことが保証されます。

--ロックを追加する
このオプションは、LOCK TABLE および UNLOCK TABLE ステートメントを INSERT ステートメントにバンドルします。これにより、レコードがデータベースに再インポートされている間に、他のユーザーがテーブルに対して操作を実行できなくなります。

- -タブ
このオプションでは 2 つのファイルが作成されます。1 つは区切りテキスト ファイルで、バックアップされたデータ テーブルの各行はテキスト行として保存され、「table name.txt」として保存されます。もう 1 つの出力ファイルは、データ テーブルの CREATE TABLE ステートメントで、「table name.sql」として保存されます。

-quick または -opt
--quick または --opt オプションを使用しない場合、mysqldump は結果をダンプする前に内容全体をメモリにロードします。これは、大規模なデータベースをダンプするときに問題になる可能性があります。このオプションはデフォルトでオンになっていますが、--skip-opt を使用してオフにすることができます。

-コメントをスキップ
エクスポートされたファイル内のコメントを削除するには、--skip-comments を使用します。

- -コンパクト
-compact オプションを使用すると、コメントやテーブル削除ステートメントなどを出力せずに、最も重要なステートメントのみを出力できます。

SQL形式でのデータのバックアップ

バックアップ ファイル名 .sql で場所が指定されていない場合は、デフォルトで ~\MySQL\MySQL Server 5.6\bin ディレクトリに配置されます。

データのバックアップ パスを指定します。
mysqldump –h 主機名–u 用戶名–p - -all-databases > C:\備份文件名.sql

すべてのデータベースをバックアップするには、--all-databases オプションを指定して mysqldump を呼び出します。
mysqldump –h 主機名–u 用戶名–p - -all-databases > C:\備份文件名.sql

指定されたデータベースをバックアップするには、--databases オプションを指定して mysqldump を呼び出します。
mysqldump –u 用戶名–p - -databases db1 db2 db3 … > C:\備份文件名.sql

指定したデータベースをバックアップするには、mysqldump を呼び出します。
mysqldump –u 用戶名–p - -databases db > 備份文件名.sql

特定のデータベースをバックアップするには、--databases オプションなしで mysqldump を使用します。生成されたバックアップ ファイルには、CREATE DATABASE および USE ステートメントは含まれません。
mysqldump –u 用戶名–p db > 備份文件名.sql

注:データベースをバックアップする場合、-databases を省略できますが、その場合、バックアップ ファイル名 .sql に CREATE DATABASE および USE ステートメントが含まれなくなります。バックアップ ファイルを復元する場合、サーバーがバックアップ ファイルを復元するデータベースを認識できるように、デフォルトのデータベース名を指定する必要があります。これにより、元のデータベース名とは異なるデータベース名を使用することになります。

データベース内の複数のテーブルをバックアップするには、mysqldump を呼び出します。
mysqldump –u用戶名–p 數據庫名表名1 表名2 表名3… > 備份文件名.sql

SQL形式のバックアップファイルを復元する

mysqldump によってバックアップされたファイルに --all-databases または --databases オプションが使用されている場合、バックアップ ファイルには CREATE DATABASE および USE ステートメントが含まれるため、バックアップ ファイルを復元するためにデータベース名を指定する必要はありません。

シェルコマンドの場合:

shell> mysql –u ユーザー名 –p < バックアップファイル.sql

mysql コマンドの下で、source コマンドを使用してバックアップ ファイルをインポートします。

mysql> source backup file.sql; // すでにmysqlにログインしている場合は、sourceコマンドを使用します

mysqldump によって単一のデータベースがバックアップされ、--databases オプションが使用されていない場合、バックアップ ファイルには CREATE DATABASE および USE ステートメントが含まれていないため、復元時に最初にデータベースを作成する必要があります。

シェルコマンドの場合:

shell> mysqladmin –u ユーザー名 –p データベース名の作成 //データベースの作成 shell> mysql –u ユーザー名 –p データベース名 < バックアップファイル.sql

mysql コマンドの場合:

mysql> データベースが存在しない場合はデータベースを作成します。database_name;
mysql> USE データベース名;
mysql> ソースバックアップファイル.sql;

注意:ソース コマンドは cmd インターフェイスでのみ実行でき、mysql ツールでは実行できません。cmd はコマンドを実行するために mysql.exe を直接呼び出すため、エラーが報告されます。

区切りテキストファイル形式でデータをバックアップする

データベースをバックアップするには、--tab=dir_name オプションを指定して mysqldump を呼び出します。ここで、dir_name は出力ファイルのディレクトリを表します。このディレクトリでは、バックアップするテーブルごとに 2 つのファイルが生成されます。たとえば、t1 という名前のテーブルには、t1.sql と t1.txt という 2 つのファイルが含まれています。 .sql ファイルには CREATE TABLE ステートメントが含まれています。.txt ファイル内の 1 行はデータ テーブル内のレコードであり、列の値は「タブ」で区切られています。

注意: --tab=dir_name オプションを指定した mysqldump の使用は、ローカル サーバーでのみ行うのが最適です。リモート サーバーで使用する場合、--tab によって生成されたディレクトリはローカル ホストとリモート ホストの両方に存在するため、.txt ファイルはサーバーによってリモート ホストのディレクトリに書き込まれ、.sql ファイルはローカル ホストのディレクトリに書き込まれます。

データベースをバックアップするには、-tab=dir_name オプションを指定して mysqldump を呼び出します。
mysqldump –u 用戶名–p - -tab=dir_name 數據庫名

[例] - -tab=dir_name オプションを指定した mysqldump を使用して、データベース テストをバックアップし、ドライブ D に配置します。

テスト データベース内のテーブル:

バックアップコマンドを実行します。

出力結果:

区切りテキスト ファイル形式でバックアップ ファイルを復元します。mysql コマンドを使用して .sql ファイルを処理してテーブル構造を復元し、次に .txt ファイルを処理してレコードをロードします。

shell> mysql –u ユーザー名 –p データベース名 < テーブル名.sql //テーブル構造を復元 shell> mysqlimport –u ユーザー名 –p データベース名 テーブル名.txt //レコードを復元

あるいは、mysqlimport コマンドの代わりに LOAD DATA INFILE を使用することもできますが、今回は mysql コマンドを使用する必要があります。

mysql> use database name; //データベースを選択mysql> LOAD DATA INFILE 'table name.txt' INTO TABLE table name; //レコードを復元

[例] テスト データベースの stucou テーブルを復元します。テスト データベースのテーブルを確認すると、stucou テーブルが存在しません。

stucou.sql ファイルを使用して stucou テーブル構造を復元します。

stucou データ テーブルが正常に復元されました:

stucou データ テーブルにレコードがありません:

stucou.txt ファイルを使用して stucou テーブル レコードを復元します。

mysqlコマンドを使用してクエリの中間結果をエクスポートします

クエリ結果をテキストファイルにインポートする

mysql は機能豊富なツール コマンドです。mysql を使用して、コマンド ライン モードで SQL 命令を実行し、クエリ結果をテキスト ファイルにインポートすることもできます。 mysqldump と比較すると、mysql ツールによってエクスポートされた結果はより読みやすくなります。 MySQL サーバーが別のマシンであり、ユーザーがクライアントで操作している場合、ユーザーは mysql -e ステートメントを使用してデータ結果をクライアント マシンにインポートできます。

【注文】：
shell> mysql -u root -p --execute="SELECT 語句" dbname > filename.txt

このコマンドは --execute オプションを使用します。これは、このオプションに続くステートメントを実行して終了することを意味します。次のステートメントは二重引用符で囲む必要があります。

dbname はエクスポートするデータベースの名前です。エクスポートされたファイル内の各列はタブで区切られます。最初の行にはフィールド名が含まれます。

[例] mysql コマンドを使用して、テスト データベースの person テーブル レコードをテキスト ファイルにエクスポートします。
shell> mysql -u root -p --execute="SELECT * FROM person;" test > C:\person3.txt

person3.txtの内容は次のとおりです

ID 名前 年齢 職業

1 グリーン 29 ローワー

2 suse 26 ダンサー

3 エヴァンス 27 スポーツマン

4 メアリー 26 歌手

ご覧のとおり、person3.txt ファイルには各フィールドと各レコードの名前が含まれています。レコードの行に多くのフィールドがある場合は、1 行に完全に表示されない場合があります。-vertical パラメータを使用して、各レコードを複数の行に分割できます。

[例] mysql コマンドを使用してテスト データベースの person テーブルをエクスポートし、--vertical パラメータを使用して次を表示します。
shell> mysql -u root -p --vertical --execute="SELECT * FROM person;" test > C:\person4.txt

************************** 1. 行 ****************************

ID: 1

名前: グリーン

年齢: 29

職業: 弁護士

************************** 2. 行 ****************************

ID: 2

名前: suse

年齢: 26

職業: ダンサー

************************** 3. 行 ****************************

ID: 3

名前: エヴァンス

年齢: 27

職業: スポーツマン

************************** 4. 行 ****************************

ID: 4

名前: メアリー

年齢: 26

職業: 歌手

人物テーブルのレコードが長すぎる場合は、この表示の方が読みやすくなります。

クエリ結果をHTMLファイルにインポートする

mysqlコマンドを使用して、テストライブラリのpersonテーブルレコードをhtmlファイルにエクスポートします。入力ステートメントは次のとおりです。

シェル> mysql -u root -p --html --execute="SELECT * FROM PERSON;" テスト > C:\person5.html 

クエリ結果をXMLファイルにインポートする

XML ファイルにエクスポートする場合は、–xml オプションを使用して、mysql コマンドでテスト ライブラリの person テーブル内のレコードを XML ファイルにエクスポートします。

シェル> mysql -u root -p --xml --execute="SELECT * FROM PERSON;" テスト > C:\person6.xml

<?xml バージョン="1.0"?>

<結果セット ステートメント="SELECT * FROM PERSON" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

 <行>

  <フィールド名="ID">1</フィールド>

  <field name="名前">緑</field>

  <field name="年齢">29</field>

  <field name="job">弁護士</field>

 </行>

 <行>

  <フィールド名="ID">2</フィールド>

  <field name="名前">suse</field>

  <field name="年齢">26</field>

  <field name="job">ダンサー</field>

 </行>

 <行>

  <フィールド名="ID">3</フィールド>

  <field name="名前">エヴァンス</field>

  <field name="年齢">27</field>

  <field name="job">スポーツマン</field>

 </行>

 <行>

  <フィールド名="ID">4</フィールド>

  <field name="名前">メアリー</field>

  <field name="年齢">26</field>

  <field name="job">歌手</field>

 </行>

</結果セット>

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