Shulou(Shulou.com)06/03 Report--

Let's learn about MySQL using the tool Inception to achieve automated operation and maintenance. I believe you will benefit a lot after reading it. The text is not much in essence. I hope MySQL uses the tool Inception to achieve automated operation and maintenance. This short article is what you want.

A brief introduction to Inception

Inception is an automated operation and maintenance system that integrates audit, execution and rollback. It is modified according to MySQL code. It can be used to clearly, detailedly and accurately audit MySQL SQL statements. Its working mode is exactly the same as MySQL. It can directly use MySQL client to connect, but does not need to verify permissions. It is a cloud server relative to the application (upper audit process system, etc.). When connecting, you need to specify the CVM address and the port of the Inception CVM. Compared with the online MySQL CVM corresponding to the statements to be audited or executed, it is a client. Internally, it needs to connect to the database CVM in real time to obtain the required information, or directly execute the corresponding statements online and obtain the binlog. Inception is an intermediate service. The following figure shows the architecture of Inception.

Inception provides rich functions. First of all, it can parse all submitted statements and return the corresponding error information to the reviewer if there is something wrong with the syntax. It also provides semantic analysis, reporting errors when a table, library, column, etc., is incorrect or does not conform to the specification, or using a non-existent object Times error, and so on. It also provides a number of features for SQL normative constraints, and these DBA can be configured through system parameters. A more advanced function is to assist DBA in analyzing the performance of a query statement, which can be checked if the index is not used or if the query is slow for some reason. It also provides the execution function of SQL statements, and the types of executable statements include commonly used DML and DDL statements and truncate table operations. Inception also provides the function to generate rollback statements when executing DML, and the corresponding operation records and rollback statements are stored on the backup machine, which is specified by configuring the Inception parameter.

Project address: https://github.com/mysql-inception/inception

Document address: http://mysql-inception.github.io/inception-document/inception

II. Inception installation

The following installation is the CentOS system, if it is Ubuntu/Debian, please see the official documentation.

2.1 Git download the source package

2.2 install dependency packages

2.3 start compilation and installation

The first is compilation. There is a file inception_build.sh under the root of the source code. If you execute the command sh inception_build.sh, you will output the usage method. In fact, you only need to execute inception_build.sh debug [Xcode], the latter platform is optional, if you do not specify the linux platform, but if you want to specify Xcode, then specify Xcode, and debug is the compiled directory, after compilation, all the generated files are in this directory, including the executable Inception. The executable is under the debug/sql/Debug/ directory (it may vary from platform to platform).

By the way, in fact, compiling Inception is the same as compiling MySQL source code. If there are students who don't know much about MySQL source code, you can first look at the compilation of MySQL source code online. Any problems I want to encounter can be solved.

Once the compilation is complete, you need a configuration file (inc.cnf):

2.4 start the Inception program

Start in the same way as MySQL.

Note: because Inception supports the functions performed by OSC, it is done by calling the pt-online-schema-change tool, but if Inception starts (&) in the background, it may lead to the problem that pt-online-schema-change will not return for a long time after the execution is completed, resulting in Inception stuck. This problem will be solved later, but at this stage, please try not to use background startup. Or you can start it using nohup Inception &.

If the startup does not report an error, it means that the startup has been successful, in fact, it is very difficult to get it to report an error, because it is very lightweight.

After the startup is successful, you can simply try it through the MySQL client

After logging in, execute one more command:

Output all the variables, congratulations, has been started successfully, all said very simple.

III. Use of Inception

Inception is actually a service program, so it should have its own set of friendly way to use, must have simple, efficient, easy to use and other features. So in order to make Inception have these characteristics, at the beginning of the design, it is specified how to use it, as described below.

When auditing statements through Inception, you must tell Inception the database address and database port corresponding to these statements, as well as the user name and password used by Inception to connect to the database, instead of simply executing a sql statement, so you must convey these information to Inception in some way. The way we choose is to put the necessary information at the front of the statement in the form of comments in order not to affect the meaning of the statement, that is to say, all this information is enclosed by / * /, and each parameter is separated by a semicolon, in a similar way:

Of course, these are not the only parameters that are supported, and some other parameters will be introduced later. What Inception needs to do is to review a block of statements, and it needs to introduce a rule to surround the statements to be executed. Inception stipulates that inception_magic_start; statements should be added at the beginning of the statement, and inception_magic_commit; statements should be added at the end of the block of executive statements. These two statements are legal, marked SQL statements that can be correctly parsed in Inception. All enclosed statements that need to be audited or executed must be followed by a semicolon, which is actually batch execution of SQL statements. (you should also add a semicolon after the use database statement, which is different from the MySQL client), otherwise there will be syntax errors.

During specific execution, if you find another statement to be executed before parsing to inception_magic_start, you will report an error directly, because inception_magic_start is mandatory in the rule. If the inception_magic_commit does not appear at the end of the executed statement block, the error will be reported directly and nothing will be done. In the previous comments section, you need to specify some options for actions, including online user name, password, database address, check / execution, and so on. Here is a simple example:

So the above paragraph is a batch of SQL statements that can be executed normally. Currently, the execution only supports access to the Inception through the C _ Inception + and Python interfaces. This section must be submitted to the Inception once through the execution interface. Then after the processing is completed, the Inception will return a result set to tell us what errors exist in these statements, or whether they are completely normal, and so on.

Do not put the following SQL statement block into the MySQL client to execute, because this is an automated OPS tool, and it is meaningless to use an interactive command line. You can only access the Inception CVM by writing programs.

The only thing that can be executed through the MySQL client is the Inception command, please refer to the section.

Here is an example of a Python program that executes the above statement:

After executing this program, the result returned is as follows:

IV. About Inception_web

At present, the most popular on the Internet is Inception_web, which is the web encapsulated interface for Inception. The related functions can be realized through the web interface. It is written in Python 2.7.Based on the flask framework, the function is relatively simple and can not meet the needs of the enterprise. The following requirements need to be developed by ourselves.

Source code address: https://github.com/dbalihui/inception_web

After reading the article that MySQL uses the tool Inception to automate operation and maintenance, many readers will definitely want to know more about it. For more industry information, you can follow our industry information section.

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.