MySQL备份——（十）

最新推荐文章于 2022-08-28 16:24:19 发布

QZP51ZX

最新推荐文章于 2022-08-28 16:24:19 发布

阅读量336

点赞数

分类专栏： mysql 文章标签：数据库 mysql sql MySQL备份

本文链接：https://blog.csdn.net/QZP51ZX/article/details/104154457

版权

mysql 专栏收录该内容

17 篇文章 4 订阅

订阅专栏

MySQL备份——（十）

1.为什么要MySQL备份？

保证重要数据不丢失
数据转移

2.MySQL备份方式

方式一：

直接拷贝物理文件data文件夹

方式二：

在navicat可视化软件中手动导出或者转储sql文件

选择导出的数据库或者表，右键，选着导出向导或者转储sql文件
导出向导导出选择sql脚本导出，且只能是表中数据，不包含表结构，可以选择包含列的标题，导出的都是sql语句（数据包含在里面，只需要执行这段语句，就可以将数据插入表中）

-- 普通导出向导效果
INSERT INTO `student` VALUES (1000, '111111', '郭靖', 1, 1, '13500000001', '北京海淀区中关村大街1号', '1986-12-11 00:00:00', 'test1@bdqn.cn', '450323198612111234');
INSERT INTO `student` VALUES (1001, '123456', '李文才', 1, 2, '13500000002', '河南洛阳', '1981-12-31 00:00:00', 'test1@bdqn.cn', '450323198112311234');
INSERT INTO `student` VALUES (1002, '111111', '李斯文', 1, 1, '13500000003', '天津市和平区', '1986-11-30 00:00:00', 'test1@bdqn.cn', '450323198611301234');
INSERT INTO `student` VALUES (1003, '123456', '武松', 1, 3, '13500000004', '上海卢湾区', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612314234');
INSERT INTO `student` VALUES (1004, '123456', '张三', 1, 4, '13500000005', '北京市通州', '1989-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311244');
INSERT INTO `student` VALUES (1005, '123456', '张秋丽 ', 2, 1, '13500000006', '广西桂林市灵川', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311214');
INSERT INTO `student` VALUES (1006, '123456', '肖梅', 2, 4, '13500000007', '武汉加油', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311134');
INSERT INTO `student` VALUES (1007, '111111', '欧阳峻峰', 1, 1, '13500000008', '北京东城区', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311133');
INSERT INTO `student` VALUES (1008, '111111', '梅超风', 1, 1, '13500000009', '河南洛阳', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311221');
INSERT INTO `student` VALUES (1009, '123456', '刘毅', 1, 2, '13500000011', '安徽', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311231');
INSERT INTO `student` VALUES (1010, '111111', '大凡', 1, 1, '13500000012', '河南洛阳', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311044');
INSERT INTO `student` VALUES (1011, '111111', '奥丹斯', 1, 1, '13500000013', '北京海淀区中关村大街*号', '1984-12-31 00:00:00', 'test1@bdqn.cn', '450323198412311234');
INSERT INTO `student` VALUES (1012, '123456', '多伦', 2, 3, '13500000014', '广西南宁中央大街', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311334');
INSERT INTO `student` VALUES (1013, '123456', '李梅', 2, 1, '13500000015', '上海卢湾区', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311534');
INSERT INTO `student` VALUES (1014, '123456', '张得', 2, 4, '13500000016', '北京海淀区中关村大街*号', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311264');
INSERT INTO `student` VALUES (1015, '123456', '李东方', 1, 4, '13500000017', '广西桂林市灵川', '1976-12-31 00:00:00', 'test1@bdqn.cn', '450323197612311234');
INSERT INTO `student` VALUES (1016, '111111', '刘奋斗', 1, 1, '13500000018', '上海卢湾区', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311251');
INSERT INTO `student` VALUES (1017, '123456', '可可', 2, 3, '13500000019', '北京长安街1号', '1981-9-10 00:00:00', 'test1@bdqn.cn', '450323198109108311');
INSERT INTO `student` VALUES (10066, '', 'Tom', 1, 1, '13500000000', '', '0000-0-0 00:00:00', 'email@22.com', '33123123123123123');

-- 导出向导中包含列的标题效果
INSERT INTO `student` (`StudentNo`, `LoginPwd`, `StudentName`, `Sex`, `GradeId`, `Phone`, `Address`, `BornDate`, `Email`, `IdentityCard`) VALUES (1000, '111111', '郭靖', 1, 1, '13500000001', '北京海淀区中关村大街1号', '1986-12-11 00:00:00', 'test1@bdqn.cn', '450323198612111234');
INSERT INTO `student` (`StudentNo`, `LoginPwd`, `StudentName`, `Sex`, `GradeId`, `Phone`, `Address`, `BornDate`, `Email`, `IdentityCard`) VALUES (1001, '123456', '李文才', 1, 2, '13500000002', '河南洛阳', '1981-12-31 00:00:00', 'test1@bdqn.cn', '450323198112311234');
INSERT INTO `student` (`StudentNo`, `LoginPwd`, `StudentName`, `Sex`, `GradeId`, `Phone`, `Address`, `BornDate`, `Email`, `IdentityCard`) VALUES (1002, '111111', '李斯文', 1, 1, '13500000003', '天津市和平区', '1986-11-30 00:00:00', 'test1@bdqn.cn', '450323198611301234');
INSERT INTO `student` (`StudentNo`, `LoginPwd`, `StudentName`, `Sex`, `GradeId`, `Phone`, `Address`, `BornDate`, `Email`, `IdentityCard`) VALUES (1003, '123456', '武松', 1, 3, '13500000004', '上海卢湾区', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612314234');
INSERT INTO `student` (`StudentNo`, `LoginPwd`, `StudentName`, `Sex`, `GradeId`, `Phone`, `Address`, `BornDate`, `Email`, `IdentityCard`) VALUES (1004, '123456', '张三', 1, 4, '13500000005', '北京市通州', '1989-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311244');
INSERT INTO `student` (`StudentNo`, `LoginPwd`, `StudentName`, `Sex`, `GradeId`, `Phone`, `Address`, `BornDate`, `Email`, `IdentityCard`) VALUES (1005, '123456', '张秋丽 ', 2, 1, '13500000006', '广西桂林市灵川', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311214');
INSERT INTO `student` (`StudentNo`, `LoginPwd`, `StudentName`, `Sex`, `GradeId`, `Phone`, `Address`, `BornDate`, `Email`, `IdentityCard`) VALUES (1006, '123456', '肖梅', 2, 4, '13500000007', '武汉加油', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311134');
INSERT INTO `student` (`StudentNo`, `LoginPwd`, `StudentName`, `Sex`, `GradeId`, `Phone`, `Address`, `BornDate`, `Email`, `IdentityCard`) VALUES (1007, '111111', '欧阳峻峰', 1, 1, '13500000008', '北京东城区', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311133');
INSERT INTO `student` (`StudentNo`, `LoginPwd`, `StudentName`, `Sex`, `GradeId`, `Phone`, `Address`, `BornDate`, `Email`, `IdentityCard`) VALUES (1008, '111111', '梅超风', 1, 1, '13500000009', '河南洛阳', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311221');
INSERT INTO `student` (`StudentNo`, `LoginPwd`, `StudentName`, `Sex`, `GradeId`, `Phone`, `Address`, `BornDate`, `Email`, `IdentityCard`) VALUES (1009, '123456', '刘毅', 1, 2, '13500000011', '安徽', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311231');
INSERT INTO `student` (`StudentNo`, `LoginPwd`, `StudentName`, `Sex`, `GradeId`, `Phone`, `Address`, `BornDate`, `Email`, `IdentityCard`) VALUES (1010, '111111', '大凡', 1, 1, '13500000012', '河南洛阳', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311044');
INSERT INTO `student` (`StudentNo`, `LoginPwd`, `StudentName`, `Sex`, `GradeId`, `Phone`, `Address`, `BornDate`, `Email`, `IdentityCard`) VALUES (1011, '111111', '奥丹斯', 1, 1, '13500000013', '北京海淀区中关村大街*号', '1984-12-31 00:00:00', 'test1@bdqn.cn', '450323198412311234');
INSERT INTO `student` (`StudentNo`, `LoginPwd`, `StudentName`, `Sex`, `GradeId`, `Phone`, `Address`, `BornDate`, `Email`, `IdentityCard`) VALUES (1012, '123456', '多伦', 2, 3, '13500000014', '广西南宁中央大街', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311334');
INSERT INTO `student` (`StudentNo`, `LoginPwd`, `StudentName`, `Sex`, `GradeId`, `Phone`, `Address`, `BornDate`, `Email`, `IdentityCard`) VALUES (1013, '123456', '李梅', 2, 1, '13500000015', '上海卢湾区', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311534');
INSERT INTO `student` (`StudentNo`, `LoginPwd`, `StudentName`, `Sex`, `GradeId`, `Phone`, `Address`, `BornDate`, `Email`, `IdentityCard`) VALUES (1014, '123456', '张得', 2, 4, '13500000016', '北京海淀区中关村大街*号', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311264');
INSERT INTO `student` (`StudentNo`, `LoginPwd`, `StudentName`, `Sex`, `GradeId`, `Phone`, `Address`, `BornDate`, `Email`, `IdentityCard`) VALUES (1015, '123456', '李东方', 1, 4, '13500000017', '广西桂林市灵川', '1976-12-31 00:00:00', 'test1@bdqn.cn', '450323197612311234');
INSERT INTO `student` (`StudentNo`, `LoginPwd`, `StudentName`, `Sex`, `GradeId`, `Phone`, `Address`, `BornDate`, `Email`, `IdentityCard`) VALUES (1016, '111111', '刘奋斗', 1, 1, '13500000018', '上海卢湾区', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311251');
INSERT INTO `student` (`StudentNo`, `LoginPwd`, `StudentName`, `Sex`, `GradeId`, `Phone`, `Address`, `BornDate`, `Email`, `IdentityCard`) VALUES (1017, '123456', '可可', 2, 3, '13500000019', '北京长安街1号', '1981-9-10 00:00:00', 'test1@bdqn.cn', '450323198109108311');
INSERT INTO `student` (`StudentNo`, `LoginPwd`, `StudentName`, `Sex`, `GradeId`, `Phone`, `Address`, `BornDate`, `Email`, `IdentityCard`) VALUES (10066, '', 'Tom', 1, 1, '13500000000', '', '0000-0-0 00:00:00', 'email@22.com', '33123123123123123');

转储sql文件可以导出数据库或者表，其中结构和数据是指导出的是表结构和数据，仅结构导出的是表结构，不包含数据

/*
Navicat MySQL Data Transfer

Source Server         : mage
Source Server Version : 50727
Source Host           : localhost:3306
Source Database       : school

Target Server Type    : MYSQL
Target Server Version : 50727
File Encoding         : 65001

Date: 2020-02-03 10:38:32
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `StudentNo` int(4) NOT NULL COMMENT '学号',
  `LoginPwd` varchar(20) DEFAULT NULL,
  `StudentName` varchar(20) DEFAULT NULL COMMENT '学生姓名',
  `Sex` tinyint(1) DEFAULT NULL COMMENT '性别，取值0或1',
  `GradeId` int(11) DEFAULT NULL COMMENT '年级编号',
  `Phone` varchar(50) NOT NULL COMMENT '联系电话，允许为空，即可选输入',
  `Address` varchar(255) NOT NULL COMMENT '地址，允许为空，即可选输入',
  `BornDate` datetime DEFAULT NULL COMMENT '出生时间',
  `Email` varchar(50) NOT NULL COMMENT '邮箱账号，允许为空，即可选输入',
  `IdentityCard` varchar(18) DEFAULT NULL COMMENT '身份证号',
  PRIMARY KEY (`StudentNo`),
  UNIQUE KEY `IdentityCard` (`IdentityCard`),
  KEY `Email` (`Email`),
  KEY `GradeId_student` (`GradeId`),
  FULLTEXT KEY `studentName` (`StudentName`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1000', '111111', '郭靖', '1', '1', '13500000001', '北京海淀区中关村大街1号', '1986-12-11 00:00:00', 'test1@bdqn.cn', '450323198612111234');
INSERT INTO `student` VALUES ('1001', '123456', '李文才', '1', '2', '13500000002', '河南洛阳', '1981-12-31 00:00:00', 'test1@bdqn.cn', '450323198112311234');
INSERT INTO `student` VALUES ('1002', '111111', '李斯文', '1', '1', '13500000003', '天津市和平区', '1986-11-30 00:00:00', 'test1@bdqn.cn', '450323198611301234');
INSERT INTO `student` VALUES ('1003', '123456', '武松', '1', '3', '13500000004', '上海卢湾区', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612314234');
INSERT INTO `student` VALUES ('1004', '123456', '张三', '1', '4', '13500000005', '北京市通州', '1989-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311244');
INSERT INTO `student` VALUES ('1005', '123456', '张秋丽 ', '2', '1', '13500000006', '广西桂林市灵川', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311214');
INSERT INTO `student` VALUES ('1006', '123456', '肖梅', '2', '4', '13500000007', '武汉加油', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311134');
INSERT INTO `student` VALUES ('1007', '111111', '欧阳峻峰', '1', '1', '13500000008', '北京东城区', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311133');
INSERT INTO `student` VALUES ('1008', '111111', '梅超风', '1', '1', '13500000009', '河南洛阳', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311221');
INSERT INTO `student` VALUES ('1009', '123456', '刘毅', '1', '2', '13500000011', '安徽', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311231');
INSERT INTO `student` VALUES ('1010', '111111', '大凡', '1', '1', '13500000012', '河南洛阳', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311044');
INSERT INTO `student` VALUES ('1011', '111111', '奥丹斯', '1', '1', '13500000013', '北京海淀区中关村大街*号', '1984-12-31 00:00:00', 'test1@bdqn.cn', '450323198412311234');
INSERT INTO `student` VALUES ('1012', '123456', '多伦', '2', '3', '13500000014', '广西南宁中央大街', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311334');
INSERT INTO `student` VALUES ('1013', '123456', '李梅', '2', '1', '13500000015', '上海卢湾区', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311534');
INSERT INTO `student` VALUES ('1014', '123456', '张得', '2', '4', '13500000016', '北京海淀区中关村大街*号', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311264');
INSERT INTO `student` VALUES ('1015', '123456', '李东方', '1', '4', '13500000017', '广西桂林市灵川', '1976-12-31 00:00:00', 'test1@bdqn.cn', '450323197612311234');
INSERT INTO `student` VALUES ('1016', '111111', '刘奋斗', '1', '1', '13500000018', '上海卢湾区', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311251');
INSERT INTO `student` VALUES ('1017', '123456', '可可', '2', '3', '13500000019', '北京长安街1号', '1981-09-10 00:00:00', 'test1@bdqn.cn', '450323198109108311');
INSERT INTO `student` VALUES ('10066', '', 'Tom', '1', '1', '13500000000', '', '0000-00-00 00:00:00', 'email@22.com', '33123123123123123');

/*
Navicat MySQL Data Transfer

Source Server         : mage
Source Server Version : 50727
Source Host           : localhost:3306
Source Database       : school

Target Server Type    : MYSQL
Target Server Version : 50727
File Encoding         : 65001

Date: 2020-02-03 10:46:24
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `StudentNo` int(4) NOT NULL COMMENT '学号',
  `LoginPwd` varchar(20) DEFAULT NULL,
  `StudentName` varchar(20) DEFAULT NULL COMMENT '学生姓名',
  `Sex` tinyint(1) DEFAULT NULL COMMENT '性别，取值0或1',
  `GradeId` int(11) DEFAULT NULL COMMENT '年级编号',
  `Phone` varchar(50) NOT NULL COMMENT '联系电话，允许为空，即可选输入',
  `Address` varchar(255) NOT NULL COMMENT '地址，允许为空，即可选输入',
  `BornDate` datetime DEFAULT NULL COMMENT '出生时间',
  `Email` varchar(50) NOT NULL COMMENT '邮箱账号，允许为空，即可选输入',
  `IdentityCard` varchar(18) DEFAULT NULL COMMENT '身份证号',
  PRIMARY KEY (`StudentNo`),
  UNIQUE KEY `IdentityCard` (`IdentityCard`),
  KEY `Email` (`Email`),
  KEY `GradeId_student` (`GradeId`),
  FULLTEXT KEY `studentName` (`StudentName`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

方式三：

使用命令行导出mysqldump命令执行使用

-- ==================================导出表或者库==============================================
-- 导出一张表
-- mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名
mysqldump -h localhost -uroot -proot school student >F:/a.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.-- 执行成功

-- 导出多张表
-- mysqldump -h主机 -u用户名 -p密码 数据库 表名1 表名2 表名n >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -proot school student result >F:/c.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.-- 执行成功

-- 导出数据库
-- mysqldump -h主机 -u用户名 -p密码 数据库 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -proot school >f:/d.xql
mysqldump: [Warning] Using a password on the command line interface can be insecure.-- 执行成功


-- ==================================导入表或者库==============================================
-- 导入表（登录情况下操作）
-- 切换到指定使用的数据库
mysql> use school;
Database changed
-- source 物理磁盘位置/文件名
mysql> source f:/a.sql;

-- 未登录情况下(不推荐使用)
mysql -r用户名 -p密码 数据库名 <物理磁盘位置/文件名

QZP51ZX

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL备份——（十）

MySQL备份——（十）1.为什么要MySQL备份？保证重要数据不丢失数据转移2.MySQL备份方式方式一：直接拷贝物理文件data文件夹方式二：在navicat可视化软件中手动导出或者转储sql文件选择导出的数据库或者表，右键，选着导出向导或者转储sql文件导出向导导出选择sql脚本导出，且只能是表中数据，不包含表结构，可以选择包含列的标题，导出的都是sql语句（数...
复制链接

扫一扫