MySQL之truncate表后恢复思路整理(前提是有备份且开启binlog)
来源:互联网 发布:决战武林宠物进阶数据 编辑:程序博客网 时间:2024/05/02 02:40
1.1对数据库thunder进行备份
mysqldump -S /tmp/mysql3316.sock --single-transaction --master-data=2 thunder >thunder_full_2015112.sql
1.2进行truncate table操作并insert into table
(work)root@localhost:mysql3316.sock [(none)]>select * from thunder.tb1;+----+---------+| id | name |+----+---------+| 1 | test || 4 | thun || 5 | thunder || 6 | thun || 7 | thun || 8 | thun || 9 | test |+----+---------+7 rows in set (0.00 sec)(work)root@localhost:mysql3316.sock [(none)]>truncate table thunder.tb1;Query OK, 0 rows affected (0.02 sec)(work)root@localhost:mysql3316.sock [(none)]>insert into thunder.tb1(name) values('like'); Query OK, 1 row affected (0.00 sec)(work)root@localhost:mysql3316.sock [(none)]>insert into thunder.tb1(name) values('lik');Query OK, 1 row affected (0.00 sec)(work)root@localhost:mysql3316.sock [(none)]>insert into thunder.tb1(name) values('li');Query OK, 1 row affected (0.00 sec)(work)root@localhost:mysql3316.sock [(none)]>insert into thunder.tb1(name) values('l');Query OK, 1 row affected (0.00 sec)(work)root@localhost:mysql3316.sock [(none)]>select * from thunder.tb1;+----+---------+| id | name |+----+---------+| 1 | test || 4 | thun || 5 | thunder || 6 | thun || 7 | thun || 8 | thun || 9 | test || 10 | like || 11 | lik || 12 | li || 13 | l |+----+---------+11 rows in set (0.00 sec)
1.3查看备份时binlog位置并找出误操作语句的位置
[root@mysqlnode1 mysql3316]# grep MASTER thunder_full_2015112.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=25191;[root@mysqlnode1 ~]# mysql -S /tmp/mysql3316.sock -e "show binlog events in 'mysql-bin.000003'" |grep -i truncatemysql-bin.000003 1011 Query 1163316 1091 truncate mysql.dbmysql-bin.000003 25239 Query 1163316 25328 truncate table thunder.tb1
1.4用mysqlbinlog命令在binlog中找出相关记录
[root@mysqlnode1 mysql3316]# mysqlbinlog -v --base64-output=decode-rows /data/mysql/mysql3316/logs/mysql-bin.000003 >3.sql [root@mysqlnode1 mysql3316]# vim 3.sql # at 25191#151122 19:59:50 server id 1163316 end_log_pos 25239 CRC32 0xac936e4e GTID [commit=yes]SET @@SESSION.GTID_NEXT= 'c009ae77-8def-11e5-ab11-000c29ea831c:78'/*!*/;# at 25239#151122 19:59:50 server id 1163316 end_log_pos 25328 CRC32 0xd020ddc8 Query thread_id=175 exec_time=0 error_code=0SET TIMESTAMP=1448193590/*!*/;truncate table thunder.tb1/*!*/;# at 25328#151122 20:00:50 server id 1163316 end_log_pos 25376 CRC32 0xa12c299c GTID [commit=yes]SET @@SESSION.GTID_NEXT= 'c009ae77-8def-11e5-ab11-000c29ea831c:79'/*!*/;# at 25376#151122 20:00:50 server id 1163316 end_log_pos 25444 CRC32 0xaa7072db Query thread_id=175 exec_time=0 error_code=0SET TIMESTAMP=1448193650/*!*/;BEGIN/*!*/;# at 25444#151122 20:00:50 server id 1163316 end_log_pos 25496 CRC32 0xd45864c2 Table_map: `thunder`.`tb1` mapped to number 267# at 25496#151122 20:00:50 server id 1163316 end_log_pos 25541 CRC32 0x71343558 Write_rows: table id 267 flags: STMT_END_F### INSERT INTO `thunder`.`tb1`### SET### @1=1### @2='like'# at 25541#151122 20:00:50 server id 1163316 end_log_pos 25572 CRC32 0xd860159a Xid = 4442COMMIT/*!*/;经查看在备份前master status位置后紧跟truncate命令,而后是insert,所以不需要执行binlog增量恢复,只需要把insert的数据还原,此处如果有数据,在导入备份后还需要执行这条语句mysqlbinlog --start-position=18311 --stop-position=19557 mysql-bin.000003 |mysql -S /tmp/mysql3316.sock thunder其中--start-position为数据备份文件中的位置,--stop-position为误操作前的位置
。
1.5新建一个数据库thued,并还原之前thunder库的备份
[root@mysqlnode1 mysql3316]# mysql -S /tmp/mysql3316.sock thued<thunder_full_2015112.sql
1.6在thued.tb1中插入新增加的数据,并查看
(work)root@localhost:mysql3316.sock [thued]>insert into tb1(name) select name from thunder.tb1;Query OK, 4 rows affected (0.00 sec)Records: 4 Duplicates: 0 Warnings: 0(work)root@localhost:mysql3316.sock [thued]>select * from tb1; +----+---------+| id | name |+----+---------+| 1 | test || 4 | thun || 5 | thunder || 6 | thun || 7 | thun || 8 | thun || 9 | test || 10 | like || 11 | lik || 12 | li || 13 | l |+----+---------+11 rows in set (0.00 sec)
1.7删除thunder.tb1,把thued.tb1改名成thunder.tb1
(work)root@localhost:mysql3316.sock [thued]>drop table thunder.tb1;Query OK, 0 rows affected (0.00 sec)(work)root@localhost:mysql3316.sock [thued]>rename table thued.tb1 to thunder.tb1;Query OK, 0 rows affected (0.00 sec)(work)root@localhost:mysql3316.sock [thued]>select * from thunder.tb1;+----+---------+| id | name |+----+---------+| 1 | test || 4 | thun || 5 | thunder || 6 | thun || 7 | thun || 8 | thun || 9 | test || 10 | like || 11 | lik || 12 | li || 13 | l |+----+---------+11 rows in set (0.00 sec)
泰岳MySQL DBA 学院 二期
本文出自 “ThunderMeng” 博客,请务必保留此出处http://thundermeng.blog.51cto.com/9414441/1715746
0 0
- MySQL之truncate表后恢复思路整理(前提是有备份且开启binlog)
- mysql之 innobackupex备份+binlog日志的完全恢复(命令行执行模式)
- mysql 整理之mysql 备份和恢复
- mysql之 binlog维护详细解析(开启、binlog相关参数作用、mysqlbinlog解读、binlog删除)
- Truncate 表之恢复
- mysql dump备份恢复,binlog基于时间点的恢复
- Mysql之binlog日志恢复操作记录
- 无备份恢复truncate掉的表
- Mysql总结4-binlog备份恢复及集群
- MySQL恢复之update忘加where条件误操作后数据恢复(提前条件binlog为row行格式)
- 开启Mysql binlog
- MySQL开启binlog日志
- MySQL开启binlog
- MySQL通过Binlog恢复删除的表
- MySQL(二)BinLog恢复数据
- mysql binlog恢复数据库
- MYSQL数据BINLOG恢复
- mysql binlog恢复数据
- Shanghai
- zabbix 编译安装
- Hadoop HA
- FastDFS+Nginx安装配置
- FastDFS配置和运行中出现的问题及解决(FAQ)
- MySQL之truncate表后恢复思路整理(前提是有备份且开启binlog)
- TPCC-MySQL基准测试
- MySQL各种日志总结
- InputStreamReader和BufferedReader用法及真实案例
- String 转化成java.sql.Date和java.sql.Time
- 在做自动化测试之前你需要知道的
- Android 让一个线程控制另一个线程
- 【剑指offer】2.3.1 数组——面试题3:二维数组中的查找
- Xcode7.2版本carthage包管理器兼容问题解决