MySQL使用脚本进行整库数据备份【表（结构+数据）、视图、函数、事件】

　　前言

　　通常情况下，我们需要改什么地方就备份什么地方就可以了，但也免不了需要整库备份的时候，本文记录实现MySQL使用脚本进行整库数据备份【表（结构+数据）、视图、函数、事件】

　　主要是使用mysqldump.exe，mysqldump 是 mysql 用于转存储数据库的实用程序。它主要产生一个 SQL 脚本，其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。

　　为方便测试，使用java先造一下测试数据

package cn.huanzi.qch;

import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

/**
 * 造一批数据，方便进行测试
 */
public class App {

    public static void main(String[] args) {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMdd");
        for (int i = 1; i <= 10; i++) {
            //当前时间-i天
            Calendar c = Calendar.getInstance();
            c.setTime(new Date());
            c.add(Calendar.DATE, -1 * i);

            try {
                File fileToChange = new File("D:\\mysql_data_back\\jfinal_demo_"+simpleDateFormat.format(c.getTime())+".sql");
                fileToChange.createNewFile();

                //更改最后修改时间
                fileToChange.setLastModified(c.getTime().getTime());
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

　　bat脚本

::  MySQL整库数据备份脚本【表（结构+数据）、视图、函数、事件】，支持备份远程库
::  注：bat解决中文乱码：改成使用ANSI格式
::  注：MySQL服务器版本号、客户端版本要一致，否则会报错
    
@echo OFF

echo 删掉之前的数据，只保留7天...

::    删掉之前的数据，按修改时间只保留7天（cmd使用set后forfiles失败，只能放在前面执行了）
::forfiles /p "D:\mysql_data_back" /s /m *.sql /d -7 /c "cmd /c echo @file"
forfiles /p "D:\mysql_data_back" /s /m *.sql /d -7 /c "cmd /c del @path";


::    年月日，路径
set ymd=%date:~,4%%date:~5,2%%date:~8,2%
set path=D:\mysql_data_back

::    数据库地址、端口，数据库、账号、密码
set ip=127.0.0.1
set port=3306
set dbname=jfinal_demo
set username=root
set password=123456

echo 开始备份，文件生成路径：%path%...

::    -h地址 -P端口 -u账号 -p密码 数据库
::    常用命令：
::    --no-create-db, -n/--no-create-info, -t 只导出数据，而不添加CREATE TABLE 语句
::    --no-data, -d 不导出任何数据，只导出数据库表结构
::    --routines, -R 导出存储过程以及自定义函数
::    --events, -E 导出事件
::    --force 在导出过程中忽略出现的SQL错误
D:\MySQL5.5\bin\mysqldump.exe -P%port% -h%ip% -u%username% -p"%password%" %dbname% -R -E> %path%\%dbname%_%ymd%.sql

echo %path%\%dbname%_%ymd%.sql，备份完成!

::    执行完不关闭窗口
pause

　　使用forfiles命令，可以快速实现备份数据保留7天

　　下面这个命令是筛选出7天之前的文件

　　shell脚本

#!/bin/bash
#  MySQL整库数据备份脚本【表（结构+数据）、视图、函数、事件】，支持备份远程库
#  注：bat解决中文乱码：改成使用ANSI格式
#  注：MySQL服务器版本号、客户端版本要一致，否则会报错

#    年月日，路径
export ymd=`date +%Y%m%d`
export path=/root/mysql/mysql_data_back

#    数据库地址、端口，数据库、账号、密码
export ip='127.0.0.1'
export port='3306'
export dbname='test'
export username='root'
export password='123456'

echo "删掉之前的数据，只保留7天..."

#    删掉之前的数据，按修改时间只保留7天
find $path -type f -name "*.sql" -mtime +7 -exec rm -rf {} \;

echo "开始备份，文件生成路径：$path..."

#    -h地址 -P端口 -u账号 -p密码 数据库
#    常用命令：
#    --no-create-db, -n/--no-create-info, -t 只导出数据，而不添加CREATE TABLE 语句
#    --no-data, -d 不导出任何数据，只导出数据库表结构
#    --routines, -R 导出存储过程以及自定义函数
#    --events, -E 导出事件
#    --force 在导出过程中忽略出现的SQL错误
/usr/bin/mysqldump -P$port -h$ip -u$username -p"$password" $dbname -R -E> $path/$dbname$ymd.sql

echo "$path/$dbname$ymd.sql，备份完成!"

　　首先把我们生成的测试文件上传到linux，并修改文件的修改日期，cd到对应文件夹，执行以下命令

touch -d "2021-08-21 03:41:10" test20210821.sql&
touch -d "2021-08-22 03:41:10" test20210822.sql&
touch -d "2021-08-23 03:41:10" test20210823.sql&
touch -d "2021-08-24 03:41:10" test20210824.sql&
touch -d "2021-08-25 03:41:10" test20210825.sql&
touch -d "2021-08-26 03:41:10" test20210826.sql&
touch -d "2021-08-27 03:41:10" test20210827.sql&
touch -d "2021-08-28 03:41:10" test20210828.sql&
touch -d "2021-08-29 03:41:10" test20210829.sql&
touch -d "2021-08-30 03:41:10" test20210830.sql&

　　forfiles命令是window特有的，因此要改成find的方式查找文件

　　效果

　　bat脚本

　　shell脚本

　　生成的sql文件，包含表（结构+数据）、视图、函数/存储过程、事件

-- MySQL dump 10.13  Distrib 5.5.28, for Win64 (x86)
--
-- Host: 127.0.0.1    Database: jfinal_demo
-- ------------------------------------------------------
-- Server version    5.5.28

/*!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 `blog`
--

DROP TABLE IF EXISTS `blog`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `blog` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '博客id',
  `title` varchar(200) DEFAULT NULL COMMENT '博客标题',
  `content` mediumtext COMMENT '博客内容',
  `user_id` varchar(255) DEFAULT NULL COMMENT '用户id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 COMMENT='博客表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `blog`
--

LOCK TABLES `blog` WRITE;
/*!40000 ALTER TABLE `blog` DISABLE KEYS */;
INSERT INTO `blog` VALUES (2,'test 1','test 1','1'),(3,'test 2','test 2','1'),(4,'test 4','test 4','2'),(5,'test 5','test 5','2'),(6,'test 6','test 6','1'),(11,'11','11','3'),(12,'12','12','3'),(13,'13','13','3'),(14,'14','14','3'),(15,'15','15','3'),(16,'16','16','3');
/*!40000 ALTER TABLE `blog` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `user`
--

DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
  `user_id` varchar(255) NOT NULL COMMENT '用户id',
  `user_name` varchar(255) DEFAULT NULL COMMENT '用户名称',
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user`
--

LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES ('1','张三'),('2','李四'),('3','王五');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Temporary table structure for view `v_test`
--

DROP TABLE IF EXISTS `v_test`;
/*!50001 DROP VIEW IF EXISTS `v_test`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `v_test` (
  `id` tinyint NOT NULL,
  `title` tinyint NOT NULL,
  `content` tinyint NOT NULL,
  `user_id` tinyint NOT NULL,
  `user_name` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Dumping events for database 'jfinal_demo'
--
/*!50106 SET @save_time_zone= @@TIME_ZONE */ ;
/*!50106 DROP EVENT IF EXISTS `ev_test` */;
DELIMITER ;;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;;
/*!50003 SET character_set_client  = utf8mb4 */ ;;
/*!50003 SET character_set_results = utf8mb4 */ ;;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;;
/*!50003 SET @saved_time_zone      = @@time_zone */ ;;
/*!50003 SET time_zone             = 'SYSTEM' */ ;;
/*!50106 CREATE*/ /*!50117 DEFINER=`root`@`localhost`*/ /*!50106 EVENT `ev_test` ON SCHEDULE EVERY 3 SECOND STARTS '2021-08-31 11:02:25' ON COMPLETION PRESERVE DISABLE DO BEGIN



END */ ;;
/*!50003 SET time_zone             = @saved_time_zone */ ;;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;;
/*!50003 SET character_set_client  = @saved_cs_client */ ;;
/*!50003 SET character_set_results = @saved_cs_results */ ;;
/*!50003 SET collation_connection  = @saved_col_connection */ ;;
DELIMITER ;
/*!50106 SET TIME_ZONE= @save_time_zone */ ;

--
-- Dumping routines for database 'jfinal_demo'
--
/*!50003 DROP FUNCTION IF EXISTS `fun_test` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `fun_test`() RETURNS int(11)
BEGIN



    #Routine body goes here...







    RETURN 0;



END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `pro_test` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `pro_test`()
BEGIN



    #Routine body goes here...







END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

--
-- Final view structure for view `v_test`
--

/*!50001 DROP TABLE IF EXISTS `v_test`*/;
/*!50001 DROP VIEW IF EXISTS `v_test`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v_test` AS select `b`.`id` AS `id`,`b`.`title` AS `title`,`b`.`content` AS `content`,`b`.`user_id` AS `user_id`,`u`.`user_name` AS `user_name` from (`blog` `b` left join `user` `u` on((`b`.`user_id` = `u`.`user_id`))) */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;
/*!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 2021-08-31 15:21:04

jfinal_demo_20210831.sql

　　恢复数据

-- 创建数据库
create database jfinal_demo;
-- 使用已创建的数据库
use jfinal_demo;
-- 设置编码
set names utf8;
-- 导入备份数据库
source '文件路径'
commit;

　　使用Navicat等工具导入sql文件

　　后记

　　需要进行备份时（例如：更新代码、进行系统版本迭代等），双击执行脚本即可

　　可以在配置定时执行脚本，实现自动备份

　　Navicat数据迁移

　　工具 -> 数据传输

　　Oracle备份/恢复

　　PS：可以使用tables命令，按需导出、导入表（‘_’匹配任何一个字符，'%'则匹配任何数量的字符包括0个字符）

导入导出“TBXX”开头的表
tables=TBXX%

导入导出ABC三个表
tables=(A,B,C)

　　Oracle可以使用 exp 命令进行数据备份，例如：（整库备份）

D:\orcale\11.2.0\dbhome_1\BIN\exp username/password@127.0.0.1:1521/orcl file="E:\backup_files\test.DMP" log="E:\backup_files_log\test.txt"

　　exp命令大全

关键字 说明 默认
USERID 用户名/口令
FULL 导出整个文件 (N)
BUFFER 数据缓冲区的大小
OWNER 导出指定的所有者用户名列表
FILE 输出文件 (EXPDAT.DMP)
TABLES 导出指定的表名列表
COMPRESS 是否压缩导出的文件 (Y)
RECORDLENGTH IO 记录的长度
GRANTS 导出权限 (Y)
INCTYPE 增量导出类型
INDEXES 导出索引 (Y)
RECORD 跟踪增量导出 (Y)
ROWS 导出数据行 (Y)
PARFILE 参数文件名
CONSTRAINTS 导出限制 (Y)
CONSISTENT 交叉表一致性
LOG 屏幕输出的日志文件
STATISTICS 分析对象(ESTIMATE)
DIRECT 直接路径 (N)
TRIGGERS 导出触发器 (Y)
FEEDBACK 显示每 x 行 (0) 的进度
FILESIZE 各转储文件的最大尺寸
QUERY 选定导出表子集的子句
TRANSPORT_TABLESPACE 导出可传输的表空间元数据 (N)
TABLESPACES 导出指定的表空间列表

　　Oracle可以使用 imp 命令恢复数据（仅导入指定表）

imp username/password@127.0.0.1:1521/orcl file=E:\backup_files\test.DMP log=E:\backup_files\test.log tables=(table1)

　　imp命令大全

关键字     说明     默认
USERID    用户名/口令
FULL     导入整个文件       (N)
BUFFER    数据缓冲区大小
FROMUSER    所有人用户名列表
FILE    输入文件      (EXPDAT.DMP)
TOUSER    用户名列表
SHOW     只列出文件内容    (N)
TABLES    表名列表
IGNORE    忽略创建错误    (N) 
RECORDLENGTH     IO记录的长度
GRANTS    导入权限    (Y)
INCTYPE    增量导入类型
INDEXES    导入索引      (Y)
COMMIT     提交数组插入     (N)
ROWS    导入数据行     (Y) 
PARFILE    参数文件名
LOG    屏幕输出的日志文件
CONSTRAINTS     导入限制     (Y)
DESTROY    覆盖表空间数据文件     (N)
INDEXFILE    将表/索引信息写入指定的文件
SKIP_UNUSABLE_INDEXES    跳过不可用索引的维护      (N)
FEEDBACK    每 x 行显示进度
TOID_NOVALIDATE     跳过指定类型 ID 的验证
FILESIZE     每个转储文件的最大大小
STATISTICS    始终导入预计算的统计信息
RESUMABLE    在遇到有关空间的错误时挂起
RESUMABLE_NAME    用来标识可恢复语句的文本字符串
RESUMABLE_TIMEOUT    RESUMABLE 的等待时间
COMPILE    编译过程, 程序包和函数     (Y)
STREAMS_CONFIGURATION    导入 Streams 的一般元数据     (Y)
STREAMS_INSTANITATION     导入 Streams 的实例化元数据     (N)
TRANSPORT_TABLESPACE    导入可传输的表空间元数据
TABLESPACES     将要传输到数据库的表空间
DATAFILES    将要传输到数据库的数据文件
TTS_OWNERS    拥有可传输表空间集中数据的用户

　　Oracle创建/删除用户

-- 创建用户并赋予密码
create user "TEST_USER" identified by "TESTUSER_123456"; 

-- 授予用户登录数据库权限
grant create session to "TEST_USER"; 

-- 授予用户增删改表权限
grant create table to "TEST_USER"; 

-- 授予用户dba权限
grant dba to "TEST_USER"; 

-- 删除用户以及用户下的所有数据 
drop user "TEST_USER" cascade; 

-- 给其他用户分配指定表的查询权限
grant select on TEST_TABLE to TEST_USER2;

posted @ 2021-08-31 16:14 huanzi-qch 阅读(1034) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

huanzi-qch

18届物联网工程专业本科生，一名普通程序员，工作认真负责，对技术充满热情，善于探索，热爱开源。
PS：坐标南宁，正在留意好的职业发展机会🎈

MySQL使用脚本进行整库数据备份【表（结构+数据）、视图、函数、事件】

前言

bat脚本

shell脚本

效果

bat脚本

shell脚本

恢复数据

后记

Navicat数据迁移

Oracle备份/恢复

Oracle创建/删除用户

版权声明

AD广告位（长期招租，如有需要请私信）

捐献、打赏

QQ群交流群

公告

huanzi-qch

18届物联网工程专业本科生，一名普通程序员，工作认真负责，对技术充满热情，善于探索，热爱开源。 PS：坐标南宁，正在留意好的职业发展机会🎈

MySQL使用脚本进行整库数据备份【表（结构+数据）、视图、函数、事件】

前言

bat脚本

shell脚本

效果

bat脚本

shell脚本

恢复数据

后记

Navicat数据迁移

Oracle备份/恢复

Oracle创建/删除用户

公告

18届物联网工程专业本科生，一名普通程序员，工作认真负责，对技术充满热情，善于探索，热爱开源。
PS：坐标南宁，正在留意好的职业发展机会🎈

　　前言

　　bat脚本

　　shell脚本

　　效果

　　bat脚本

　　shell脚本

　　恢复数据

　　后记

　　Navicat数据迁移

　　Oracle备份/恢复

　　Oracle创建/删除用户