Mysql常用自动备份及恢复实战

1、使用技术

MySQL数据库备份和恢复主要是使用 mysqldump 和 mysqlbinlog 来完成

备份

1）使用crontab每天凌晨1点全量备份数据库，并将备份文件压缩，拷贝到其他服务器。
2）全量备份同时将binlog日志文件也同步到指定服务器。

恢复

1、正常恢复

使用全量文件恢复到指定日期

2、如果出现误操作或程序缺陷，误删除或修改一些数据需要恢复

先恢复到误操作时间最近的全量文件，然后利用binlog恢复最近那次全量备份操作到误操作中间的数据。

binlog

选项

-d, --database=name      仅显示指定数据库的转储内容。
-o, --offset=#           跳过前N行的日志条目。
-r, --result-file=name   将输入的文本格式的文件转储到指定的文件。
-s, --short-form         使用简单格式。
--set-charset=name       在转储文件的开头增加'SET NAMES character_set'语句。
--start-datetime=name    转储日志的起始时间。
--stop-datetime=name     转储日志的截止时间。
-j, --start-position=#   转储日志的起始位置。
--stop-position=#        转储日志的截止位置。

 示例

1.查看所有binlog日志列表
mysql> show master logs;

2.查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值
mysql> show master status;

3.刷新log日志，自此刻开始产生一个新编号的binlog日志文件
mysql> flush logs;
注：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；

4.重置(清空)所有binlog日志
mysql> reset master;

查看日志

mysqlbinlog /var/lib/mysql/mysql-bin.000001

1.查询第一个(最早)的binlog日志：
mysql> show binlog events;

2.指定查询 mysql-bin.000021 这个文件：
mysql> show binlog events in 'mysql-bin.000021';

3.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起：
mysql> show binlog events in 'mysql-bin.000021' from 8224;

4.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，查询10条
mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10;

5.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，偏移2行，查询10条
mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10\G;

 导入数据

bin/mysqlbinlog --start-position=245 --stop-position=582 /var/lib/mysql/master-bin.000009 | mysql -uroot -proot

bin\mysqlbinlog  --base64-output=decode-rows -vv    data\binlog.000256 >result.sql

bin\mysqlbinlog --base64-output=decode-rows -vv  --start-datetime="2024-05-10 14:15:00" --stop-datetime="2024-05-10 14:19:00"   data\binlog.000256 >result.sql

Mysqldump

mysqldump --opt --single-transaction=TRUE --master-data=2 --user=root --password=root --host=127.0.0.1 --protocol=tcp --port=3306 --default-character-set=utf8 --single-transaction=TRUE --routines --events cbps >backup.sql

实战脚本下载地址：

Mysql全量备份与配套恢复脚本，可使用crontab定时自动全量备份-MySQL文档类资源-CSDN下载

定时任务crontab

1）安装crontab（centos7默认已经安装）：yum install crontabs

服务操作说明

#/bin/systemctl start crond.service //启动服务

#/bin/systemctl stop crond.service //关闭服务

#/bin/systemctl restart crond.service //重启服务

#/bin/systemctl reload crond.service //重新载入

bin/systemctl status crond.service //服务状态

配置:

加入开机自动启动：chkconfig –level 35 crond on

（2）在命令行输入:crontab -e

#每个星期日凌晨3:00执行完全备份脚本0 3 * * 0 /bin/bash -x /home/mysql/Mysql-FullyBak.sh >/dev/null 2>&1#周一到周六凌晨3:00做增量备份0 3 * * 1-6 /bin/bash -x /home/mysql/Mysql-DailyBak.sh >/dev/null 2>&1

添加相应的任务，wq存盘退出

说明：默认情况下，crontab执行一次任务后，会通过email通知用户，为避免每次发信息，加入/dev/null 2>&1

（3）查看定时任务：#crontab -l