mysqldump是MySQL官网的一个逻辑备份工具，适用于数据量不大的备份场景，大量数据备份推荐物理备份工具xtrabackup
# 数据库备份
提示：可以结合rsync将备份文件复制到远程服务器上实现灾备，再使用mysqlbinlog工具同步binlog到远程服务器上以备份增量数据
# 手动备份
mysqldump -h127.0.0.1 -uroot -proot 库名 --master-data=2 --ignore-table=库名.不备份的表名1 --ignore-table=库名.不备份的表名2 2>/dev/null | gzip > backup.sql.gz
# 自动备份
全库备份并压缩为gz，保留最近30天
vim /usr/local/scripts/mysql_backup.sh
#!/bin/bash
BACKUP_DIR=/data/backups
NAME=database_backup_
TIME=`date +%Y%m%d%H%M%S`
if [ ! -d $BACKUP_DIR ];then
mkdir -p /data/backups
fi
mysqldump -h127.0.0.1 -uroot -proot 库名 --master-data=2 --ignore-table=库名.不备份的表名1 --ignore-table=库名.不备份的表名2 2>/dev/null | gzip > $BACKUP_DIR/$NAME$TIME.sql.gz
find $BACKUP_DIR -name "$NAME*.sql.gz" -type f -mtime +30 -exec rm {} \; > /dev/null 2>&1
每天定时执行
echo '0 0 * * * root /usr/local/scripts/mysql_backup.sh' >> /etc/crontab
# 数据库恢复
zcat backup.sql.gz | mysql -h127.0.0.1 -uroot -proot 库名
# MySQL binlog基于时间点恢复
mysqldump备份数据时记录MySQL的binlog日志的文件名称和position（需开启MySQL的binlog）
开启binlog vim /etc/my.cnf
[mysqld]
log_bin=mysql-bin
server-id=1
expire_logs_days=30
备份时新增参数--master-data=2以注释的方式记录binlog文件名称和position
mysqldump -uroot -proot test --master-data=2 > backup.sql
head -n 30 backup.sql
...
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=419;
...
根据binlog基于时间点恢复数据 注意事项：恢复前关闭binlog日志
mysql -uroot -proot -e 'set sql_log_bin=0'
MASTER_LOG_POS=`head -n 30 backup.sql|grep "CHANGE MASTER TO"|awk 'END{a=substr($6,index($6,"=")+1);a=substr(a,1,length(a)-1);print a}'`
MASTER_LOG_FILE=`head -n 30 backup.sql|grep "CHANGE MASTER TO"|awk 'END{a=substr($5,index($5,"=")+2);a=substr(a,1,length(a)-2);print a}'`
mysqlbinlog --start-position=$MASTER_LOG_POS --stop-datetime="2022-07-05 14:13:00" /var/lib/mysql/$MASTER_LOG_FILE > data.sql
mysql -uroot -proot < data.sql
mysql -uroot -proot -e 'set sql_log_bin=1'
mysqlbinlog参数说明：
--base64-output=decode-rows -v 解码为可读的文本，但不能用于恢复
--start-position=875 起始pos点
--stop-position=954 结束pos点
--start-datetime="2016-9-25 22:01:08" 起始时间点
--stop-datetime="2019-9-25 22:09:46" 结束时间点
--database=test 指定只恢复test数据库
🕑 最后更新时间: 2022-10-16 14:57