利用MyFlash闪回丢失数据
[root@zlm1 :: /vagrant/MyFlash-master]
#ls -l
total
drwxrwxrwx vagrant vagrant Jun : binary
-rwxrwxrwx vagrant vagrant Oct build.sh
drwxrwxrwx vagrant vagrant Jun : doc
-rwxrwxrwx vagrant vagrant Oct License.md
-rwxrwxrwx vagrant vagrant Oct README.md
drwxrwxrwx vagrant vagrant Jun : source
drwxrwxrwx vagrant vagrant Jun : testbinlog
the official document recommend to install the tool by dynamic compliling link way to install,but i prefer to use the binary way instead.let's see the parameter and usage of the command:
[root@zlm1 :: /vagrant/MyFlash-master/binary]
#./flashback --help
Usage:
flashback [OPTION...]
Help Options:
-h, --help Show help options
Application Options:
--databaseNames databaseName to apply. if multiple, seperate by comma(,)
--tableNames tableName to apply. if multiple, seperate by comma(,)
--start-position start position
--stop-position stop position
--start-datetime start time (format %Y-%m-%d %H:%M:%S)
--stop-datetime stop time (format %Y-%m-%d %H:%M:%S)
--sqlTypes sql type to filter . support INSERT, UPDATE ,DELETE. if multiple, seperate by comma(,)
--maxSplitSize max file size after split, the uint is M
--binlogFileNames binlog files to process. if multiple, seperate by comma(,)
--outBinlogFileNameBase output binlog file name base
--logLevel log level, available option is debug,warning,error
--include-gtids gtids to process
--exclude-gtids gtids to skip
root@localhost:mysql3306.sock [zlm]::>create table test_flashbk(
-> id bigint not null auto_increment,
-> name varchar() not null default '',
-> primary key(id)
-> ) engine=innodb default charset=utf8mb4;
Query OK, rows affected (0.04 sec) root@localhost:mysql3306.sock [zlm]::>delimiter //
root@localhost:mysql3306.sock [zlm]::>create procedure pro_flashbk()
-> begin
-> declare id int;
-> set id = ;
-> while id> do
-> insert into test_flashbk(name) values ('aaron8219');
-> set id=id-;
-> end while;
-> end //
Query OK, rows affected (0.04 sec) root@localhost:mysql3306.sock [zlm]::>delimiter ;
root@localhost:mysql3306.sock [zlm]::>call pro_flashbk();
Query OK, row affected (11.06 sec) root@localhost:mysql3306.sock [zlm]::>select count(*) from test_flashbk;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.02 sec) root@localhost:mysql3306.sock [zlm]::>select id,name from test_flashbk limit ,;
+----+-----------+
| id | name |
+----+-----------+
| | aaron8219 |
| | aaron8219 |
| | aaron8219 |
| | aaron8219 |
| | aaron8219 |
+----+-----------+
rows in set (0.00 sec) root@localhost:mysql3306.sock [zlm]::>select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| |
+--------------+
row in set (0.03 sec) root@localhost:mysql3306.sock [zlm]::>
root@localhost:mysql3306.sock [zlm]::>update test_flashbk set name='zlm';
Query OK, rows affected (2.29 sec)
Rows matched: Changed: Warnings: root@localhost:mysql3306.sock [zlm]::>select id,name from test_flashbk limit ,;
+----+------+
| id | name |
+----+------+
| | zlm |
| | zlm |
| | zlm |
| | zlm |
| | zlm |
+----+------+
rows in set (0.00 sec) root@localhost:mysql3306.sock [zlm]::>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.04 sec) root@localhost:mysql3306.sock [zlm]::>exit
Bye [root@zlm1 :: ~]
#cd /data/mysql/mysql3306/logs [root@zlm1 :: /data/mysql/mysql3306/logs]
#ls -l
total
-rw-r----- mysql mysql May : mysql-bin.
-rw-r----- mysql mysql May : mysql-bin.
-rw-r----- mysql mysql May : mysql-bin.
-rw-r----- mysql mysql May : mysql-bin.
-rw-r----- mysql mysql Jun : mysql-bin.
-rw-r----- mysql mysql Jun : mysql-bin.
-rw-r----- mysql mysql Jun : mysql-bin.index [root@zlm1 :: /data/mysql/mysql3306/logs]
#
now,let's using the MyFlash tool to flashback the correct data.you should notice that only one binary log can be put in the parameter "binlogFileNames".it cannot be too big up to 256M,'cauze in my early case,i put 100w records into the test table at the begining,the operation was killed by OS automatically twice,it's amazing...sometime i'll test it again to find out the real reason for that.
[root@zlm1 :: ~]
#cd /vagrant/MyFlash-master/binary/ [root@zlm1 :: /vagrant/MyFlash-master/binary]
#ls -l
total
-rwxrwxrwx vagrant vagrant Oct flashback
-rwxrwxrwx vagrant vagrant Oct mysqlbinlog20160408 [root@zlm1 :: /vagrant/MyFlash-master/binary]
#./flashback --databaseNames zlm --tableNames test_flashbk --sqlTypes update --maxSplitSize= --binlogFileNames=/data/mysql/mysql3306/logs/mysql-bin. [root@zlm1 :: /vagrant/MyFlash-master/binary]
#ls -l
total
-rwxrwxrwx vagrant vagrant Jun : binlog_output_base.
-rwxrwxrwx vagrant vagrant Jun : binlog_output_base.
-rwxrwxrwx vagrant vagrant Jun : binlog_output_base.
-rwxrwxrwx vagrant vagrant Jun : binlog_output_base.
-rwxrwxrwx vagrant vagrant Jun : binlog_output_base.
-rwxrwxrwx vagrant vagrant Jun : binlog_output_base.
-rwxrwxrwx vagrant vagrant Jun : binlog_output_base.
-rwxrwxrwx vagrant vagrant Oct flashback
-rwxrwxrwx vagrant vagrant Oct mysqlbinlog20160408 [root@zlm1 :: /vagrant/MyFlash-master/binary]
#
[root@zlm1 :: ~]
#cat > recover.sh <<aaron8219
> #!/bin/bash
> BASEDIR=/vagrant/MyFlash-master/binary
> FILE=\`find \${BASEDIR} -name "binlog_output_base.00000*"|sort -n\`
> for i in \${FILE}
> do
> mysqlbinlog \${i} | mysql
> done
> aaron8219 [root@zlm1 :: ~]
#cat recover.sh
#!/bin/bash
BASEDIR=/vagrant/MyFlash-master/binary
FILE=`find ${BASEDIR} -name "binlog_output_base.00000*"|sort -n`
for i in ${FILE}
do
mysqlbinlog ${i} | mysql
done [root@zlm1 :: ~]
#ls -l
total
-rw-------. root root Jul anaconda-ks.cfg
-rwxr-xr-x root root Apr : mysqld.sh
-rwxr-xr-x root root Jun : mysql.sh
-rw-r--r-- root root Jun : recover.sh
-rw-r--r-- root root May : rename_tb.sql [root@zlm1 :: ~]
#sh recover.sh
ERROR (HY000) at line : @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.
ERROR (HY000) at line : @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.
ERROR (HY000) at line : @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.
ERROR (HY000) at line : @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.
ERROR (HY000) at line : @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.
ERROR (HY000) at line : @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.
Bug #85480 | @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON | ||
---|---|---|---|
Submitted: | 16 Mar 2017 12:01 | Modified: | 26 Mar 2017 19:04 |
Reporter: | kfpanda kf | Email Updates: | |
Status: | Verified | Impact on me: | None |
Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
Version: | 5.7.17 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
Description:
mysqlbinlog printed a ROLLBACK at the end of the binary log
file, which when played back caused the error
-@@SESSION.GTID_NEXT cannot be set to ANONYMOUS when
@@GLOBAL.GTID_MODE = ON..- This occurred when the binary log file
did not include any data related events. How to repeat: Generate a binary log file which did not include any data related events. mysql -uroot -p123456 mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 201 |
+------------------+-----------+
mysql> flush logs;
mysql> flush logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 201 |
| mysql-bin.000003 | 201 |
| mysql-bin.000004 | 201 |
+------------------+-----------+ # mysqlbinlog mysql-bin.000003|mysql -uroot -p'123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1782 (HY000) at line 19: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON. Suggested fix:
stops a ROLLBACK from setting gtid_next to ANONYMOUS when gtid_next has not yet been determined by a subsequent event.
root@localhost:mysql3306.sock [(none)]::>select @@global.gtid_next;
ERROR (HY000): Variable 'gtid_next' is a SESSION variable
root@localhost:mysql3306.sock [(none)]::>select @@gtid_next;
+-------------+
| @@gtid_next |
+-------------+
| AUTOMATIC |
+-------------+
row in set (0.00 sec) root@localhost:mysql3306.sock [(none)]::>
GTID
The GTID column contains the value of gtid_next
, which can be one of ANONYMOUS
, AUTOMATIC
, or a GTID using the formatUUID:NUMBER
. For transactions that use gtid_next=AUTOMATIC
, which is all normal client transactions, the GTID column changes when the transaction commits and the actual GTID is assigned. If gtid_mode
is either ON
or ON_PERMISSIVE
, the GTID column changes to the transaction's GTID. If gtid_mode
is either OFF
or OFF_PERMISSIVE
, the GTID column changes to ANONYMOUS
.
now i try to set gtid_mode=off_permissive step by step:
[root@zlm1 :: ~]
#mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.7.-log MySQL Community Server (GPL) Copyright (c) , , Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@localhost:mysql3306.sock [(none)]::>select @@global.gtid_mode;
+--------------------+
| @@global.gtid_mode |
+--------------------+
| ON |
+--------------------+
row in set (0.00 sec) root@localhost:mysql3306.sock [(none)]::>set @@global.gtid_mode=off_permissive;
ERROR (HY000): The value of @@GLOBAL.GTID_MODE can only be changed one step at a time: OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON. Also note that this value must be stepped up or down simultaneously on all servers. See the Manual for instructions.
root@localhost:mysql3306.sock [(none)]::>select @@global.gtid_mode;
+--------------------+
| @@global.gtid_mode |
+--------------------+
| ON_PERMISSIVE |
+--------------------+
row in set (0.00 sec) root@localhost:mysql3306.sock [(none)]::>set @@global.gtid_mode=off_permissive;
Query OK, rows affected (0.01 sec) root@localhost:mysql3306.sock [(none)]::>select @@global.gtid_mode;
+--------------------+
| @@global.gtid_mode |
+--------------------+
| OFF_PERMISSIVE |
+--------------------+
row in set (0.00 sec) root@localhost:mysql3306.sock [(none)]::>exit Bye
[root@zlm1 :: ~]
#sh recover.sh ERROR (HY000) at line : Can't find record in 'test_flashbk'
ERROR (HY000) at line : Can't find record in 'test_flashbk'
ERROR (HY000) at line : Can't find record in 'test_flashbk'
then i modify the shell script file add "--skip-gtids" but it still not work normally,this time,i even got the ERROR 1062 excepts the ERROR 1032:
[root@zlm1 :: ~]
#vi recover.sh [root@zlm1 :: ~]
#cat recover.sh
#!/bin/bash
BASEDIR=/vagrant/MyFlash-master/binary
FILE=`find ${BASEDIR} -name "binlog_output_base.00000*"|sort -n`
for i in ${FILE}
do
mysqlbinlog --skip-gtids ${i} | mysql
done [root@zlm1 :: ~]
#sh recover.sh
ERROR (HY000) at line : Can't find record in 'test_flashbk'
ERROR (HY000) at line : Can't find record in 'test_flashbk'
ERROR (HY000) at line : Can't find record in 'test_flashbk'
ERROR () at line : Duplicate entry '' for key 'PRIMARY'
ERROR () at line : Duplicate entry '' for key 'PRIMARY'
ERROR () at line : Duplicate entry '' for key 'PRIMARY' [root@zlm1 :: ~]
#
最新文章
- Android动画效果之Frame Animation(逐帧动画)
- 理解#define offsetof(struct_t,member) ((int)&;((struct_t *)0)->;member)
- Python笔记总结week8
- windows里面的批处理命令不停地处理同一条命令
- MySQL online ddl原理
- NopCommerce之任务执行
- Project Management - 3) Manage Your Meetings
- [深度学习]实现一个博弈型的AI,从五子棋开始(1)
- 什么是 lnmp 实现原理。
- 基于.net的分布式系统限流组件
- get请求02
- 安装完Ubuntu后要做的事情
- Vant UI 安装
- 基于Qt的图像处理技术和算法
- 卡方分布(Chi-Square Distribution):
- 《转载》RPC入门总结(一)RPC定义和原理
- unity Flash Animation Toolset插件使用
- MapReduce编程解析
- Redis-五种数据类型解析
- poi 读取数据处理方式
热门文章
- Linux基础之命令练习Day4-fdisk,mkfs,mlabel,mount,umount,mkswap,swapon,dd,top,free,ps,kill,rpm,yum,make
- POI读取xls和xlsx
- DataS-2
- Bash命令行 bash &;>; >;&; Bash One-Liners Explained
- php curl 总结
- spring的声明式事务,及redis事务。
- angular中ngOnChanges与组件变化检测的关系
- hdu 6208 The Dominator of Strings【AC自动机】
- 如何遍历一个JSON对象的属性值???
- Django 查询集的过滤内置条件