MySQL automated operation and maintenance tool Inception

1. Introduction to Inception

Inception is an automated operation and maintenance system that integrates auditing, execution, and rollback. It is modified based on MySQL code. It can be used to audit MySQL SQL statements in a clear, detailed and accurate manner, its working mode and MySQL is exactly the same, you can directly use the MySQL client to connect, but do not need to verify the authority, it is a server relative to the application (upper audit process system, etc.), you need to specify the server address and the port of the Inception server when connecting. Yes, but it is a client relative to the online MySQL server corresponding to the statement to be audited or executed. It needs to connect to the database server in real time to obtain the required information, or directly execute the corresponding online. Statements and access to binlog, etc., Inception is an intermediate service. The figure below shows the architecture of Inception.

Open class summary | MySQL automated operation and maintenance tool Inception

The functions provided by Inception are very rich. First of all, it can analyze the syntax of all submitted statements. If there is a problem with the syntax, it will return the corresponding error message to the reviewer. It also provides semantic analysis, when a table, library, column and other information is incorrect or does not conform to the specification, an error is reported, or an error is reported when a non-existing object is used, etc. It also provides many functions for SQL normative constraints, which DBAs can configure through system parameters. The more advanced function is that it can assist the DBA to analyze the performance of a query statement. If the index is not used or the query is slow for some reason, it can be checked. It also provides the execution function of SQL statements. The executable statement types include commonly used DML and DDL statements and operations such as truncate table. Inception also provides the function of generating rollback statements when executing DML. The corresponding operation records and rollback statements will be stored on the backup machine. The backup machine 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

2. Inception installation
The following installation is CentOS system, if it is Ubuntu/Debian, please refer to the official documentation.

2.1 Git download source package
Open class summary | MySQL automated operation and maintenance tool Inception

2.2 Install dependency packages
Open class summary | MySQL automated operation and maintenance tool Inception

2.3 Start compiling and installing

The first is to compile. There is a file inception_build.sh under the source code root directory. Execute the command sh inception_build.sh, and the usage method will be output. In fact, you only need to execute inception_build.sh debug [Xcode]. The latter platform is optional. If you do not specify it, it is the linux platform. If you want to specify Xcode, specify Xcode later, and debug is the compiled directory. Compile After that, all the generated files are under this directory, including the executable Inception. The executable file is under the debug/sql/Debug/ directory (may be different for different platforms).
Open class summary | MySQL automated operation and maintenance tool Inception

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

After the compilation is completed, it is used, then a configuration file (inc.cnf) is required:
Open class summary | MySQL automated operation and maintenance tool Inception

2.4 Start the Inception program

The startup method is the same as MySQL.
Open class summary | MySQL automated operation and maintenance tool Inception

Note: Because Inception supports the functions performed by OSC, it is done by calling the pt-online-schema-change tool, but if Inception is started in the background (&), it may cause pt-online-schema-change to be executed after completion. The problem of not returning for a long time will cause Inception to get stuck. This problem will be officially solved later, but at this stage, please try not to use the background startup method, or you can use the nohup Inception & method to start.
If the startup does not report an error, it means that the startup has been successful. In fact, it is difficult to make it report an error because it is very lightweight.

After the startup is successful, you can simply try it out, through the MySQL client
Open class summary | MySQL automated operation and maintenance tool Inception

After logging in, execute another command:
Open class summary | MySQL automated operation and maintenance tool Inception

All the variables are output, congratulations, it has been successfully started, it is very simple.

3. Inception use
Inception is actually a service program, so it should have its own set of friendly ways to use it, and it must have the characteristics of simplicity, efficiency, and ease of use. So in order for Inception to have these characteristics, at the beginning of the design, its usage is specified, as described below.
When auditing a statement through Inception, you must tell Inception the database address, database port, and user name, password and other information that Inception uses to connect to the database, instead of simply executing a sql statement, so you must pass a certain A way to communicate this information to Inception. And the way we choose is, in order not to affect the meaning of the statement, put all the necessary information in the front of the statement in the form of comments, that is to say, all the information is enclosed by /**/, and each parameter is are separated by semicolons, in a similar way:
Open class summary | MySQL automated operation and maintenance tool Inception

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

During specific execution, if other statements are found to be executed before inception_magic_start is parsed, an error will be reported directly, because inception_magic_start is mandatory in the rules. If inception_magic_commit does not appear at the end of the executed statement block, an error will be reported directly and no operation will be performed. In the previous comment section, you need to specify some operation options, including online user name, password, database address, check/execution, etc. Here is a simple example:
Open class summary | MySQL automated operation and maintenance tool Inception

Then the above paragraph is a batch of normally executable SQL statements. Currently, the execution only supports access to Inception through the C/C++ interface and the Python interface. This paragraph must be submitted to Inception through the execution interface at one time, then after the processing is completed , Inception will return a result set to tell us what is wrong with these statements, or is completely normal, etc.

Please do not execute the following SQL statement block in the MySQL client, because this is an automated operation and maintenance tool. It is meaningless to use the interactive command line. You can only access the Inception server by writing a program.

The only commands that can be executed through the MySQL client are Inception commands, please refer to the section <>.

Here is an example of a Python program that executes the above statement:
Open class summary | MySQL automated operation and maintenance tool Inception

After executing this program, the results returned are as follows:

Open class summary | MySQL automated operation and maintenance tool Inception

4. About Inception_web
The most popular on the Internet is Inception_web, which is a web-encapsulated interface for Inception. It can implement related functions through the web interface. It is written in Python 2.7 and based on the flask framework. The function is relatively simple and cannot meet the needs of enterprise-level use. Need to develop by yourself

V. Conclusion
In the follow-up, we will introduce an Inception _web management terminal based on Django development, with complete user management, permission control and audit execution functions.