Technology sharing | mysql access to DBeaver case sharing

Author: xuty

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

I. Outline

This article shares some improvements to my2sql and a practical case of connecting it to DBeaver for convenient development.

I believe that many developers will encounter daily DBA accidentally deleted data (DML flashback) or need requirements traceability data reasons for the changes (to resolve the original SQL), and when this demand increasingly more time (Do not ask why, say more than all It is tears), you will be very tired of this kind of work, so you want to implement a set of tools for development or implementation yourself, without the need for DBA access processing.

My2sql is one of the better binlog parsing tools that I have used personally, because it has excellent parsing performance and is relatively simple to use.

1. Accidentally deleted data flashback: relying on the function of mysql, it can quickly generate a DML operation rollback statement for the specified operation of the specified table, and after confirmation, execute the rollback SQL to restore the data.
2. Trace the reason of data change: My2sql cannot complete this function, it needs to rely on DBA to manually parse binlog, analyze rows_query_log, and get original execution SQL for further analysis.

Let's take the following figure as an example: the flashback function needs to implement rollback to generate the corresponding DML rollback SQL, which is a function that comes with mysql; and if you want to know the real reason for the data change, you need to implement query to get the original execution SQL It is better to analyze (of course, you need to ensure that binlog_rows_query_log_events is enabled in MySQL to record the original SQL in the binlog).

2. My2sql improvement

In order to make it more convenient for non-DBAs to use, some improvements have been made to mysql, mainly including 2 points:

1. To realize the query capability is actually to add Rows_query on the original basis, so that my2sql can also parse the corresponding original SQL according to the database table and operation type, instead of only implementing the rollback of row data before SQL or Roll SQL.
2. The use of native mysql usually requires specify the star-file, that is, the starting binlog, which is troublesome for those who are not familiar with MySQL. If you do not specify the starting binlog and only specify the time range, then mysql will start from the first binlog. It is time-consuming to start parsing, and the performance is not good. My approach here is to only need to input the time range, because this is relatively simple for development, and the binlog range will be automatically adapted through the input time range. The principle is that each binlog that exists in MySQL will only be parsed Its first event, get each binlog time range, and then automatically match the corresponding binlog range according to the input time range, so that you don't need to parse the useless binlog and save time.

Tucao: It is really difficult to get the time interval of each binlog in MySQL, can't the official add time output in show binary logs?

BEGIN
/*!*/;
# at 291
#220117 12:49:23 server id 1000000  end_log_pos 328 CRC32 0xc0df907c    Rows_query
# delete from a
# at 328
#220117 12:49:23 server id 1000000  end_log_pos 373 CRC32 0x7bc9707d    Table_map: `test`.`a` mapped to number 5705
# at 373
#220117 12:49:23 server id 1000000  end_log_pos 435 CRC32 0x15ae3096    Delete_rows: table id 5705 flags: STMT_END_F
### DELETE FROM `test`.`a`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`a`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`a`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2=3 /* INT meta=0 nullable=1 is_null=0 */
# at 435
#220117 12:49:23 server id 1000000  end_log_pos 466 CRC32 0x22eae195    Xid = 548
COMMIT/*!*/;

3. Access DBeaver

After the above improvement of my2sql, it is actually very friendly to non-DBAs (no need to input the starting binlog), because only need to input some parameters to call my2sql to use.

./my2sql -user root -password xxx -host 127.0.0.1 -port 3306 \
-databases test -tables a -sql delete \
-start-datetime "2021-04-02 11:05:00" -stop-datetime "2021-04-02 11:06:00" \
-work-type rollback \
-add-extraInfo -output-dir /opt/tmp

But the development will also say that there is no graphics, I am not very good at getting the command line, I will add a graphics for you.

The company has a special development based on open source DBeaver, which has been transformed, adding some personalized functions, and using DBeaver as a unified database client tool for the whole company, so it should be very convenient to integrate my2sql into DBeaver. The approximate steps are: Use DBeaver to select a series of input parameters such as libraries, tables, and operation types, and then externally call the my2sql program to generate scripts (connection information is stored in DBeaver itself).

The general use is as follows:

rollback type:

query type:

4. Summary

For the improved use of my2sql, although it is not as accurate as the traditional DBA's analysis after specifying the corresponding binlog&position according to the misoperation, but for development, I feel it is enough, even if there are other non-misoperation rollback SQL in this time period, You can also filter by yourself.

Finally, my2sql only supports DML statements, so it does not add DDL parsing function, because DDL misoperation can only be restored through backup + binlog playback, and cannot be directly flashed back.