mysql 数据库备及移动当天数据到历史表里 window下可用-优快云博客

1 数据移动到历史表中，同时删除当天数据

test-move-record.bat

c:

cd C:\FQ_ManageServer\mysqlback


mysql.exe  -h 10.71.1.236 -u"root" -p"" test --execute="replace into test_history select * from test;delete from test ;commit;select count(1),now() as date ,'move' from test_history;" >>C:\FQ_ManageServer\mysqlback\history.txt

echo ---------------move end----------------- >>C:\FQ_ManageServer\mysqlback\history.txt
exit

结果

count(1)	date	move
4	2017-06-14 13:30:39	move
---------------move end-----------------

2 备份数据且把数据移动到历史表中

start_backup.bat

c:

cd C:\FQ_ManageServer\mysqlback

mysqldump -h 10.71.1.236 -uroot -p test>C:\FQ_ManageServer\mysqlback\dbbackup\DB_backup_test%date:~0,4%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%%time:~6,2%%time:~9,2%
start test-move-record.bat

echo ---------------backup end-----------------  >>C:\FQ_ManageServer\mysqlback\history.txt

3 从历史表中恢复数据和上面类似

test-recovery-record.bat

1 c:
2 
3 cd C:\FQ_ManageServer\mysqlback
4 
5 
6 mysql.exe  -h 10.71.1.236 -u"root" -p"" test --execute="replace into test select * from test_history ;commit;select count(1),now() as date, 'recovery' from test;" >>C:\FQ_ManageServer\mysqlback\history.txt
7 
8 echo ---------------recovery end----------------- >>C:\FQ_ManageServer\mysqlback\history.txt