数据库备份与恢复

数据库备份与恢复

备份

创建备份目录

mkdir -p /home/mysql-bak/scripts
mkdir -p /home/mysql-bak/full
mkdir -p /home/mysql-bak/binlog

备份全部数据库(定时任务：每天0点)

1.vi /home/mysql-bak/scripts/backup_inno.sh

#!/bin/bash
mysqldump --master-data=2 --single-transaction --triggers --routines --events --max_allowed_packet=32M -B -A > /home/mysql-bak/full/data_`date +%F_%H-%M-%S`.sql
1
2

2.创建每天夜里0点执行的定时任务：
echo “0 0 * * * root sh /home/mysql-bak/scripts/backup_inno.sh > /dev/null 2>&1” >> /etc/crontab

binlog备份（定时任务：1小时一次）

1.vi /home/mysql-bak/scripts/rsync_binlog.sh

#!/bin/bash
rsync -avz /var/lib/mysql/mariadb-bin.* /home/mysql-bak/binlog/
1
2

2.设置1小时执行一次的定时任务
echo “0 */1 * * * root sh /home/mysql-bak/scripts/rsync_binlog.sh > /dev/null 2>&1” >> /etc/crontab

清理机制（定时任务：每天凌晨1点半）

数据库备份保留三天 & binlog保留七天

1.vi /home/mysql-bak/scripts/clean_file.sh

#!/bin/bash
find /home/mysql-bak/full -mtime +3 -name "*.sql" -exec rm "{}" ";"
find /home/mysql-bak/binlog -type f  -mtime +7 -exec rm {} \;
1
2
3

2.创建每天凌晨1点半执行的定时任务：
echo “30 1 * * * root sh /home/mysql-bak/scripts/clean_file.sh > /dev/null 2>&1” >> /etc/crontab

恢复

删除数据库文件

mkdir -p /var/lib/mysql-prebackup
cd /var/lib/mysql
mv !(mysql|performance_schema) /var/lib/mysql-prebackup

先恢复数据库到最近一个备份时间点

mysql < /home/mysql-bak/full/data_2022-09-08_07-24-51.sql
binlog补偿（指定最近时间点）
mysqlbinlog –stop-datetime=”2005-04-20 9:59:59” /home/mysql-bak/binlog/mariadb-bin.000056 | mysql