windows mysql备份数据库及清除过期数据（bat脚本）

查看数据库使用的存储引擎

MariaDB [(none)]> show engines \G;
*************************** 1. row ***************************
      Engine: CSV
     Support: YES
     Comment: Stores tables as CSV files
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MyISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: Non-transactional engine with good performance and small data foot
print
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: SEQUENCE
     Support: YES
     Comment: Generated tables filled with sequential values
Transactions: YES
          XA: NO
  Savepoints: YES
*************************** 6. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, foreign keys and encrypt
ion for tables
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 7. row ***************************
      Engine: Aria
     Support: YES
     Comment: Crash-safe tables with MyISAM heritage
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
8 rows in set (0.001 sec)

ERROR: No query specified

Support:DEFAULT的为默认存储引擎。 

InnoDB(默认数据库引擎)

 备份所有数据库

D:\Program Files\MariaDB 10.3\bin>mysqldump -u root -h localhost -p --all-databases > d:\mysqldbback\all.sql

 备份指定数据库

D:\Program Files\MariaDB 10.3\bin>mysqldump -u root -h localhost -p singledatabase > d:\mysqldbback\singledatabase.sql

还原所有数据库

MariaDB [(none)]> source d:\mysqldbback\all.sql

自动备份脚本

@echo off  

color 0D  

MODE con: COLS=71 LINES=25 

title mysql数据库自动备份脚本(任务计划)--脚本作者:荆盼

set "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"

set "obj_dir= d:\mysqldbback"

md %obj_dir%



@echo 开始备份数据库。

mysqldump -u root -h localhost -proot --all-databases  > %obj_dir%\%Ymd%all.sql

@echo 删除七天前备份的文件。

forfiles /p "d:\mysqldbback" /s /m *.* /d -7 /c "cmd /c del @path"

@echo 数据库备份完成，3秒后程序退出。

ping /n 3 127.0.0.1 >nul

exit

@echo off  
 
color 0D  
 
MODE con: COLS=71 LINES=25 
 
title mysql数据库自动备份脚本(任务计划)--脚本作者:荆盼
 
set "Ymd=%Date:~0,4%%Date:~5,2%%Date:~8,2%%Time:~0,2%%Time:~3,2%%Time:~6,2%"
 
set "obj_dir= c:\mysqlback"
 
md %obj_dir%
 
 
 
@echo 开始备份数据库。
 
mysqldump -u root -h localhost -proot --all-databases  > %obj_dir%\%Ymd%all.sql
 
@echo 删除2天前备份的文件。
 
forfiles /p "c:\mysqlback" /s /m *.* /d -2 /c "cmd /c del @path"
 
@echo 数据库备份完成，10秒后程序退出。
 
ping /n 10 127.0.0.1 >nul
 
exit