Recommend several MySQL related tools

Preface:

With the continuous development of Internet technology, the MySQL-related ecosystem has become more and more complete, and more and more tools have emerged. Some companies or individuals have released some good open source tools. This article mainly introduces several MySQL-related practical tools. As a reminder, graphical operation tools such as Navicat are not introduced here.

1. SQL Audit web tool

SQL auditing and execution may be a problem that troubles every company or team. Without platform tool support, it can only be handled manually, which is inefficient and the quality cannot be guaranteed. In order to standardize operations and improve efficiency, most teams may develop or introduce open source tools themselves. Here are two open source and free web-based SQL auditing platforms.

Yearning

Project address: github.com/cookieY/Yea…
Project introduction: guide.yearning.io/

Yearning is a web-based visual SQL auditing platform that can meet the SQL auditing needs of most companies. In addition to implementing the regular SQL audit function, a series of convenient functions such as data query are added.

Key features:

• Automated SQL statement auditing can automatically detect and execute SQL.
• A rollback statement is automatically generated after the DDL/DML statement is executed.
• Review/query audit function.
• Supports LDAP login/DingTalk and email message push.
• Supports custom review workflows.
• Supports fine-grained permission allocation.
  

advantage:

• Good-looking, smooth to use.
• Some audit rules can be customized.
• Easy deployment, few dependencies.
• It is rich in functions and can audit and execute SQL as well as query.
• The author is continuously maintaining it and it has community support.

Archery

Project address: https://github.com/hhyo/Archery
Use documentation: https://archerydms.com/

Archery is positioned as a SQL audit and query platform, aiming to improve the work efficiency of DBAs, support SQL launch and query of multiple databases, and support rich MySQL operation and maintenance functions. All functions are compatible with mobile phone operations.

Key features:

• Integrated SQL query, audit, execution, and backup.
• The authority is clearly divided and the review and execution are separated.
• Automatically approve SQL work orders and reject high-risk statements.
• Quickly launch other instances.
• Operation and maintenance functions such as slow log management and SQL optimization.
• Supports session management and parameter configuration.
• Other tool plug-ins can be integrated.

advantage:

• In addition to MySQL, it also supports a variety of mainstream databases.
• The functions are richer, truly achieving multiple uses on one platform.
• It has operation and maintenance functions such as SQL optimization and slow log management, which is more friendly to DBAs.
• Based on Python and Django, it is conducive to secondary development and transformation.
• As for Yearning and Archery, both have their own advantages and disadvantages, and both are excellent open source tools. Yearning only works with MySQL, has simple deployment, clear functions, and is easy to use. Archery supports multiple databases, has slightly more complex deployment, richer functionality, and supports many advanced operation and maintenance features. There is no better one, only more suitable one.

2. Binlog parsing tool

The previous article also introduced binlog parsing related content and tools. By using binlog parsing tools, we can clearly see the historical content executed by the database, and obtain the reverse content for rollback. There are many binlog parsing tools that can be searched on GitHub. Here are two commonly used ones.

MyFlash

Project address: https://github.com/Meituan-Dianping/MyFlash

MyFlash is a tool for rolling back DML operations developed and maintained by the Technical Engineering Department of Meituan Dianping. This tool completes the rollback operation by parsing the binlog of the v4 version. Compared with the existing rollback tools, it adds more filtering options to make rollback easier.

Key features:

• Parse binlog and roll back various DML statements.
• Provides multiple native filtering methods based on database, table, SQL type, location, time, etc.
• Supports multiple versions of MySQL and GTID format.

binlog2sql

Project address: https://github.com/danfengcao/binlog2sql
binlog2sql is also an open source tool for parsing binlog. The installation is simple and convenient, it is developed based on Python, and the source code is easy to get started with.

Key features:

• It can parse out the original SQL, rollback SQL, INSERT SQL without primary key, etc.
• Rapid data rollback (flashback).
• Repair the problem of new master losing data after master-slave switch.

3. Percona Toolkit

Official website address: https://www.percona.com/doc/percona-toolkit/3.0/index.html#
Percona Toolkit is a set of advanced management MySQL toolkits that can be used to perform various system tasks that are very complex and troublesome to perform manually. Referred to as PT tool, it is developed and maintained by Percona and is a good helper for database maintenance personnel.

The PT toolkit consists of various small tools, different tools have different functions. The main functions include checking the data consistency of master-slave replication, checking duplicate indexes, archiving data, online DDL, etc. Here is a brief introduction to the functions of several commonly used tools:

• pt-archiver: Mainly used to clean and archive historical data.
• pt-duplicate-key-checker: List and remove duplicate indexes and foreign keys.
• pt-kill: Kills qualified database connections.
• pt-online-schema-change: Modify the table structure online, commonly used for large table DDL.
• pt-query-digest: Analyzes MySQL logs and generates reports, often used for slow log analysis.
• pt-table-checksum: Checks the consistency of master-slave replication.
  

Summarize:

This article briefly introduces several types of tools, including SQL auditing, binlog parsing, and Percona Toolkit. You still need to explore in detail how to use each tool. Students who are interested can install and learn it. Good tools can make work more efficient.

The above are the detailed contents of several recommended MySQL-related tools. For more information about MySQL-related tools, please pay attention to other related articles on 123WORDPRESS.COM!