MySQL コピーテーブルと許可分析の 3 つの実装方法

テーブルを素早くコピーする方法

まず、テーブル db1.t を作成し、1000 行のデータを挿入します。同時に、同じ構造のテーブル db2.t を作成します。

db1.tのa>900のデータ行をエクスポートし、db2.tに挿入する必要があるとします。

mysqldump メソッド

いくつかの重要なパラメータに関する注意事項:

• –single-transaction は、テーブル db1.t をロックせずにデータをエクスポートするために使用されます。
• 一貫性のあるスナップショットでトランザクションを開始する方法。
• –no-create-info はテーブル構造をエクスポートする必要がないことを意味します。
• –result-file は出力ファイルのパスを指定します。ここで、client は生成されたファイルがクライアント マシン上にあることを意味します。
  

CSVファイルをエクスポート

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

このステートメントは結果をサーバーに保存します。コマンドを実行するクライアントと MySQL サーバーが同じマシン上にない場合、t.csv ファイルはクライアント マシンの一時ディレクトリに生成されません。

このコマンドはファイルを上書きしないので、/server_tmp/t.csv ファイルが存在していないことを確認する必要があります。存在しない場合は、同じ名前のファイルが存在するため、ステートメントの実行時にエラーが報告されます。

.csv エクスポート ファイルを取得したら、次のデータ ロード コマンドを使用して、データをターゲット テーブル db2.t にインポートできます。

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

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

取引を開始します。

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

• 異なる場合は、エラーが直接報告され、トランザクションがロールバックされます。
• 同じ場合は、行が構築され、InnoDB エンジン インターフェイスが呼び出され、データがテーブルに書き込まれます。

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

物理コピー方式

mysqldump メソッドと CSV ファイルのエクスポート メソッドはどちらも論理的なデータ インポート メソッドです。つまり、テーブル db1.t からデータを読み取り、テキストを生成し、それをターゲット テーブル db2.t に書き込みます。データを物理的にインポートする方法はありますか?たとえば、db1.t テーブルの .frm ファイルと .ibd ファイルを db2 ディレクトリに直接コピーすることは可能ですか?答えはノーです。

なぜなら、これら 2 つの物理ファイルを含めることに加えて、InnoDB テーブルもデータ ディクショナリに登録する必要があるためです。これら 2 つのファイルを直接コピーすると、データ ディクショナリに db2.t テーブルがないため、システムはそれらを認識して受け入れません。

MySQL 5.6 では、テーブルスペースをエクスポートおよびインポートすることでテーブルを物理的にコピーする機能を実現できるトランスポータブル テーブルスペース方式が導入されました。

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

• 同じテーブル構造を持つ空のテーブルを作成するには、create table r like t を実行します。
• alter table r discard tablespaceを実行すると、r.ibdファイルが削除されます。
• エクスポート用に flush table t を実行すると、t.cfg ファイルが生成されます。
• db1 ディレクトリで次の 2 つのコマンドを実行します: cp t.cfg r.cfg; cp t.ibd r.ibd;
• テーブルのロック解除を実行すると、t.cfg ファイルが削除されます。
• alter table r import tablespace を実行し、r.ibd ファイルをテーブル r の新しいテーブルスペースとして使用します。このファイルのデータ内容は t.ibd と同じなので、テーブル r にはテーブル t と同じデータが含まれます。
  

これら3つの方法の長所と短所

物理コピー方式は、特に大きなテーブルをコピーする場合に最も高速です。ただし、部分的なコピーではなく、完全なコピーである必要があります。データはサーバーにコピーする必要があります。ユーザーがデータベース ホストにログインできない場合は使用できません。また、ソース テーブルとターゲット テーブルの両方で InnoDB エンジンを使用する必要があります。

mysqldump を使用して INSERT ステートメントを含むファイルを生成する場合、where パラメータにフィルタリング条件を追加して、データの一部だけをエクスポートできます。この方法の欠点の 1 つは、join などのより複雑な where 条件を使用できないことです。

select ... into outfile メソッドは最も柔軟性が高く、すべての SQL 書き込みメソッドをサポートします。ただし、この方法の欠点の 1 つは、一度にエクスポートできるのは 1 つのテーブルのデータのみであり、テーブル構造も別のステートメントで個別にバックアップする必要があることです。

後者の 2 つは論理バックアップ方法であり、エンジン間で使用できます。

mysql グローバル権限

SELECT * FROM MYSQL.USER WHERE USER='UA'\G すべての権限を表示

スコープはMySQL全体であり、情報はMySQLのユーザーテーブルに保存されます。

ユーザー ua に最高権限を付与します。

grant all privileges on *.* to 'ua'@'%' with grant option;

この grant コマンドは 2 つの処理を実行します。ディスク上の mysql.user テーブルの権限フィールドを Y に変更し、メモリ内の acl_user 内のユーザーの対応するオブジェクトのアクセス値を「すべて 1」に変更します。

新しいクライアントがユーザー名 ua で正常にログインすると、MySQL は新しい接続のスレッド オブジェクトを維持します。グローバル権限に関するすべての判断は、スレッド オブジェクト内に格納されている権限ビットを使用して直接行われます。

grant コマンドは、グローバル権限のディスクと対応するメモリを更新します。新しく作成された接続では、新しい権限が使用されます。既存の接続の場合、そのグローバル権限は grant の影響を受けません。
上記の権限を取り消したい場合は、次の手順に従います。

revoke all privileges on *.* from 'ua'@'%';

対応する操作も 2 つあります。ディスク内のアクセス許可フィールドがビット N に変更され、メモリ内のオブジェクトのアクセス値がビット 0 に変更されます。

mysqlDB 権限

grant all privileges on db1.* to 'ua'@'%' with grant option;

現在のユーザーの db 権限と、ディスク上およびメモリ内のオブジェクトを変更する権限を表示するには、SELECT * FROM MYSQL.DB WHERE USER = 'UA'\G を使用します。

db権限はmysql.dbテーブルに保存されます。

注意: グローバル権限とは異なり、db 権限は既存の接続オブジェクトに影響します。

mysql テーブル権限と列権限

テーブルの権限は mysql.tables_priv に保存され、列の権限は mysql.columns_priv に保存され、これら 2 種類の権限が結合されてメモリ内のハッシュ構造 column_priv_hash に保存されます。

db 権限と同様に、これら 2 つの権限が付与されるたびに、データ テーブルが変更され、メモリ内のハッシュ構造が同期的に変更されます。そのため、これら 2 種類の権限の操作は、既存の接続にも影響します。

フラッシュ権限の使用シナリオ

一部のドキュメントでは、権限付与ステートメントを有効にするには、権限を付与した直後にフラッシュ権限コマンドを実行する必要があると記載されています。実際、より正確な表現は、データ テーブル内の権限がメモリ内の権限データと一致していない場合、フラッシュ権限ステートメントを使用してメモリ データを再構築し、一貫した状態を実現できる、というものです。

たとえば、ある時点でデータ テーブル内のレコードが削除されたが、メモリ内にデータがまだ存在する場合、データ テーブル内にレコードが見つからないため、ユーザーへの認証は失敗します。
同時に、メモリ内で判断すると、このユーザーがまだ存在するとみなされるため、このユーザーを再作成しても機能しません。

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。