【MySQL】mysqldumpで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

上記コマンドを実行することで、ダンプされた対象データが全て置き換わります。

テーブルを指定した場合は対象テーブルのみ、データベース全体ならすべてのテーブルが置き換わるので注意してください。

 



投稿日:2019-12-05    更新日:2019-12-18

[スポンサーリンク]

関連記事
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
サイト内検索
プロフィール

プロフィール

[Name : じゃぶじゃぶ(@jbjb_2019)]
都内で社内SEをしているおじさん。
仕事で得られる知識だけでは限界を感じ、 WEBの勉強がてらITブログを開始。
サーバからWEBサイトまでフルスクラッチで開発しました。
現在は勉強のモチベーションを保つために活用中。
興味があることを雑記的に書いていきます。

[スポンサーリンク]

[スポンサードリンク]

最近の記事