joppot

コピペで絶対動く。説明を妥協しない

unix

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

投稿日:2014年2月19日 更新日:

Pocket

hdd-154463_640

概要

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

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

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

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

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


前提

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

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

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

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


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

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

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

MAMP_と_MAMP-4

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

MAMP_と_名称未設定-4

MAMP-3

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

bin_—_bash-5

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

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

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

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

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

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

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

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

mysqldump -u root -p btest > alldata.sql

mysqlbackup_—_bash-2

ファイルをのぞくと
alldata_sql_と_mysqlbackup
バックアップができました。


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

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

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

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

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

になります。

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

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

mysqlbackup_—_bash-2 2

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

mysqlbackup_—_bash-2 3


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

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

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

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

MAMP

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

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

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

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

mysqlbackup_—_bash-4

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

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

かなり莫大な量です。

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


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

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

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

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

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

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

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

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

mysqlbackup_—_bash-5

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

onlyusersdata_sql


まとめ

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

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

スポンサードリンク

「為になったなぁ」と思ったら、シェアお願いします。

-unix
-,

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

mysqlデータベースのバックアップと復元(リストア)

概要 みなさんこんにはcandleです。今回はmysqlのバックアップとリストアを試してみましょう。 前回くらいに、様々なバックアップを試しましたが、それを問題なくリストア(復元)できなくては、意味な …

cdとlsを合体させて移動したら中身を表示する

概要 みなさんこんにちわ candleです。今回はcdとlsを組み合わせて、移動する度に中身を表示する方法を試してみましょう。そもそも、なぜこんなことにニーズがあるかというと、cdしてディレクトリを移 …

正規表現を極めるその1

概要 みなさんこんにちはcandleです。今回から正規表現を1から100まで極めようと思います。 正規表現って、使えると便利ですが、それを勉強して使いこなせている人って少ないと思います。この機会にぜひ …

Macのgrepの結果に色を付ける

概要 みなさんこんにはcandleです。今回はgrepの結果に色を付ける技を紹介します。 grepはファイルの中身を検索してくる便利な検索ツールです。grepは基本的に色づけはしてくれませんが、オプシ …

macターミナルのウィンドタイトルとタブ名を変更する

概要 みなさんこんにちはcandleです。 ターミナルのウィンドタイトルとタブ名を変更する企画をやっていきたいと思います。 ウィンドタイトルというのは何処かというと下の画像の部分になります。タブ名もそ …

  • English
  • 日本語

プロフィール


ベンチャー企業のCTOをやってます。大学時代にプログラミングを始め、javaから入門し、C++へて、PHPへと進み、会社ではRailsを使用。自動化が大好きなプログラマー

スポンサードリンク

アーカイブ