mysqldump的详细使用教程，定时备份脚本

一、mysqldump的简介

mysqldump是一个用于备份MySQL数据库的命令行工具。它可以将数据库结构和数据导出到一个SQL文件中，以便在需要时进行恢复或迁移数据库。mysqldump可以备份整个数据库、特定的表或者特定的查询结果。

使用mysqldump可以轻松地创建数据库的备份，并且备份文件可以在不同的MySQL服务器上进行恢复。它还可以用于将数据库从一个服务器迁移到另一个服务器，或者将数据导出到其他数据存储系统中。

mysqldump支持许多选项和参数，可以根据需要进行定制备份的方式。例如，可以选择是否包括数据库的创建语句、是否包括表的创建语句、是否包括数据等。此外，还可以设置备份文件的压缩格式，以减小备份文件的大小。

总之，mysqldump是一个强大的工具，可以帮助用户轻松地备份和恢复MySQL数据库，同时还能够灵活地定制备份的方式。它是MySQL数据库管理中非常重要的一个工具，也是数据库管理员和开发人员经常使用的工具之一。

二、mysqldump的具体使用

mysqldump命令是MySQL数据库管理系统中用于备份数据库的命令行工具。它允许用户将数据库中的表和数据导出到一个SQL脚本文件中，以便在需要时可以恢复数据库。

使用mysqldump命令可以备份整个数据库、单个表或者指定的查询结果。该命令还可以设置备份文件的格式、压缩方式、以及备份时是否包含数据库的结构等选项。

1、各参数含义

以下是mysqldump命令的基本语法：

mysqldump -u [username] -p [password] [options] [database] > [backup_file.sql]

其中，-u和-p参数分别用于指定数据库的用户名和密码，[options]用于设置备份的选项，[database]是要备份的数据库名称，> [backup_file.sql]表示将备份数据输出到一个SQL脚本文件中。
当使用mysqldump命令时，可以使用以下选项参数：

• 登录选项参数

  参数	含义
  -h [host]	指定要连接的MySQL服务器的主机名或IP地址
  -P [port]	指定要连接的MySQL服务器的端口号
  -u [username]	指定要使用的用户名
  -p [password]	提示输入密码，如果不指定密码则会要求输入

• 备份选项参数

  --r, --result-file=文件名：指定备份文件保存路径和文件名。
  - --all-databases：备份所有数据库（包括系统数据库）。
  - --all-tables=数据库名：备份指定数据库中的所有表结构。
  - --no-create-info：不输出创建表的 SQL 语句。
  - --no-data：不输出表的数据内容（只输出表结构）。
  - --where=条件表达式：可选参数，用于指定备份的过滤条件（例如，仅备份某些表）。
  - --compact：压缩备份文件，减少存储空间占用。
  - --skip-extended-insert：使用较简单的插入语句，减少输出大小。
  - --tables：备份指定的表。
  - --where：指定备份数据的条件。
  - --lock-tables：在备份时锁定表以确保数据一致性。
  - --single-transaction：使用事务来确保备份的数据一致性。
  - --compress：使用压缩格式备份数据。
  - --add-drop-database：在备份文件中包含 DROP DATABASE 语句，用于恢复时删除现有数据库。
  - --add-drop-table：在备份文件中包含 DROP TABLE 语句，用于恢复时删除现有表。
  - --extended-insert：使用扩展的 INSERT 语句来提高备份和恢复的性能。
  - --complete-insert：使用完整的 INSERT 语句，而不是多行的 INSERT 语句。
  - --routines：备份存储过程和函数。
  - --triggers：备份触发器。
  - --events：备份事件。
  - --ignore-table：在备份中忽略指定的表。

这些参数允许用户根据需要指定备份的数据库、表，以及备份时的一些特定选项。

2、备份命令

**导出命令大全**
#导出education数据库里面的users表的表数据和表结构
	mysqldump -u[用户名] -h[ip] -p[密码] -P[端口号] 数据库名 表名 >导出的文件名.sql
	mysqldump -uroot -h127.0.0.1 -proot -P3306 education users>d:/user.sql
#导出包括系统数据库在内的所有数据库（all.sql默认保存在bin文件夹下面）
	mysqldump -uroot -proot --all-databases>all.sql
#导出多张表：
	mysqldump -uroot -proot --databases test --tables t1 t2>two.sql
#只导出表结构不导表数据，添加“-d”命令参数
    mysqldump -uroot -h127.0.0.1 -proot -P3306 -d education users>d:/user.sql
#只导出表数据不导表结构，添加“-t”命令参数
    mysqldump -uroot -h127.0.0.1 -proot -P3306 -t education users>d:/user.sql
#只导出test数据库的表结构
 导出：mysqldump -uroot -proot --no-data --databases test>s1.sql
 导入：mysql -uroot -proot -h127.0.0.1 -P3306 test<s1.sql
 
**导入命令大全**
 格式：mysql -h[ip] -P[(大写)端口] -u[用户名] -p[密码]  [数据库名] < d:XX.sql(路径) 
 	mysql -uroot -proot -h127.0.0.1 -P3306 education<d:/database.sql
 	mysql -uroot -proot -h127.0.0.1 -P3306 <d:/all_database.sql 
#命令行导入
  mysql> use test;
  mysql> source /home/test/database.sql

三、数据库备份脚本

以下shell脚本的主要目的是备份数据库，并在需要时删除旧的备份文件以节省空间。它使用 mysqldump 命令来执行数据库备份，将备份文件存储在指定的路径下，并根据文件数量的阈值来删除旧的备份文件。扫描文章末尾二维码关注公众号回复04可获取此脚本

#!/bin/bash

# 定义脚本名称、日期、用户等信息
# NAME="数据库备份"
# DATE=$(date +%Y-%m-%d-%H-%M)
# USER="***"

# 设置本机数据库登录信息
mysql_user="user"
mysql_password="passwd"
mysql_host="localhost"
mysql_port="3306"
mysql_charset="utf8mb4"

# 获取当前日期和时间，并赋值给 date_time 变量
date_time=$(date +%Y-%m-%d-%H-%M)

# 保存目录中的文件个数
count=10

# 备份路径
path=/***/

# 备份数据库 sql 文件并指定目录
mysqldump --all-databases --single-transaction --flush-logs --master-data=2 -h$mysql_host -u$mysql_user -p$mysql_password > $path_$(date +%Y%m%d_%H:%M).sql

# 检查上一行命令的返回值，如果不等于 0，则输出备份失败的消息并退出脚本
[ $? -ne 0 ] && echo "-----------------数据备份失败-----------------" && exit 1

# 列出指定路径下的备份文件，并通过 awk 提取最新的文件名赋值给 delfile 变量
delfile=$(ls -l -crt $path/*.sql | awk '{print $9 }' | head -1)

# 通过 wc -l 统计指定路径下的备份文件数量，并赋值给 number 变量
number=$(ls -l -crt $path/*.sql | awk '{print $9 }' | wc -l)

# 判断现在的备份数量是否大于阈值
if [ $number -gt $count ]
then
    # 删除最早的备份文件
    rm $delfile
    # 更新删除文件日志
    echo "-----------------已删除过去备份 sql $delfile-----------------"
fi

这段脚本是一个用于数据库备份的 Bash 脚本。它包含了以下主要功能：

1. 设置数据库连接信息：定义了数据库用户名、密码、主机、端口和字符集。
2. 获取当前日期和时间：使用 date 命令获取当前日期和时间，并将其存储在变量 date_time 中。
3. 指定备份路径和文件计数：定义了备份文件的保存目录和要保留的备份文件数量。
4. 执行数据库备份：使用 mysqldump 命令执行数据库备份，并将备份文件保存到指定的路径，文件名中包含当前日期和时间。
5. 检查备份是否成功：通过检查$?变量的返回值（0 表示成功，非 0 表示失败），输出备份成功或失败的消息。
6. 查找要删除的备份文件：使用 ls 命令列出备份目录中的所有备份文件，并使用 awk 命令提取文件名。
7. 判断备份文件数量是否超过阈值：计算目录中备份文件的数量，并与阈值进行比较。
8. 删除最早的备份文件：如果备份文件数量超过阈值，使用 rm 命令删除最早的备份文件，并输出删除文件的日志消息。

请注意，在运行这个脚本之前，你需要确保满足以下条件：

1. 你有适当的权限来执行 mysqldump 命令和对备份目录进行读写操作。
2. 你已经安装了 mysqldump 工具，并且可以通过命令行执行。
3. 你已经设置了正确的数据库连接信息（用户名、密码、主机、端口等）。
4. 备份目录和文件名路径是存在且可写的。

根据你的实际环境和需求，你可能需要对这些变量和命令进行适当的调整和验证。在测试和运行脚本之前，建议你仔细检查和理解每一行代码的功能和影响。

四、设置定时备份

要设置数据库定时备份，你可以使用操作系统的定时任务功能来自动执行 backup.sh 脚本。不同的操作系统有不同的方法来设置定时任务，但一般来说，你可以按照以下步骤进行操作：

1. 打开操作系统的任务调度器或定时任务管理工具。

   • 在 Linux 系统中，你可以使用 crontab 命令来管理定时任务。你可以使用 crontab -e 命令编辑自己的定时任务。
   • 在 Windows 系统中，你可以使用任务计划程序来创建定时任务。

2. 添加一个新的定时任务。

   • 在 Linux 中，使用 vi 或其他文本编辑器打开 crontab 文件，添加以下一行来执行 backup.sh 脚本：

     crontab -e
     * * * * * /path/to/backup.sh
     

     这将在每个小时的第 0 分钟执行 backup.sh 脚本。你可以根据需要修改时间间隔。

     各个 * 的含义如下：
     - 第一个 *：表示分钟字段，可以是 0 到 59 之间的任意整数。
     - 第二个 *：表示小时字段，可以是 0 到 23 之间的任意整数。
     - 第三个 *：表示日期字段，可以是 1 到 31 之间的任意整数。
     - 第四个 *：表示月份字段，可以是 1 到 12 之间的任意整数。
     - 第五个 *：表示星期字段，可以是 0 到 7 之间的任意整数，0 代表星期日，6 代表星期六。
     - /path/to/backup.sh：表示要执行的备份脚本的路径。

        如果你想要更改执行备份脚本的时间间隔，可以修改表达式中的值。例如，如果你希望每天凌晨 2 点执行备份脚本，可以将表达式修改为 `0 2 * * *`。
        
        以下是一些常见的时间间隔示例：
        
        - `* * * * *`：每分钟执行一次。
        - `0 * * * *`：每小时的整点时刻执行一次。
        - `0 0 * * *`：每天的凌晨 0 点执行一次。
        - `0 0 * * 1`：每个月的第一天的凌晨 0 点执行一次。
        - `0 0 1 * *`：每年的 1 月 1 日的凌晨 0 点执行一次。
        
        请根据你的需求选择适合的时间间隔，并确保备份脚本具有正确的权限并且可以在指定的路径下执行。
     

   • 在 Windows 中，打开任务计划程序，创建一个新的任务，设置任务执行的时间、频率和触发条件。在任务的操作选项中，选择“运行程序”，并指定 backup.sh 脚本的路径。

3. 保存并启用定时任务。

   • 在 Linux 中，保存 crontab 文件并退出编辑器。定时任务将立即生效。
   • 在 Windows 中，保存任务设置并启用任务计划。

这样，定时任务将在指定的时间自动执行 backup.sh 脚本，完成数据库备份操作。

请确保 backup.sh 脚本具有适当的权限，并且可以在指定的路径下执行。另外，你还可以使用其他工具或脚本来实现定时备份，如使用 Python 的 schedule 库或使用容器编排工具（如 Docker）来管理定时任务。根据你的具体需求和环境选择适合的方法。

---

更多精彩文章可扫码关注公主号查看：

若需要各种相关资源可关注公众号留言