code up

スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

mysqldumpでデータのエクスポート

mysqldumpを使ってデータをエクスポートした。いつも使っているオプションについての解説。

環境はEOLを迎えてしまったMySQL 5.0.82 + 同じくEOLを迎えてしまったRHEL 4(/etc/redhat-release; Red Hat Enterprise Linux AS release 4 (Nahant Update 4))。EOLについてはサーバーを運用して頂いているところからサポートは得られているのであと少しは大丈夫。

シチュエーション

  1. 開発環境に空のデータベースを手動で作成 (CREATE DATABASE ...)
  2. そのデータベースにアクセスするユーザーを作成。この時のユーザー名は稼働中のデータベースとは異なっている (CREATE USER ...)
  3. そのユーザーはそのデータベースにアクセスできる状態に (GRANT ... PRIVILEGES ...)
  4. 稼働中のデータベースをダンプ ←ここを解説
  5. ダンプしたデータをそのデータベースに流し込む←この記事の一番下

クラスタなし、レプリケートなしのシングル構成である。データの整合性は完全でなくてもよく稼働中のシステムには影響が出なければいいという程度でエクスポートした。

マニュアルは、5.0だけど参照したのは5.1(英語はこっち)。マニュアルで5.1以降と書いてあるものが実は5.0でも同じだったりするので。

今回のmysqldumpとオプション

mysqldump --user=root --password=root --host=remote_host --hex-blob --single-transaction --routines --skip-comments --skip-lock-tables --max_allowed_packet=160M --default-character-set=utf8 db_name | sed '/^\/\*\!50013 DEFINER=/d' > db_name.txt

これで、テーブル構造とその中身、ビュー、プロシージャ、ファンクション、トリガーがエクスポートされる。

--user=foo, -u foo
ユーザー名。省略するとログインユーザー名
--password=foo, -pfoo
パスワード。省略するとパスワードなしでログインを試みる。'--password'というように"="をつけないとパスワード入力を求められた上でログインすることになる(パスワードを履歴に残さない時など)。'-p'の場合は間にスペースがないことに注意
--host=foo, -h
ホスト名。省略するとlocalhost
--hex-blob
バイナリデータを16進文字列で出力。blobでこれ指定しないとインポートできなかったことがあったので
--single-transaction
ダンプ時にBEGINを発行してから、実行する。InnoDBテーブルのみ。MySQLはデフォルトではautocommitがオンである。つまりステートメント毎にコミットしてしまうので各ステートメント間でデータの変化があってもそのままで出力される。BEGINでトランザクションを開始しておくことで、(分離レベルを変更してなければ)REPEATABLE-READの範囲で出力されるようになる(マニュアルには明示されてないので不確か。ISOLATION LEVEL(分離レベル)を変更してると問題があったらしい)。ここを見るとmysqldumpで強制的にREPEATABLE-READを行ってるとのこと。ただ、バージョンがいくつからか分からない・・・
--routines, -R
FUNCTION/PROCEDUREをダンプする
--skip-comments
プログラムバージョン等のコメントを抑止する。デフォルトでは出力するようになっている(--comments)
--skip-lock-tables
デフォルトで有効になるテーブルロック(--add-locks)を無効にする。--single-transactionを指定すると自動的にこのオプションがつく(もしくは--lock-tablesが指定されない)みたいだが、ドキュメントには明示されていないので一応指定
--max_allowed_packet=160M
ダンプ時にmysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `table_name` at row: 5といったパケットサイズのエラーが出る場合に指定。最大1GBまで
--default-character-set=utf8
デフォルトはUTF-8だが、my.cnf/my.iniがないときにシステムの文字コード(Windows-31J)を使ったりしないか心配なので
| sed '/^\/\*\!50013 DEFINER=/d'

Windowsの場合cygwin環境などがあることが前提となる。CREATE VIEW時にSQL SECURITY INVOKERを指定せず、エクスポート時とインポート時のユーザー名が異なる場合に必要となる。

mysqldumpの出力結果にはDEFINERというそのビュー、ファンクション、プロシージャー、トリガーのオーナーとなるべきユーザーが一緒に出力される。

... /*!50017 DEFINER=`root`@`localhost` */ ... (TRIGGER)
... /*!50020 DEFINER=`root`@`localhost`*/ ... (FUNCTION/PROCEDURE)
/*!50013 DEFINER=`foo`@`localhost` SQL SECURITY DEFINER */ (VIEW)

今の環境はTRIGGER、FUNCTION、PROCEDUREはrootで作成しているが、VIEWはそのデータベース用のユーザーがそれぞれ作成していた。sedで50013 DEFINERのある行を削除しているのは、この行が残っているとインポート時に、

ERROR 1227 (42000) at line 657: Access denied; you need the SUPER privilege for this operation

というようなエラーが出てしまう。これを素直に受け止め、rootでインポートしなおすと今度は作成されるビューがエクスポート元のユーザーがDEFINERとなった状態で作成される。このあたり(stackoverflow)を参考にした。

DEFINERは5.0だとMySQLに存在しないユーザーでも指定ができる。そして作成されたビューをデータベース用ユーザー(=DEFINERとは違うユーザー)で検索しようとすると、

* DEFINERで指定したユーザーが存在して、検索対象のビューが参照しているテーブルの閲覧権限を持っていない場合

ERROR 1356 (HY000): View 'foo.view_bar' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

* DEFINERに指定されたユーザーが存在しない場合

ERROR 1449 (HY000): There is no 'foo'@'localhost' registered

そもそもCREATE VIEWのDEFINERの部分はSQL SECURITY INVOKERを指定しておく方がリスクが少ないとされているので、そう作っておけばこんなことしなくてもよかったのかもしれない。該当部分を少しだけ引用

To minimize the risk potential for stored program and view creation and use, follow these guidelines:
  • For a stored routine or view, use SQL SECURITY INVOKER in the object definition when possible so that it can be used only by users with permissions appropriate for the operations performed by the object.

ファンクション、プロシージャー、トリガーはrootで作成しているので、DEFINER=`root`@`...`のままで問題はないが、異なるユーザーで作成している場合には出力されたエクスポートデータに対して同様の処理が必要になるだろう。

指定はしてないけど、適用されているデフォルト値

--port=3306
ポート番号はデフォルトから変えていない
--opt
--add-drop-table(DROP TABLE文追加) --add-locks(出力されるデータにLOCK/UNLOCK TABLESが入る) --create-options(CREATE TABLE文にMySQL独自オプションを追加) --disable-keys(リアルタイムインデックス作成処理を抑止) --extended-insert(複数行INSERT文の生成) --lock-tables(ダンプ中すべてのテーブルをロック) --quick(バッファリングせずに行を読み出すので早く読み込めるらしい) --set-charset(SET NAMESを出力)。これらのショートカット。実はこれがデフォルトで指定されてることになってる。上記の繰り返しになるが、これらのオプションの中で--lock-tablesだけ無効(--skip-lock-tables)にしている
--triggers
トリガーを出力。デフォルトで有効
--tz-utc
デフォルトでSET TIME_ZONEを出力
--quote-names
テーブル名やカラム名などを'`'または'"'でクオートする。どんな時にオフにするのかは不明

これらは/etc/my.cnf(とかmy.ini)の[mysqldump]セクションで指定でも可能

[mysqldump]
quick
max_allowed_packet = 200M

データのインポート

エクスポートしたデータは次のようにインポートする。今回エクスポートしたデータにはCREATE FUNCTION文、CREATE TRIGGER文(どちらもrootがDEFINER)が含まれているためrootユーザーで実行する。

mysql --user=root --password=root --host=remote_host db_name < db_name.txt

DDLのエクスポート

データベースの構造を比較する時とか用。

mysqldump --user=root --password=root --host=remote_host --skip-opt --create-options --set-charset --single-transaction --routines --skip-comments --no-data db_name > db_name.ddl.txt

ダンプしたDDLが文字化け

DDLをダンプしたところ、カラムコメントが文字化けしていた。EUCにすると読める状態。my.ini/my.cnfでは一通りdefault-character-set=utf8を指定しているし、show variables like '%char%';でもutf8が出てくるので問題はない。

結論だけ書くと、CREATE TABLE文をPuTTYで直接流し込んでいた。その時の文字コードがEUCとなっていたため文字化けしたようだ。そんなもんなん?

関連記事
タグ:MySQL Linux
上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。