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

概要

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

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

前提

mysqlがインストールされている
unixコマンドが最低限使える

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

チュートリアル形式で実践したい場合はサンプルデータベースを作ります。mysqlにログインして

mysq -u root -p

下のmysql文をコピーしてmsyql上で実行してください。
btestデータベースとboardテーブルとusersテーブルそれとサンプルデータが作られます。

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, 'SSL通信の必要性について', '2014-02-19 16:15:29', '2014-02-18 09:42:27'),
(2, 2, 'javascriptの圧縮', '2014-02-06 11:03:50', '2014-02-18 09:43:01'),
(3, 1, 'cssの書き方', '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はバックアップをとる時に使用するコマンドです。

brewでmysqlを入れた場合は大体一緒にインストールされています。

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

makemysqldumpfolder

任意のデータベース全てをバックアップ

これの利点はデータベース全ての情報が入っているので、データの取りこぼしがないことです。
問題はバックアップファイルが肥大化します。

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

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

上のコマンドをサンプルデータベースで試します。
実行してbtestデータベースを丸ごとバックアップとります。

mysqldump -u root -p btest > alldata.sql

ファイルをのぞくと

alldata_sql_と_mysqlbackup

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

任意のテーブルのバックアップをとる

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

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

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

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

になります。

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

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

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

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

Mysqlの全てのデータベースを保存する方法があります。例えば、Mysqlのバージョンをアップグレードする時や、サーバを移管する時に使うと思います。

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

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

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

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

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

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

かなり莫大な量です。

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

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

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

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

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

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

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

mysqldump -u root -p --no-create-info btest > onlydata.sql

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

まとめ

様々なオプションとコマンドでMysqlのデータベースをバックアップする方法を見てきました。
次回以降にバックアップをとり、復元までの一連の流れをやってみたいと思います。