MySQL之truncate表后恢复思路整理(前提是有备份且开启binlog)_MySQL
May 27, 2016 pm 01:46 PM1.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 truncate mysql-bin.000003 1011 Query 1163316 1091 truncate mysql.db mysql-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=0 SET 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=0 SET 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 = 4442 COMMIT/*!*/;
登录后复制
经查看在备份前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之truncate表后恢复思路整理(前提是有备份且开启binlog)_MySQL的内容,更多相关内容请关注PHP中文网(www.php.cn)!
本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

热门文章
R.E.P.O.能量晶体解释及其做什么(黄色晶体)
2 周前
By 尊渡假赌尊渡假赌尊渡假赌
仓库:如何复兴队友
4 周前
By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒险:如何获得巨型种子
3 周前
By 尊渡假赌尊渡假赌尊渡假赌
击败分裂小说需要多长时间?
3 周前
By DDD
R.E.P.O.保存文件位置:在哪里以及如何保护它?
3 周前
By DDD

热门文章
R.E.P.O.能量晶体解释及其做什么(黄色晶体)
2 周前
By 尊渡假赌尊渡假赌尊渡假赌
仓库:如何复兴队友
4 周前
By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒险:如何获得巨型种子
3 周前
By 尊渡假赌尊渡假赌尊渡假赌
击败分裂小说需要多长时间?
3 周前
By DDD
R.E.P.O.保存文件位置:在哪里以及如何保护它?
3 周前
By DDD

热门文章标签

记事本++7.3.1
好用且免费的代码编辑器

SublimeText3汉化版
中文版,非常好用

禅工作室 13.0.1
功能强大的PHP集成开发环境

Dreamweaver CS6
视觉化网页开发工具

SublimeText3 Mac版
神级代码编辑软件(SublimeText3)

如何修复 MySQL 8.4 上的 mysql_native_password 未加载错误
