Webサイト制作コースのお申し込みはこちら

MySQLのバックアップについて、コピペで使えるコマンドサンプルを紹介しながらまとめています。

以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。

サンプルデータベースのインストール方法は、以下の記事を参考にしてください。

【関連記事】
MySQLの入門には、GUIツールで慣れ、サンプルDBを使った学習が効果的

バックアップ実行コマンドmysqldumpの使い方

MySQLのデータバックアップには、mysqldumpコマンドを使用します。使い方は以下のとおりです。

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] --all-databases [OPTIONS]

MySQL :: MySQL 5.6 リファレンスマニュアル :: 4.5.4 mysqldump — データベースバックアッププログラム

以下、mysqlのrootアカウントのパスワードがmy-secret-pwという前提で解説しています。

まずは、show databasesでデータベース一覧を確認してみましょう。

% mysql -u root -pmy-secret-pw
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

employeesというデータベースが存在することが確認できました。

以下のコマンドで、employeesデータベースをbackup.sqlというファイル名でバックアップします。

% mysqldump -u root -pmy-secret-pw employees > backup.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

上記の警告は、コマンドラインインタフェースでパスワードを使用するとセキュリティ的に問題であることを示しています。以下のように実行し、「Enter password」でパスワード(ここではmy-secret-pw)を入力することで警告が解消できます。

% mysqldump -u root -p employees > backup.sql
Enter password:

なお、mysqldumpに必要な権限は、バックアップ対象のテーブルに対するselect権限、ビューを含む場合はshow view権限、トリガーを含む場合は、trigger権限、–single transactionオプションを使わない場合はlock tables権限など。オプション指定によっては、さらに別の権限が必要なケースもあります。

出力されたバックアップファイル(ここではbackup.sql)は、以下のような内容になります。

-- MySQL dump 10.13  Distrib 5.7.28, for Linux (x86_64)
--
-- Host: localhost    Database: employees
-- ------------------------------------------------------
-- Server version       5.7.28

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
 :
 :

データベース構造を構築するためのDDLと、データ自体をinsertするSQLで構成されています。

以下は、departmentsテーブルを構築するためのDDLです。

--
-- Dumping data for table `departments`
--

LOCK TABLES `departments` WRITE;
/*!40000 ALTER TABLE `departments` DISABLE KEYS */;
INSERT INTO `departments` VALUES ('d009','Customer Service'),('d005','Development'),('d002','Finance'),('d003','Human Resources'),('d001
','Marketing'),('d004','Production'),('d006','Quality Management'),('d008','Research'),('d007','Sales');
/*!40000 ALTER TABLE `departments` ENABLE KEYS */;
UNLOCK TABLES;
 :
 :

以下は、dept_empテーブルにデータをinsertするためのSQLです。

LOCK TABLES `dept_emp` WRITE;
/*!40000 ALTER TABLE `dept_emp` DISABLE KEYS */;
INSERT INTO `dept_emp` VALUES (10001,'d005','1986-06-26','9999-01-01'),(10002,'d007','1996-08-03','9999-01-01'),(10003,'d004','1995-12-0
3','9999-01-01'),(10004,'d004','1986-12-01','9999-01-01'),(10005,'d003','1989-09-12','9999-01-01'),(10006,'d005','1990-08-05','9999-01-0
 :
 :

テーブルを指定して、MySQLをバックアップするには、DB名の後にテーブル名を指定

データベースまるごとではなく、対象のテーブルのみバックアップしたい場合は、データベース名の後ろにテーブル名を指定します。複数テーブルの指定が可能です。

% mysqldump -u root -pmy-secret-pw employees departments titles dept_emp > backup.sql

上記コマンドは、employeesデータベースのうち、departments、titles、dept_empテーブルをバックアップします。

mysqldumpのリストアは、mysqlコマンドにダンプファイルをリダイレクト

バックアップしたファイルを使ってリストア(リロード)するには、以下のコマンドを実行します。

% mysql -u root -pmy-secret-pw < backup.sql

異なるバージョン間でリストアを行う場合は、データベースのデフォルト値が異なっている場合があるため注意が必要です。

【関連記事】
MySQLバージョン サポート終了期間に注意して、GA版から選ぶ

mysqldumpのオプション

データベース構造のみ(スキーマ)のみバックアップするには、-tオプション

% mysqldump -no-data -u root -pmy-secret-pw employees > backup.sql

DDLのみバックアップされ、データ部(insert文など)は省略されます。

データのみバックアップするには、–no-dataオプション

% mysqldump --no-data -u root -pmy-secret-pw employees > backup.sql

-dオプションでも同様です。ネットでは、「-dはドロップしそうで怖いから使わない」という意見もありました。

データベースへのアクセスを止めずにバックアップするには、–single-transactionオプション

InnoDBテーブルの場合に限って、–single-transactionオプションを指定すると、アプリケーションを止めることなく整合性が取れた状態でダンプを行います。

% mysqldump --single-transaction -u root -pmy-secret-pw employees > backup.sql

条件に合致したデータのみバックアップするには–whereオプション

–whereの条件に合ったデータのみをバックアップします。

% mysqldump --where "emp_no < 50000" -u root -pmy-secret-pw employees employees > backup.sql

上記のコマンドは、employeesデータベースのemployees(社員)テーブルのうち、emp_no(社員番号)が50000以下のデータのみ抽出します。

テーブルを複数指定した場合は、すべてのテーブルに同一のwhere条件を適用します。

差分バックアップを実行するには、有償のmysqlbackupを使用

大容量データベースの場合、毎回フルバックアップを取得しているとディスク領域を圧迫するため、差分バックアップが望ましいケースが有ります。

差分バックアップを取得するには、mysqlbackupを使用します。

参考) mysqlbackup コマンドリファレンス

mysqlbackupは、MySQLのEnterprise サブスクリプションの一部として含まれています。

MyISAMテーブルのバックアップ用のmysqlhotcopyは、5.7で削除

バックアップ対象テーブルが、旧来のMyISAMテーブルの場合は、テーブルの元ファイルを直接コピーするmysqlhotcopyを利用することで高速にバックアップを行うことができました。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 4.6.10 mysqlhotcopy — データベースバックアッププログラム

しかし、MySQL5.7では削除されているため、mysqldumpを使うようにしましょう。

まとめ

ポテパンダの一言メモ
  • MySQLバックアップには、mysqldumpコマンドを使う
  • 特定テーブルのみのバックアップは、DB名の後ろにテーブル名を指定する
  • 差分バックアップは、有償サブスクリプションのmysqlbackupを使用

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか?

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。