Install the Inception service and use Python 3 to audit MySQL

Install the Inception service and use Python 3 to audit MySQL

Bison is one of the packages that the Inception service depends on. However, some Linux versions have already installed Bison, or the Bison installed through yum, which is usually in version Bison 3.0 +.
For the Inception program, the Bison version is too high, which may cause an error in the Inception compilation process. According to official suggestions, it is best to use the Bison 2.5 version.
Therefore, you need to manually install the dependency package of the Bison 2.5 version.

Bison Installation

Bison download

, Select the appropriate version and compression File.

Extract

Xz-d bison-2.5.1.tar.xz
Tar-xvf bison-2.5.1.tar

Install

Go to the decompressed bison path, cd bison-2.5.1, and compile and install it according to the normal source code package.
./Configure
Make & make install

　　

Complete the installation and check the Bison version, bison-V (remember to set the environment variables before, and identify the Bison command after installation ).

　　

Finally, install other dependent packages.
Yum install gcc-c ++ cmake openssl-devel ncurses-devel MySQL-python git-y

 

Inception Installation

Source code download

Download source code git clone https://github.com/mysql-inception/inception.git

Compile & Install

Enter the Inception directory and run bash inception_build.sh debug [Xcode].
After 10 minutes of compilation and installation, confirm that the installation is successful.
If the installation fails for the first time, it may be because the dependent package is missing or the version of the dependent package is incorrect. You can handle the specific error and delete the debug directory generated when the installation fails, otherwise, the installation cannot continue.

　　

Add a basic Inception configuration file

　　

Start the Inception Service

/Usr/local/inception/debug/mysql/bin/Inception -- defaults-file =/etc/inc. cnf &

　　

Connect from local to Inception Service

　　

So far, the Inception service has been fully installed successfully.

Use Python (Python3) to audit MySQL jobs.

Inception has its own specific format when verifying the effectiveness of the MySQL script. Therefore, it is necessary to assemble the SQL script with verification (execution) into the format specified by the Inception service.

-- Inception start flag/* -- user = username; -- password = *****; -- host = ***. ***. ***. * **; -- enable-check; -- port = 3306; */inception_magic_start; -- SQL statement use mysql; CREATE TABLE adaptive_office (id int ); -- Inception end mark inception_magic_commit;

In most online environments, Python should be used for Inception auditing. If Python2 is used for auditing, it cannot be connected to the Inception service.
During normal connection, the following error is reported: "invalid literal for int () with base 10: 'exception2 '"
The reason should be that connections. py has a problem when connecting to the Inception service parameter resolution type conversion.
Reference: callback
In this way, Python3 can be normally connected to the Inception service.

 

The following is a demo of using Python for MySQL script audit.

#! /Usr/bin/python #-\ *-coding: UTF-8-\ *-import pymysqlimport sys, osimport time # Run or verify operation = '-- enable-Check' # operation =' -- enable-execute; -- enable-ignore-warnings; -- enable-force '# target server connstr_target = {'host ':'***. ***. ***. * ** ', 'Port': 3306, 'user': 'root', 'Password':' *** ', 'db': 'ception _ testdb ', 'charset': 'utf8mb4'} # inception serverconnstr_inception = {'host ':'***. ***. ***. * ** ', 'Port': 6669, 'user': 'root', 'Password': '', 'db':'', 'charset ': 'utf8mb4'} # The inception format must be prefix_format = "/* -- user ={}; -- password ={}; -- host ={};-- port = {}; */". format (connstr_target ['user'], connstr_target ['Password'], connstr_target ['host'], operation, connstr_target ['Port']) \ + '\ n' \ + "inception_magic_start;" suffix_format = "inception_magic_commit;" # SQL statement to be executed SQL _demo1 = ''' use inception_testdb; alter table test_inception add column remark varchar (200); ''' try: # combine the SQL statements to be executed into the format recognized by inception SQL _demo1_with_format = prefix_format + "\ n" + SQL _demo1 + "\ n" + suffix_format print (SQL _demo1_with_format) # connect to the inception server conn_inception = pymysql. connect (host = connstr_inception ['host'], port = connstr_inception ['Port'], user = connstr_inception ['user'], password = connstr_inception ['Password'], charset = connstr_inception ['charset']) cur = conn_inception.cursor () cur.exe cute (SQL _demo1_with_format) result = cur. fetchall () num_fields = len (cur. description) field_names = [I [0] for I in cur. description] print (field_names) # print out the audit results of Inception on MySQL statements for row in result: print (row [0], "|", row [1], "|", row [2], "|", row [3], "|", row [4], "|", row [5], "| ", row [6], "|", row [7], "|", row [8], "|", row [9], "| ", row [10]) cur. close () conn_inception.close () failed t Exception as err: print (err) finally: print ('****************')

Errlevel = 0 indicates that the statement is normal, 1 indicates that there is warning information, for example, the field does not have a default value, no comment, and 2 indicates a serious error, for example, add an existing field to the table.
For example, the following audit result contains an errlevel = 2, because the remark field already exists in the table and a field with the same name is added, therefore, "Column 'remark' have existed is displayed. "This error,
In addition to the preceding errors, the alter table test_inception add column remark varchar (200) Statement is executed. Other types of errors (or warnings) generated by this statement are displayed ).

You need to know that some potential problems are not serious errors, and their warning types can be configured,
For example, if the field does not have a comment, you can configure it at the Inception service level to enable no comment to display no warning, or if the field does not have a default value, Inception will also give a warning about these non-serious errors, can be configured according to the situation (whether to give an alarm)
Of course, this is just a demo. The results of the Inception audit can be more visualized as required.

['ID', 'stage', 'errlevel', 'stagestatus', 'errormessage', 'SQL', 'Affected_rows', 'sequence', 'backup_dbname', 'execute_time', 'sqlsha1']1 | CHECKED | 0 | Audit completed | None | use inception_testdb | 0 | '0_0_0' | None | 0 | 2 | CHECKED | 2 | Audit completed | Column 'remark' have existed.Column 'remark' in table 'test_inception' have no comments.Column 'remark' in table 'test_inception' is not allowed to been nullable.Set Default value for column 'remark' in table 'test_inception' | alter table test_inception             add column remark varchar(200) | 1 | '0_0_1' | 116_196_94_250_8000_inception_testdb | 0 | 

The Inception audit results are not completely reasonable. For example, the index creation statement in mysql supports two syntaxes: alter table and create index.
In earlier versions of mysql, indexes were added through the alter table syntax. mysql later supported the create index syntax, but Inception also provided a severe warning for the create index statement.
In addition, DML statements have limited support. For example, if an insert statement inserts a value that conflicts with a primary key in the current table, Inception cannot be detected,
Inception is more about detecting errors at the syntax level.

Others

As a whole, Inception is a very powerful software (service). In this article, I just tried it for reference. For more information, see http://mysql-inception.github.io/inception-document /.

Finally, refer to the official documentation.

I have to say that Inception is still one of the most powerful MySQL management tools in China for auditing (Execution, rollback, etc.). At least it is open-source. It is because Marathon has come out, the degree of recognition is still relatively high, and there is no considerable strength. It cannot be blocked.
After using Python to connect to the Inception to implement the most basic SQL audit, you can try your own audit method and explore more Inception functions.
The best of course is the official reference documentation: http://mysql-inception.github.io/inception-document/

Reference

Http://mysql-inception.github.io/inception-document/