数据备份和还原
数据存储引擎:
innodb:只有表结构,数据全部存储在一个文件中
myisam:表、数据、和索引全部单独分开存储
-- 创建 innodb 表:
create table m_innodb(
id int
)charset utf8 engine=innodb;
-- 创建 myisam表:
create table m_myisam(
id int
)charset utf8 engine=myisam;
数据表备份:不通过sql语句来备份,直接复制数据库文件复制对应的表结构以及数据文件 适用于myisam
1.新建database : create database m_engine;
2.将数据文件拷贝到 m_engine 文件夹下
单表数据备份:只能备份数据不能备份表结构
select * into outfile 本机文件夹 from 数据源
字段处理方式:fields: enclosed by 字段使用什么内容包裹,默认是空字符串
terminated by 字段以什么结束 默认是‘\r’
escaped by 特殊字符用什么方式处理 默认是“\\”
行处理方式 lines:
starting by 每行以什么开始,默认是空字符串
terminaled by每行以什么结束默认是’\r\n‘
select * into outfile 'E:/mysql-8.0.13/Data/backup/m_copy1.txt'
FIELDS
TERMINATED BY '|' -- 用|间隔字段
ENCLOSED BY '"' -- 用“包裹字段
LINES
STARTING BY 'START:' -- 每行以START: 开头
from m_copy;
数据恢复
-- 删除数据
delete from m_copy;
-- 查询表中数据为空
select * from m_copy ;
Empty set (0.00 sec)
-- 数据还原
--load data 文件路径 into table 表名(字段列表) fields 字段处理方式 lines 行处理方式
load data infile 'E:/mysql-8.0.13/Data/backup/m_copy1.txt' into table m_copy
FIELDS TERMINATED BY '|' ENCLOSED BY '"'
LINES STARTING BY 'START:';
-- 还原后结果
select * from m_copy;
+------+-------+-------+-------+
| id | name | sex | class |
+------+-------+-------+-------+
| 1 | name1 | women | 4 |
| 2 | name2 | man | 4 |
| 3 | name3 | women | 2 |
| 4 | name4 | man | 2 |
| 5 | name5 | women | 1 |
| 6 | name6 | women | 1 |
| 7 | name7 | women | 3 |
| 8 | name8 | women | 3 |
| 9 | name1 | man | 4 |
| 10 | name2 | man | 4 |
| 11 | name3 | women | 2 |
| 12 | name4 | man | 2 |
| 13 | name5 | man | 1 |
| 14 | name6 | man | 1 |
| 15 | name7 | man | 3 |
| 16 | name8 | women | 3 |
+------+-------+-------+-------+
SQL备份
备份的是sql语句:系统会对表结构以及数据进行处理,变成对应的sql语句,然后进行备份;(主要针对表结构)
-- mysqldump 连接服务器
mysqldump -hlocalhost -P3306 -uroot -p
-- 备份数据
-- mysqldump -hlocalhost -P3306 -uroot -p 数据库名字 [数据库表名字1] [数据表名字2] > 外部文件
mysqldump -hlocalhost -P3306 -uroot -p test m_copy > E:/mysql-8.0.13/Data/backup/m_copy1.sql
-- 备份后的m_copy.sql文件
-- MySQL dump 10.13 Distrib 8.0.13, for Win64 (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 8.0.13
/*!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 */;
SET NAMES utf8mb4 ;
/*!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 `m_copy`
--
DROP TABLE IF EXISTS `m_copy`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4 ;
CREATE TABLE `m_copy` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) NOT NULL,
`sex` enum('man','women') DEFAULT NULL,
`class` int(11) DEFAULT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `m_copy`
--
LOCK TABLES `m_copy` WRITE;
/*!40000 ALTER TABLE `m_copy` DISABLE KEYS */;
INSERT INTO `m_copy` VALUES (1,'name1','women',4),(2,'name2','man',4),(3,'name3','women',2),(4,'name4','man',2),(5,'name5','women',1),(6,'name6','women',1),(7,'name7','women',3),(8,'name8','women',3),(9,'name1','man',4),(10,'name2','man',4),(11,'name3','women',2),(12,'name4','man',2),(13,'name5','man',1),(14,'name6','man',1),(15,'name7','man',3),(16,'name8','women',3);
/*!40000 ALTER TABLE `m_copy` 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-06-07 0:38:49
整库备份
mysqldump -hlocalhost -P3306 -uroot -p test > E:/mysql-8.0.13/Data/backup/test.sql
还原数据
-- 还原数据
-- 方式1:mysql -hPup 数据表 < 本地路径
mysql -uroot -p test < E:/mysql-8.0.13/Data/backup/database_test.sql
-- 方式2 :切换到数据库 然后 source 本地路径
source E:/mysql-8.0.13/Data/backup/database_test.sql