环境准备
见 《数据库恢复环境搭建-MySQL8.4.md》
基础数据准备
自建MySQL 8.4.2
使用备份脚本做好完备
#!/bin/bash
# mysqlbackup
# author: wangcw
# Generated: 2022-08-19 17:30:46
# 1:00 am every full backup crontab
# 0 01 * * * /mysqldata/mysqlbak/mysqlbak_all.sh
#
# 备份数据库账号需有SELECT,PROCESS,LOCK TABLES权限
# CREATE USER 'user_backup'@'%' IDENTIFIED BY 'Lunz@2024';
# GRANT SELECT,PROCESS,LOCK TABLES ON *.* TO '$user_backup'@'%';
# FLUSH PRIVILEGES;
# 在 mysq.cnf 末尾添加，一遍免密使用mysqldump指令
# [mysqldump]
# user=user_backup
# password="Lunz@2024"
#备份服务器配置
db_host="192.168.2.81"
db_user="user_backup"
db_pass="Lunz@2024"
db_port="3306"
db_except="information_schema|performance_schema|sys|mysql"
bak_dir="/mysqldata/mysqlbak"
bak_date="$(date +%Y%m%d)"
bak_day=7
bak_log=${bak_dir}/mysqlbak.log
#文件夹创建
if [ ! -d "$bak_dir" ]; then
mkdir -p $bak_dir
fi
if [ ! -f "$bak_log" ]; then
touch $bak_log
fi
fun_bak() {
mysql -h$db_host -P$db_port -u$db_user -p$db_pass -e "SHOW DATABASES;" 2>/dev/null -N | egrep -v $db_except > dbname
while read db
do
[ -d ${bak_dir}/$db ] || mkdir -p ${bak_dir}/$db
file_db_name=${db}-${bak_date}
dump_db_file=${bak_dir}/$db/${file_db_name}
echo $(date +'%Y-%m-%d %T')" ==> Start Dumping database: ${db}..." >>${bak_log}
mysqldump -h$db_host -P$db_port -B $db > ${dump_db_file}.sql 2>>${bak_log} 2>&1
echo $(date +'%Y-%m-%d %T')" ==> Start zip file: ${dump_db_file}.sql..." >>${bak_log}
cd ${bak_dir}/$db
tar zcf "$file_db_name".tar.gz "$file_db_name".sql --transform='s/^.*\// /' --remove & > /dev/null
mysql -h$db_host -P$db_port -u$db_user -p$db_pass -N -e "show tables from $db" 2>/dev/null > tbname
while read tb
do
file_tb_name=${db}-${tb}-${bak_date}
dump_tb_file=${bak_dir}/$db/${file_tb_name}
echo $(date +'%Y-%m-%d %T')" ==> Start Dumping table: ${db}.${tb}..." >> ${bak_log}
mysqldump -h$db_host -P$db_port $db $tb > ${dump_tb_file}.sql 2>>${bak_log} 2>&1
echo $(date +'%Y-%m-%d %T')" ==> Start zip file: ${file_tb_name}.sql..." >>${bak_log}
cd ${bak_dir}/$db/
tar zcf "$file_tb_name".tar.gz "$file_tb_name".sql --transform='s/^.*\// /' --remove & > /dev/null
echo $(date +'%Y-%m-%d %T')" ==> Success backup table: ${db}.${tb}..." >> ${bak_log}
done < tbname
echo $(date +'%Y-%m-%d %T')" ==> Success backup database: ${db} with file name: ${file_db_name}.tar.gz!" >> ${bak_log}
done < dbname
rm -rf tbname ../dbname
}
fun_rm() {
echo $(date +'%Y-%m-%d %T')" ==> Start delete backup file." >>${bak_log}
for db_dir in ${bak_dir}/*/ ; do
db_name=$(basename $db_dir)
find $db_dir -type f -name "*.tar.gz" -mtime +${bak_day} | while read file; do
echo "Deleting ${file} ..."
rm -f "$file"
done
done
echo $(date +'%Y-%m-%d %T')" ==> Finished delete backup file." >>${bak_log}
}
cd $bak_dir
fun_bak
fun_rm单表单SQL文件批量还原
find . -type f -name "*.sql" -exec sh -c 'mysql -uroot -D whcenter -pLunz@2017 < "$0"' {} \;删库还原==传备份文件到新实例还原库(mysqldump导出文件，均为逻辑备份)
mysql> drop database whcenter;
Query OK, 14 rows affected (0.21 sec)tar -zxvf whcenter-20240820.tar.gz
[root@mysql84 whcenter]# mysql -p < whcenter-20240820.sql
Enter password:
还原完毕
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| whcenter |
+--------------------+
5 rows in set (0.01 sec)
mysql> use whcenter;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------+
| Tables_in_whcenter |
+---------------------+
| tb_instockdetail |
| tb_instockextra |
| tb_instockinfo |
| tb_outstockdetail |
| tb_outstockextra |
| tb_outstockinfo |
| tb_ownerinfo |
| tb_stockinfo |
| tb_supplier |
| tb_warehouse |
| tb_warehousemanager |
| tb_whaddress |
| tb_wharea |
| tb_whcustinfo |
+---------------------+
14 rows in set (0.00 sec)还原至完备后的某个时间点
binglog数据生成
# 更新数据
UPDATE `whcenter`.`tb_instockdetail` SET `InStockNum` = 20, `InStockedNum` = 20 WHERE `Id` = 'ID9992087803';
UPDATE `whcenter`.`tb_instockdetail` SET `InStockNum` = 10, `InStockedNum` = 10, `InStockSurplus` = 10 WHERE `Id` = 'ID9992087835';
UPDATE `whcenter`.`tb_instockdetail` SET `InStockNum` = 80, `InStockedNum` = 80 WHERE `Id` = 'ID9992088000';
UPDATE `whcenter`.`tb_instockdetail` SET `InStockPrice` = 50, `UpdatedAt` = NOW() WHERE `Id` = 'ID9992088000';
## 2024-8-23 15:29:29
UPDATE `whcenter`.`tb_outstockdetail` SET `OutStockNum` = 50 WHERE `Id` = 'OD9969345444';
UPDATE `whcenter`.`tb_outstockdetail` SET `OutStockNum` = 60 WHERE `Id` = 'OD9969345487';
UPDATE `whcenter`.`tb_outstockdetail` SET `OutStockNum` = 500 WHERE `Id` = 'OD9969345773';
UPDATE `whcenter`.`tb_outstockdetail` SET `OutStockPrice` = 1000, `UpdatedAt` = NOW() WHERE `Id` = 'OD9969345773';
## 2024-8-23 15:29:38
UPDATE `whcenter`.`tb_stockinfo` SET `StockNum` = 21, `RealityNum` = 21 WHERE `Id` = 104;
UPDATE `whcenter`.`tb_stockinfo` SET `StockNum` = 31, `RealityNum` = 31 WHERE `Id` = 191;
UPDATE `whcenter`.`tb_stockinfo` SET `RealityNum` = 20 WHERE `Id` = 298;
UPDATE `whcenter`.`tb_stockinfo` SET `StockNum` = 48 WHERE `Id` = 4792;
UPDATE `whcenter`.`tb_stockinfo` SET `WaringValue` = 500, `UpdatedAt` = NOW() WHERE `Id` = 4792;
## 2024-8-23 15:29:49
# 删除数据
DELETE FROM whcenter.tb_instockdetail WHERE Id IN ('ID9999040178','ID9999040179','ID9999040180','ID9999040181',
'ID9999040182','ID9999040183','ID9999040184','ID9999040185','ID9999040186','ID9999040187');
DELETE FROM whcenter.tb_instockinfo WHERE Id IN ('IN9999067312','IN9999067313','IN9999067314','IN9999067315',
'IN9999067316','IN9999067317','IN9999067318','IN9999067319','IN9999067320','IN9999067321');
DELETE FROM whcenter.tb_outstockdetail WHERE Id IN ('OD9999881772','OD9999881779','OD9999881780','OD9999881781',
'OD9999881782','OD9999881783','OD9999881784','OD9999881785','OD9999881786','OD9999881787');
DELETE FROM whcenter.tb_outstockinfo WHERE Id IN ('OT9999906003','OT9999906012','OT9999906013','OT9999906014',
'OT9999906015');
DELETE FROM whcenter.tb_wharea WHERE Id >= 523 AND Id <= 532;
## 2024-8-23 15:29:59
# 截断、清空数据(不能还原)
TRUNCATE TABLE tb_whcustinfo;
## 2024-8-23 15:29:12
# 添加数据
DELETE FROM `whcenter`.`tb_warehouse` WHERE Id='WH9999997790';
DELETE FROM `whcenter`.`tb_warehouse` WHERE Id='WH9999997791';
DELETE FROM `whcenter`.`tb_warehouse` WHERE Id='WH9999997792';
DELETE FROM `whcenter`.`tb_warehouse` WHERE Id='WH9999997793';
DELETE FROM `whcenter`.`tb_warehouse` WHERE Id='WH9999997794';
DELETE FROM `whcenter`.`tb_warehouse` WHERE Id='WH9999997795';
DELETE FROM `whcenter`.`tb_warehouse` WHERE Id='WH9999997796';
DELETE FROM `whcenter`.`tb_warehouse` WHERE Id='WH9999997797';
DELETE FROM `whcenter`.`tb_warehouse` WHERE Id='WH9999997798';
DELETE FROM `whcenter`.`tb_warehouse` WHERE Id='WH9999997799';
## 2024-8-23 15:28:59
INSERT INTO `whcenter`.`tb_warehouse` (`Id`, `Name`, `ParentId`, `MainPartId`, `Type`, `Mode`, `LevelCode`, `CustId`, `CustName`, `CustStoreId`, `CustStoreCode`, `CustStoreName`, `IsWaitTransfer`, `Enable`, `IsMultiCheck`, `IsReceipt`, `IsAllowTrans`, `NameSort`, `LastCloseTime`, `Remark`, `CreatedById`, `CreatedAt`, `UpdatedById`, `UpdatedAt`, `DeletedById`, `DeletedAt`, `Deleted`) VALUES ('WH9999997790', '测试3', 'WH9999998006', 'DT0000000001', 0, 4, 2, NULL, NULL, NULL, NULL, NULL, 0, 0, 1, 1, 1, 3, NULL, '123', '76a20000-3e08-0016-598d-08d7f3f2461a', '2020-08-13 15:44:55', NULL, NULL, NULL, NULL, 0);
INSERT INTO `whcenter`.`tb_warehouse` (`Id`, `Name`, `ParentId`, `MainPartId`, `Type`, `Mode`, `LevelCode`, `CustId`, `CustName`, `CustStoreId`, `CustStoreCode`, `CustStoreName`, `IsWaitTransfer`, `Enable`, `IsMultiCheck`, `IsReceipt`, `IsAllowTrans`, `NameSort`, `LastCloseTime`, `Remark`, `CreatedById`, `CreatedAt`, `UpdatedById`, `UpdatedAt`, `DeletedById`, `DeletedAt`, `Deleted`) VALUES ('WH9999997791', '测试2', 'WH9999998006', 'DT0000000001', 0, 4, 2, NULL, NULL, NULL, NULL, NULL, 0, 0, 1, 1, 1, 3, NULL, '123', '76a20000-3e08-0016-598d-08d7f3f2461a', '2020-08-13 15:42:02', NULL, NULL, NULL, NULL, 0);
INSERT INTO `whcenter`.`tb_warehouse` (`Id`, `Name`, `ParentId`, `MainPartId`, `Type`, `Mode`, `LevelCode`, `CustId`, `CustName`, `CustStoreId`, `CustStoreCode`, `CustStoreName`, `IsWaitTransfer`, `Enable`, `IsMultiCheck`, `IsReceipt`, `IsAllowTrans`, `NameSort`, `LastCloseTime`, `Remark`, `CreatedById`, `CreatedAt`, `UpdatedById`, `UpdatedAt`, `DeletedById`, `DeletedAt`, `Deleted`) VALUES ('WH9999997792', '测试1', 'WH9999998006', 'DT0000000001', 0, 4, 2, NULL, NULL, NULL, NULL, NULL, 0, 0, 1, 1, 1, 3, NULL, '123', '76a20000-3e08-0016-598d-08d7f3f2461a', '2020-08-13 15:33:07', NULL, NULL, NULL, NULL, 0);
INSERT INTO `whcenter`.`tb_warehouse` (`Id`, `Name`, `ParentId`, `MainPartId`, `Type`, `Mode`, `LevelCode`, `CustId`, `CustName`, `CustStoreId`, `CustStoreCode`, `CustStoreName`, `IsWaitTransfer`, `Enable`, `IsMultiCheck`, `IsReceipt`, `IsAllowTrans`, `NameSort`, `LastCloseTime`, `Remark`, `CreatedById`, `CreatedAt`, `UpdatedById`, `UpdatedAt`, `DeletedById`, `DeletedAt`, `Deleted`) VALUES ('WH9999997793', '优工ZR20080006李钰泽', 'WH9999998559', 'DT0000000001', 0, 1, 2, NULL, NULL, NULL, NULL, NULL, 0, 0, NULL, 1, 1, 15, NULL, NULL, NULL, '2020-08-12 19:00:22', NULL, NULL, NULL, NULL, 0);
INSERT INTO `whcenter`.`tb_warehouse` (`Id`, `Name`, `ParentId`, `MainPartId`, `Type`, `Mode`, `LevelCode`, `CustId`, `CustName`, `CustStoreId`, `CustStoreCode`, `CustStoreName`, `IsWaitTransfer`, `Enable`, `IsMultiCheck`, `IsReceipt`, `IsAllowTrans`, `NameSort`, `LastCloseTime`, `Remark`, `CreatedById`, `CreatedAt`, `UpdatedById`, `UpdatedAt`, `DeletedById`, `DeletedAt`, `Deleted`) VALUES ('WH9999997794', '测试二级仓0028', 'WH9999999998', 'DT0000000001', 0, 0, 2, NULL, NULL, NULL, NULL, NULL, 0, 1, NULL, 1, 1, 9, NULL, '不找零', '4f500000-4c4f-0200-7208-08d30c1369fa', '2020-08-12 14:22:44', '5f2cdd40-1b38-4519-96c4-8fb582a79185', '2020-08-12 14:25:46', NULL, NULL, 0);
INSERT INTO `whcenter`.`tb_warehouse` (`Id`, `Name`, `ParentId`, `MainPartId`, `Type`, `Mode`, `LevelCode`, `CustId`, `CustName`, `CustStoreId`, `CustStoreCode`, `CustStoreName`, `IsWaitTransfer`, `Enable`, `IsMultiCheck`, `IsReceipt`, `IsAllowTrans`, `NameSort`, `LastCloseTime`, `Remark`, `CreatedById`, `CreatedAt`, `UpdatedById`, `UpdatedAt`, `DeletedById`, `DeletedAt`, `Deleted`) VALUES ('WH9999997795', 'G实体二级租赁啊', 'WH9999999998', 'DT0000000001', 0, 4, 2, NULL, NULL, NULL, NULL, NULL, 0, 0, 0, 0, 0, 8, NULL, NULL, '76a20000-3e08-0016-9b00-08d81b48e69e', '2020-08-12 13:44:47', NULL, NULL, '7e351f96-d9d9-4504-89d4-41323a0ee321', '2020-08-14 15:04:57', 1);
INSERT INTO `whcenter`.`tb_warehouse` (`Id`, `Name`, `ParentId`, `MainPartId`, `Type`, `Mode`, `LevelCode`, `CustId`, `CustName`, `CustStoreId`, `CustStoreCode`, `CustStoreName`, `IsWaitTransfer`, `Enable`, `IsMultiCheck`, `IsReceipt`, `IsAllowTrans`, `NameSort`, `LastCloseTime`, `Remark`, `CreatedById`, `CreatedAt`, `UpdatedById`, `UpdatedAt`, `DeletedById`, `DeletedAt`, `Deleted`) VALUES ('WH9999997796', 'G实体二级门店啊', 'WH9999999998', 'DT0000000001', 0, 2, 2, 'CR0000005458', 'Fay', NULL, NULL, NULL, 0, 0, 1, 0, 0, 8, NULL, NULL, '76a20000-3e08-0016-9b00-08d81b48e69e', '2020-08-12 13:44:47', NULL, NULL, '7e351f96-d9d9-4504-89d4-41323a0ee321', '2020-08-14 15:05:00', 1);
INSERT INTO `whcenter`.`tb_warehouse` (`Id`, `Name`, `ParentId`, `MainPartId`, `Type`, `Mode`, `LevelCode`, `CustId`, `CustName`, `CustStoreId`, `CustStoreCode`, `CustStoreName`, `IsWaitTransfer`, `Enable`, `IsMultiCheck`, `IsReceipt`, `IsAllowTrans`, `NameSort`, `LastCloseTime`, `Remark`, `CreatedById`, `CreatedAt`, `UpdatedById`, `UpdatedAt`, `DeletedById`, `DeletedAt`, `Deleted`) VALUES ('WH9999997797', 'G实体二级优工啊', 'WH9999999998', 'DT0000000001', 0, 1, 2, NULL, NULL, NULL, NULL, NULL, 0, 0, NULL, 0, 0, 8, NULL, NULL, '76a20000-3e08-0016-9b00-08d81b48e69e', '2020-08-12 11:15:33', NULL, NULL, '7e351f96-d9d9-4504-89d4-41323a0ee321', '2020-08-14 15:05:05', 1);
INSERT INTO `whcenter`.`tb_warehouse` (`Id`, `Name`, `ParentId`, `MainPartId`, `Type`, `Mode`, `LevelCode`, `CustId`, `CustName`, `CustStoreId`, `CustStoreCode`, `CustStoreName`, `IsWaitTransfer`, `Enable`, `IsMultiCheck`, `IsReceipt`, `IsAllowTrans`, `NameSort`, `LastCloseTime`, `Remark`, `CreatedById`, `CreatedAt`, `UpdatedById`, `UpdatedAt`, `DeletedById`, `DeletedAt`, `Deleted`) VALUES ('WH9999997798', '二级数据仓测试的呀', 'WH9999998745', 'DT0000000001', 0, 3, 2, NULL, NULL, NULL, NULL, NULL, 0, 0, NULL, 1, 1, 9, NULL, NULL, '5fac0000-3e0a-0016-8f6e-08d70b4d97cd', '2020-08-11 20:29:34', NULL, NULL, NULL, NULL, 0);
INSERT INTO `whcenter`.`tb_warehouse` (`Id`, `Name`, `ParentId`, `MainPartId`, `Type`, `Mode`, `LevelCode`, `CustId`, `CustName`, `CustStoreId`, `CustStoreCode`, `CustStoreName`, `IsWaitTransfer`, `Enable`, `IsMultiCheck`, `IsReceipt`, `IsAllowTrans`, `NameSort`, `LastCloseTime`, `Remark`, `CreatedById`, `CreatedAt`, `UpdatedById`, `UpdatedAt`, `DeletedById`, `DeletedAt`, `Deleted`) VALUES ('WH9999997799', '二级数据仓测试的', 'WH9999998745', 'DT0000000001', 0, 3, 2, NULL, NULL, NULL, NULL, NULL, 0, 0, NULL, 1, 1, 8, NULL, NULL, '5fac0000-3e0a-0016-8f6e-08d70b4d97cd', '2020-08-11 20:26:05', NULL, NULL, NULL, NULL, 0);
# 2024-8-23 15:30:16生成新日志文件
FLUSH LOGS;
SHOW BINARY LOG STATUS;使用完备还原至备份时间点
[root@mysql84 mysqlbak]# cd /mysqldata/mysqlbak/whcenter/
[root@mysql84 whcenter]# ll
total 17268
-rw-r--r-- 1 root root 2925646 Aug 20 16:37 whcenter-20240820.tar.gz
-rw-r--r-- 1 root root 2925649 Aug 21 01:00 whcenter-20240821.tar.gz
-rw-r--r-- 1 root root 2925649 Aug 22 01:00 whcenter-20240822.tar.gz
-rw-r--r-- 1 root root 149562 Aug 20 16:37 whcenter-tb_instockdetail-20240820.tar.gz
-rw-r--r-- 1 root root 149563 Aug 21 01:00 whcenter-tb_instockdetail-20240821.tar.gz
-rw-r--r-- 1 root root 149563 Aug 22 01:00 whcenter-tb_instockdetail-20240822.tar.gz
-rw-r--r-- 1 root root 88805 Aug 20 16:37 whcenter-tb_instockextra-20240820.tar.gz
-rw-r--r-- 1 root root 88806 Aug 21 01:00 whcenter-tb_instockextra-20240821.tar.gz
-rw-r--r-- 1 root root 88806 Aug 22 01:00 whcenter-tb_instockextra-20240822.tar.gz
-rw-r--r-- 1 root root 227331 Aug 20 16:37 whcenter-tb_instockinfo-20240820.tar.gz
-rw-r--r-- 1 root root 227336 Aug 21 01:00 whcenter-tb_instockinfo-20240821.tar.gz
-rw-r--r-- 1 root root 227336 Aug 22 01:00 whcenter-tb_instockinfo-20240822.tar.gz
-rw-r--r-- 1 root root 518062 Aug 20 16:37 whcenter-tb_outstockdetail-20240820.tar.gz
-rw-r--r-- 1 root root 518064 Aug 21 01:00 whcenter-tb_outstockdetail-20240821.tar.gz
-rw-r--r-- 1 root root 518062 Aug 22 01:00 whcenter-tb_outstockdetail-20240822.tar.gz
-rw-r--r-- 1 root root 423253 Aug 20 16:37 whcenter-tb_outstockextra-20240820.tar.gz
-rw-r--r-- 1 root root 423256 Aug 21 01:00 whcenter-tb_outstockextra-20240821.tar.gz
-rw-r--r-- 1 root root 423253 Aug 22 01:00 whcenter-tb_outstockextra-20240822.tar.gz
-rw-r--r-- 1 root root 947187 Aug 20 16:37 whcenter-tb_outstockinfo-20240820.tar.gz
-rw-r--r-- 1 root root 947186 Aug 21 01:00 whcenter-tb_outstockinfo-20240821.tar.gz
-rw-r--r-- 1 root root 947186 Aug 22 01:00 whcenter-tb_outstockinfo-20240822.tar.gz
-rw-r--r-- 1 root root 8601 Aug 20 16:37 whcenter-tb_ownerinfo-20240820.tar.gz
-rw-r--r-- 1 root root 8602 Aug 21 01:00 whcenter-tb_ownerinfo-20240821.tar.gz
-rw-r--r-- 1 root root 8602 Aug 22 01:00 whcenter-tb_ownerinfo-20240822.tar.gz
-rw-r--r-- 1 root root 99901 Aug 20 16:37 whcenter-tb_stockinfo-20240820.tar.gz
-rw-r--r-- 1 root root 99903 Aug 21 01:00 whcenter-tb_stockinfo-20240821.tar.gz
-rw-r--r-- 1 root root 99902 Aug 22 01:00 whcenter-tb_stockinfo-20240822.tar.gz
-rw-r--r-- 1 root root 17138 Aug 20 16:37 whcenter-tb_supplier-20240820.tar.gz
-rw-r--r-- 1 root root 17138 Aug 21 01:00 whcenter-tb_supplier-20240821.tar.gz
-rw-r--r-- 1 root root 17137 Aug 22 01:00 whcenter-tb_supplier-20240822.tar.gz
-rw-r--r-- 1 root root 206822 Aug 20 16:37 whcenter-tb_warehouse-20240820.tar.gz
-rw-r--r-- 1 root root 206823 Aug 21 01:00 whcenter-tb_warehouse-20240821.tar.gz
-rw-r--r-- 1 root root 206823 Aug 22 01:00 whcenter-tb_warehouse-20240822.tar.gz
-rw-r--r-- 1 root root 154326 Aug 20 16:37 whcenter-tb_warehousemanager-20240820.tar.gz
-rw-r--r-- 1 root root 154326 Aug 21 01:00 whcenter-tb_warehousemanager-20240821.tar.gz
-rw-r--r-- 1 root root 154326 Aug 22 01:00 whcenter-tb_warehousemanager-20240822.tar.gz
-rw-r--r-- 1 root root 69236 Aug 20 16:37 whcenter-tb_whaddress-20240820.tar.gz
-rw-r--r-- 1 root root 69237 Aug 21 01:00 whcenter-tb_whaddress-20240821.tar.gz
-rw-r--r-- 1 root root 69237 Aug 22 01:00 whcenter-tb_whaddress-20240822.tar.gz
-rw-r--r-- 1 root root 13409 Aug 20 16:37 whcenter-tb_wharea-20240820.tar.gz
-rw-r--r-- 1 root root 13411 Aug 21 01:00 whcenter-tb_wharea-20240821.tar.gz
-rw-r--r-- 1 root root 13411 Aug 22 01:00 whcenter-tb_wharea-20240822.tar.gz
-rw-r--r-- 1 root root 8515 Aug 20 16:37 whcenter-tb_whcustinfo-20240820.tar.gz
-rw-r--r-- 1 root root 8514 Aug 21 01:00 whcenter-tb_whcustinfo-20240821.tar.gz
-rw-r--r-- 1 root root 8514 Aug 22 01:00 whcenter-tb_whcustinfo-20240822.tar.gz
[root@mysql84 whcenter]# tar -zxvf whcenter-20240822.tar.gz
whcenter-20240822.sql
[root@mysql84 whcenter]# mysql -h127.0.0.1 -uroot -p < whcenter-20240822.sql
登录MySQL
mysql> SHOW BINARY LOG STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 | 158 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
查看
[root@mysql84 whcenter]# mysqlbinlog -vv --base64-output=decode-rows --start-datetime='2024-8-22 01:00:0' --stop-datetime='2024-8-22 15:43:20' -d whcenter /mysqldata/data/binlog.000002
240822 14:49:42
98376849
98399861
还原：
mysqlbinlog --start-position=98376849 --stop-position=98399861 -d whcenter /mysqldata/data/binlog.000002 | mysql -uroot -h127.0.0.1 -p(误!)删除数据还原(通过binlog)
binlog解析指令在MySQL 8.4前后有些区别，这里仅以MySQL8.4以后版本为例
还原工具
总结前人经验，这里使用此通用工具解析、反向生成回滚SQL，能够快速生成误删除回滚SQL、更新语句反向更新SQL等，详情见官方文档。
mysqlbinlog可以离线解析MySQL Binlog, 此工具必须连接源库，也是为了库表筛选；
这里直接以上一步的SQL场景，做还原语句
git clone https://github.com/michael-liumh/binlog2sql.git && cd binlog2sql
python -V
版本要求大于3.8.5
python -m venv ./.venv
source /root/binlog2sql/.venv/bin/activate
pip install -r requirements.txt开始解析
python /root/reverse_sql/reverse_sql_json.py -ot tb_whcustinfo -H 127.0.0.1 -P 3306 -u root -p 'Lunz@2017' -d whcenter --binlog-file='/mysqldata/data/binlog.000009' --start-time="2024-8-23 15:00:00" --end-time="2024-8-23 15:40:00"此表只做了TRUNCATE, 所以无法生成反向SQL
tb_instockdetail
python /root/reverse_sql/reverse_sql_json.py -ot tb_instockdetail -H 127.0.0.1 -P 3306 -u root -p 'Lunz@2017' -d whcenter --binlog-file='/mysqldata/data/binlog.000009' --start-time="2024-8-23 15:00:00" --end-time="2024-8-23 15:40:00"tb_stockinfo
python /root/reverse_sql/reverse_sql_json.py -ot tb_stockinfo -H 127.0.0.1 -P 3306 -u root -p 'Lunz@2017' -d whcenter --binlog-file='/mysqldata/data/binlog.000009' --start-time="2024-8-23 15:00:00" --end-time="2024-8-23 15:40:00"python /root/reverse_sql/reverse_sql_json.py -ot tb_stockinfo -H 127.0.0.1 -P 3306 -u root -p 'Lunz@2017' -d whcenter --binlog-file='/mysqldata/data/binlog.000009' --start-time="2024-8-23 15:00:00" --end-time="2024-8-23 15:40:00" --replacetb_warehouse
python /root/reverse_sql/reverse_sql_json.py -ot tb_warehouse -H 127.0.0.1 -P 3306 -u root -p 'Lunz@2017' -d whcenter --binlog-file='/mysqldata/data/binlog.000009' --start-time="2024-8-23 15:00:00" --end-time="2024-8-23 15:40:00"replace
python /root/zrbin2sql/zrbin2sql.py -ot tb_warehouse -H 127.0.0.1 -P 3306 -u root -p 'Lunz@2017' -d whcenter --binlog-file='/mysqldata/data/binlog.000009' --start-time="2024-8-23 15:00:00" --end-time="2024-8-23 15:40:00" --replacereplace-without-null
UPDATE tb_wharea SET WarehouseId=NULL WHERE Id=943;
UPDATE tb_wharea SET IsDefault=1 WHERE Id=943;
UPDATE tb_wharea SET CityCode=NULL WHERE Id=943;
UPDATE tb_wharea SET ProCode=NULL WHERE Id=943;
UPDATE tb_wharea SET WarehouseId=NULL WHERE Id=942;
UPDATE tb_wharea SET IsDefault=1 WHERE Id=942;
UPDATE tb_wharea SET CityCode=NULL WHERE Id=942;
UPDATE tb_wharea SET ProCode=NULL WHERE Id=942;
UPDATE tb_wharea SET WarehouseId=NULL WHERE Id=936;
UPDATE tb_wharea SET IsDefault=1 WHERE Id=936;
UPDATE tb_wharea SET CityCode=NULL WHERE Id=936;
UPDATE tb_wharea SET ProCode=NULL WHERE Id=936;mysqlbinlog -vv --base64-output=decode-rows --start-datetime='2024-9-9 15:00:00' --stop-datetime='2024-9-9 16:47:00' -d whcenter /mysqldata/data/binlog.000010
python /root/zrbin2sql/zrbin2sql.py -ot tb_wharea -H 127.0.0.1 -P 3306 -u root -p 'Lunz@2017' -d whcenter --binlog-file='/mysqldata/data/binlog.000010' --start-time="2024-9-9 15:00:00" --end-time="2024-9-9 16:47:00" --replace-without-null-- 更新前后字段值均为NULL，直接去掉字段更新 WarehouseId 字段
-- SQL执行时间:2024-09-09 16:44:59
-- 原生sql:
-- UPDATE `whcenter`.`tb_wharea` SET `Id`=936,`WarehouseId`=NULL,`IsDefault`=1,`ProCode`=NULL,`CityCode`=NULL,`CreatedById`='2a60d7a8-43dc-4264-b8db-65850f778588',`CreatedAt`='2023-05-23 18:07:27',`UpdatedById`=NULL,`UpdatedAt`=NULL,`DeletedById`='2a60d7a8-43dc-4264-b8db-65850f778588',`DeletedAt`='2023-05-23 18:07:28',`Deleted`=1 WHERE `Id`=936 AND `WarehouseId` IS NULL AND `IsDefault`=1 AND `ProCode`='430000' AND `CityCode` IS NULL AND `CreatedById`='2a60d7a8-43dc-4264-b8db-65850f778588' AND `CreatedAt`='2023-05-23 18:07:27' AND `UpdatedById` IS NULL AND `UpdatedAt` IS NULL AND `DeletedById`='2a60d7a8-43dc-4264-b8db-65850f778588' AND `DeletedAt`='2023-05-23 18:07:28' AND `Deleted`=1;
-- 回滚sql:
REPLACE INTO `whcenter`.`tb_wharea` (['Id', 'IsDefault', 'ProCode', 'CreatedById', 'CreatedAt', 'DeletedById', 'DeletedAt', 'Deleted']) VALUES (936,1,'430000','2a60d7a8-43dc-4264-b8db-65850f778588','2023-05-23 18:07:27','2a60d7a8-43dc-4264-b8db-65850f778588','2023-05-23 18:07:28',1);
-- ----------------------------------------------------------
-- SQL执行时间:2024-09-09 16:44:58
-- 更新前有值，但SET为了NULL，还原回原值 WarehouseId 字段
-- 原生sql:
-- UPDATE `whcenter`.`tb_wharea` SET `Id`=942,`WarehouseId`=NULL,`IsDefault`=1,`ProCode`='370000',`CityCode`='370300',`CreatedById`='f00a2eb9-fe02-436c-9b42-b443deb4150a',`CreatedAt`='2024-05-04 16:07:57',`UpdatedById`='f00a2eb9-fe02-436c-9b42-b443deb4150a',`UpdatedAt`='2024-05-04 16:08:02',`DeletedById`=NULL,`DeletedAt`=NULL,`Deleted`=0 WHERE `Id`=942 AND `WarehouseId`='WH9999996514' AND `IsDefault`=1 AND `ProCode`='370000' AND `CityCode`='370300' AND `CreatedById`='f00a2eb9-fe02-436c-9b42-b443deb4150a' AND `CreatedAt`='2024-05-04 16:07:57' AND `UpdatedById`='f00a2eb9-fe02-436c-9b42-b443deb4150a' AND `UpdatedAt`='2024-05-04 16:08:02' AND `DeletedById` IS NULL AND `DeletedAt` IS NULL AND `Deleted`=0;
-- 回滚sql:
REPLACE INTO `whcenter`.`tb_wharea` (['Id', 'WarehouseId', 'IsDefault', 'ProCode', 'CityCode', 'CreatedById', 'CreatedAt', 'UpdatedById', 'UpdatedAt', 'Deleted']) VALUES (942,'WH9999996514',1,'370000','370300','f00a2eb9-fe02-436c-9b42-b443deb4150a','2024-05-04 16:07:57','f00a2eb9-fe02-436c-9b42-b443deb4150a','2024-05-04 16:08:02',0);
-- 原来字段有值，但SET成了新值，则保留新值 IsDefault 字段
-- ----------------------------------------------------------
-- SQL执行时间:2024-09-09 16:44:58
-- 原生sql:
-- UPDATE `whcenter`.`tb_wharea` SET `Id`=943,`WarehouseId`=NULL,`IsDefault`=1,`ProCode`='370000',`CityCode`='370200',`CreatedById`='f00a2eb9-fe02-436c-9b42-b443deb4150a',`CreatedAt`='2024-05-04 16:08:11',`UpdatedById`=NULL,`UpdatedAt`=NULL,`DeletedById`=NULL,`DeletedAt`=NULL,`Deleted`=0 WHERE `Id`=943 AND `WarehouseId` IS NULL AND `IsDefault` IS NULL AND `ProCode`='370000' AND `CityCode`='370200' AND `CreatedById`='f00a2eb9-fe02-436c-9b42-b443deb4150a' AND `CreatedAt`='2024-05-04 16:08:11' AND `UpdatedById` IS NULL AND `UpdatedAt` IS NULL AND `DeletedById` IS NULL AND `DeletedAt` IS NULL AND `Deleted`=0;
-- 回滚sql:
REPLACE INTO `whcenter`.`tb_wharea` (['Id', 'IsDefault', 'ProCode', 'CityCode', 'CreatedById', 'CreatedAt', 'Deleted']) VALUES (943,1,'370000','370200','f00a2eb9-fe02-436c-9b42-b443deb4150a','2024-05-04 16:08:11',0);