MySQL数据库的备份与恢复(3)——恢复数据

    技术2022-07-13  95

    MySQL数据库的备份与恢复(3)——恢复数据

    使用MySQL自带的备份工具mysqldump备份数据,然后进行数据恢复。

    一、以SQL格式备份数据并且恢复

    1、备份所有数据库

    [root@Mysql11 ~]# mysqldump -uroot -p123456 --all-databases > /tmp/all.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.

    2、备份指定的多个数据库

    [root@Mysql11 ~]# mysqldump -uroot -p123456 --databases wgx hist > /tmp/all.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.

    恢复wgx和hist数据库

    方法一:登录mysql进行恢复

    mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hist | | mysql | | performance_schema | | sys | | wgx | +--------------------+ 6 rows in set (0.00 sec) mysql> drop database wgx; Query OK, 2 rows affected (0.03 sec) mysql> drop database hist; Query OK, 2 rows affected (0.03 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> source /tmp/all.sql; Query OK, 0 rows affected (0.00 sec) ..... mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hist | | mysql | | performance_schema | | sys | | wgx | +--------------------+ 6 rows in set (0.00 sec)

    方法二:不登录mysql 进行恢复

    [root@Mysql11 ~]# mysql -uroot -p123456 -e "drop database wgx;drop database hist;" mysql: [Warning] Using a password on the command line interface can be insecure. [root@Mysql11 ~]# mysql -uroot -p123456 -e "show databases;" mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ [root@Mysql11 ~]# mysql -uroot -p123456 < /tmp/all.sql mysql: [Warning] Using a password on the command line interface can be insecure. [root@Mysql11 ~]# mysql -uroot -p123456 -e "show databases;" mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | hist | | mysql | | performance_schema | | sys | | wgx | +--------------------+

    3、备份指定的一个数据库

    (1)指定–databases参数

    [root@Mysql11 ~]# mysqldump -uroot -p123456 --databases wgx > /tmp/wgx.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.

    此命令生成的备份文件包含create database和use命令,如下:

    [root@Mysql11 ~]# cat /tmp/wgx.sql -- -- Current Database: `wgx` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `wgx` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `wgx`;

    (2)也可以不指定–databases参数

    [root@Mysql11 ~]# mysqldump -uroot -p123456 wgx > /tmp/wgx.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.

    当对一个数据库进行备份时,可以省略- -databases参数,但是省略该参数后生成的备份文件中没有CREATE DATABASE 和USE语句,那么恢复备份文件时,必须指定一个默认的数据库名。

    此时可以使用一个和原始数据库名称不同的数据库名。

    [root@Mysql11 ~]# cat /tmp/wgx.sql -- MySQL dump 10.13 Distrib 5.7.27, for Linux (x86_64) -- -- Host: localhost Database: wgx -- ------------------------------------------------------ -- Server version 5.7.27 /*!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 `department` -- DROP TABLE IF EXISTS `department`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `department` ( `dept_id` int(11) NOT NULL AUTO_INCREMENT, `name` char(20) DEFAULT NULL, PRIMARY KEY (`dept_id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */;

    4、恢复某个数据库中的所有数据

    方法一:如果备份数据库时有–databases参数

    [root@Mysql11 ~]# mysql -uroot -p123456 -e "drop database wgx;" mysql: [Warning] Using a password on the command line interface can be insecure. [root@Mysql11 ~]# mysql -uroot -p123456 -e "show databases;" mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | hist | | mysql | | performance_schema | | sys | +--------------------+ [root@Mysql11 ~]# mysql -uroot -p123456 < /tmp/wgx.sql mysql: [Warning] Using a password on the command line interface can be insecure. [root@Mysql11 ~]# mysql -uroot -p123456 -e "show databases;" mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | hist | | mysql | | performance_schema | | sys | | wgx | +--------------------+

    方法二:如果备份数据库时没有–databases参数

    [root@Mysql11 ~]# mysql -uroot -p123456 < /tmp/wgx.sql mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1046 (3D000) at line 22: No database selected

    注意:如果没有选择数据库,会出现错误:ERROR 1046 (3D000) at line 22: No database selected

    因此,必须先使用use命令选择要恢复的数据库:

    [root@Mysql11 ~]# mysql -uroot -p123456 -e "drop database wgx;create database wanggx;use wanggx;source /tmp/wgx.sql;" mysql: [Warning] Using a password on the command line interface can be insecure. [root@Mysql11 ~]# mysql -uroot -p123456 -e "show databases;" mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | hist | | mysql | | performance_schema | | sys | | wanggx |

    可以看到,恢复数据的同时修改了数据库名称。

    5、恢复某个数据库中的若干张表

    方法一:指定–databases和–tables参数

    [root@Mysql11 ~]# mysqldump -uroot -p123456 --databases hist --tables dept stu > hist.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@Mysql11 ~]# cat hist.sql ............ -- -- Table structure for table `dept` -- DROP TABLE IF EXISTS `dept`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `dept` ( `dept_id` int(11) NOT NULL AUTO_INCREMENT, `name` char(20) DEFAULT NULL, PRIMARY KEY (`dept_id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `dept` -- LOCK TABLES `dept` WRITE; /*!40000 ALTER TABLE `dept` DISABLE KEYS */; INSERT INTO `dept` VALUES (1,'guanli'),(2,'jingji'),(3,'jidian'),(4,'jisuanji'); /*!40000 ALTER TABLE `dept` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `stu` -- DROP TABLE IF EXISTS `stu`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `stu` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(20) DEFAULT NULL, `age` int(11) DEFAULT NULL, `address` varchar(20) DEFAULT NULL, `phone` char(11) DEFAULT NULL, `dept_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `stu` -- LOCK TABLES `stu` WRITE; /*!40000 ALTER TABLE `stu` DISABLE KEYS */; INSERT INTO `stu` VALUES (1,'zhangsan',20,'Xinxiang','15578941258',1),(2,'tom',20,'Xinxiang','13778942222',1),(3,'jack',20,'Zhengzhou','13675871454',1),(4,'john',21,'Zhengzhou','13937681111',2),(5,'mark',22,'Aanyang','13055882233',2); /*!40000 ALTER TABLE `stu` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; ........... -- Dump completed on 2020-07-02 10:07:03

    方法二:不指定–databases参数

    如果不指定–databases参数,则第一个名称默认为数据库名,后面的其他名称为表名,格式如下:

    mysqldump -uuser -p db_name t_name1 t_name2 > file_name

    例如:

    [root@Mysql11 ~]# mysqldump -uroot -p123456 hist dept stu > /tmp/hist-bak.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@Mysql11 ~]# cat /tmp/hist-bak.sql ............... -- -- Table structure for table `dept` -- DROP TABLE IF EXISTS `dept`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `dept` ( `dept_id` int(11) NOT NULL AUTO_INCREMENT, `name` char(20) DEFAULT NULL, PRIMARY KEY (`dept_id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `dept` -- LOCK TABLES `dept` WRITE; /*!40000 ALTER TABLE `dept` DISABLE KEYS */; INSERT INTO `dept` VALUES (1,'guanli'),(2,'jingji'),(3,'jidian'),(4,'jisuanji'); /*!40000 ALTER TABLE `dept` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `stu` -- DROP TABLE IF EXISTS `stu`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `stu` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(20) DEFAULT NULL, `age` int(11) DEFAULT NULL, `address` varchar(20) DEFAULT NULL, `phone` char(11) DEFAULT NULL, `dept_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `stu` -- LOCK TABLES `stu` WRITE; /*!40000 ALTER TABLE `stu` DISABLE KEYS */; INSERT INTO `stu` VALUES (1,'zhangsan',20,'Xinxiang','15578941258',1),(2,'tom',20,'Xinxiang','13778942222',1),(3,'jack',20,'Zhengzhou','13675871454',1),(4,'john',21,'Zhengzhou','13937681111',2),(5,'mark',22,'Aanyang','13055882233',2); /*!40000 ALTER TABLE `stu` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; ................. -- Dump completed on 2020-07-02 10:10:24

    二、备份为带分隔符的文本文件格式并进行恢复

    1.使用参数【–tab=目录】备份

    [root@Mysql11 tmp]# mysqldump -uroot -p123456 --tab=/tmp/ hist mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@Mysql11 tmp]# ls dept.sql dept.txt stu.sql stu.txt

    调用mysqldump时,如果带有- -tab=dir_name选项去备份数据库,则dir_name表示输出文件的目录,在这个目录中,要备份的每个表将会产生两个文件,一个是sql文件,包含CREATE TABLE语句;另一个是txt文件,文件中的每一行为数据表中的一条记录,列值与列值之间以‘tab’分隔。

    dept.sql文件的内容如下:

    [root@Mysql11 tmp]# cat dept.sql -- MySQL dump 10.13 Distrib 5.7.27, for Linux (x86_64) -- -- Host: localhost Database: hist -- ------------------------------------------------------ -- Server version 5.7.27 /*!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' */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `dept` -- DROP TABLE IF EXISTS `dept`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `dept` ( `dept_id` int(11) NOT NULL AUTO_INCREMENT, `name` char(20) DEFAULT NULL, PRIMARY KEY (`dept_id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!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 2020-07-02 10:23:16 [root@Mysql11 tmp]#

    dept.txt文件的内容如下:

    [root@Mysql11 tmp]# cat dept.txt 1 guanli 2 jingji 3 jidian 4 jisuanji

    2、恢复数据

    恢复数据时,首先用mysql命令处理.sql文件去还原表结构,然后处理.txt文件去载入记录。

    (1)还原表结构

    [root@Mysql11 tmp]# mysql -uroot -p123456 -e "use hist;source /tmp/dept.sql;" mysql: [Warning] Using a password on the command line interface can be insecure.

    也可使用:mysql –u 用户名 –p 数据库名 < 表名.sql

    [root@Mysql11 tmp]# mysql -uroot -p123456 hist < dept.sql mysql: [Warning] Using a password on the command line interface can be insecure.

    (2)恢复数据

    [root@Mysql11 tmp]# mysqlimport -uroot -p123456 hist /tmp/dept.txt mysqlimport: [Warning] Using a password on the command line interface can be insecure. hist.dept: Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

    使用LOAD DATA INFILE命令恢复记录:

    [root@Mysql11 tmp]# mysql -uroot -p123456 -e "use hist;load data infile '/tmp/dept.txt' into table dept;" mysql: [Warning] Using a password on the command line interface can be insecure.

    三、使用select…into outfile命令导出数据并恢复数据

    1、导出数据

    [root@Mysql11 tmp]# mysql -uroot -p123456 hist -e "select * from stu into outfile '/tmp/stu.txt' fields terminated by ',';" mysql: [Warning] Using a password on the command line interface can be insecure. [root@Mysql11 tmp]# cat /tmp/stu.txt 1,zhangsan,20,Xinxiang,15578941258,1 2,tom,20,Xinxiang,13778942222,1 3,jack,20,Zhengzhou,13675871454,1 4,john,21,Zhengzhou,13937681111,2 5,mark,22,Aanyang,13055882233,2

    2、恢复数据

    [root@Mysql11 tmp]# mysql -uroot -p123456 hist -e "load data infile '/tmp/stu.txt' into table stu fields terminated by ',';" mysql: [Warning] Using a password on the command line interface can be insecure. [root@Mysql11 tmp]# mysql -uroot -p123456 hist -e "select * from stu;" mysql: [Warning] Using a password on the command line interface can be insecure. +----+----------+------+-----------+-------------+---------+ | id | name | age | address | phone | dept_id | +----+----------+------+-----------+-------------+---------+ | 1 | zhangsan | 20 | Xinxiang | 15578941258 | 1 | | 2 | tom | 20 | Xinxiang | 13778942222 | 1 | | 3 | jack | 20 | Zhengzhou | 13675871454 | 1 | | 4 | john | 21 | Zhengzhou | 13937681111 | 2 | | 5 | mark | 22 | Aanyang | 13055882233 | 2 | +----+----------+------+-----------+-------------+---------+
    Processed: 0.035, SQL: 9