mysqlのデータベース、テーブル、データをmysqldumpを使用してバックアップする

Pocket

概要

みなさんこんにちはcandleです。今回はmysqlのデータベースのバックアップを様々試します。

データベースのバックアップと言っても、テーブルのみとか構造のみとか、様々なオプションがあります。

データベースの目的別に適切にバックアップをとりましょう。

今回はMAMPのmysqlを使用しますが、phpmyadminのバックアップ機能は使用せずに、unix経由でバックアップをとります。

仮に、MAMPが入っていなくてもmysqlになれていればかまいません。

前提

MAMPが入っているかmysqlが入っているか

MAMPの導入に関してはこのサイトの下の記事を参照してください。

ローカル開発環境のMAMPをインストール

unixコマンドが最低限使える

サンプルデータベースを用意する

下のmysql文をコピーしてmsyql上で実行してください。

MAMPを使用しているならば、phpmyadminの「SQL」という所をクリックしてください。

下のSQL文をコピーアンドペーストして実行すれば、btestデータベースが作成されます。

sql文

CREATE DATABASE IF NOT EXISTS `btest` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `btest`;


CREATE TABLE `board` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) NOT NULL,
  `title` varchar(100) NOT NULL,
  `created` datetime NOT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

INSERT INTO `board` (`id`, `user_id`, `title`, `created`, `modified`) VALUES
(1, 2, '今日の夕ご飯の献立', '2014-02-19 16:15:29', '2014-02-18 09:42:27'),
(2, 2, 'ガルボーおいしい', '2014-02-06 11:03:50', '2014-02-18 09:43:01'),
(3, 1, '小学校にトーテムポールあったよね？', '2014-02-12 19:07:13', '2014-02-18 09:44:01');


CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `age` int(10) NOT NULL,
  `created` datetime NOT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

INSERT INTO `users` (`id`, `name`, `age`, `created`, `modified`) VALUES
(1, 'alice', 10, '2014-02-18 05:29:13', '2014-02-18 09:40:22'),
(2, 'bob', 12, '2014-02-17 21:11:44', '2014-02-18 09:40:43'),
(3, 'tom', 15, '2014-02-17 04:37:07', '2014-02-18 09:41:18');

mysqldumpを使う準備

mysqldumpというのはバックアップをとる時に使用するコマンドです。これが大活躍します。

MAMPを使う場合、Mac OSにあるmysqldumpを使うのではなくMAMPのmysqldumpを使用する必要があります。

MAMPのmysqldumpは以下の所にあります。

/Applications/MAMP/Library/bin/mysqldump

バックアップしたデータはファイルで出力されるので、そのファイルを置く場所を確保しましょう。私はデスクトップに「mysqlbackup」フォルダを作りそこに置いていきます。

Mysqlのデータベースを1つ指定して全てのデータをバックアップする

これの利点は任意のデータベース、全ての情報が入っているので、データの取りこぼしがないことです。問題はデータベースの構造とテーブルの構造が同時に保存されるので、微々たるものですが、データのみのバックアップより容量をとります。

一般に下の様なコマンドを打ってバックアップをとります。

mysqldump -u ユーザー名 -p  バックアップしたいデータベース名 > バックアップファイル名

上のコマンドをサンプルデータベースで試します。
MAMPを使用している場合は、セキュリティの問題もありますが、

ユーザー名「root」パスワードを求められたら「root」で実行できます。

では実行してbtestデータベースを丸ごとバックアップとります。

mysqldump -u root -p btest > alldata.sql

ファイルをのぞくと

バックアップができました。

データベースの指定したテーブルのバックアップをとる

データベース全てのデータをバックアップしなくても、１つのテーブルまたは複数のテーブルだけをバックアップとれば十分な場合もあります。テーブルを指定したバックアップの取り方をみてみましょう。

上のサンプルデータベースには「users」と「board」テーブルがあります。

テーブルを指定してバックアップをとる一般的な書き方は下の様になります。

mysqldump -u ユーザー名 -p データベース名 テーブル名 > バックアップファイル名

になります。

試しに、サンプルデータベースのusersテーブルのバックアップをとってみます。

mysqldump -u root -p btest users > usersdata.sql

中身をみてみると、確かにusersテーブルのデータがバックアップされています。

Mysqlにある全てのデータベースをバックアップする

Mysqlの全てのデータベースを保存する方法があります。多くの場合は１つのデータベースのみを使用しているのではなくて、複数のデータベースを併用して使っていると思います。それぞれのデータベースを毎回コマンドを打ってバックアップとるのは面倒です。

それらをひっくるめてバックアップします。

現在、私のMysqlデータベースは下の様になっています。

一般に下のコマンドで、全てのデータベースをバックアップします。

mysqldump -u ユーザー名 -p  --all-databases > バックアップファイル名

試しに、全てのデータベースをバックアップしてみます。MAMPを使用している人は下のコマンドで実行できます。

mysqldump -u root -p --all-databases > mydatabases.sql

多少、Warningとか出ていますが、たぶん—eventとかいうオプションをつければ解決できそうですね。

このバックアップをみると、Mysqlにある全てのデータが保存されているのが確認できます。

かなり莫大な量です。

ほんの小さなデータベース2つ作っただけでも、元からある、データベースとあわせると800行くらい達してしまいます。

任意のデータベースのデータだけを保存する

１つのデータベースの中身を保存するのと、対して変わらないのですが、テーブルにおさめられているデータだけをバックアップするオプションもあります。

これはデータベースの作成クエリとテーブルの作成クエリが書かれていないデータのインサート文のみのバックアップになります。

私、個人の意見としては、データだけバックアップとるくらいなら、データベースの構造も、テーブルの構造もバックアップとっておいて良いと思います。

テーブルのデータのみを保存するコマンドは一般に下のコマンドになります。

mysqldump -u root -p --no-create-info データベース名 > バックアップファイル名

試しにbtestデータベースのテーブルに収まっている値だけバックアップとってみましょう。

mysqldump -u root -p --no-create-info > onlyusersdata.sql

バックアップファイルを閲覧するとデータのみが保存されているのが分かります。

まとめ

様々なオプションとコマンドでMysqlのデータベースをバックアップする方法を見てきました。

次回以降にバックアップをとり、復元までの一連の流れをやってみたいと思います。