Shulou(Shulou.com)05/31 Report--

This article mainly introduces "introduction of related tools in MySQL". In daily operation, I believe many people have doubts about the introduction of related tools in MySQL. Xiaobian consulted all kinds of materials and sorted out simple and easy operation methods. I hope to help you answer the doubts of "introduction of related tools in MySQL"! Next, please follow the small series to learn together!

1. SQL Audit web tools

SQL auditing and execution may be something that bothers individual companies or teams. If there is no platform tool support, it can only be handled manually, with low efficiency and no way to guarantee quality. To standardize operations and improve efficiency, most teams will probably develop or introduce open source tools themselves. Here are two open source free web-side SQL auditing platforms.

Yearning

Project Address:

https://github.com/cookieY/Yearning

Project Profile:

https://guide.yearning.io/

Yearning is a web-side visual SQL auditing platform that meets most corporate SQL auditing needs. In addition to implementing 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.

Automatically generates rollback statements after DDL/DML statements are executed.

Audit/query audit function.

Support LDAP login/nail and mail message push.

Support custom audit workflow.

Granular permission assignment is supported.

Advantages:

High face value, smooth use.

Some audit rules can be customized.

Simple deployment with few dependencies.

Function rich, both audit execution SQL and query.

The author is continuously maintained and has community support.

Archery

Project Address:

https://github.com/hhyo/Archery

Use documentation:

https://archerydms.com/

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

Main functions:

Integrated SQL query, audit, execution, backup.

Authority distinction is clear, audit execution separation.

SQL work orders are automatically approved and high-risk statements are rejected.

Quickly launch other instances.

Operation and maintenance functions such as slow log management and SQL optimization.

Support session management and parameter configuration.

Other tool plug-ins can be integrated.

Advantages:

In addition to MySQL, it also supports a variety of mainstream databases.

The function is richer, and one platform is truly multi-purpose.

With SQL optimization, slow log management and other operation and maintenance functions, more DBA-friendly.

Based on Python and Django, it is conducive to secondary development and transformation.

For Yearning and Archery, both have their pros and cons, and both are excellent open source tools. Yearning is only applicable to MySQL. It is simple to deploy, clear in function and easy to use. Archery supports a variety of databases, slightly more complex deployment, richer features, and supports many advanced O & M functions. No one is better, only one is better.

2. binlog parsing tool

Previous articles have also introduced binlog parsing related content and tools. Using binlog parsing tools we can clearly see the history of database execution, and can get the reverse content can be used for rollback. Binlog parsing tools can be searched for many on GitHub. Here are the following 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 Meituan Dianping's Technical Engineering Department. The tool completes the rollback operation by parsing the v4 version of binlog. Compared to existing rollback tools, it adds more filtering options to make rollback easier.

Main functions:

Parse binlog and roll back all kinds of DML statements.

Provides native filtering methods based on database, table, SQL type, location, time, etc.

Multiple versions of MySQL are supported and GTID format is supported.

binlog2sql

Project Address:

https://github.com/danfengcao/binlog2sql

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

Main functions:

You can parse original SQL, rollback SQL, INSERT SQL with primary key removed, etc.

Fast data rollback (flashback).

Repair lost data of new master after master-slave switch.

3. Percona Toolkit Toolkit

Official website address:

https://www.percona.com/doc/percona-toolkit/3.0/index.html#

Percona Toolkit is an advanced set of MySQL administration toolkits that can be used to perform a variety of very complex and cumbersome system tasks by hand. PT tool for short, developed and maintained by Percona, is a good helper for database maintainers.

PT kit consists of various small tools, different tools have different roles. The main functions include checking 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: Lists and removes duplicate indexes and foreign keys.

pt-kill: Kill qualified database connections.

pt-online-schema-change: Modify table structure online, often used for large table DDL.

pt-query-digest: Analyze MySQL logs and generate reports, often used for slow log analysis.

pt-table-checksum: Checks consistency of master-slave replication.

At this point, the study of "Introduction to related tools in MySQL" is over, hoping to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.