专栏首页DBA随笔MySQL备份与恢复(二)

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的命令。

本文分享自微信公众号 - DBA随笔(gh_acc2bbc0d447)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-01-20

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 「大型网站架构设计」—— 前言

    根据大型网站的特点,该架构能够解决大多数的技术问题。下面我们将这个架构图进行简单的分解。

    tomas家的小拨浪鼓
  • 推荐系统数据集大列表

    今天给大家介绍一个github仓库,收集了非常多的推荐系统的数据集,非常的全面,非常的实用,做推荐系统相关的同学可以收藏一下。

    石晓文
  • mysql 类型自动化转换问题

    询问了一个朋友,他给了一个方向,mysql会自动转换类型,当你select语句的条件和数据中的type不一致的时候; 一拍大腿,是呀,之前有碰到过类似问题,如下...

    千往
  • 常用SQL语句分享

    日常工作或学习过程中,我们可能会经常用到某些SQL,建议大家多多整理记录下这些常用的SQL,这样后续用到会方便很多。笔者在工作及学习过程中也整理了下个人常用的S...

    MySQL技术
  • Mybatis的批处理

    本文主要测试Mybatis在mysql数据库下的批量插入、批量删除、批量更新。

    用户6055494
  • 数据库|如何解决数据库插入中文字体时显示问号

    我们在进行数据库的增删改查的操作时,当我们插入英文或者数字等字符串的时候能够正常显示,但的当我们插入中文字体的时候我们就会在我们的表中发现显示的是一连串的问号,...

    算法与编程之美
  • 高频面试题:什么是B树?为啥文件索引要用B树而不用二叉查找树?

    小秋:树形结构例如想 B 树,B+ 树,二叉查找树都是有序的,所以查询效率很高,可以再 O(logn) 的时间复杂度查找到目标数据。

    乔戈里
  • MyCAT让人诟病的配置文件,说说破局的思路

    最近在做MySQL分布式环境的统筹管理,目前碰到的痛点是对于集群的配置管理目前是松散的,几套环境还能忍受,如果环境多了之后还是很容易凌乱,所以我们需要一套机制...

    jeanron100
  • 用 Python 爬取网易严选妹子内衣信息,探究妹纸们的偏好

    在商品页面,打开 Chrome 的控制台,切换至 Network 页,再把商品页面切换到评价标签下,选择一个评论文字,如“薄款、穿着舒适、满意”,在 Netwo...

    Java识堂
  • Katalon Studio处理数据库

    在日常的测试工作中需要经常要用到:数据库的处理,执行SQL语句。 Katalon Studio可以通过定制关键字的方式连接到数据库,并执行SQL语句。 基本实现...

    Altumn

扫码关注云+社区

领取腾讯云代金券