每日MySQL之004：备份与恢复

MySQL备份与恢复

备份

与mysql一样，mysqldump也是一个数据库client工具，它可以用来备份数据库, mysqldump产生的是一系列的SQL语句，可以直接执行来创建数据库和表，除了备份数据库之外，也可以方便地将数据导入到另一个mysql server，常见语法如下:

shell> mysqldump [options] db_name [tbl_name ...]
shell> mysqldump [options] --databases db_name ...
shell> mysqldump [options] --all-databases

详细的语法不作介绍，有兴趣的可以查询reference manual:
https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

在备份之前，先在sample数据库里创建另一张表：
mysql> use sample;
mysql> create table t1(id int, name char(20));
mysql> insert into t1 values(1,'aa'),(2,'bb');

示例1：备份sample数据库下所有表的定义和数据：
db2a:~ # mysqldump -h localhost -u root -pqingsong sample > sample.ddl

mysqldump: [Warning] Using a password on the command line interface can be insecure.

sample.ddl如下

db2a:~ # more sample.ddl 
-- MySQL dump 10.13  Distrib 5.7.19, for Linux (x86_64)
--
-- Host: localhost    Database: sample
-- ------------------------------------------------------
-- Server version       5.7.19

/*!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 */;

--
-- Table structure for table `pet`
--

DROP TABLE IF EXISTS `pet`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `pet` (
  `name` varchar(20) DEFAULT NULL,
  `owner` varchar(20) DEFAULT NULL,
  `species` varchar(20) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `death` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `pet`
--

LOCK TABLES `pet` WRITE;
/*!40000 ALTER TABLE `pet` DISABLE KEYS */;
INSERT INTO `pet` VALUES ('Fluffy','Harold','cat','f','1993-02-04',NULL),('Claws','Gwen','cat','m','1994-03-17',NULL),('B
uffy','Harold','dog','f','1989-05-13','1996-07-31'),('Fang','Benny','dog','m','1990-08-27',NULL),('Bowser','Diane','dog',
'm','1989-08-31','1995-07-29'),('Chirpy','Gwen','bird','f','1998-09-11',NULL),('Whistler','Diane','bird',NULL,'1997-12-09
',NULL),('Puffball','Diane','hamster','f','1999-03-30',NULL);
/*!40000 ALTER TABLE `pet` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `t1`
--

DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t1`
--

LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1,'aa'),(2,'bb');
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
/*!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 2017-07-24 17:10:49

可以看到sample.ddl里都是可以直接执行的SQL语句：
DROP TABLE IF EXISTS `pet`;
CREATE TABLE `pet`;
INSERT INTO `pet` VALUES (...);
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1`;
INSERT INTO `t1` VALUES (...);
但并不包含drop/create database语句

示例2：备份sample下t1定义和数据：
db2a:~ # mysqldump -h localhost -u root -pqingsong sample t1 > sample.t1.ddl
mysqldump: [Warning] Using a password on the command line interface can be insecure.

db2a:~ # more sample.t1.ddl 
...
DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t1`
--

LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1,'aa'),(2,'bb');
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

...

-- Dump completed on 2017-07-24 17:16:25

示例3：备份sample下t1,pet两个表的定义和数据：
db2a:~ # mysqldump -h localhost -u root -pqingsong sample t1 pet > sample.t1.pet.ddl

示例4：备份所有数据库：
db2a:~ # mysqldump -h localhost -u root -pqingsong --all-databases > alldb.sql

示例5：将一个mysql server上的数据库的数据复制到另一个mysql server上可以使用类似如下语法：
shell> mysqldump --opt db_name | mysql --host=remote_host db_name

恢复

假设不小心把sample里面的两张表删掉了：
db2a:~ # mysql -u root -pqingsong sample --execute="drop table pet"
db2a:~ # mysql -u root -pqingsong sample --execute="drop table t1"

可以使用下面的命令恢复：
db2a:~ # mysql -u root -pqingsong sample < sample.ddl 
mysql: [Warning] Using a password on the command line interface can be insecure.

可以看到，已经恢复成功：
db2a:~ # mysql -u root -pqingsong sample --execute="show tables"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+
| Tables_in_sample |
+------------------+
| pet                 |
| t1                   |
+------------------+
db2a:~ # mysql -u root -pqingsong sample --execute="select * from t1"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+
| id   | name |
+------+------+
|    1 | aa   |
|    2 | bb   |
+------+------+
db2a:~ # mysql -u root -pqingsong sample --execute="select * from pet"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | 1996-07-31 |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Diane  | bird    | NULL | 1997-12-09 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+