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は、MySQLのEnterprise サブスクリプションの一部として含まれています。
MyISAMテーブルのバックアップ用のmysqlhotcopyは、5.7で削除
バックアップ対象テーブルが、旧来のMyISAMテーブルの場合は、テーブルの元ファイルを直接コピーするmysqlhotcopyを利用することで高速にバックアップを行うことができました。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 4.6.10 mysqlhotcopy — データベースバックアッププログラム
しかし、MySQL5.7では削除されているため、mysqldumpを使うようにしましょう。
まとめ
- MySQLバックアップには、mysqldumpコマンドを使う
- 特定テーブルのみのバックアップは、DB名の後ろにテーブル名を指定する
- 差分バックアップは、有償サブスクリプションのmysqlbackupを使用
