How to build MySQL operation and maintenance system

Abstract

Database, especially mySQL, which is not very mature commercial database, is actually not convenient to use, which is why many Internet companies, such as Tencent or Meituan, have special team DBA to support the final operation and maintenance work.

MySQL operation and maintenance is the first thing to fix is the installation and deployment of the piece, for ordinary developers if they do not spend a few hours or days to learn it is generally difficult to improve the deployment of MySQL related services. Then, after the number of instances or clusters increases, it needs to be managed, including the whole cluster topology relationship, switch relationship. What’s left is all of the high frequency handoffs that databases care about, and it’s also important to be able to cut out in case of a database failure. Finally, there are some ancillary things, including monitoring alarms, backup and recovery, data migration, performance optimization, auditing and auditing, and regular system inspections. For financial or data-sensitive companies, auditing and auditing is a top priority.

When I started my business, I was thinking about what kind of things we could do to satisfy the needs of most people and bring some benefits to everyone. Let the database in a wider range of promotion, can be very easy to use. Finally, we made a product called ArkControl, which is a cloud management platform. The community version can be downloaded and used for free. It can realize a comprehensive mysql operation and maintenance system with one click, which can realize some basic functions of database operation and maintenance without too much cost.

The target users of this product are not large enterprises like Meituan, but companies that use mysql but don’t have dbAs or don’t have the resources of DBAs.

According to the subsequent development, we will also support more NewSQL, NoSQL and big data systems, such as Redis, which will be supported soon. On the data level, Redis and mysql are the most users and demands, one is cache, the other is database.

Also in the process of managing database, appeared two requirements, one is a heterogeneous data synchronization, general enterprise will use a variety of database at the same time, such as use Redis for caching, with big system, data storage, data analysis, under normal circumstances in OLTP mysql database as the data of benchmark, used for caching or large data analysis. There are actually no good tools or products to support this kind of heterogeneous real-time synchronization. The second requirement is hybrid cloud database management, which we already support.

The figure above shows the capabilities that AkeControl needs to understand and support at the database level, as well as the processing requirements to be addressed. It may seem complicated, but parsing it out is actually quite simple, and the core is the database cluster.

In order to use a mysql cluster well, it is necessary to have a mysql middleware as an access layer to access different clusters, separated from the business access and system architecture, so that the database can be easier to manage at the bottom. In terms of online database monitoring, we made a distributed sentinel similar to Redis, and then used the voting mechanism to solve the problem of whether the database can judge the survival, which is more accurate and efficient.

The section on the far left, from host administration to auditing below, shows the capabilities that AkrContro can provide to meet the needs of the main developer for database operation and maintenance.

As for the data heterogeneous transmission mentioned just now, we have a special data transmission service for the heterogeneous operation of mysql to other databases, so as to realize the full connection of the underlying data and easily transfer hot or hot data to different systems for real-time analysis.

There is also a very important piece, which is also the focus of our future development — intelligent operation and maintenance, including intelligent optimization of database, intelligent diagnosis and inspection. Gradually from manual operation and maintenance to intelligent operation and maintenance, such as optimization interface or optimization configuration functions.

Here’s a quick overview of what AkrControl has installed. It consists of several parts, starting with a basic Web page for the front end presentation and input and output for some operations. There is also an AKr-Service written in GO for data scheduling, which is equivalent to the Control layer of the Web. Then there is AKR-Agent, which is installed on all the servers that need to be managed and is responsible for collecting database information and host information, including the installation and uninstall commands transmitted from the central control, which is equivalent to a remote operation agent.

Next is storage, one mysql, the other two are ElasticSearch and Grafana, they are used to store monitoring data, we will use ES to collect monitoring information, and then display it in Grafana, this is the plan at present, we will probably replace Grafana with our own Web layer later.

Arkit-service and arkgate-service are also installed. Arkit-service is used for auditing, and arkgate-service is used for data transmission.

The figure above shows the configuration required to deploy AkrControl. The minimum configuration is two machines, a repository, and a backup data store. Below are recommended configurations for more security.

Over time, the minimum configuration may be just one, with minimal installations that can be experienced on a laptop. It’s still being refined, but should be released soon.

(For details about the deployment interface, please watch the last 5 minutes of the video.) Video address: t.cn/EAZ6zVc

That’s all for today’s sharing. Thank you!

Editor: IT big guy said, reprint please indicate copyright and source