摘要:按需备份数据一备份提要通过在配置实现操作日志的记录,并配合的全备份来实现全备和按需恢复数据的数据备份方案。本文章实现单机备份方案,主从备份,后续再补充。最大缓存区大小文件最大的大小二进制日志自动删除的天数。
Mysql按需备份数据
一. 备份提要通过在my.cnf配置bin-log,实现操作日志的记录,并配合mysqldump的全备份来实现全备和按需恢复数据的数据备份方案。
再以实例来说明方案的实现。
本文章实现单机备份方案,主从备份,后续再补充。
实施环境说明:
--centos:6.5
--mysql5.7.12
注:本文以linux系统下的方案,windows系统可作为参考。
创建备份文件夹
创建备份用户
配置my.cnf
创建备份脚本
创建定时任务执行备份脚本
实例操作备份方案
三. 方案实施步骤##1. 创建备份文件夹
按需创建自己的备份文件夹,本方案创建两个文件夹,一个放全备,一个放bin-log日志 全备:/home/databackup/backups 日志:/home/databackup/bin-log2. 创建备份用户
GRANT SELECT,SHOW DATABASES,SHOW VIEW,LOCK TABLES,TRIGGER,RELOAD,REPLICATION CLIENT ON *.* TO "dumper"@"127.0.0.1" IDENTIFIED BY "password" WITH GRANT OPTION;FLUSH PRIVILEGES;
授权后续说明
3. 配置my.cnf按如下配置my.cnf之后,重启service mysql restart, 在/home/databackup/bin-log文件夹下,会生成msyql-bin.index和mysql-bin.000001。
#database backup explicit_defaults_for_timestamp = 1 # server-id = 1 ## binlog存放路径 log-bin=/home/databackup/bin-log/mysql-bin.log ## binlog记录的格式,有row、statement、mixed三种选项 binlog-format = row ## binlog写缓冲区设置大小,由于是内存,写速度非常快,可以有效提高binlog的写效率,如果数据库>中经常出现大事务,可以酌情提高该参数。 binlog_cache_size = 32m ## 最大缓存区大小 max_binlog_cache_size = 512m ## binlog文件最大的大小 max_binlog_size = 1000m ## 二进制日志自动删除的天数。默认值为0,表示“没有自动删除” expire_logs_days = 60 ## 需要备份的数据库名,如果备份多个数据库,重复设置这个选项即可 binlog-do-db = databaseName1 binlog-do-db = databaseName2 ...... ## 不需要备份的数据库,如果备份多个数据库,重复设置这个选项即可 binlog-ignore-db = databaseName1 binlog-ignore-db = databaseName2 ......4. 创建备份脚本
创建备份脚本,文件名为mysql-databackup.sh,内容如下:
## !/bin/ash ## mysql-databackup.sh # Database info B_USER="dumper" DB_PASS="Dum#Per2017!" DB_HOST="127.0.0.1" # Database array DB_NAME=("power_organization") # Others vars BIN_DIR="/home/databackup/bin-log" #the mysql bin path BCK_DIR="/home/databackup/backups" #the backup file directory DATE=`date +%F` # create file mkdir $BCK_DIR/$DATE # TODO # /usr/bin/mysqldump --opt -ubatsing -pbatsingpw -hlocalhost timepusher > /mnt/mysqlBackup/db_`date +%F`.sql for var in ${DB_NAME[@]}; do mysqldump --opt -uuser -ppassword -h$DB_HOST --single-transaction --flush-logs --master-data=2 $var | gzip > $BCK_DIR/$DATE/$var.sql.gz done # 删除10天之前的备份文件 find /home/databackup/backups -mtime +10 -name "*.*" -exec rm -Rf {} ;5. 创建定时任务执行备份脚本
crontab -e内容
0 3 * * * /home/databackup/mysql-databackup.sh6. 实例操作备份方案 6.1 创建实例数据库test
创建实例数据库test,并随意插入几条数据。并 登录mysql执行flush logs,生成新日志mysql-bin.000002
msyql>flush log;6.2 查看bin-log有无开启
mysql> show variables like "%log_bin%";6.3 用mysqldump实现全备,并使用参数--flush-logs再生成mysql-bing.000003
首先对test数据库做一个完整备份:
$ mysqldump -hlocalhost -uuser -ppassword --flush-logs -P3306 --master-data=2 --single-transaction --opt test > test_bak_full.sql
这时候就会得到一个全备文件test.sql
6.4 模拟插入数据和误操作a. 在test库的某个表插入一些数据,然后执行flush logs命令。这时将会产生一个新的二进制日志文件mysql-bin.000004,mysql-bin.000004则保存了全备过后的所有更改,既增加记录的操作也保存在了mysql-bin.00004中。
b. 再在test库中的t_user表中增加两条记录,然后误删除t_user任意记录。t_user中增加记录的操作和删除表的操作都记录在mysql-bin.000005中。
6.5 用mysqldump再次实现全备,并使用参数--flush-logs再生成mysql-bing.000006$ mysqldump -hlocalhost -uuser -ppassword --flush-logs -P3306 --master-data=2 --single-transaction --opt test > test_bak_full2.sql6.6 删除数据库test,执行flush logs,生成新日志mysql-bin.000007 6.6 开始恢复数据
恢复过程不要记录日志:
mysql > set sql_log_bin=0;
首先导入全备数据
$ mysql -hlocalhost -uuser -ppassword < test_bak_full2.sql
此时数据库恢复到删除前的数据,test数据库恢复
查看当前所在二进制日志中的位置:
mysql> show master status;
会显示最新的日志记录mysql-bin.000007
mysql> show binlog events in "mysql-bin.000007";
根据上面命令的记录可以查看当然日志的位置position。
恢复数据
也可以通过命令mysqlbinlog查看日志明细,能大概确定需要完整恢复哪几个binlog文件。
如果知道误操作的命令如DROP TABLE,则可以通过下面的方法在binlog文件中找到误操作之前的那个或那位置段/时间段。
position: (如下面的信息显示,误操作DROP TABLE之前的pos是775,在datetime 141204 15:08:04或pos 882时完成DROP TABLE操作) $ mysqlbinlog /var/lib/mysql/mysql-bin.000003 |grep -C 5 "DROP TABLE" #141204 15:07:05 server id 1 end_log_pos 775 Xid = 376 COMMIT/*!*/; # at 775 #141204 15:08:04 server id 1 end_log_pos 882 Query thread_id=10 exec_time=0 error_code=0 SET TIMESTAMP=1417676884/*!*/; DROP TABLE `t_user` /* generated by server */ /*!*/; # at 882
数据恢复到数据库test的数据表t_user的误删记录前
根据记录,可选择mysql-bin.000005进行恢复,使用mysqlbinlog来查看恢复到什么位置。
这个日志中包括了新增记录和误删表两个部分,我们需要恢复到新增记录之后、误删操作以前的位置。
以下是恢复命令:
msyqlbinlog mysql-bin.000005 --start-position --stop-position | msyql -hlocalhost -uuser -ppassword
或
mysqlbinlog mysql-bin.000005 --start-datetime=775 --stop-datetime | mysql -h localhost -uroot -p
或
mysqlbinlog mysql-bin.000005 --start-datetime=775 --stop-datetime | restore.sql
然后执行restore.sql来恢复数据
注:在待恢复的position或时间点以前、全备以后的binlog需要全部恢复,多个文件以空格隔开
确定恢复成功后记得打开日志记录:
mysql > set sql_log_bin=1;
三. 其他 1. 报错内容一unknown variable "default-character-set=utf8"
在使用mysqlbinlog查看二进制日志的时候,提示下面的错误:
/usr/local/mysql/bin/mysqlbinlog: unknown variable "default-character-set=utf8"
原因是在为了统一mysql客户端到服务端的的字符编码,在/etc/my.cnf文件的[client]、[mysqld]等节加入了default-character-set = utf8,mysqlbinlog会从my.cnf中的[client]读取配置,但奈何mysqlbinlog并不认识这个选项(据说是个bug)导致的。
应对这个bug的方法有两个:
第一,自然是注释到[client]中的这个字符集配置;
第二,改用loose-default-character-set = utf8。在选项前加了loose-,表示当程序不认识此选项时会略过此选项,并给出一个警告。
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/47459.html
摘要:为最终恢复数据做准备。如果是差异备份全备和差异备份合并。恢复数据自动化备份脚本基础配置脚本文件全备份脚本文件增量备份脚本文件差异备份脚本文件 安装xtrabackup wget -O /etc/yum.repos.d /epel.repo http://mirrors.aliyun.com/repo/epel-6.repo #配置epel源 yum -y install perl p...
摘要:为最终恢复数据做准备。如果是差异备份全备和差异备份合并。恢复数据自动化备份脚本基础配置脚本文件全备份脚本文件增量备份脚本文件差异备份脚本文件 安装xtrabackup wget -O /etc/yum.repos.d /epel.repo http://mirrors.aliyun.com/repo/epel-6.repo #配置epel源 yum -y install perl p...
摘要:为最终恢复数据做准备。如果是差异备份全备和差异备份合并。恢复数据自动化备份脚本基础配置脚本文件全备份脚本文件增量备份脚本文件差异备份脚本文件 安装xtrabackup wget -O /etc/yum.repos.d /epel.repo http://mirrors.aliyun.com/repo/epel-6.repo #配置epel源 yum -y install perl p...
摘要:增量备份的原理就是使用了的日志。本次操作的版本为。增量备份要确保打开了二进制日志,参考的日志系统首先对数据库做一个完整备份这时候就会得到一个全备文件。 小量的数据库可以每天进行完整备份,因为这也用不了多少时间,但当数据库很大时,就不太可能每天进行一次完整备份了,这时候就可以使用增量备份。增量备份的原理就是使用了mysql的binlog日志。 本次操作的MySQL版本为5.5.40 fo...
摘要:手抖写错条件写错表名错连生产库造成的误删库表和数据总有听说,那么删库之后除了跑路,还能做什么呢,当然是想办法恢复,恢复数据的基础就在于完善的备份策略。 手抖、写错条件、写错表名、错连生产库造成的误删库表和数据总有听说,那么删库之后除了跑路,还能做什么呢,当然是想办法恢复,恢复数据的基础就在于完善的备份策略。 备份和恢复是同一个话题,篇幅有限,就分开两章写 MySQL备份策略 MySQ...
阅读 2617·2021-10-14 09:42
阅读 986·2021-09-24 10:32
阅读 2765·2021-09-23 11:21
阅读 2687·2021-08-27 13:10
阅读 3213·2019-08-29 18:41
阅读 2099·2019-08-29 15:16
阅读 1085·2019-08-29 13:17
阅读 787·2019-08-29 11:22