【MySQL】mysqldumpでMySQLのバックアップを取る方法
-
カテゴリ：
- DB
-
タグ：
- #MySQL
MySQLでダンプする方法のメモです！
そもそもダンプとは「データを出力する」という意味がらしいですが、データベース関連でダンプというと「データのバックアップを取ること」を指します。
MySQLではダンプを取るためのツールとして「mysqldump」というものが用意されています。
mysqldumpの使い方
MySQLがインストールされている環境で、Windowsならコマンドプロント、LinuxとMacならコマンドラインから以下コマンドを実行することでダンプができます。
-- 構文
mysqldump -u[user] -p[password] -h[host] [options] [database] {[table1],[table2],・・・} > [保存先のパス]
-- 例
mysqldump -uUSER -pPASS -hlocalhost sampleDB sampleTB1,sampleTB1 > /var/tmp/sampleDB.sql
様々なオプションが用意されています。詳細は下記HPを参照してください。
https://dev.mysql.com/doc/refman/5.6/ja/mysqldump.html
上記例では保存先（/var/tmp/sampleDB.sql）を指定することで、指定したフォルダにSQLファイルが作成されます。
中身はこんな感じ。
-- MySQL dump 10.14 Distrib 5.5.56-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database: sampleDB
-- ------------------------------------------------------
-- Server version 5.5.56-MariaDB
--
-- Table structure for table `sampleTB1`
--
DROP TABLE IF EXISTS `sampleTB1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sampleTB1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`account_id` bigint(20) NOT NULL,
`name` varchar(255) NOT NULL,
`detail` longtext NOT NULL,
PRIMARY KEY (`id`),
KEY `account_id` (`account_id`),
CONSTRAINT `sampleTB1_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `sampleTB1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `sampleTB1`
--
LOCK TABLES `sampleTB1` WRITE;
/*!40000 ALTER TABLE `sampleTB1` DISABLE KEYS */;
INSERT INTO `sampleTB1` VALUES (1,1,××××,××××,×××××,×××××,×××××,×××××);
INSERT INTO `sampleTB1` VALUES (1,1,××××,××××,×××××,×××××,×××××,×××××);
INSERT INTO `sampleTB1` VALUES (1,1,××××,××××,×××××,×××××,×××××,×××××);
INSERT INTO `sampleTB1` VALUES (1,1,××××,××××,×××××,×××××,×××××,×××××);
INSERT INTO `sampleTB1` VALUES (1,1,××××,××××,×××××,×××××,×××××,×××××);
/*!40000 ALTER TABLE `sampleTB1` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `sampleTB2`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sampleTB2` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
・・・・・・
ダンプはデータのバックアップと説明しましたが、実はMySQL のダンプの中身はSQL文がつらつらと書かれているだけのものです。データベースに存在するテーブル1つ1つに対して「Drop table -> Create table -> Insert 」のSQLが記述されています。
テーブルを削除した後に新規作成して、再度データを登録する内容になっているので、ダンプしたタイミングの状態を復元できるという寸法です。
mysqldumpの実行例
■特定のテーブルのある条件でダンプ
mysqldump --opt -u root -p データベース名 テーブル名 "-w 条件(id=1など)" > sampleDB.sql
mysqldump -u root -p データベース名 テーブル名 -w="条件(id=1など)" > sampleDB.sql
mysqldump -u root -p データベース名 テーブル名 --where "条件(id=1など)" > sampleDB.sql
mysqldump -t -u[user] -p [database] [table] --where "(year = '2017' and month = '03') or (year = '2017' and month = '04')" > /tmp/sampleDB.sql■特定のテーブルをダンプ
mysqldump -u [user] -p[pass] [database] [table] > /var/tmp/m_accounts_backup_`date +%Y%m%d-%H%M%S`.sql■特定のテーブルを除外してダンプ
mysqldump -u xxxuser -p --ignore-table=xxxtable --ignore-table=yyytable xxxdatabase > xxx.sql■プロシージャーも含めてダンプ
mysqldump --routines -u [user] -p[pass] [database] > /var/tmp/sampleDB.sql
mysqldump --extended --routines -u [user] -p[pass] [database] > /var/tmp/sampleDB.sql■テーブルのスキーマ(構造)のみダンプ
mysqldump -u [user] -p[pass] -d [database] > /var/tmp/sampleDB.sql■スキーマ（ビューを含む）のみダンプ
mysqldump -u root -ppassword db_name --no-data --skip-triggers --skip-dump-data > only_schema.sql■データのみダンプ
mysqldump -u root -ppassword db_name --no-create-info --skip-triggers --skip-dump-data > only_data.sql■ストアドとトリガーのみダンプ
mysqldump -ntd -R -uuser -ppass dbname > /var/tmp/prc.sql
mysqldump -u root -ppassword db_name --no-create-info --no-data --routines --skip-dump-data > only_stored.sql
※オプションの説明
--no-create-info create table : 文を出力しない
--no-data : データを出力しない
--routines : ストアド情報を出力
--skip-triggers : トリーガを出力しない
--triggers : トリーガを出力する
ダンプしたデータをインポート
mysql -u [user] -p[pass] [database] < /var/tmp/sampleDB.sql上記コマンドを実行することで、ダンプされた対象データが全て置き換わります。
テーブルを指定した場合は対象テーブルのみ、データベース全体ならすべてのテーブルが置き換わるので注意してください。
