MySQL道普請便り

第4回 テーブルをコピーするついでにデータディレクトリーの中身を知る

この記事を読むのに必要な時間:およそ 5.5 分

勉強用のMySQLをいじっている時に,「もとに戻せない操作しちゃったけど戻せない……」ということはありませんか? MySQLのDDLDROP TABLEALTER TABLEなどの,定義を変更するためのステートメントのことです)はトランザクション非対応なので,一度DROPしてしまうとロールバックすることはできません。それどころか,DDLが実行される直前に「暗黙のコミット」が走る作りになっているので,トランザクションの最中にDDLを実行してしまうと,そこまでの操作はコミットされてしまうのです。そしてデフォルトでオートコミット……うっかりの種はどこにでも転がっています。

今回は,「勉強用のMySQLのテーブルをコピーしてバックアップする」「ついでにデータディレクトリーの構造を勉強する」がテーマです。

デモンストレーション環境について

今回利用している環境は,第2回 MySQLにはじめてのデータを入れてみるで利用したものを「そのまま」利用しています。第2回で登場したテーブルの名前が出てきますので,今回のコマンドをコピー&ペーストで試す際には第2回のコマンドも一通り実行しておいてください。

いくつかのテーブルをコピーする方法

まずはテーブルをコピーしてみましょう。といっても,ここでは「本番環境で求められるような一貫性の取れたバックアップ」の話はしません。トラフィックが全く流れてこない状態で,さくっとテーブルをコピーするためのいくつかの方法です。

CREATE TABLE .. LIKEINSERT INTO .. SELECTを利用したコピー

mysql> use vmstat
mysql> CREATE TABLE vmstat_bak LIKE vmstat;
mysql> INSERT INTO vmstat_bak SELECT * FROM vmstat;

CREATE TABLE vmstat_bak LIKE vmstat「vmstatテーブルと同じカラム定義とテーブル属性でvmstat_bakというテーブルを作る」という意味のステートメントです。同じカラム定義とテーブル属性を持っているvmstat_bakテーブルに,INSERT INTO vmstat_bak SELECT * FROM vmstatとすることで,「vmstatテーブルから全ての行の全てのカラムをSELECTした結果をvmstat_bakテーブルにINSERT」し,vmstat_bakテーブルはvmstatテーブルの(その時点での)完全なコピーになります。切り戻す場合はRENAME TABLE vmstat TO vmstat_old, vmstat_bak TO vmstatステートメントを実行することで,定義情報,データ全てがコピー取得時の状態に戻せます。筆者はこの方法を好んで使いますが,時にはもう少し良い方法があったりもします。

CREATE TABLE .. AS SELECT ..を利用したコピー

mysql> CREATE TABLE vmstat_bak_2 AS SELECT * FROM vmstat;

CREATE TABLE vmstat_bak2 AS SELECTクエリーは,「SELECTクエリーの結果をvmstat_bak_2テーブルとして固定化する」という意味のステートメントです。1ステートメントで済むのでこちらの方が簡単ですか? このステートメントは,「SELECTクエリーの結果を固定化する」ものなので,「SELECT結果に含まれない情報」はvmstat_bak_2テーブルにはコピーされません。端的には,オートインクリメントの値やインデックスはテーブルの属性であってクエリーの結果には含まれないので,コピーされません。SHOW CREATE TABLEステートメントでテーブル定義を確認してみると,コピー先のテーブルにはPRIMARY KEYが定義されていないことが確認できます。

mysql> SHOW CREATE TABLE vmstat\G
mysql> SHOW CREATE TABLE vmstat_bak_2\G

このテーブルの属性情報はコピーされないというのは良し悪しで,「テーブル定義を変更するのでコピーを取っておこう」という用途には使えません。ただし,「テーブル定義は変更せず,データを削除したり更新したりするだけ」であればこのコピーには問題はありません。また,インデックスは存在しないほうがテーブルへのデータの挿入は高速なため,「単にデータだけで良いコピー」が欲しい場合はこちらのほうが高速にコピー可能です。このコピーからデータを切り戻す場合は,TRUNCATE TABLE vmstatステートメントで一度vmstatテーブルの中身を空にしてから,INSERT INTO vmstat SELECT * FROM vmstat_bak_2ステートメントで行を全てコピーしなおします。

mysqldumpコマンドを利用してコピー

本当はこれはテーブルの「コピー」ではなく,「バックアップ」に類するものです。mysqldumpコマンドは論理バックアップを取得するための,MySQL付属のコマンドラインクライアントです。引数としてデータベース名(vmstatデータベース)とテーブル名(vmstatテーブル)を渡すことで,指定したテーブルの論理バックアップを取得できます。

mysqldumpコマンドはバックアップを標準出力に出力してしまうので,リダイレクトでファイルに出力させ,その中身を見てみてください。CREATE TABLEステートメントとINSERT INTOステートメントが並んでいるのが確認できます。このように,「そのテーブルと同じものをもう一度作るために必要なSQL」を出力してくれるのがmysqldumpコマンドです。mysqlコマンドの標準出力にsqlファイルを与えるか,mysqlコマンドからsource vmstat.sqlとすることで,vmstatテーブルを新しく作成することができます(この方法の場合,別のテーブル名でリストアするには,sqlファイルを編集する必要があります)。

$ mysqldump vmstat vmstat > vmstat.sql
$ less vmstat.sql
$ mysql vmstat < vmstat.sql

著者プロフィール

yoku0825

GMOメディア株式会社のDBA。日本MySQLユーザ会員。主に地雷担当。主食はMySQLだがPercona Server,MariaDB,InfiniDBにMroongaストレージエンジンなど,mysqldでありさえすれば雑食。

Twitter:@yoku0825

コメント

コメントの記入