MySQL 定时备份数据库详解

一. mysqldump命令备份

数据在MySQL中提供了命令行导出数据库数据以及文件的一种方便的工具mysqldump,我们可以通过命令行直接实现数据库内容的导出dump,首先我们简单了解一下mysqldump命令用法:

MySQL 不输出列名 可以用 -N 或者 --skip-column-names 参数

MySQLdump常用

mysqldump -u root -p --databases 数据库1 数据库2 > xxx.sql

二. mysqldump常用操作示例

1. 备份全部数据库的数据和结构

mysqldump -uroot -p123456 -A > /data/mysqlDump/mydb.sql

2.备份全部数据库的结构（加 -d 参数）

mysqldump -uroot -p123456 -A -d > /data/mysqlDump/mydb.sql

3. 备份全部数据库的数据(加 -t 参数)

mysqldump -uroot -p123456 -A -t > /data/mysqlDump/mydb.sql

4.备份单个数据库的数据和结构(,数据库名mydb)

mysqldump -uroot-p123456 mydb > /data/mysqlDump/mydb.sql

5. 备份单个数据库的结构

mysqldump -uroot -p123456 mydb -d > /data/mysqlDump/mydb.sql

6. 备份单个数据库的数据

mysqldump -uroot -p123456 mydb -t > /data/mysqlDump/mydb.sql

7. 备份多个表的数据和结构（数据，结构的单独备份方法与上同）

mysqldump -uroot -p123456 mydb t1 t2 > /data/mysqlDump/mydb.sql

8. 一次备份多个数据库

mysqldump -uroot -p123456 --databases db1 db2 > /data/mysqlDump/mydb.sql

三. 还原 MySQL 备份内容

有两种方式还原，第一种是在 MySQL 命令行中，第二种是使用 SHELL 行完成还原

1. 在系统命令行中，输入如下实现还原：

mysql -uroot -p123456 < /data/mysqlDump/mydb.sql

2. 在登录进入mysql系统中,通过source指令找到对应系统中的文件进行还原：

mysql> source /data/mysqlDump/mydb.sql

四.示例 单个数据库备份Mysql 8.0

@echo off
setlocal enabledelayedexpansion
:: 设置MySQL可执行文件、登录信息和备份目录
set MYSQL_BIN_PATH="C:\Program Files\MySQL\MySQL Server 8.0"
set MYSQL_USER=root
set MYSQL_PASSWORD=root
set BACKUP_DIR="d:\mysql_backups"
set DATABASE="jjxcb"
set TIMESTAMP=%Date:~0,4%%Date:~5,2%%Date:~8,2%%Time:~0,2%%Time:~3,2%%Time:~6,2%
:: 创建备份目录
if not exist %BACKUP_DIR% (mkdir %BACKUP_DIR%)
%MYSQL_BIN_PATH%"\bin\mysqldump.exe" -u%MYSQL_USER% -p%MYSQL_PASSWORD% %DATABASE% >%BACKUP_DIR%\%TIMESTAMP%.sql
echo Backup completed.

五.调试

如有中文乱码,用文本编辑软件 如notepad++ 修改字符编码由 Utf-8 修改为ANSI并保存.

@echo off
@echo 标题
titlebat命令行
 
@echo 获取系统年月日 其中~0,4 表示从第 0 位开始截取 4 个字符
echo %Date:~0,4%%Date:~5,2%%Date:~8,2%
rem 获取时分秒
::备注命令
@echo 获取时分秒
::打印命令
echo %time:~0,2%%time:~3,2%%time:~6,2%
::调试窗口不会退出,按其他键可退出,便于窗口打印调试
pause

调试备份全部数据库一种实现方式 用指令 同上指令多个数据库 mysqldump -uroot -p123456 --databases db1 db2

@echo off
setlocal enabledelayedexpansion
 
:: 设置MySQL可执行文件、登录信息和备份目录
set MYSQL_BIN_PATH="C:\Program Files\MySQL\MySQL Server 8.0\bin"
set MYSQL_USER=root
set MYSQL_PASSWORD=your_password
set BACKUP_DIR="C:\mysql_backups"
 
:: 设置日期格式为 yyyy-MM-dd
for /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set date=%%c-%%a-%%b)
set TIMESTAMP=%date%
 
:: 创建备份目录
if not exist %BACKUP_DIR% (mkdir %BACKUP_DIR%)
 
:: 获取所有数据库列表
"%MYSQL_BIN_PATH%\mysql.exe" --user=%MYSQL_USER% --password=%MYSQL_PASSWORD% --execute="SHOW DATABASES;" --skip-column-names > %BACKUP_DIR%\databases.txt
 
:: 备份每个数据库并压缩
for /F "tokens=*" %%A in (%BACKUP_DIR%\databases.txt) do (
  if not "%%A"=="information_schema" (
    if not "%%A"=="performance_schema" (
      if not "%%A"=="mysql" (
        if not "%%A"=="sys" (
          echo Backing up %%A...
          "%MYSQL_BIN_PATH%\mysqldump.exe" --user=%MYSQL_USER% --password=%MYSQL_PASSWORD% --databases %%A --result-file=%BACKUP_DIR%\%%A_%TIMESTAMP%.sql
          "%MYSQL_BIN_PATH%\gzip.exe" %BACKUP_DIR%\%%A_%TIMESTAMP%.sql
        )
      )
    )
  )
)
:: 删除数据库列表文件
del %BACKUP_DIR%\databases.txt
echo Backup completed.

六 创建一个新的任务计划程序。

打开“控制面板”并选择“任务计划程序”。
在右侧操作栏中点击“创建基本任务”。
输入任务名称和描述，然后点击“下一步”。
选择“每天”，点击“下一步”。
设置开始时间为晚上23点，点击“下一步”。
选择“启动程序”，点击“下一步”。
点击“浏览”，选择mysql_backup.bat文件，然后点击“下一步”。
点击“完成”以创建任务