时间:2021-05-23
一、开启binlog。
首先查看binlog是否开启
mysql> show variables like "log_bin";+---------------+-------+|Variable_name | Value +---------------+-------+| log_bin OFF +---------------+-------+1 row in set (0.00 sec)值为OFF,需开启,开启binlog方式如下:
#vim /etc/my.cnf在[mysqld]中加入
log-bin = mysql-binlog-bin = /usr/local/mysql/log/mysql-bin.log重启mysql服务
#service mysqld stop#service mysqld start二、模拟数据写入
建库
create database backup;建表
CREATE TABLE `number` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号', `updatetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;写入数据
程序2-1
#coding:utf8#python2.7import MySQLdbimport timedef connect_mysql(db_host="192.168.11.169",user="martin",passwd="martin",db="backup",charset="utf8"): conn = MySQLdb.connect(host=db_host,user=user,passwd=passwd,db=db,charset=charset) conn.autocommit(True)return conn.cursor()#数据插入for i in range(0,10):#time=time.strftime("%Y-%m-%d %H:%M:%S")sql = 'insert into number(updatetime) values(%s)'values = [(time.strftime("%Y-%m-%d %H:%M:%S"))] db1 = connect_mysql()print db1.executemany(sql,values)查询数据
mysql> select * from number;+-------+------------------------+| id | updatetime +--------------------------------+| 1 | 2016-06-29 23:27:15 || 2 | 2016-06-29 23:27:15 || 3 | 2016-06-29 23:27:15 || 4 | 2016-06-29 23:27:15 || 5 | 2016-06-29 23:27:15 || 6 | 2016-06-29 23:27:15 || 7 | 2016-06-29 23:27:15 || 8 | 2016-06-29 23:27:15 || 9 | 2016-06-29 23:27:15 || 10 | 2016-06-29 23:27:15 |+-------+------------------------+10 rows in set (0.00 sec)三、全量备份
mysqldump -uroot -p -F --master-data=2 backup |gzip> /martin/data/backup_$(date +%F).sql.gz注:加-F能刷新binlog,方便恢复时操作。
四、模拟写入增量数据
继续执行程序2-1。
查询数据
mysql> select * from number;+----+---------------------------+| id | updatetime |+----+---------------------------+| 1 | 2016-06-29 23:27:15 || 2 | 2016-06-29 23:27:15 || 3 | 2016-06-29 23:27:15 || 4 | 2016-06-29 23:27:15 || 5 | 2016-06-29 23:27:15 || 6 | 2016-06-29 23:27:15 || 7 | 2016-06-29 23:27:15 || 8 | 2016-06-29 23:27:15 || 9 | 2016-06-29 23:27:15 || 10 | 2016-06-29 23:27:15 || 11 | 2016-06-29 23:31:03 || 12 | 2016-06-29 23:31:03 || 13 | 2016-06-29 23:31:03 || 14 | 2016-06-29 23:31:03 || 15 | 2016-06-29 23:31:03 || 16 | 2016-06-29 23:31:03 || 17 | 2016-06-29 23:31:03 || 18 | 2016-06-29 23:31:03 || 19 | 2016-06-29 23:31:03 || 20 | 2016-06-29 23:31:03 |+-------+---------------------+20 rows in set (0.00 sec)五、增量备份
保留mysql-bin.000002及之后的binlog即可。
六、模拟误操作
delete from number;七、再次写入增量数据
执行程序2-1
select * from bumber;
+------+------------------------+| id | updatetime |+------+------------------------+| 21 | 2016-06-29 23:41:06 || 22 | 2016-06-29 23:41:06 || 23 | 2016-06-29 23:41:06 || 24 | 2016-06-29 23:41:06 || 25 | 2016-06-29 23:41:06 || 26 | 2016-06-29 23:41:06 || 27 | 2016-06-29 23:41:06 || 28 | 2016-06-29 23:41:06 || 29 | 2016-06-29 23:41:06 || 30 | 2016-06-29 23:41:06 |+------+------------------------+10 rows in set (0.00 sec)八、恢复
此时发现之前的delete操作为误操作,急需恢复,恢复过程如下
给该表加上读锁
lock table number read;将全量备份的数据导入
#cd /martin/data/#gzip -d number_2016-06-29.sql.gz#grep -i "change" *.sql-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=107;刷新日志
#mysqladmin -uroot -p'martin' flush-logs#cd /usr/local/mysql/log#ls|grep mysql-bin|grep -v indexmysql-bin.000001mysql-bin.000002mysql-bin.000003可确定mysql-bin.000002为增量数据binlog
导入全量备份
#cd /martin/data/#mysql -uroot -p backup < number_2016-06-29.sql#cp /usr/local/mysql/log/mysql-bin.000002 /martin/data/#mysqlbinlog mysql-bin.000002 >bin.sql#vim bin.sql在bin.sql找到之前的delete语句,删除
mysql -uroot -p <bin.sql九、确认已恢复数据
登录mysql
#mysql -uroot -p'martin' backupselect * from number;+----+---------------------+| id | updatetime |+----+---------------------+| 1 | 2016-06-29 23:27:15 || 2 | 2016-06-29 23:27:15 || 3 | 2016-06-29 23:27:15 || 4 | 2016-06-29 23:27:15 || 5 | 2016-06-29 23:27:15 || 6 | 2016-06-29 23:27:15 || 7 | 2016-06-29 23:27:15 || 8 | 2016-06-29 23:27:15 || 9 | 2016-06-29 23:27:15 || 10 | 2016-06-29 23:27:15 || 11 | 2016-06-29 23:31:03 || 12 | 2016-06-29 23:31:03 || 13 | 2016-06-29 23:31:03 || 14 | 2016-06-29 23:31:03 || 15 | 2016-06-29 23:31:03 || 16 | 2016-06-29 23:31:03 || 17 | 2016-06-29 23:31:03 || 18 | 2016-06-29 23:31:03 || 19 | 2016-06-29 23:31:03 || 20 | 2016-06-29 23:31:03 || 21 | 2016-06-29 23:41:06 || 22 | 2016-06-29 23:41:06 || 23 | 2016-06-29 23:41:06 || 24 | 2016-06-29 23:41:06 || 25 | 2016-06-29 23:41:06 || 26 | 2016-06-29 23:41:06 || 27 | 2016-06-29 23:41:06 || 28 | 2016-06-29 23:41:06 || 29 | 2016-06-29 23:41:06 || 30 | 2016-06-29 23:41:06 |+----+---------------------+30 rows in set (0.00 sec)恢复完成!以上就是本文的全部内容,在操作数据库时候要多加小心尽量避免误操作,如果万一遇到了,希望本文能够帮助大家。
声明:本页内容来源网络,仅供用户参考;我单位不保证亦不表示资料全面及准确无误,也不保证亦不表示这些资料为最新信息,如因任何原因,本网内容或者用户因倚赖本网内容造成任何损失或损害,我单位将不会负任何法律责任。如涉及版权问题,请提交至online#300.cn邮箱联系删除。
MySQL设计和命令行模式下建立详解系列文章:MySQL设计和命令行模式下建立详解C++利用MySQLAPI连接和操作数据库实例详解1.数据表的设计MySQL数
注意:在进行以下操作前,应先备份Windows注册表,以便执行了错误操作后能及时恢复系统。 删除多余的虚拟光驱图标 当我们在系统中安装了虚拟光驱后,&ldq
金山数据恢复大师是金山软件推出的一个功能强大的数据恢复软件,它能帮你找回各种误操作而丢失的文件,支持多种恢复方式,使用简单,恢复快速。今天呢,小编就给大家介绍下
上篇文章给大家介绍了Mongodb中MapReduce实现数据聚合方法详解,我们提到过Mongodb中进行数据聚合操作的一种方式——MapReduce,但是在大
MySQL的binlog日志是MySQL日志中非常重要的一种日志,记录了数据库所有的DML操作。通过binlog日志我们可以进行数据库的读写分离、数据增量备份以