mysqldump定时备份mysql数据库

1. Mysql自带备份数据库命令

MySQL备份种类

完全备份（Full Backup）：包含数据表、索引、触发器、存储过程、视图、数据文件等所有内容。

增量备份（Incremental Backup）：只包含最近一次完全备份之后修改过的数据，可以快速恢复最新的数据。

差异备份（Differential Backup）：与增量备份类似，只包含最近一次完全备份之后修改过的数据，但可以通过两个差异备份之间的差异进行恢复，更加高效。

压缩备份（Compressed Backup）：通过对数据进行压缩，减少备份文件的大小，便于存储和传输。

远程备份（Remote Backup）：通过网络传输数据到远程服务器，可以实现数据的分布式备份和恢复。

磁盘备份（Disk Backup）：将MySQL数据库文件和配置文件备份到磁盘上，可以实现快速恢复数据，但恢复速度相对较慢。

文件备份（File Backup）：将MySQL数据库文件和配置文件备份到文件中，可以通过网络传输到其他服务器进行恢复。

不同的备份种类适用于不同的场景，需要根据实际情况进行选择。

mysqldump -h [IP或域名] -P [端口号] -u[登录账号] -p[登录密码] --databases [数据库名称1 数据库名称2] > [最终生成sql文件的存储路径]/xxx.sql

注意：-p后面必须紧跟密码，之间不能有空格，不然会有问题

1.1 备份指定数据库

mysqldump -h localhost -P 3306 -uroot -p123456 --databases databases1 > /data/sql/db.sql

  如果ip是loaclhost 则可以省区

mysqldump -uroot -p123456 --databases databases1 > /data/sql/db.sql

可以使用gzip进行压缩

mysqldump -uroot -p123456 --databases database1 | gzip > /data/sql/db.sql.gz

备份单个数据库的结构

mysqldump -uroot -p123456 --databases databases1 -d > /data/sql/db.sql

备份单个数据库的数据

mysqldump -uroot -p123456 --databases databases1 -t > /data/sql/db.sql

1.2 备份多个数据库（空格隔开）

mysqldump -uroot -p123456 --databases db1 db2 db3 > /data/sql/db.sql

1.3 备份指定数据库的指定表

mysqldump -uroot -p123456 database1 table1 > /data/sql/db.sql

1.4 备份指定数据库的多个表

mysqldump -uroot -p123456 database1 table1 table2 table3 > /data/sql/db.sql

1.5 备份全部数据库

mysqldump -uroot -p123456 --all-databases > /data/sql/all.sql

1.6 恢复数据库

mysql -uroot -p123456 database < /data/mysql-buckup/db.sql

2. Linux定时任务执行器：crontab

在Linux系统中，有一个非常强大的定时任务执行器，它叫做crontab。

crontab可以让用户在固定的时间、日期或者周期性地执行某些任务或命令。

crontab的使用非常简单，用户只需要编辑自己的crontab文件，就可以添加、修改或删除自己的定时任务。

2.1 基本的crontab命令

1. 列出当前用户的所有定时任务

crontab -l

2. 编辑当前用户的定时任务

crontab -e

3. 删除当前用户的所有定时任务

crontab -r

4. 列出指定用户的所有定时任务

crontab -u user -l

5. 编辑指定用户的定时任务

crontab -u user -e

6. 删除指定用户的所有定时任务

crontab -u user -r

2.2 用 crontab -e 命令添加一个定时任务

crontab -e

在每周一的 2 点执行全量备份，将数据库 database 的内容导出为 data-2024-01-01.sql 文件。

0 2 * * 1 mysqldump -uroot -p123456 databses > /home/mysql-backup/data-`date +\%Y-\%m-\%d`.sql

在周二至周日的 2 点，脚本将执行增量备份，并将数据导出为 data-2024-01-01.sql 文件。

注意，增量备份将仅包含新数据，不包含创建信息、触发器和锁表。

0 4 * * 2-7 mysqldump -uroot -p123456 --no-create-info --skip-triggers --skip-lock-tables database > /home/mysql-backup/data-`date +\%Y-\%m-\%d`.sql

请确保在设置 crontab 脚本之前，已经安装并配置了 MySQL，并确保脚本的执行权限正确设置。

* * * * * command arg1 arg2
| | | | |
| | | | ----- Day of the Week (1 - 7)
| | | ------- Month (1 - 12)
| | --------- Day of the Month (1 - 31)
| ----------- Hour (0 - 23)
------------- Minute (0 - 59)

2.3 查看定时任务

crontab -l

3. 将crontab定时任务与mysql的备份命令结合起来

3.1 将mysql的备份命令写成 backup.sh 脚本文件

#!/bin/bash

# 备份工具
tool=mysqldump
# 用户名
username=root
# 密码，特殊字符要转化
password=root
# 要备份的数据库
database_name1=sys_user
database_name2=sys_role

# 保存备份的天数，超过31天删除之前的
number=31
# 是否删除超过31天的备份，ON为开启 OFF为关闭
is_delete=ON
# 备份日期
backup_date=`date +%Y-%m-%d`
# 备份保存路径，末尾请不要带"/"
backup_dir=/data/mysql-backup

# 如果文件夹不存在则创建
if [ ! -d $backup_dir ];
then
	mkdir -p $backup_dir;
fi

# 备份
$tool -u$username -p$password --databases $database_name1 > $backup_dir/$database_name1-$backup_date.sql
$tool -u$username -p$password --databases $database_name2 > $backup_dir/$database_name2-$backup_date.sql

# 写创建备份日志
echo "$backup_date 新建备份" >>$backup_dir/log.txt

# 如果开启了删除过期备份，则进行删除操作
if [ $is_delete == "ON" ];
then
	# 找出需要删除的备份
	find $backup_dir -name "*.sql" -type f -mtime +$number | xargs rm -rf
	 # 写删除文件日志
	 echo "删除 $number 天前的备份" >>$backup_dir/log.txt
fi

3.2 给脚本文件授权（可执行权限）

chmod +x backup.sh

3.3 使用 crontab 命令设置定时任务

crontab -e

0 2 * * 1 /data/mysql-backup/backup.sh

3.4 查看定时任务

crontab -l