MySQLで、データベースをダンプする

ダンプ

普通のダンプ。ストアドプロシージャもトリガーもビューも無いとき

mysqldump -uUSER -pPASS DBNAME --single-transaction | gzip > DB.sql.gz

トリガー以外をダンプ。データは出力する。 BLOBは16進表記にする。定義者は消す

mysqldump -uUSER -pPASS DBNAME --single-transaction -R --hex-blob --skip-triggers \
    | sed 's/DEFINER=[^*]*\*/\*/g' \
    | gzip > DB.sql.gz

トリガーだけダンプ。データは出力しない。定義者は消す

mysqldump -uUSER -pPASS DBNAME --single-transaction -d -t \
    | sed 's/DEFINER=[^*]*\*/\*/g' \
    | gzip > DB-TRIGGERONLY.sql.gz

オプション	内容
--single-transaction	テーブルロックをせず、トランザクションを使って一貫性を保つ
--hex-blob	BLOB型を16進でダンプして文字化けさせない。そのままINSERTできる
-R, --routines	ストアドプロシージャ、ファンクションを出力する（デフォルトは出力しない）
--skip-triggers	トリガーを出力しない（デフォルトは出力する）
-d, --no-data	データを出力しない
-t, --no-create-info	テーブルとビューの定義を出力しない

--opt はデフォルトで有効だがこれには --lock-tables が含まれている。が、--single-transaction を指定することでテーブルロックは無効化されるので安心
--hex-blob でダンプしても普通にinsertできるので安心
ビューとかトリガーには /*!50017 DEFINER=`USER`@`localhost`*/ のような定義者の定義が付く。これは別DBでユーザ名が変わったりしたときにすげー邪魔なので DEFINER=`USER`@`localhost` を削除する

DROP TABLE IF EXISTS
CREATE TABLE ...
INSERT INTO ...
CREATE TRIGGER ...

の順になるので、トリガーが発動する心配はない。（少し前まで勘違いして心配してた）

トリガーの確認

MariaDB [dbhoge]> select TRIGGER_NAME,EVENT_OBJECT_TABLE,EVENT_MANIPULATION,ACTION_TIMING 
    -> from information_schema.TRIGGERS where TRIGGER_SCHEMA='dbhoge';
+--------------+--------------------+--------------------+---------------+
| TRIGGER_NAME | EVENT_OBJECT_TABLE | EVENT_MANIPULATION | ACTION_TIMING |
+--------------+--------------------+--------------------+---------------+
| add_data     | kakaku_table       | INSERT             | AFTER         |
| move_data    | kakaku_table       | UPDATE             | AFTER         |
+--------------+--------------------+--------------------+---------------+

カラム名	内容
TRIGGER_NAME	トリガーの名前
EVENT_OBJECT_TABLE	トリガーになるテーブル
EVENT_MANIPULATION	契機
ACTION_TIMING	実行されるタイミング

ストアドプロシージャ、ファンクションの確認

MariaDB [dbhoge]> select ROUTINE_NAME,ROUTINE_TYPE,DTD_IDENTIFIER 
    -> from information_schema.ROUTINES where ROUTINE_SCHEMA='dbname';
+--------------+--------------+----------------+
| ROUTINE_NAME | ROUTINE_TYPE | DTD_IDENTIFIER |
+--------------+--------------+----------------+
| log_insert   | PROCEDURE    | NULL           |
| get_kakaku   | FUNCTION     | DECIMAL(10,2)  |
| get_stat     | FUNCTION     | VARCHAR(20)    |
+--------------+--------------+----------------+