Preface:

With the continuous development of Internet technology, MySQL related ecology is becoming more and more perfect, and more and more tools emerge. Some companies and individuals have come up with some great open source tools. This article focuses on some MySQL related utilities. Note that Navicat and other graphical manipulation tools are not covered here.

1. SQL audit Web tools

SQL auditing and execution can be a hassle for companies or teams. Without the support of platform tools, it can only be handled manually, which is inefficient and cannot guarantee the quality. To standardize operations and improve efficiency, most teams will probably 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… Ten years ago, ten years ago, ten years ago, ten years ago, ten years ago

Ten years ago. Ten years ago. Ten years ago. Ten years ago. This is a Web-based visual SQL audit platform that meets the SQL audit needs of most companies. In addition to the implementation of the regular SQL audit function, it also adds a series of convenient functions such as data query.

Main functions:

  • Automatic SQL statement audit, SQL can be automatically detected and executed.
  • A rollback statement is automatically generated after the DDL/DML statement is executed.
  • Audit/query audit function.
  • Support LDAP login/pinning and email message push.
  • Support custom audit workflows.
  • Supports fine-grained permission assignment.

Advantages:

  • High level of appearance, smooth use.
  • Some audit rules can be customized.
  • Simple deployment and few dependencies.
  • Rich function, can audit the execution of SQL and query.
  • The author is in continuous maintenance, with community support.

Archery

Project address: github.com/hhyo/Archer… Use the document: archerydms.com/

Archery is positioned as a SQL audit and query platform, aiming to improve the work efficiency of DBAs. It supports SQL on-line and query of multiple databases, and supports rich MySQL operation and maintenance functions. All functions are compatible with mobile terminal operation.

Main functions:

  • Integrated SQL query, audit, execution, backup.
  • Clear distinction of authority, audit execution separation.
  • SQL work orders are automatically approved and high-risk statements are rejected.
  • Quickly bring other instances online.
  • O&m functions such as slow log management and SQL optimization.
  • Supports session management and parameter configuration.
  • Other tool plug-ins can be integrated.

Advantages:

  • In addition to MySQL, a variety of mainstream databases are supported.
  • More rich functions, truly achieve a platform multi-purpose.
  • With SQL optimization, slow log management and other operation and maintenance functions, more friendly to DBAs.
  • Based on Python and Django, easy for secondary development.

Reminiscent and Archery have their advantages and disadvantages. Both are excellent open source tools. Ten years ago. Ten years ago. Ten years ago. Ten years ago. Ten years ago. Archery supports a variety of databases, which is slightly more complex to deploy and more feature-rich, supporting many advanced operations and maintenance functions. No one is better, only one is more appropriate.

2. Binlog parsing tool

Binlog parsing has also been covered in previous articles. With the binlog parsing tool, we can clearly see the history of the database execution and get the reverse content that can be used for rollback. There are many binlog parsing tools available on GitHub. Here are two common ones.

MyFlash

Project address: github.com/Meituan-Dia…

MyFlash is a tool for rolling back DML operations developed and maintained by the technical engineering department of Meituan-Dianping. The tool parses binlog VERSION V4 to perform rollback operations. Compared with existing rollback tools, it adds more filtering options, making rollback easier.

Main functions:

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

binlog2sql

Project address: github.com/danfengcao/…

Binlog2sql is also an open source tool for parsing binlogs. Simple and convenient installation, based on Python development, simple source code entry.

Main functions:

  • You can parse out raw SQL, rollback SQL, INSERT SQL that removes primary keys, and so on.
  • Fast data rollback (flash back).
  • Data lost by the new master after the master/slave switchover.

3. Percona Toolkit

Website address: www.percona.com/doc/percona…

The Percona Toolkit is a set of advanced MySQL management toolkits that can be used to perform a variety of system tasks that are very complex and cumbersome to perform manually. PT tool, developed and maintained by Percona, is a good helper for database maintenance personnel.

The PT toolkit is made up of various gadgets, each with a different purpose. The main functions include checking data consistency between primary and secondary replication, checking duplicate indexes, archiving data, and online DDL. The following describes the functions of several common tools:

  • Pt-archiver: clears and archives historical data.
  • Pt-duplicate-key-checker: lists and deletes duplicate indexes and foreign keys.
  • Pt-kill: kills the database connections that match the conditions.
  • Pt-online-schema-change: modify the table structure online. This mode is used for DDL of large tables.
  • Pt-query-digest: analyzes MySQL logs and generates a report. It is often used for slow log analysis.
  • Pt-table-checksum: verifies the primary and secondary replication consistency.

Conclusion:

This article briefly introduces SQL audit, binlog parsing, Percona Toolkit several kinds of tools, about the use of each tool, you need to explore in detail. Interested students can install learning, good tools can make work twice the result with half the effort.