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