K-PROJECT > MySQL備忘録

MySQLデータ mysqldumpによるバックアップ (SQL文の標準出力)

種類

MySQLでバックアップ方法は、大きく分けて２種類ある。

1.テーブル単位のバックアップ

2.DBファイル自体のバックアップ

3.mysqldumpによるダンプ/リストア

オプション

オプション	内容
-A,--all-database	すべてのデータベースをダンプする。
-B,--database	複数のデータベースをダンプする場合に使用する。
-P,--port=port_num	MySQLサーバーに接続するためのTCP/IPポート番号 Default 3306
-h,--host=host_name	MySQLサーバーの稼動しているホスト名
-u,--user=user_name	接続するデータベースユーザ
-p,--password[=name]	データベースユーザーのパスワード
-S,--Socket=/path/file	リモートホストからの接続を監視するUNIXソケットを指定 /var/lib/mysql/mysql.sock (当環境 Fedora9の場合)
-C,--compress	サーバ/クライアント間でのデータ通信を圧縮モードで行う
--add-drop-table	念のため、CREATE TABLE文の前にDROP TABLE文を加える
--add-locks	INSERT文の前にテーブルのLOCK TABLES文を加える
--delayed-insert	INSERT文にDELAYEDオプションを設定する
--allow-keywords	CREATE TABLE文において、テーブル名や列名をシングルクオート(')で囲む
-c,--complete-insert	列名のリストを含む形式のINSERT文を出力する
-e,--extended-insert	(複数の行データを同時に挿入する)多重INSERT文の形式でダンプする
-T,--tab=dir	指定したディレクトリdir以下に、テーブル定義と各テーブルデータを個別のファイルに保存する。
-X,--xml	XML形式でダンプする
-F,--flush-logs	ダンプに先立ち、すべてのログをFLUSHする
-f,--force	万が一、ダンプ中にSQLエラーが発生してもダンプを続ける
-l,--lock-tables	(データベース毎に)READ LOCALロックをかけてからダンプを行う
--master-data	CHANGE MASTER TO文を出力することを除き "--first-slave"オプションと同じ
--no-autocommit	SQL文をトランザクションとして実行(各SQL文を"set autocommit=0" と "commit" で囲む)
--single-transaction	ダンプに先立ち、BEGIN文を実行し、そのトランザクション内でデータをダンプする。Version 4.0.2から
-n,no-create-db	CREATE DATABASE文を出力しない
-t,no-create-info	データのみダンプ。テーブルスキーマを出力しない
-d,no-data	テーブルスキーマ(CREATE TABLE文)のみダンプ。データを出力しない
--opt	次の簡略形。--add-drop-table --add-locks --all-quick --extended-insert --lock-tables --disable-keys
-q,--quick	出力バッファに保存せず、標準出力に出力
-r,--result-file=file_name	(標準出力ではなく)指定したファイルにデータをダンプする

実行例

1-1) データベースをダンプし、/home/hoge/xoops-fix.sqlに保存する。

# mysql -uroot -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| hoge               |
| xoops              |
+--------------------+
exit;
# mysqldump -F -l xoops > /home/hoge/xoops-fix.sql

1-2) 複数のデータベースをダンプする。

# mysqldump -F -l -databases xoops hoge > /home/hoge/xoops-fix.sql

1-3) すべてのデータベースをダンプする。

# mysqldump -F -x -q --add-drop-table --all-databases | gzip -9 xoops-fix.gz

補足

XOOPS 2.0.16JPから、
XOOPS Cube Legacy 2.1.7にアップグレードした際に必要になった作業

環境

Linux Fedora9 2.6.27.25-78.2.56
MySQL Server version: 5.0.77
Apache/2.2.9

以下作業順序

2-1) /etc/my.cnf 編集

# vi /etc/my.cnf
[client]                   # クライアントがeuc-jpとして扱うことを明示的に指定する。
default-character-set=ujis # これを付けないと正しいエンコードで読まない事がある


[mysqldump]                # mysqldump を実行する場合に、euc-jpとして出力する
default-character-set=ujis # 為に必要(設定しないとEUC-JPで編集しても化ける)

2-2) 環境変数の確認

# mysql -uroot -p
mysql> use xoops;
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1 <- ujisでないと文字化けになる 
| character_set_connection | latin1 <- ujisにしないと化けるversion有り 
| character_set_database   | latin1 <- ujisでないと文字化けになる 
| character_set_filesystem | binary <- このままでよい   | 
| character_set_results    | latin1 <- ujisでないと文字化けになる 
| character_set_server     | latin1 <- このままでよい   | 
| character_set_system     | utf8   <- このままでよい   | 
| character_sets_dir       | /usr/share/mysql/charsets/ | 
+--------------------------+----------------------------+

2-3) ダンプする

# mysqldump --default-character-set=latin1 -Q --opt \
> -uroot -p xoops > xoops.sql

2-4) データベースを削除する

mysql> DROP DATABASE xoops;

2-5) データベースを ujis で作り直す

mysql> CREATE DATABASE cubedb CHARACTER SET ujis COLLATE ujis_japanese_ci;

2-6) ダンプ内容を編集(EUC-JP)

・エディタは EUC-JP を扱えるエディタを使用する
・SET NAMES latin1 を SET NAMES ujis に置換
・CHARSET=latin1; を CHARSET=ujis; に置換
・binary 属性を持っているフィールドがある場合
・character set latin1 collate latin1_bin を character set ujis collate ujis_bin に置換
・xoops-fixed.sql などとして保存

2-7) ダンプ内容をリストア

# mysql -uroot -p < xoops-fixed.sql

以上で移行作業は終わりです。文字化けなどもしっかり対応できてると思います^^

Copyright (C) K-PROJECT All Rights Reserved
ご意見ご感想は、下記のメールアドレスまで