千云物流 -低代码平台MySQL备份数据

windows备份

全量备份

创建备份目录

需要在安装数据库的服务器上创建备份目录,所有如果要做备份至少需要两倍的硬盘空间,

mkdir D:\mysql_backup\full_backup

准备备份脚本

创建一个windows批处理文件（例如 full_backup.bat），用来执行全量备份并使用 robocopy 将备份文件传输到远程服务器。

@echo off

:: 配置部分
set MYSQL_USER=root
set MYSQL_PASSWORD=yourpassword
set MYSQL_HOST=localhost
set BACKUP_DIR=C:\mysql_backups\full
set REMOTE_DIR=\\remote-server\backup\full
set TIMESTAMP=%date:~10,4%%date:~4,2%%date:~7,2%_%time:~0,2%%time:~3,2%%time:~6,2%
set BACKUP_FILE=%BACKUP_DIR%\full_backup_%TIMESTAMP%.sql

:: 创建本地备份目录
if not exist %BACKUP_DIR% (
    mkdir %BACKUP_DIR%
)

:: 执行全量备份
mysqlpump -u%MYSQL_USER% -p%MYSQL_PASSWORD% -h%MYSQL_HOST% --result-file=%BACKUP_FILE%

:: 使用robocopy传输备份文件到远程服务器
robocopy %BACKUP_DIR% %REMOTE_DIR% %BACKUP_FILE%

:: 输出备份完成信息
if %errorlevel% leq 1 (
    echo Full backup completed successfully and copied to remote server.
) else (
    echo Error during backup or file transfer.
)
pause

脚本说明：

• BACKUP_DIR：本地存储备份文件的目录。
• REMOTE_DIR：远程服务器的备份目录路径。
• TIMESTAMP：生成带时间戳的备份文件名，确保每次备份文件名唯一。
• mysqlpump：执行全量备份命令。
• robocopy：复制备份文件到远程服务器，robocopy 会自动处理网络传输中的一些问题。

执行全量备份

编写批处理脚本

自动化备份

你可以通过Windows任务计划程序将这些脚本设置为定期执行,每两天进行全量备份，每天执行增量备份

1. 打开“任务计划程序”。
2. 创建基本任务，并设置触发器（如每周或每天）。
3. 在操作中，选择“启动程序”，然后浏览并选择对应的批处理文件（full_backup.bat 或 incremental_backup.bat）。
4. 保存任务。

增量备份

启用二进制日志

这里需要启用二进制,配置文件中的配置mysql的配置my.ini

[mysqld]
log-bin=mysql-bin
binlog-format=ROW
server-id=1

编写增量备份脚本

MySQL的增量备份一般是通过备份二进制日志（Binary Logs）来实现的。以下是一个基于 mysqlpump 备份二进制日志的增量备份脚本示例：

@echo off

:: 配置部分
set MYSQL_USER=root
set MYSQL_PASSWORD=yourpassword
set MYSQL_HOST=localhost
set BINLOG_DIR=C:\mysql_binlogs
set BACKUP_DIR=C:\mysql_backups\incremental
set REMOTE_DIR=\\remote-server\backup\incremental
set TIMESTAMP=%date:~10,4%%date:~4,2%%date:~7,2%_%time:~0,2%%time:~3,2%%time:~6,2%

:: 创建本地备份目录
if not exist %BACKUP_DIR% (
    mkdir %BACKUP_DIR%
)

:: 刷新二进制日志
mysqladmin -u%MYSQL_USER% -p%MYSQL_PASSWORD% -h%MYSQL_HOST% flush-logs

:: 复制新的二进制日志到备份目录
robocopy %BINLOG_DIR% %BACKUP_DIR% mysql-bin.*

:: 使用robocopy传输增量备份文件到远程服务器
robocopy %BACKUP_DIR% %REMOTE_DIR%

:: 输出备份完成信息
if %errorlevel% leq 1 (
    echo Incremental backup completed successfully and copied to remote server.
) else (
    echo Error during incremental backup or file transfer.
)
pause

• BINLOG_DIR：二进制日志文件的存储目录。
• mysqladmin flush-logs：刷新二进制日志，生成新的日志文件，准备备份旧的日志文件。
• robocopy：将本地备份文件传输到远程服务器，并处理传输中的问题。

传输二进制日志文件

@echo off
set BINLOG_DIR=C:\mysql\data
set REMOTE_SHARE=\\192.168.1.100\BackupDirectory\incremental_backup

echo Transferring binary logs to remote share...
robocopy %BINLOG_DIR% %REMOTE_SHARE% mysql-bin.*

if %errorlevel% geq 8 (
    echo Failed to transfer binary logs to the remote share.
) else (
    echo Binary logs successfully transferred to the remote share.
)

echo Incremental backup completed.

备份测试

创建备份测试数据库和表

-- 创建数据库如果不存在db_test，默认字符集为utf8，校对规则为utf8_general_ci
create database if not exists db_test default charset utf8 collate utf8_general_ci;
-- 切换数据库
use db_test 
create table t_student
(
   sid                  int not null comment '学号',
   sname                varchar(60) not null comment '姓名',
   sex                  tinyint not null default 1 comment '性别：1男， 2女',
   age                  tinyint not null comment ' 年龄',
   icard                varchar(18) not null comment '身份证，唯一约束',
   primary key (sid),
   unique key AK_Key_2 (icard)
) comment '学生信息表';
insert into t_student values(1,'张学友',1,36,'1234567891011');
insert into t_student values(2,'刘德华',1,39,'1234567891012');
-- 查询t_student
select * from t_student

全量备份恢复数据库

• 简单备份导出

  --extended-insert：使用多个 INSERT 语句组合成一条命令来插入多行数据。这也可以显著提高数据导入的速度。
  --single-transaction 选项提到导出表锁定, 通常与 InnoDB 一起使用
  --quick：对于大表，这个选项会强制 mysqldump 逐行地检索表中的数据，而不是一次性地将整个表加载到内存中。这有助于处理那些可能超出 mysqldump 可用内存限制的表。
  --set-charset：在导出数据时，在输出中包含 SET NAMES default_character_set 语句。这有助于确保在导入数据时保持正确的字符集设置。
   --ignore-table:忽略导出的表db_test.t_student
  --routines（或简写为-R）选项用于在备份过程中包含存储过程和函数的定义。这意味着，当使用mysqldump进行备份时，如果指定了--routines选项，那么数据库中所有的存储过程和函数都会被导出到备份文件中。这样，在恢复数据库时，这些存储过程和函数也会被重新创建，从而保证了数据库功能的完整性。
  --events选项用于在备份过程中包含事件调度器的信息。MySQL的事件调度器允许用户定义在特定时间或时间间隔自动执行的任务（即事件）。通过指定--events选项，mysqldump会将数据库中所有已定义的事件导出到备份文件中。这样，在恢复数据库时，这些事件也会被重新创建，并能够在预定的时间或时间间隔自动执行，从而保证了数据库操作的自动化和定时性。
  
  mysqldump --opt --quick --extended-insert --host=127.0.0.1 --protocol=tcp --port=3306 --default-character-set=utf8 --single-transaction=TRUE -u root --password=janle@etms db_test t_student > "E:\mysql\mysql-5.7.44-winx64\full_backup\full_back.sql"
  -- 压缩导出
  mysqldump --user=用户名 --password=密码 --host=主机名 --databases 数据库名 | gzip > 备份文件名.sql.gz
  
  

• 脚本进行备份操作

  rem Auther By Janle
  rem date:20240923
  rem ******Backup MySQL Start******
  @echo off
  setlocal enabledelayedexpansion
  
  :: 配置部分 - 请根据你的实际情况修改这些路径和配置
  set MYSQL_HOME=%~dp0
  ::设置时间变量
  set "Ymd=%date:~0,4%%date:~5,2%%date:~8,2%"
   
  ::创建存储的文件夹
  if not exist "%MYSQL_HOME%\full_backup" md "%MYSQL_HOME%\full_backup"
   
  ::执行备份操作
  "%MYSQL_HOME%\bin\mysqldump" --opt --user=root --password=janle@etms --host=127.0.0.1 --protocol=tcp --port=3306 --default-character-set=utf8 --single-transaction=TRUE --routines --events "db_test" >"%MYSQL_HOME%\full_backup\full_back_%Ymd%.sql"
   
  ::删除7天前的备份数据
  forfiles /p "%MYSQL_HOME%\full_backup" /m backup_*.sql -d -7 /c "cmd /c del /f @path"
   
  @echo on
  rem ******Backup MySQL End******
  

• 进行备份文件的恢复

  create database if not exists db_test default charset utf8 collate utf8_general_ci;
  use db_test 
  source E:\mysql\mysql-5.7.44-winx64\full_backup\full_back_20240923.sql
  

增量备份数据库

-- 插入两条新数据
INSERT INTO `db_test`.`t_student` (`sid`, `sname`, `sex`, `age`, `icard`) VALUES (3, '周星驰', 1, 36, '1234567891013');
INSERT INTO `db_test`.`t_student` (`sid`, `sname`, `sex`, `age`, `icard`) VALUES (4, '张曼玉', 1, 39, '1234567891014');
--执行truncate t_student;命令，删除t_student学生表里面的数据,这里不能执行dorp table命令,那要只能全量恢复了
truncate t_student;
-- 查询t_student
select * from t_student

增量备份的脚本文件insert_backup.bat

rem Auther By Janle
rem date:20240923
rem ******Backup MySQL Start******
@echo off
::设置时间变量
set "Ymd=%date:~0,4%%date:~5,2%%date:~8,2%"
 
::执行增量备份操作刷新binlog日志
mysqladmin -u root -pjanle@etms flush-logs
 
@echo on
rem ******Backup MySQL End******

show binlog events in 'mysql-bin.000002';

-- 导出增量部分sql
mysqlbinlog --no-defaults E:\mysql\mysql-5.7.44-winx64\data\mysql-bin.000002 --start-position=2631 --stop
-position=4517 > E:\mysql\mysql-5.7.44-winx64\full_backup\zengliang02.sql
-- 执行导入
source E:\mysql\mysql-5.7.44-winx64\full_backup\zengliang.sql

备份计划

打开windows任务计划程序

创建基本任务-> 添加名称(全量数据库备份)->触发器任务配置->启动任务->任何用户都可以执行

恢复备份

恢复全量备份

mysql -u root -p < /path/to/backup/directory/full_backup_YYYYMMDD.sql

应用增量备份

mysqlbinlog /path/to/backup/directory/incremental_backup/mysql-bin.000001 | mysql -u root -p
mysqlbinlog /path/to/backup/directory/incremental_backup/mysql-bin.000002 | mysql -u root -p

最终版本mysql

mysql在windows集成版

windows从binlog恢复库删除数据

执行 BAT 脚本，它将生成 SQL 文件（例如：table_name1.insert.sql 和 table_name2.insert.sql），用于恢复已删除的记录。recovery_delete.bat文件

@echo off
setlocal enabledelayedexpansion

:: 设置数据库名称
set "dbname=database_name"

:: 设置开始和结束时间
set "start_time=2023-11-09 12:30:00"
set "end_time=2023-11-09 13:10:00"

:: 循环处理表清单
for %%t in (table_name1 table_name2) do (
    set "table=%%t"
    echo Processing table: !table!

    :: 使用 mysqlbinlog 获取删除行并保存到临时文件
    mysqlbinlog -vv -d !dbname! --start-datetime=!start_time! --stop-datetime=!end_time! binlog.001221 > !table!.delete.txt

    :: 过滤和转换 DELETE 语句为 INSERT INTO 语句
    (for /f "tokens=*" %%a in (!table!.delete.txt) do (
        set "line=%%a"

        :: 查找 DELETE 语句并转换为 INSERT INTO
        echo !line! | findstr /c:"DELETE FROM !dbname!.!table!" >nul
        if !errorlevel! equ 0 (
            :: 处理 DELETE 语句的每一行
            set "insert_line=!line:DELETE FROM=INSERT INTO!"
            set "insert_line=!insert_line:WHERE=VALUES("
            echo !insert_line!
        )
    )) > !table!.insert.sql

    :: 修复 VALUES 语句和去掉多余的逗号
    for /f "tokens=*" %%b in (!table!.insert.sql) do (
        set "insert_line=%%b"
        :: 删除逗号和关闭括号
        set "insert_line=!insert_line:,);=);!"
        echo !insert_line! >> !table!.insert.sql
    )

    :: 清理临时文件
    del /f /q !table!.delete.txt

    :: 显示生成的 SQL 文件路径
    echo Processed SQL for table !table! is saved to: !table!.insert.sql
    echo.
)

endlocal

linux备份

linux从binlog恢复库删除数据

执行 BAT 脚本，它将生成 SQL 文件（例如：table_name1.insert.sql 和 table_name2.insert.sql），用于恢复已删除的记录。

# 循环 tab 表清单进行 delete 操作转换 insert sql文件
# 输入 DBname
dbname=database_name
# for 循环 tab 表清单进行 delete 操作转换 insert sql文件
for tab in table_name1 table_name2; do
    # mysqlbinlog 抓取指定表的delete行到 $tab.delete.txt 文件，一般挖掘的delete语句格式为"DELETE FROM `$dbname`.`$tab`"
    mysqlbinlog -vv -d $dbname --start-datetime='2023-11-09 12:30:00' --stop-datetime='2023-11-09 13:10:00' binlog.001221 | awk -v a="$dbname.$tab" -v b="\`$dbname\`.\`$tab\`" -v dl="DELETE FROM" '$0~dl && ($0~a || $0~b) {
    while(1){
        print $0;
        getline;
        if($0 !~ /^###/){
            break;
        };
    }
}' >$tab.delete.txt
    # 替换每行开头字符串 "### " 为空(即删除)
    sed -i 's/^### //g' $tab.delete.txt
    # 替换每行开头字符串 "DELETE FROM" 为"INSERT INTO"
    sed -i "s/^DELETE FROM/INSERT INTO/g" $tab.delete.txt
    # 替换每行开头字符串 "WHERE" 为"VALUES("
    sed -i "s/^WHERE/VALUES(/g" $tab.delete.txt
    # 或取表的列数量
    lastnum=$(tail -n 1 $tab.delete.txt | awk -F"=" '{print $1}')
    # 在每个最后列下一行插入字符串");"
    sed -i "/${lastnum}=.*/a\);" $tab.delete.txt
    echo "${dbname}.${tab}-${lastnum}"
    # 替换每行第一个"="为"JeffStartSplit",作为截取字符串开始标识
    sed -i 's/=/JeffStartSplit/' $tab.delete.txt
    # 替换每行第后一个"/*"(即：字符串倒序第一个"*/")为"JeffStopSplit"(即：字符串倒序"tilpSpotSffeJ"),作为截取字符串结束标识
    rev $tab.delete.txt | sed 's/\*\//tilpSpotSffeJ/' | rev >$tab.temp.txt
    # 以字符串JeffStartSplit或JeffStopSplit截取文件，判断文件中每行是否以"INSERT"或"VALUES"或");"字符串开头
    #     是：打印所有列；否：以","拼接打印第二列(即列值，如果最后会多一个","，后面有处理)，打印内容输入到文件$tab.insert.sql
    cat $tab.temp.txt | awk -F"JeffStartSplit|JeffStopSplit" '{
    if($0 ~ /^INSERT|^VALUES|^);/){
        print $0;
    }else{
        printf $2",";
    };
}' >$tab.insert.sql
    rm -rf $tab.*.txt
    tail -3 $tab.insert.sql
    # 替换 ",);" 为 ");"
    sed -i 's/,);/);/g' $tab.insert.sql
    # 该表处理完成，打印生成的sql文件完整路径
    echo -e "# Get insert sql in $(realpath $tab.insert.sql)\n\n"
done