MySQL 全てのデータベースのバックアップとリカバリー
MySQLで全てのデータベースのバックアップを作成する方法と、リカバリーを行う方法について、以下に示します。
※ここではMySQLのバージョン5.6を使用して、動作確認を行っています。
※CentOSへのMySQLのインストールについては、当サイトのCentOSのyumリポジトリにMySQLを追加し、最新バージョンをインストールのページ、CentOSにMySQLをRPMからインストールのページもご覧ください。
※特定のデータベースのバックアップとリカバリーについては、当サイトのMySQL 特定のデータベースのバックアップとリカバリーのページをご覧ください。
※特定のテーブルのバックアップとリカバリーについては、当サイトのMySQL 特定のテーブルのバックアップとリカバリーのページをご覧ください。
動作確認用のデータベース「db01」の作成
ここでは、データベースのバックアップとリカバリーの動作確認用に、以下のようなデータベース「db01」を使用します。
データベース「db01」を作成
データベース「db01」を以下のように作成します。
mysql>, CREATE DATABASE db01 CHARACTER SET utf8 COLLATE utf8_bin,以降のデータベース「db01」へのテーブルの作成、データの登録は、以下のように「db01」を選択した状態で行うものとします。
mysql>, USE db01,データベース「db01」にテーブル「tbl11」を作成
データベース「db01」にテーブル「tbl11」を以下のように作成します。
mysql>, CREATE TABLE tbl11 (id CHAR(5) NOT NULL, num11 INT, txt11 VARCHAR(50), date11 DATETIME, PRIMARY KEY (id)) ENGINE InnoDB,テーブル「tbl11」にデータを登録
テーブル「tbl11」に以下のようなデータを登録します。
mysql>, INSERT INTO tbl11 (id, num11, txt11, date11) VALUES ('00001', 1, '①あ〜あ001', '2015-01-02 03:04:05'),mysql>, INSERT INTO tbl11 (id, num11, txt11, date11) VALUES ('00002', 2, '②い〜い002', '2015-11-12 13:14:15'),データベース「db01」にテーブル「tbl12」を作成
データベース「db01」にテーブル「tbl12」を以下のように作成します。
mysql>, CREATE TABLE tbl12 (num12 INT UNSIGNED NOT NULL AUTO_INCREMENT, txt12 VARCHAR(30), date12 DATETIME, PRIMARY KEY (num12)) ENGINE InnoDB,テーブル「tbl12」にデータを登録
テーブル「tbl12」に以下のようなデータを登録します。
mysql>, INSERT INTO tbl12 (txt12, date12) VALUES ('aaa', '2015-01-01 00:00:00'),mysql>, INSERT INTO tbl12 (txt12, date12) VALUES ('bbb', '2015-01-01 01:00:00'),動作確認用のデータベース「db02」の作成
ここでは、データベースのバックアップとリカバリーの動作確認用に、以下のようなデータベース「db02」を使用します。
データベース「db02」を作成
データベース「db02」を以下のように作成します。
mysql>, CREATE DATABASE db02 CHARACTER SET utf8 COLLATE utf8_bin,以降のデータベース「db02」へのテーブルの作成、データの登録は、以下のように「db02」を選択した状態で行うものとします。
mysql>, USE db02,データベース「db02」にテーブル「tbl21」を作成
データベース「db02」にテーブル「tbl21」を以下のように作成します。
mysql>, CREATE TABLE tbl21 (id CHAR(3) NOT NULL, num21 INT, txt21 VARCHAR(40), date21 DATETIME, PRIMARY KEY (id)) ENGINE InnoDB,テーブル「tbl21」にデータを登録
テーブル「tbl21」に以下のようなデータを登録します。
mysql>, INSERT INTO tbl21 (id, num21, txt21, date21) VALUES ('101', 11, 'あああ', '2015-02-01 01:02:03'),mysql>, INSERT INTO tbl21 (id, num21, txt21, date21) VALUES ('102', 12, 'いいい', '2015-02-02 04:05:06'),データベース「db02」にテーブル「tbl22」を作成
データベース「db02」にテーブル「tbl22」を以下のように作成します。
mysql>, CREATE TABLE tbl22 (num22 INT UNSIGNED NOT NULL AUTO_INCREMENT, txt22 VARCHAR(30), date22 DATETIME, PRIMARY KEY (num22)) ENGINE InnoDB,テーブル「tbl22」にデータを登録
テーブル「tbl22」に以下のようなデータを登録します。
mysql>, INSERT INTO tbl22 (txt22, date22) VALUES ('AAA', '2015-02-01 01:00:00'),mysql>, INSERT INTO tbl22 (txt22, date22) VALUES ('BBB', '2015-02-01 02:00:00'),動作確認用のデータベース接続ユーザーの作成
ここでは、データベースのバックアップとリカバリーの動作確認用に、以下のようなデータベース接続ユーザーを使用します。
ユーザー「user01」を作成
ユーザー「user01」を以下のように作成します。
mysql>, GRANT ALL ON *.* TO 'user01'@'%' IDENTIFIED BY 'pass01' WITH GRANT OPTION,ユーザー「user02」を作成
ユーザー「user02」を以下のように作成します。
mysql>, GRANT ALL ON db02.* TO 'user02'@'localhost' IDENTIFIED BY 'pass02' WITH GRANT OPTION,全てのデータベースのバックアップを作成
全てのデータベースのバックアップを作成するには、以下のようなコマンドを実行します。
# mysqldump -u root -pxxxxxxxxxx --all-databases >, all.sql-
バックアップの作成には、「mysqldump」コマンドを使用します。
-
「-u」オプションに続けて、MySQLに接続するユーザーを指定します。
ここでは、rootユーザーを指定しています。
-
「-p」オプションに続けて、rootユーザーのパスワードを指定します。（-pとパスワードの間にスペースは空けません。）
ただし、この場合「Warning: Using a password on the command line interface can be insecure.」とパスワードをコマンドラインに指定することは、安全でないことを示す警告メッセージが表示されます。
ここで「-p」オプションにパスワードを指定しなかった場合は、「Enter password:」と表示され、パスワードの入力を求められるようになります。
-
「–all-databases」オプションを指定すると、全てのデータベースのバックアップが作成されます。
-
リダイレクトを使用して、バックアップの内容をファイルに出力するようにします。
ここでは、ファイル「all.sql」に出力するようにしています。
作成されたバックアップの内容は、以下のようになります。
-- MySQL dump 10.13 Distrib 5.6.25, for Linux (x86_64) -- -- Host: localhost Database: -- ------------------------------------------------------ -- Server version 5.6.25-log /*!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 */, /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */, /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */, /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */, -- -- Current Database: `db01` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db01` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */, USE `db01`, -- -- Table structure for table `tbl11` -- DROP TABLE IF EXISTS `tbl11`, /*!40101 SET @saved_cs_client = @@character_set_client */, /*!40101 SET character_set_client = utf8 */, CREATE TABLE `tbl11` ( `id` char(5) COLLATE utf8_bin NOT NULL, `num11` int(11) DEFAULT NULL, `txt11` varchar(50) COLLATE utf8_bin DEFAULT NULL, `date11` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin, /*!40101 SET character_set_client = @saved_cs_client */, -- -- Dumping data for table `tbl11` -- LOCK TABLES `tbl11` WRITE, /*!40000 ALTER TABLE `tbl11` DISABLE KEYS */, INSERT INTO `tbl11` VALUES ('00001',1,'①あ〜あ001','2015-01-02 03:04:05'),('00002',2,'②い〜い002','2015-11-12 13:14:15'), /*!40000 ALTER TABLE `tbl11` ENABLE KEYS */, UNLOCK TABLES, -- -- Table structure for table `tbl12` -- DROP TABLE IF EXISTS `tbl12`, /*!40101 SET @saved_cs_client = @@character_set_client */, /*!40101 SET character_set_client = utf8 */, CREATE TABLE `tbl12` ( `num12` int(10) unsigned NOT NULL AUTO_INCREMENT, `txt12` varchar(30) COLLATE utf8_bin DEFAULT NULL, `date12` datetime DEFAULT NULL, PRIMARY KEY (`num12`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin, /*!40101 SET character_set_client = @saved_cs_client */, -- -- Dumping data for table `tbl12` -- LOCK TABLES `tbl12` WRITE, /*!40000 ALTER TABLE `tbl12` DISABLE KEYS */, INSERT INTO `tbl12` VALUES (1,'aaa','2015-01-01 00:00:00'),(2,'bbb','2015-01-01 01:00:00'), /*!40000 ALTER TABLE `tbl12` ENABLE KEYS */, UNLOCK TABLES, -- -- Current Database: `db02` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db02` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */, USE `db02`, -- -- Table structure for table `tbl21` -- DROP TABLE IF EXISTS `tbl21`, /*!40101 SET @saved_cs_client = @@character_set_client */, /*!40101 SET character_set_client = utf8 */, CREATE TABLE `tbl21` ( `id` char(3) COLLATE utf8_bin NOT NULL, `num21` int(11) DEFAULT NULL, `txt21` varchar(40) COLLATE utf8_bin DEFAULT NULL, `date21` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin, /*!40101 SET character_set_client = @saved_cs_client */, -- -- Dumping data for table `tbl21` -- LOCK TABLES `tbl21` WRITE, /*!40000 ALTER TABLE `tbl21` DISABLE KEYS */, INSERT INTO `tbl21` VALUES ('101',11,'あああ','2015-02-01 01:02:03'),('102',12,'いいい','2015-02-02 04:05:06'), /*!40000 ALTER TABLE `tbl21` ENABLE KEYS */, UNLOCK TABLES, -- -- Table structure for table `tbl22` -- DROP TABLE IF EXISTS `tbl22`, /*!40101 SET @saved_cs_client = @@character_set_client */, /*!40101 SET character_set_client = utf8 */, CREATE TABLE `tbl22` ( `num22` int(10) unsigned NOT NULL AUTO_INCREMENT, `txt22` varchar(30) COLLATE utf8_bin DEFAULT NULL, `date22` datetime DEFAULT NULL, PRIMARY KEY (`num22`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin, /*!40101 SET character_set_client = @saved_cs_client */, -- -- Dumping data for table `tbl22` -- LOCK TABLES `tbl22` WRITE, /*!40000 ALTER TABLE `tbl22` DISABLE KEYS */, INSERT INTO `tbl22` VALUES (1,'AAA','2015-02-01 01:00:00'),(2,'BBB','2015-02-01 02:00:00'), /*!40000 ALTER TABLE `tbl22` ENABLE KEYS */, UNLOCK TABLES, -- -- Current Database: `mysql` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */, USE `mysql`, -- -- Table structure for table `columns_priv` -- ：（略） -- -- Table structure for table `user` -- DROP TABLE IF EXISTS `user`, /*!40101 SET @saved_cs_client = @@character_set_client */, /*!40101 SET character_set_client = utf8 */, CREATE TABLE `user` ( `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '', `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '', `ssl_cipher` blob NOT NULL, `x509_issuer` blob NOT NULL, `x509_subject` blob NOT NULL, `max_questions` int(11) unsigned NOT NULL DEFAULT '0', `max_updates` int(11) unsigned NOT NULL DEFAULT '0', `max_connections` int(11) unsigned NOT NULL DEFAULT '0', `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0', `plugin` char(64) COLLATE utf8_bin DEFAULT 'mysql_native_password', `authentication_string` text COLLATE utf8_bin, `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', PRIMARY KEY (`Host`,`User`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges', /*!40101 SET character_set_client = @saved_cs_client */, -- -- Dumping data for table `user` -- LOCK TABLES `user` WRITE, /*!40000 ALTER TABLE `user` DISABLE KEYS */, INSERT INTO `user` VALUES ('localhost','root','*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','','N'),('localhost.localdomain','root','*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','','N'),('127.0.0.1','root','*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','','N'),('::1','root','*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','','N'),('%','user01','*C622305CD165FFAA9E89A3A6B60BDDCC5A5FD729','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','','N'),('localhost','user02','*F353D0B1ED51F5B73571A618A1765A3E0B7AE4D3','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'mysql_native_password','','N'), /*!40000 ALTER TABLE `user` ENABLE KEYS */, UNLOCK TABLES, ：（略） -- -- Table structure for table `slow_log` -- /*!40101 SET @saved_cs_client = @@character_set_client */, /*!40101 SET character_set_client = utf8 */, CREATE TABLE IF NOT EXISTS `slow_log` ( `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `user_host` mediumtext NOT NULL, `query_time` time NOT NULL, `lock_time` time NOT NULL, `rows_sent` int(11) NOT NULL, `rows_examined` int(11) NOT NULL, `db` varchar(512) NOT NULL, `last_insert_id` int(11) NOT NULL, `insert_id` int(11) NOT NULL, `server_id` int(10) unsigned NOT NULL, `sql_text` mediumtext NOT NULL, `thread_id` bigint(21) unsigned NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log', /*!40101 SET character_set_client = @saved_cs_client */, /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */, /*!40101 SET SQL_MODE=@OLD_SQL_MODE */, /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */, /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */, /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */, /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */, /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */, /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */, -- Dump completed on 2015-11-14 17:00:01-
全てのデータベースをバックアップ対象に指定した場合、新しく作成したデータベース「db01」、「db02」と、MySQLにデフォルトで用意されているデータベース「mysql」のバックアップが作成されます。
-
MySQLにデフォルトで用意されているデータベースには、「mysql」の他に「information_schema」、「performance_schema」がありますが、これらはバックアップには含まれません。
-
バックアップの内容は、データベースの作成、USE文で使用するデータベースの指定、テーブルの作成、データの登録といった流れを各データベースに対して行っていくようになっています。
全てのデータベースのバックアップからのリカバリー
上記で取得した全てのデータベースのバックアップ「all.sql」からリカバリーを行うには、以下のようなコマンドを実行します。
# mysql -u root -pxxxxxxxxxx <, all.sql-
バックアップからのリカバリーには、「mysql」コマンドを使用します。
-
「-u」オプションに続けて、MySQLに接続するユーザーを指定します。
ここでは、rootユーザーを指定しています。
-
「-p」オプションに続けて、rootユーザーのパスワードを指定します。（-pとパスワードの間にスペースは空けません。）
ただし、この場合「Warning: Using a password on the command line interface can be insecure.」とパスワードをコマンドラインに指定することは、安全でないことを示す警告メッセージが表示されます。
ここで「-p」オプションにパスワードを指定しなかった場合は、「Enter password:」と表示され、パスワードの入力を求められるようになります。
-
リダイレクトを使用して、バックアップの内容をファイルから入力するようにします。
ここでは、ファイル「all.sql」から入力するようにしています。
全てのデータベースのバックアップには、データベース「mysql」のテーブル「user」も含まれ、rootユーザーのパスワードもバックアップで指定されているものに変更されるのですが、MySQLのサービスを再起動するまでは、mysqlコマンドでrootユーザーを使用して接続する際に指定するパスワードは、リカバリー前のものになります。
MySQL 全てのデータベースのバックアップとリカバリーについては、以上です。
SQLのテクニックに関するおすすめの書籍
当サイトの書籍紹介ページで、SQLのテクニックに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。
SQLのパフォーマンスに関するおすすめの書籍
当サイトの書籍紹介ページで、SQLのパフォーマンスに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。
DB設計、SQLに関するおすすめの書籍
当サイトの書籍紹介ページで、DB設計、SQLに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。