MySQL 数据库如何定时自动备份？

有多种方法可以定时备份 MySQL 数据库，以下列举几种常见的方法：

1. 使用 crontab 定时任务：在 Linux 系统中可以使用 crontab 来设置定时任务，例如每天凌晨 2 点备份 MySQL 数据库。可以编写脚本来实现备份操作，然后将脚本添加到定时任务中。

2. 使用 MySQL 自带的 mysqldump 工具：mysqldump 是 MySQL 自带的备份工具，可以使用它来备份数据库。可以编写脚本来实现备份操作，然后将脚本添加到定时任务中。

3. 使用第三方备份工具：还有一些第三方备份工具可以用来备份 MySQL 数据库，例如 Percona XtraBackup、Mariabackup 等。这些工具可以通过脚本或命令来实现备份操作，然后将脚本添加到定时任务中。

以下是使用 crontab 定时任务配合 mysqldump 命令来定时备份 MySQL 数据库：

1. 编写备份脚本

使用 mysqldump 命令备份 MySQL 数据库，可以将备份脚本命名为 backup_mysql.sh，并保存到指定目录下，例如 /home/mysql_backup/backup_mysql.sh。备份脚本内容如下：

#!/bin/bash

# 备份目录

BACKUP_DIR=/home/mysql_backup

# 数据库用户名

DB_USER=root

# 数据库密码

DB_PASSWD=123456

# 数据库名称

DB_NAME=my_db

# 备份文件名

BACKUP_FILE=mysql_$(date +%Y%m%d%H%M%S).sql

# 创建备份目录

mkdir -p $BACKUP_DIR

# 备份数据库

mysqldump -u $DB_USER -p$DB_PASSWD $DB_NAME > $BACKUP_DIR/$BACKUP_FILE

# 压缩备份文件

gzip $BACKUP_DIR/$BACKUP_FILE

2. 添加定时任务

使用 crontab 命令添加定时任务，例如每天凌晨 2 点备份 MySQL 数据库。在终端中执行以下命令：

crontab -e

在打开的文件中添加以下内容：

0 2 * * * /bin/bash /home/mysql_backup/backup_mysql.sh

这表示每天凌晨 2 点执行 /home/mysql_backup/backup_mysql.sh 脚本。

3. 保存并退出

保存并退出 crontab 文件即可。

以上就是使用 crontab 定时任务备份 MySQL 数据库的具体操作步骤。需要根据实际情况修改备份脚本中的参数，例如数据库用户名、密码、数据库名、备份目录等。

需要注意的是，备份 MySQL 数据库时应该选择合适的备份策略，包括全量备份、增量备份、差异备份等，以及备份的存储位置、备份文件的命名规则等。同时，备份的频率也要根据业务需求和数据量来确定。

全量备份脚本dbback.sh：
#!/bin/bash
##备份数据库脚
#MySQL User Information
HOST=127.0.0.1
USERNAME=root
PASSWORD=*****
#Date Format
DATE=`date +%Y%m%d`
#Back directory
DAYS=20
#备份文件存放位置
BACKUP_DIR=/data/db-backup/
#MySQL directory
MYSQL_DIR=/usr/bin/
#Go to the backup directory
if [ -d ${BACKUP_DIR} ];then
cd ${BACKUP_DIR}
else
mkdir -p ${BACKUP_DIR}
fi
#The first instance of the backup
while read dbname
do
if [ -f ${dbname}_${DATE}.gz ]; then
echo "MySQL Database ${dbname}_${DATE}.gz already exists."
else
${MYSQL_DIR}/mysqldump -u${USERNAME} -p${PASSWORD} -h${HOST} --default-character-set=utf8 --master-data=2 --single-transaction  ${dbname} | /bin/gzip > ${BACKUP_DIR}/${dbname}_${DATE}.gz
fi
done < /opt/ly-traffic/db-backup/dbs.txt

##删除旧备份文件
find  ${BACKUP_DIR} -name "*.gz" -type f -mtime +22|tee -a ${BACKUP_DIR}/del.log|xargs rm -r 2>>${BACKUP_DIR}/err.lo

exit 0

数据备份可用行测试：
gzid -d  文件名
mysql -uroot -p -h127.0.0.1  数据库 < 文件名

对于自建的数据库，没有使用云数据库的时候，就需要自己保证数据的可用性，需要及时对数据进行备份。

这里讲一下如何通过脚本，每天定时对数据库进行全量备份，并对数据文件保存14天（当然天数可以自己随意设置，根据服务器容量大小和具体要求设置）。

1、创建一个备份脚本文件，例如 backup_mysql.sh,并将以下代码复制到文件中：
#!/bin/bash

# 设置 MySQL 登录信息
MYSQL_USER="your_mysql_username"
MYSQL_PASSWORD="your_mysql_password"
MYSQL_DATABASE="your_database_name"
BACKUP_DIR="/path/to/backup/directory"
DATE=$(date +%Y-%m-%d)
TIME=$(date +%H-%M-%S)

# 创建备份目录和文件名
mkdir -p $BACKUP_DIR/$DATE
FILENAME=$BACKUP_DIR/$DATE/$MYSQL_DATABASE-$TIME.sql.gz

# 使用 mysqldump 命令备份数据库
mysqldump --user=$MYSQL_USER --password=$MYSQL_PASSWORD --databases $MYSQL_DATABASE | gzip > $FILENAME

# 输出备份完成信息
echo "Backup completed on $(date +%Y-%m-%d %H:%M:%S) for database $MYSQL_DATABASE to $FILENAME."

# 删除超过14天的备份文件
find $BACKUP_DIR/* -mtime +14 -exec rm {} \;

2、为脚本文件添加可执行权限：
chmod +x backup_mysql.sh

3、将脚本文件添加到 crontab 中，以每天凌晨 2 点执行备份任务：
crontab -e

4、在打开的编辑器中，新增以下行：
0 2 * * * /path/to/backup_mysql.sh >/dev/null 2>&1

保存并退出编辑器。现在，脚本将在每天凌晨 2 点执行，并输出一条备份计划信息。

上面的shell脚本做了以下事情：
1.获取当前日期和时间。
2.使用 mkdir 命令创建备份目录和文件名。
3.使用 mysqldump 命令备份数据库，并将结果压缩成 gzip 格式。
4.输出一条备份完成信息。
5.使用 find 命令查找超过14天的备份文件，并将其删除。

自己做备份时，记得修改mysql的登录账号、密码，数据库，备份文件夹等信息。也可以修改备份文件保存时间。是不是很简单啊？

MySQL的全量备份与增量备份

全量备份：可以使用mysqldump直接备份整个库或者是备份其中某一个库或者一个库中的某个表。

备份所有数据库：
[root@my ~]# mysqldump -uroot -p123456 --all-databases >/opt/all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

备份单个数据库的所有表：
[root@my ~]# mysqldump -uroot -p123456 --databases test>/opt/test.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

备份数据库中的单个表的结构：
[root@my ~]# mysqldump -uroot -p123456 -d test userinfo >/opt/test_userinfo_jiegou.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

备份数据库中的单个表的内容：
[root@my ~]# mysqldump -uroot -p123456 test userinfo>/opt/test_userinfo_neirong.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

增量备份：增量备份是针对于数据库的bin-log日志进行备份的，需要开始数据库的bin-log日志。增量备份是在全量的基础上进行操作的。增量备份主要是靠mysql记录的bin-log日志。（可以把二进制日志保存成每天的一个文件）

1.开启二进制日志文件；
[root@my ~]# echo -e "log-bin = /usr/local/mysql/logs/mysql_bin \nserver_id = 1\n max_binlog_size = 100M " >>/etc/my.cnf 

根据position值位置进行恢复；
[root@my ~]# mysqlbinlog --start-position=1 --stop-position=795 /usr/local/mysql/logs/mysql_bin.000001 |mysql -uroot -p123456

根据时间点位置进行恢复；-d指定数据库，选项-h指定主机
[root@my ~]#mysqlbinlog --start-date='2019-05-30 14:49:25' --stop-date='2019-05-30 14:52:39' -d linux -h127.0.0.1 /usr/local/mysql/logs/mysql-bin.000001 |mysql -uroot -p123456

将二进制日志文件中所有的数据记录全部恢复；
[root@my ~]# mysqlbinlog /usr/local/mysql/logs/mysql_bin.000001 |mysql -uroot -p123123

脚本：
全量备份：
[root@calldb1 ~]# cat /shell/fs_sql_bak.sh 
#!/bin/bash
##beifen sql

Bakdir=/data/sql-bak
Time=$(date +"%F_%T")
[ -f /usr/bin/mysqldump ] && echo "ok" || exit
/usr/bin/mysqldump -uroot -p123456 -R --all-databases > $Bakdir/$Time"_34.sql"
#echo $Bakdir/$Time"_34.sql"

/usr/bin/mysqldump dbname -uroot -p123456 > $Bakdir/$Time"-dbname.sql"

增量备份：
[root@calldb2 shell]# cat zeng_sql.sh 
#!/bin/bash
## zeng liang bak sql
Logdir=/usr/local/mysql/logs
Time=$(date +%F)
mkdir $Logdir/"log-"$Time
find $Logdir -type f -mmin 1 -exec cp {} $Logdir/"log-"$Time/ \;

if [ -f $Logdir/"log-"$Time/mysql-bin.* ];then
  echo "mysql is zeng backup success on time-$(date +%F)" >>$Logdir/"log-"$Time/mysql_zeng_backup.log
else
  echo "mysql is zeng backup fail on time-$(date +%F)" >>$Logdir/"log-"$Time/mysql_zeng_backup.log
fi
mysqladmin -uroot -p123456 flush-logs >/dev/null

生产环境：
建议一周或者三天进行一次全量备份，一天一次增量备份。