MySQLテーブルをコピーする方法

1.mysqlダンプ

実行プロセス:

1. データを SQL ファイルとしてエクスポートします。

mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql

データを SQL ファイルにエクスポートして保存します。上記のパラメータの意味は次のとおりです。

1. –single-transaction の機能は、データをエクスポートするときに、テーブル db1.t をロックする必要がなく、START TRANSACTION WITH CONSISTENT SNAPSHOT メソッドを使用することです。

2. –add-locks が 0 に設定されている場合、出力ファイルの結果に「LOCK TABLES t WRITE;」は追加されません。

3. –no-create-info はテーブル構造をエクスポートする必要がないことを意味します。

4. –set-gtid-purged=off は、GTID 関連情報が出力されないことを意味します。

5. –result-file は出力ファイルのパスを指定します。ここで、client は生成されたファイルがクライアント マシン上にあることを意味します。

2. ファイルを実行してテーブルに追加する

mysql -h127.0.0.1 -P13000 -uroot db2 -e "ソース /client_tmp/t.sql"

ソースは SQL ステートメントではなく、クライアント コマンドです。つまり、サーバーはファイル内の SQL ステートメントを 1 つずつ実行し、binlog には特定の SQL が記録されます。

特徴

1. 生成されたSQLファイルはクライアントに保存されます

2. データの保存方法は、次の形式に示すように、複数のレコードペアで保存されます。

1 つのレコードのみを 1 つのステートメントとして保存する場合は、パラメーター –skip-extended-insert を追加できます。

2. CSVファイルをエクスポートする（最も柔軟性が高い）

実行プロセス

1. CSVファイルにエクスポート

db1.t から a>900 となるものを * として選択し、出力ファイル '/server_tmp/t.csv' に入力します。

知らせ：

1. into outfile は、ファイルが生成される場所 (/server_tmp/) を指定します。この場所は secure_file_priv パラメータによって制限される必要があります。

secure_file_priv パラメータのオプションの値と機能は次のとおりです。

1) 空に設定されている場合、ファイルが生成される場所に制限がないことを意味し、これは安全ではない設定です。

2) パスを表す文字列に設定されている場合、生成されたファイルは指定されたディレクトリまたはそのサブディレクトリにのみ配置されます。

3) NULL に設定されている場合、この MySQL インスタンスでは select ... into outfile 操作が禁止されていることを意味します。

2. 同じディレクトリに同じ名前のファイルがある場合は、エラーが報告されます。

3. 通常、1 つのレコードは CSV ファイル内の 1 行に対応します。ただし、フィールド値に「改行文字またはタブ文字」が含まれている場合は、それもファイルに含まれ、「\」でエスケープされます。

2. データのインポート

ファイル '/server_tmp/t.csv' のデータをテーブル db2.t にロードします。

プロセス：

1. ファイル /server_tmp/t.csv を開き、フィールド間の区切り文字としてタブ文字 (\t) を使用し、レコード間の区切り文字として改行文字 (\n) を使用して、データを読み取ります。

2. 取引を開始します。

3. 各行のフィールド数がテーブル db2.t のフィールド数と同じかどうかを判断します。

1) 異なる場合は、エラーが直接報告され、トランザクションがロールバックされます。

2) 同じ場合は、行を構築し、InnoDB エンジン インターフェイスを呼び出して、テーブルに書き込みます。

4. /server_tmp/t.csv ファイル全体が読み込まれるまで手順 3 を繰り返し、トランザクションをコミットします。

特徴

1. ファイルはサーバー上に保存されます

2. binlog レコードに関しては、プロセスは次のとおりです。

1) メインデータベースが実行されると、/server_tmp/t.csv ファイルの内容が binlog ファイルに直接書き込まれます。

2) ステートメント load data local infile '/tmp/SQL_LOAD_MB-1-0' INTO TABLE `db2`.`t` を binlog ファイルに書き込みます。

3) バイナリログをスタンバイ データベースに転送します。

4) スタンバイ データベースの適用スレッドがこのトランザクション ログを実行すると、次のようになります。

a. まず、binlog 内の t.csv ファイルの内容を読み取り、ローカルの一時ディレクトリ /tmp/SQL_LOAD_MB-1-0 に書き込みます。

b. ロード データ ステートメントを再度実行して、プライマリ データベースと同じデータをスタンバイ データベースの db2.t テーブルに挿入します。

「ローカル」について:

1) 「local」がない場合、サーバー側のファイルが読み取られます。このファイルは secure_file_priv で指定されたディレクトリまたはサブディレクトリに存在する必要があります。

2) mysql クライアントがファイルにアクセスする権限を持っている限り、クライアントのファイルを読み取り、「local」を追加します。このとき、MySQL クライアントはまずローカル ファイルをサーバーに転送し (他のセッションに関連する操作)、次に上記のデータ ロード処理を実行します。

3. 上記のエクスポート操作ではテーブル構造はエクスポートされません。したがって、テーブル構造をエクスポートする場合は、mysqldump を使用して CSV とテーブル構造を同時にエクスポートできます。

mysqldump -h$host -P$port -u$user --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --tab=$secure_file_priv

$secure_file_priv で定義されたディレクトリにテーブル作成ステートメントを保存するための t.sql ファイルが作成され、CSV データを保存するための t.txt ファイルが作成されます。

3. 物理コピー（最速）

5.6 より前では、.frm ファイルと .ibd ファイルをコピー先のディレクトリに直接コピーすることはできません。これは、これらの 2 つのファイルに加えて、Innodb テーブルをデータ ディクショナリに登録する必要があるためです。ただし、この問題は 5.6 以降で解決できます。5.6 ではトランスポータブル領域が導入され、表領域のエクスポート + インポートによってコピーを実現できます。

プロセス

現在の目標は、db1 データベース内のテーブル t と同一のテーブル r をコピーすることだと仮定します。具体的な実行手順は次のとおりです。

1. create table r like t を実行して、同じテーブル構造を持つ空のテーブルを作成します。

2. alter table r discard tablespace を実行します。r.ibd ファイルは削除されます。

3. エクスポート用に flush table t を実行します。db1 ディレクトリに t.cfg ファイルが生成されます。

4. db1 ディレクトリで次のコマンドを実行します: cp t.cfg r.cfg; cp t.ibd r.ibd。(MySQL プロセスには、コピーされた 2 つのファイルに対する読み取りおよび書き込み権限が必要です。)

5. テーブルのロック解除を実行すると、t.cfg ファイルが削除されます。

6. alter table r import tablespace を実行し、r.ibd ファイルをテーブル r の新しいテーブルスペースとして使用します。このファイルのデータ内容は t.ibd と同じなので、テーブル r にはテーブル t と同じデータが含まれます。

知らせ：

1. 手順 3 で flsuh table コマンドを実行した後、unlock tables コマンドが実行されるまで、db1.t テーブル全体が読み取り専用状態になります。

2. インポート テーブルスペースを実行すると、r.ibd のテーブルスペース ID が変更され、ファイル内のテーブルスペース ID がデータ ディクショナリ内のテーブルスペース ID と一致するようになります。そして、このテーブルスペース ID はすべてのデータ ページに存在します。したがって、非常に大きなファイル（TB レベルなど）の場合は、各データ ページを変更する必要があるため、このインポート ステートメントの実行には時間がかかることがわかります。もちろん、論理的なインポート方法と比較すると、インポート ステートメントにかかる時間は非常に短くなります。

制限事項

1. 条件付きコピーではなく、テーブル全体をコピーする必要があります

2. データをサーバーにコピーする必要があるため、ユーザーがデータベースホストにログインできない場合は使用できません。

3. 物理ファイルのコピーで実装されるため、ソーステーブルとターゲットテーブルの両方が InnoDB エンジンを使用している場合にのみ使用できます。

要約する

1. 最初の 2 つは論理バックアップであるため、エンジン間で使用できますが、最後のものは使用できません。

2. 最初の2つは条件付きでコピーできますが、最後の1つは条件付きでコピーできません。

3. 2 番目の機能は最も柔軟性がありますが、クラスターがライブラリからデータを受信するときに時間がかかります (最初に CSV ファイル データをローカルの一時ファイルにコピーする必要があります)。最後の機能は実行効率が最も高くなりますが、エンジンをまたぐことはできず、完全なコピーしか実行できません。

上記はMySQLテーブルのコピー方法の詳細です。MySQLコピーテーブルの詳細については、123WORDPRESS.COMの他の関連記事に注目してください。