前言
通常情况下，我们需要改什么地方就备份什么地方就可以了，但也免不了需要整库备份的时候，本文记录实现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.5inmysqldump.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
恢复数据
-- 创建数据库 create database jfinal_demo; -- 使用已创建的数据库 use jfinal_demo; -- 设置编码 set names utf8; -- 导入备份数据库 source '文件路径' commit;
使用Navicat等工具导入sql文件
后记
需要进行备份时（例如：更新代码、进行系统版本迭代等），双击执行脚本即可
可以在配置定时执行脚本，实现自动备份
Oracle可以使用exp命令进行数据表备份，例如：
D:orcale11.2.0dbhome_1BINexp username/password@127.0.0.1:1521/orcl file="E:ackup_files est.DMP" log="E:ackup_files_log est.txt"
导出命令不同，其他的都一样
