mysql定时备份数据

mysql定时备份数据

项目上线后，客户找第三方做了个安全测评，然后找出了一些安全问题，让我们尽快修复，其中一项就是未定期备份数据… 好记性不如烂笔头，于是闲暇之余整理了一下。

mysql（v5.7.22)定时备份需要用到定时任务crontab、全量备份mysqldump、增量备份mysqlbinlog。

1、开启binlog

binlog是一个二进制的文件，它记录了所有对数据库的修改操作，当数据库崩溃后，可以通过mysqlbinlog命令读取binlog恢复数据。mysql默认是关闭binlog的。

在my.cnf中的[mysqld]下方加入如下配置来开启binlog

log-bin=/mnt/mysql/log/mysql-bin
server-id=100

然后重启mysql

shell>/etc/my.cnf

log-bin=/mnt/mysql/log/mysql-bin
server-id=100

shell>service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service

log-bin指定了日志的路径和二进制文件的前缀，日志开启后会在/mnt/mysql/log/目录下生成mysql-bin.000001、mysql-bin.000002等等

二进制日志是不能直接使用vi或者cat等文本工具直接查看的，可以使用命令查看

shell>mysqlbinlog --base64-output=never mysql-bin.000001

–base64-output指不输出base64；

如果执行上述命令报[ERROR] unknown variable ‘default-character-set=utf8mb4’，可以加上–no-defaults命令，或者修改/etc/my.cnf配置文件,把default-character-set=utf8mb4修改为 character-set-server = utf8mb4，并重启mysql服务即可

server-id用来指定服务器id，随便指定一个就行

注意binlog的目录最好不要跟数据文件放一起，放在另一个区，甚至是另一个物理磁盘，以免数据文件和binlog同时丢失

2、mysqldump备份

shell>mysqldump -uroot -p12345678Aa --single-transaction --flush-logs --master-data=2 --all-databases --add-drop-database >/mnt/mysql/backup/backup20190924.sql

–single-transaction使用一致的读取并保证mysqldump看到的数据不会更改

–flush-logs 刷新binlog日志，生成一个新的日志文件（定期增量备份），按备份日期区分更好维护

–master-data=2会将二进制日志信息写入其输出，因此生成的.sql备份文件会包含备份日志信息

–all-databases指备份所有数据库，也可以使用 --databases db1 db2 db3 指定备份的数据库名称

​ 即mysqldump --databases db1 db2 db3 > dump.sql

二进制文件时间长了，可能会很大，可以使用–delete-master-logs删除之前的二进制文件

# mysqldump -uroot -p12345678Aa --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs >/mnt/mysql/backup/backup20190924.sql

3、定时备份数据

手动执行备份多麻烦，当然要自动定时备份呐

 -- 进入crontab定时任务界面，添加定时任务
shell>crontab -e

# 每天凌晨3点执行
00 3 * * * sh /mnt/mysql/backup.sh
# 每天凌晨1:30执行
30 1 * * * sh /mnt/mysql/backup_clean.sh


-- 查看定时任务
shell>crontab -l

shell>vi /mnt/mysql/backup.sh

mysqldump -uroot -p12345678Aa --single-transaction --flush-logs --master-data=2 --all-databases --add-drop-database >/mnt/mysql/backup/backup20190924.sql

shell>vi /mnt/mysql/backup_clean.sh

#删除30天前备份数据
find /mnt/mysql/backup -mtime +30 -name "*.gz" -exec rm -rf {} \;
#删除30天前的日志
find /mnt/mysql/log -mtime +30 -type f -exec rm -rf {} \;

如果没有安装，可以使用 yum install -y crontab 安装

备份的时候我们也可以使用gzip压缩.sql文件，如

mysqldump -uroot -p12345678Aa --single-transaction --flush-logs --master-data=2 --all-databases --add-drop-database | gzip >/mnt/mysql/backup/backup20190924.sql.gz

4、数据恢复

假设最近一次备份是20190924 03:00备份的，20190925 11:00发生了灾难性事故造成数据丢失，需要从备份恢复。

a.先恢复最近的一次完整备份

shell> mysql -uroot -p12345678Aa < backup20190924.sql 

如果是在mysql内部则使用source命令

mysql> source backup20190924.sql 

b.使用脚本恢复只能恢复20190924 03:00之前的数据，而20190924 03:00到20190925 11:00之间8个小时的数据可以通过增量备份binlog恢复

shell>mysqlbinlog --no-defaults mysql-bin.000025 | mysql -uroot -p12345678Aa

如果有多个日志文件，空格分隔就行：

mysqlbinlog --no-defaults mysql-bin.000025 mysql-bin.000026 | mysql -uroot -p12345678Aa

5、附录

crontab命令示例

命令	说明
* * * * * command	每1分钟执行一次command
30 * * * * command	每30分钟执行一次command
3,59 * * * * myCommand	每小时的第3和第59分钟执行
3,59 9-18 * * * myCommand	在上午9点到18点的第3和第59分钟执行
3,59 9-18 */2 * * myCommand	每隔两天的上午9点到18点的第3和第59分钟执行
3,59 9-18 * * 1 myCommand	每周一上午9点到18点的第3和第59分钟执行

mysqldump常用命令

–no-data选项告诉 mysqldump不要转储表数据，从而导致转储文件仅包含创建表的语句。相反，该 --no-create-info选项告诉mysqldump不要 CREATE语句，以便转储文件仅包含表数据。

shell> mysqldump --no-data test > dump-defs.sql
shell> mysqldump --no-create-info test > dump-data.sql

mysqlbinlog常用命令

查看所有二进制文件列表

mysql> show binary logs;

查看当前二进制文件名称

mysql> SHOW MASTER STATUS;

分页查看binlog

shell>mysqlbinlog   mysql-bin.000001 | more

把binlog日志输出值文本文件中

shell>mysqlbinlog --no-defaults mysql-bin.000006 -r pos.sql

或者

shell>mysqlbinlog --no-defaults mysql-bin.000006 > pos.sql

这个时候就可以vi编辑这个文件了，删除某些不要的数据，可以使用下面的命令恢复数据

shell> mysql -u root -p < pos.sql

指定时间点恢复使用–start-datetime和 --stop-datetime选项

例如，假设恰好在2019年9月20日上午10:00不小心执行了一条删除大表的SQL语句。要还原表和数据，可以还原前一秒的备份

shell> mysqlbinlog --stop-datetime="2019-09-20 9:59:59" mysql-bin.000026 | mysql -u root -p

如果是想恢复之后的数据，可以使用

shell> mysqlbinlog --start-datetime="2019-09-20 10:01:00" mysql-bin.000026 | mysql -u root -p

除了指定时间外，mysqlbinlog还可以指定位置， --start-position和 --stop-position选项 可用于指定日志位置

shell> mysqlbinlog --stop-position=35789  mysql-bin.000026 | mysql -u root -p

shell> mysqlbinlog --start-position=34789  mysql-bin.000026 | mysql -u root -p

第一条命令将恢复所有事务，直到给出停止位置为止。第二个命令从给定的起始位置恢复所有事务，直到二进制日志结束

更多mysql5.7备份恢复知识请移步官方文档：https://dev.mysql.com/doc/refman/5.7/en/backup-and-recovery.html