MySQL备份与恢复(二)
MySQL备份与恢复(二)
备份与恢复方法示例
前天的文章中简单写了备份与恢复的方法，今天我们主要来看看不同的备份方法备份出来的文件结果，以及它们的恢复方法。
首先我们来看mysqldump备份出来的文件的内容，现将表t进行备份：
root@localhost:3306 [test]>select * from t;
+----+-----+
| id | age |
+----+-----+
| 1 | 5 |
| 2 | 4 |
| 3 | 3 |
| 4 | 2 |
| 5 | 1 |
+----+-----+
5 rows in set (0.08 sec)
root@localhost:3306 [test]>exit
Bye
C:\Users\yeyz>mysqldump --single-transaction -uroot -p test t > dump.sql
Enter password: ********
然后打开备份文件，我们可以看到备份文件中的内容如下：
-- MySQL dump 10.13 Distrib 5.7.24, for Win32 (AMD64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.7.24-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 */;
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=154;
--
-- Table structure for table `t`
--
DROP TABLE IF EXISTS `t`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`age` int(11) NOT NULL,
KEY `idx_t` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t`
--
LOCK TABLES `t` WRITE;
/*!40000 ALTER TABLE `t` DISABLE KEYS */;
INSERT INTO `t` VALUES (1,5),(2,4),(3,3),(4,2),(5,1);
/*!40000 ALTER TABLE `t` 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 2019-01-20 19:24:07
可以看到第22行是当前数据库的二进制日志位置，这通常用于在从库同步主库的数据的时候，第31-35行是表的定义，38行是表的数据，而且只备份了数据库test中的t这一个表。
当我们想恢复这个备份文件的时候，我们可以通过mysql -u<user> -p dbname < dump.sql的命令来进行恢复，示例如下：
root@localhost:3306 [test]>show tables;
+----------------+
| Tables_in_test |
+----------------+
| t |
| v_t |
| z |
| zz |
+----------------+
4 rows in set (0.00 sec)
root@localhost:3306 [test]>drop table t;
Query OK, 0 rows affected (0.77 sec)
root@localhost:3306 [test]>exit
Bye
C:\Users\yeyz>mysql -uroot -p test < dump.sql
Enter password: ********
C:\Users\yeyz>mysql -uroot -p
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.24-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@localhost:3306 [test]>use test
Database changed
root@localhost:3306 [test]>show tables;
+----------------+
| Tables_in_test |
+----------------+
| t |
| v_t |
| z |
| zz |
+----------------+
4 rows in set (0.00 sec)
root@localhost:3306 [test]>select * from t;
+----+-----+
| id | age |
+----+-----+
| 1 | 5 |
| 2 | 4 |
| 3 | 3 |
| 4 | 2 |
| 5 | 1 |
+----+-----+
5 rows in set (0.00 sec)
再来看看select into outfile导出文件的试验过程，话不多说，备份的代码如下：
root@localhost:3306 [test]>select * from t;
+----+-----+
| id | age |
+----+-----+
| 1 | 5 |
| 2 | 4 |
| 3 | 3 |
| 4 | 2 |
| 5 | 1 |
+----+-----+
5 rows in set (0.00 sec)
root@localhost:3306 [(none)]>use test
Database changed
root@localhost:3306 [test]>select * from t into outfile 'back.sql' ;
Query OK, 5 rows affected (0.01 sec)
#查看文件内容
1 5
2 4
3 3
4 2
5 1
我们可以看到，这种备份方法相当于只备份了表里面的数据，而没有备份表的结构。如果我们觉得这个备份结果的格式不是我们想要的，还可以改成其他的风格，如下：
root@localhost:3306 [test]>select * from t into outfile 'back3.sql' fields termi
nated by ',' lines starting by '本行记录内容:' terminated by '\r\n' ;
本行记录内容:1,5
本行记录内容:2,4
本行记录内容:3,3
本行记录内容:4,2
本行记录内容:5,1
代码中的fields terminated表示列之间的分隔符，lines starting表示每一行开始的符号，lines terminated表示每一行结束的符号，windows中'\r\n'表示换行。
这种备份方式恢复的时候需要采用load data infile的方式，下面给出例子：
root@localhost:3306 [test]>show tables;
+----------------+
| Tables_in_test |
+----------------+
| t |
| v_t |
| z |
| zz |
+----------------+
4 rows in set (0.00 sec)
root@localhost:3306 [test]>drop table t;
Query OK, 0 rows affected (0.38 sec)
root@localhost:3306 [test]>load data into table t ignore 1 lines infile 'data.sql';
root@localhost:3306 [test]>load data into table t ignore 1 lines infile 'e:/mysq
l-5.7.24-win32/data/test/back.sql';
root@localhost:3306 [test]>load data infile 'e:/mysql-5.7.24-win32/data/test/ba
ck.sql' into table t;
ERROR 1146 (42S02): Table 'test.t' doesn't exist
root@localhost:3306 [test]>create table t (id int,age int);
Query OK, 0 rows affected (0.63 sec)
root@localhost:3306 [test]>load data infile 'e:/mysql-5.7.24-win32/data/test/back.sql' into table t;
Query OK, 5 rows affected (0.21 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
root@localhost:3306 [test]>select * from t;
+------+------+
| id | age |
+------+------+
| 1 | 5 |
| 2 | 4 |
| 3 | 3 |
| 4 | 2 |
| 5 | 1 |
+------+------+
5 rows in set (0.00 sec)
root@localhost:3306 [test]>
上面的例子有个小问题需要注意一下，就是我们在导入一个表数据的时候，这个表不能删除，表结构必须存在，不然是没办法导入的，会提示table doesn't exist的错误。
再来看看mysqlimport方法，从本质上来说，它是load data infile的命令接口，而且大多数的选项都和load data infile语法相同，它的语法格式如下：
mysqlimport dbname text1 text2
与load data infile不同的是，mysqlimport可以用来导入多张表，并且通过--user-thread参数并发地导入不同的文件，这里的并发值得是并发导入多个文件，而不是指mysqlimport可以并发的导入一个文件，这是有明显区别的。如果有兴趣，可以试一下在load data的同时，连接mysql然后在命令行中show full processlist;这个时候，就可以看到mysqlimport实际上是同时执行多句load data infile的命令。
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2019-01-20，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。