Flashback feature for MySQL

叁歲伎倆 2022-09-21 01:27 184阅读 0赞

本文转载自：http://mysql.taobao.org/index.php/Patch\_source\_code\#Add\_flashback\_feature\_for\_mysqlbinlog

# Patch source code #

<table style="font-size:12px; color:black; background-color:rgb(249,249,249); border:1px solid rgb(170,170,170); padding:5px"> 
 <tbody> 
 <tr> 
 <td> 
 <div style="text-align:center"> 
 <h2 style="margin:0px 0px 0.6em; padding:0px; border:none; font-size:12px; display:inline"> 目录</h2> &nbsp; 
 [<a style="color:rgb(90,54,150)" rel="nofollow">隐藏</a>] 
 </div> 
 <ul style="line-height:1.5em; list-style-type:none; margin:0.3em 0px 0px; padding:0px"> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#Server_Patches_.28Base_Percona_5.5.18.29" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">1&nbsp;Server Patches (Base Percona 5.5.18)</a> 
 <ul style="line-height:1.5em; list-style-type:none; margin:0px 0px 0px 2em; padding:0px"> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#Multi-master_replication" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">1.1&nbsp;Multi-master replication</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#Kill_idle_transaction" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">1.2&nbsp;Kill idle transaction</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#per-thread_memory_usage_statistics" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">1.3&nbsp;per-thread memory usage statistics</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#buf_fix_:_create_table_using_convert_may_cause_replication_stop" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">1.4&nbsp;buf fix&nbsp;: create table using convert may cause replication stop</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#InnoDB_pre-alloc_tablespace" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">1.5&nbsp;InnoDB pre-alloc tablespace</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#Optimization_when_copy_int_data" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">1.6&nbsp;Optimization when copy int data</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#Correct_update_time_of_InnoDB_table" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">1.7&nbsp;Correct update_time of InnoDB table</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#Optimization_of_.22order_by_rand.28.29.22" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">1.8&nbsp;Optimization of "order by rand()"</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#Purge_thread_exception_cause_innodb_init_fail" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">1.9&nbsp;Purge thread exception cause innodb init fail</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#Fix_bug_caused_by_userstat" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">1.10&nbsp;Fix bug caused by userstat</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#A_new_value_of_slave_exec_mode_to_handle_slave_error" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">1.11&nbsp;A new value of slave_exec_mode to handle slave error</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#A_new_column_shows_the_active_time_of_current_transaction" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">1.12&nbsp;A new column shows the active time of current transaction</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#Doublebinlog_for_MySQL" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">1.13&nbsp;Doublebinlog for MySQL</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#dynamic_switch_for_performance_schema" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">1.14&nbsp;dynamic switch for performance schema</a></li> 
 </ul> </li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#mysql_tools_patches_.28Base_Percona_5.5.18.29" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">2&nbsp;mysql tools patches (Base Percona 5.5.18)</a> 
 <ul style="line-height:1.5em; list-style-type:none; margin:0px 0px 0px 2em; padding:0px"> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#Full_query_for_mysqldump" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">2.1&nbsp;Full query for mysqldump</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#Report_progress_for_mysqldump" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">2.2&nbsp;Report progress for mysqldump</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#Forbid_add-drop-database_deleting_.60mysql.60" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">2.3&nbsp;Forbid add-drop-database deleting `mysql`</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#prefetch_data_for_slave_with_relay-fetch" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">2.4&nbsp;prefetch data for slave with relay-fetch</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#Add_flashback_feature_for_mysqlbinlog" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">2.5&nbsp;Add flashback feature for mysqlbinlog</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#Flashback_feature_for_MySQL" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">2.6&nbsp;Flashback feature for MySQL</a></li> 
 </ul> </li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#Bug_fixes_and_feature_enhancement_in_community" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">3&nbsp;Bug fixes and feature enhancement in community</a> 
 <ul style="line-height:1.5em; list-style-type:none; margin:0px 0px 0px 2em; padding:0px"> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#mysql_enhance_semi-sync_replication_feature_.28MySQL-5.5.29" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">3.1&nbsp;mysql enhance semi-sync replication feature (MySQL-5.5)</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#the_original_table_missing_while_execute_the_ddl_.27alter_table_add_column_.27" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">3.2&nbsp;the original table missing while execute the ddl 'alter table add column '</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#event_is_passed_back_and_forth_between_dual_master_if_event_is_from_some_other_mysqld" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">3.3&nbsp;event is passed back and forth between dual master if event is from some other mysqld</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#connection_is_refused_while_excuting_.22flush_privileges.22" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">3.4&nbsp;connection is refused while excuting "flush privileges"</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#init_connect_would_kill_current_connection_if_read_only_is_on_for_some_case" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">3.5&nbsp;init_connect would kill current connection if read_only is on for some case</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#enhanced_binlog_row_verbose_shown_for_the_escape_characters" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">3.6&nbsp;enhanced binlog row verbose shown for the escape characters</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#Fix_MVCC_broken_with_implicit_lock" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">3.7&nbsp;Fix MVCC broken with implicit lock</a></li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#Fix_for_bug_of_optimizer" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">3.8&nbsp;Fix for bug of optimizer</a></li> 
 </ul> </li> 
 <li style="margin-bottom:0.1em"><a href="http://mysql.taobao.org/index.php/Patch_source_code#MySQL_Transfer_.28Base_MySQL.2FOracle_5.1.48.29" style="text-decoration:none; color:rgb(90,54,150)" rel="nofollow">4&nbsp;MySQL Transfer (Base MySQL/Oracle 5.1.48)</a></li> 
 </ul> </td> 
 </tr> 
 </tbody> 
</table>

# Server Patches (Base Percona 5.5.18) #

All the patches list here can be use "patch -p0 < fname" directly on Percona Server 5.5.18.

#### Multi-master replication ####

from [plinux][]

There is some requirement that one slave replicates data from several masters. Multi-master replication patch modifies the slave server, makes it can register itself as slave of more than one masters. Data from these masters should guaranteed that there are no conflicts.

[\[source code\]][source code]

#### Kill idle transaction ####

from [plinux][]

MySQL Server Kill Idle Transaction patch, it can clear all idle transaction and release their locks. I provide three options, they can control how long to kill read-only transactions or write transactions, and all transactions.

[\[source code\]][source code 1]

#### per-thread memory usage statistics ####

from [plinux][]

This patch can display how many memory used of threads, it will let DBAs know the memory cost detail.

[\[source code\]][source code 2]

#### buf fix : create table using convert may cause replication stop ####

from [dingqi][]

As descripe [here][], when runs create table statement "create table tmpp as select b.a, cast(substr(a.feature,15) as signed integer) from t1 a, t2 b;" , the binlog will record the third column as bigint(7986). This query can not reply in slave, because the fieldwidth of a bigint can not bigger than 255.

This patch changes the statement that log into master's binlog. It limits the fieldwidth of a bigint and int, not more than 255.

[\[source code\]][source code 3]

#### InnoDB pre-alloc tablespace ####

from [dingqi][]

In some applications, the InnoDB tables grow rapidly. If the tablespace can be extended beforehand, the tps can be benefit.

New command "alter tablespace \`dbname/tblname\` set extent\_size=1024;" can be used to extend the size of tblname.ibd file to 16M.

[\[source code\]][source code 4]

#### Optimization when copy int data ####

from [dingqi][]

Code of translating from little-endian to big-endian cost CPU. We can change it from using "for" statement to assamble code. This save 10% of cpu usage in my test case, which needs to retrive lots of int data from InnoDB engine to MySQL server.

[\[source code\]][source code 5]

#### Correct update\_time of InnoDB table ####

from [dingqi][]

InnoDB tables always have an update\_time of NULL when doing SHOW TABLE STATUS. It is true that there is not place to record the last update time of a table, but for tables that created under innodb\_file\_per\_table mode, which is more common now, we can simply get it from the mtime of the ibd file.

[\[source code\]][source code 6]

#### Optimization of "order by rand()" ####

from [dingqi][]

As "explain select \* from table\_name order by rand()" , we can find "Using temporary" in the Extra info. This patch makes such a query do not use temporary table, so improve performance.

[\[source code\]][source code 7]

#### Purge thread exception cause innodb init fail ####

from [yinfeng][]

The server can't start When starting with innodb\_force\_recovery>2 and innodb\_purge\_thread =1

[\[source code\]][source code 8]

#### Fix bug caused by userstat ####

from [yinfeng][]

mysqld can't startup when a plugin had being installed and userstat=1

[\[source code\]][source code 9]

#### A new value of slave\_exec\_mode to handle slave error ####

from [yinfeng][]

a new value of slave\_exec\_mode to handle slave error: SMART, and the basic idea is:

1) HA\_ERR\_KEY\_NOT\_FOUND  
UPDATE\_ROWS\_EVENT: write 'Before Image' of the record, and then update it  
DELETE\_ROWS\_EVENT: write the record and then delete it

2)HA\_ERR\_FOUND\_DUPP\_KEY  
WRITE\_ROWS\_EVENT: just overwrite the record  
UPDATE\_ROWS\_EVENT : Not handled

[\[source code\]][source code 10]

#### A new column shows the active time of current transaction ####

from [dingqi][]

Adding a new column in the result of "show processlist". Some clients will run "begin; select \* from t limit n",and then idle, which will affect the server. Such as the InnoDB share systemspace will become large. Such clients maybe bug from programs or some unreasonable operations from manual, monitors can get information from the output,warning or kill idle threads.

[\[source code\]][source code 11]

#### Doublebinlog for MySQL ####

from [dingqi][]

In some projects that require very strict data safety, DBAs will except there is a solution to ensure the safety of binlog. That means after the worst case, OS crash or disk damage, there is a way to find out a whole copy of binlog.

Doublebinlog patch is done for this requirement. It offers a switch to enable the behavior: any write and fsync operation on binary logs are duplicated in another file. So if the original binlog file is destroyed by disk damage, the bak file can be used.

[\[source code\]][source code 12] [More Detail][]

#### dynamic switch for performance schema ####

from [dingqi][]

The Performance Schema provides a way to inspect internal execution of the server at runtime. But not a dynamic switch. This patch provides a parameter to switch the dynamically.

set global performance_schema_collecting = on/off;

[\[source code\]][source code 13]

# mysql tools patches (Base Percona 5.5.18) #

#### Full query for mysqldump ####

from [dingqi][]

Assume that we need to dump table A using this query "select A.\* from A,B where A.f1=B.f1 and B.f2=xxx", there are no proper parameters for the requirement in an efficient way. This patch of mysqldump provide a param "full-query" to allow users write full query to dump data.

The usage can be found in the test case in patch file.

[\[source code\]][source code 14]

#### Report progress for mysqldump ####

from [dingqi][]

Add progress report when mysqldump running. New parameter --report-progress for it.

Note:

1) if "-r" is not specified, report-progress is disable forcely.

2) if "--tab" is specified, report-progress can not take effect.

[\[source code\]][source code 15]

#### Forbid add-drop-database deleting \`mysql\` ####

from [dingqi][]

In normal running mysqld, the "log\_slow\_queries" is ON.

If runs mysqldump with --all-databases --add-drop-database and then run "mysql -e 'source dumpfile'", there is an error shows

"ERROR 1580 .... You cannot 'DROP' a log table if logging is enabled".

So we should forbid the dumpped file deleting database \`mysql\`.

[\[source code\]][source code 16]

#### prefetch data for slave with relay-fetch ####

from [yinfeng][]

A tool used to prefetch realy log for slave based on row replication, speeding the slave's performance, therefore, reduced the gap between master and slave without any injection for MySQL.

[\[source code][source code 17]\]

#### Add flashback feature for mysqlbinlog ####

from [plinux][]

Add an option "-B/--flashback" for mysqlbinlog tool, it can reverse binlog event type and reverse execution sequence.

[\[source code\]][source code 18]

[\[source code, mysql CLI version\]][source code_ mysql CLI version]

#### Flashback feature for MySQL ####

from [plinux][] [dingqi][]

This patch allows you flashback to a point of MySQL, after mis-operation such as "forgot where clause when update or delete" and "drop or truncate table ", a new parameter -B for mysqlbinlog can provide the flashback statements.

This patch includes the flashback feature in above. What difference is that this one includes flashback for DDL operatins. So it changes the server code.

[\[source code\]][source code 19]

# Bug fixes and feature enhancement in community #

#### mysql enhance semi-sync replication feature (MySQL-5.5) ####

from orczhou

[resource in launchpad][]

#### the original table missing while execute the ddl 'alter table add column ' ####

from [xiyu][]

The patch fixed the possible thread deadlock risk for DDL. Tweaked patch is merged into 5.1.62, 5.5.22, 5.6.5.

Detail info and related patch would refer to: [related bug][]

#### event is passed back and forth between dual master if event is from some other mysqld ####

from [xiyu][]

Fixed the event with another server\_id passing between dual-master, for the fail-over cluster. This patch fixed Taobao's automate switching detected bug.

Detail info and related patch would refer to: [related bug][related bug 1]

#### connection is refused while excuting "flush privileges" ####

from [xiyu][]

When "flush privileges", most of the connections would be rejected, and it's caused a serious bug in FEED cluster of Taobao. This patch fixed the bug above.

Detail info and related patch would refer to: [related bug][related bug 2]

#### init\_connect would kill current connection if read\_only is on for some case ####

from [xiyu][]

For the sake of auditing, init\_connect is set with inserting access log info to accesslog table Once we forgot the remove the init\_connect content after switch of master-slave, all connection would be killed. It's patched for all inner versions.

Detail info and related patch would refer to [related bug][related bug 3]

#### enhanced binlog row verbose shown for the escape characters ####

from [xiyu][]

The stock binlog verbose output for row format is not friendly if we want to used for some other usage, such as rollback from binlog. This patch fixed the existing issues used for rollbacking from binlog, and used for TMHA.

Detail info and related patch would refer to [related bug][related bug 4]

#### Fix MVCC broken with implicit lock ####

from [plinux][]

Because Primary Key Index use the del-marked entry to store new inserted entry, Secondary Index can't judge implicit lock.

Detail info and related patch would refer to [related bug][related bug 5] [\[source code\]][source code 20]

#### Fix for bug of optimizer ####

from [zzx][]

Different cost calculation of ref and range result in worse query plan. The bug was first seen at [Bug\#12113][Bug_12113]

[\[source code\]][source code 21]

# MySQL Transfer (Base MySQL/Oracle 5.1.48) #

from [dingqi][]

Transfer-MySQL is a tool for improving replication performance between MySQL master and slaves. Transfer is forked from MySQL source code (5.1.48) and acts as an independent process.

The struct simply as \[MASTER -> TRANSFER => SLAVE\]. TRANSFER is the direct slave of MASTER, and update SLAVE as a common client.

[More Detail][More Detail 1]

An certainly, the "Transfer" can simply act as a slave,when we patch it to the slave.

注：近期参加MySQL运维学习，老师推荐该文章作为学习和技术提高的扩展阅读，先记录到自己的博客中，随后慢慢消化、学习、提高。本文章与“日志系统”主题相关。

[plinux]: http://mysql.taobao.org/index.php/%E7%94%A8%E6%88%B7:Plinux
[source code]: http://mysql.taobao.org/images/3/30/5.5.18_multi_master_repl.diff
[source code 1]: http://mysql.taobao.org/images/4/48/5.5.18_server_kill_idle_trx.diff
[source code 2]: http://mysql.taobao.org/images/1/1f/5.5.18_thread_mem_usage.diff
[dingqi]: http://mysql.taobao.org/index.php/%E7%94%A8%E6%88%B7:Dingqi.lxb
[here]: http://bugs.mysql.com/bug.php?id=66773
[source code 3]: http://mysql.taobao.org/images/b/b1/66773.diff
[source code 4]: http://mysql.taobao.org/images/3/3a/Extend_space.diff
[source code 5]: http://mysql.taobao.org/images/e/ec/Swap_int_assemble.diff
[source code 6]: http://mysql.taobao.org/images/2/22/Innodb_update_time.diff
[source code 7]: http://mysql.taobao.org/images/3/3d/Order_by_rand.diff
[yinfeng]: http://mysql.taobao.org/index.php/%E7%94%A8%E6%88%B7:Yinfeng.zwx
[source code 8]: http://mysql.taobao.org/images/3/30/Infinite_loop_with_recovery.diff
[source code 9]: http://mysql.taobao.org/images/f/fa/Userstat_crash.diff
[source code 10]: http://mysql.taobao.org/images/7/7d/Smart.diff
[source code 11]: http://mysql.taobao.org/images/e/e2/Processlist_trans_active.PS5518.diff
[source code 12]: http://mysql.taobao.org/images/5/58/Doublebinlog.PS5518.diff
[More Detail]: http://http//dinglin.iteye.com/blog/1773683
[source code 13]: http://mysql.taobao.org/images/9/96/Patch.performance_schema_switch.PS5518.diff
[source code 14]: http://mysql.taobao.org/images/4/4a/Mysqldump_full_query.diff
[source code 15]: http://mysql.taobao.org/images/0/02/Mysqldump_report_progress.diff
[source code 16]: http://mysql.taobao.org/images/4/40/Mysqldump_drop_mysql_database.diff
[source code 17]: http://code.google.com/p/relay-fetch/
[source code 18]: http://mysql.taobao.org/images/0/0f/5.5.18_flashback.diff
[source code_ mysql CLI version]: http://mysql.taobao.org/images/2/25/5.5.18_flashback_CLI.diff
[source code 19]: http://mysql.taobao.org/images/5/53/5.5.18_flashback_all.diff
[resource in launchpad]: https://code.launchpad.net/~orczhou/mysql-server/ESR
[xiyu]: http://mysql.taobao.org/index.php/%E7%94%A8%E6%88%B7:Xiyu.lh
[related bug]: http://bugs.mysql.com/bug.php?id=62100
[related bug 1]: https://bugs.launchpad.net/percona-server/+bug/940404
[related bug 2]: https://bugs.launchpad.net/percona-server/+bug/966254
[related bug 3]: https://bugs.launchpad.net/percona-server/+bug/1014991
[related bug 4]: https://bugs.launchpad.net/percona-server/+bug/966148
[related bug 5]: http://bugs.mysql.com/bug.php?id=65389
[source code 20]: http://mysql.taobao.org/images/1/16/5.5.18_bug65389_mvcc_implicit_lock.diff
[zzx]: http://mysql.taobao.org/index.php/%E7%94%A8%E6%88%B7:Supu.zzx
[Bug_12113]: http://bugs.mysql.com/bug.php?id=12113
[source code 21]: http://mysql.taobao.org/images/0/02/Optimizerfixbug12113.diff
[More Detail 1]: http://dinglin.iteye.com/blog/1672742