joppot

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

unix

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

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


hdd-154463_640

概要

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

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

前提

  1. mysqlがインストールされている
  2. 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

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

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

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

上のサンプルデータベースには「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行くらい達してしまいます。

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

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

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

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

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

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

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

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

まとめ

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

スポンサードリンク

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

-unix
-,

執筆者:


comment

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

関連記事

最高に見やすいターミナルのフォントを選ぶ

概要 みなさんこんにちはcandleです。 今回は最高に見やすいターミナルのフォントと題し、解説していきたいと思います。 フォントを選ぶことは人生を選ぶくらい大事なことなのはお分かりだと思います。 プ …

Pythonのライブラリをpipで管理する

概要 みなさんこんにちはcandleです。Pythonのライブラリを管理するpipの導入を行いましょう。 そもそも、pythonのライブラリとはなんでしょうか? pythonはすっごく便利なライブラリ …

emacsでバックアップファイルを作らない設定

概要 emacsは初期設定のままだと、自動的にバックアップファイルが作られます。 バックアップファイルとはemacsの作業中、何かの拍子に閉じちゃったりした時に復元されるものなのですが、正直、邪魔です …

mecabで文章を分かち書きする

概要 みなさんこんにちはcandleです。今回は「分かち書き」をしてみましょう。 分かち書きというのは文章を品詞毎に分ける事です。品詞とは名詞や動詞などのことです。 ちょっとみてみましょうか。 「私は …

MacPortsの使い方

前提 MacPortsがインストールされている。 されていない場合は、予めインストールしておいてください。 用語として、パッケージというのが出てきます。これはunixのソフトみたいなものです。 Mac …

  • English
  • 日本語

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