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

What this article shares with you is about how to use Inception to build an automated operation and maintenance platform for MySQL. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Brief introduction of MySQL Automation Operation and maintenance tools (concept I and 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 server relative to the application (upper audit process system, etc.). When connecting, you need to specify the address of the server and the port of the Inception server. Compared with the online MySQL server corresponding to the statements to be audited or executed, it is a client. It needs to connect to the database server in real time to get the required information, or directly execute the corresponding statements online and obtain binlog. Inception is an intermediate service. Figure 1.1 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

one

$git clone https://github.com/mysql-inception/inception.git

2.2 install dependency packages

one

$yum install gcc gcc-c++ cmake bison openssl-devel ncurses-devel MySQL-python-y

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, only need to execute inception_build.sh debug [Xcode], the latter platform is optional, if not specified is the linux platform, and 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).

one

two

$cd inception

$bash inception_build.sh debug [Xcode]

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):

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

$cat / etc/inc.cnf

[inception]

General_log=1

General_log_file=inception.log

Port=6669

Socket=/tmp/inc.socket

Character-set-client-handshake=0

Character-set-server=utf8

Inception_remote_system_password=root

Inception_remote_system_user=wzf1

Inception_remote_backup_port=3306

Inception_remote_backup_host=127.0.0.1

Inception_support_charset=utf8mb4

Inception_enable_nullable=0

Inception_check_primary_key=1

Inception_check_column_comment=1

Inception_check_table_comment=1

Inception_osc_min_table_size=1

Inception_osc_bin_dir=/data/temp

Inception_osc_chunk_time=0.1

Inception_enable_blob_type=1

Inception_check_column_default_value=1

2.4 start the Inception program

Start in the same way as MySQL.

one

$nohup / data/inception/debug/mysql/bin/Inception-- defaults-file=/etc/inc.cnf &

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 cause pt-online-schema-change not to return for a long time after execution, which will be solved later, but at this stage, try not to use background startup, or you can use nohup Inception & to start.

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

one

$mysql-uroot-h227.0.0.1-P6669

After logging in, execute one more command:

one

Mysql > inception get variables

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:

one

/ *-- user=username;--password=xxxx;--host=127.0.0.1;--port=3306;*/

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:

one

two

three

four

five

six

/ *-- user=zhufeng;--password=xxxxxxxxxxx;--host=xxxxxxxxxx

-- enable-check;--port=3456;*/

Inception_magic_start

Use mysql

CREATE TABLE adaptive_office (id int)

Inception_magic_commit

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.

Please do not put the following SQL statement block into the MySQL client to execute, because this is an automated operation and maintenance tool, if you use an interactive command line, it does not make sense, you can only write programs to access the Inception server.

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:

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

#! / usr/bin/python

#-coding: utf-8-\ *-

Import MySQLdb

Sql='/*--user=root;--password=123456;--host=172.17.0.2;--execute=1;--port=3306;*/\

Inception_magic_start;\

Create database if not exists test charset utf8mb4;\

Use test;\

CREATE TABLE adaptive_office (id int);\

Inception_magic_commit;'

Try:

Conn=MySQLdb.connect (host='127.0.0.1',user='',passwd='',db='',port=6669)

Cur=conn.cursor ()

Ret=cur.execute (sql)

Result=cur.fetchall ()

Num_fields = len (cur.description)

Field_names = [I [0] for i in cur.description]

Print field_names

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.close ()

Except MySQLdb.Error,e:

Print "Mysql Error% d:% s"% (e.args [0], e.args [1])

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

one

two

three

four

five

six

seven

eight

['ID',' stage', 'errlevel',' stagestatus', 'errormessage',' SQL', 'Affected_rows',' sequence', 'backup_dbname',' execute_time', 'sqlsha1']

1 | CHECKED | 0 | Audit completed | None | 2 | CHECKED | 0 | Audit completed | None | 3 | CHECKED | 1 | Audit completed | Set engine to innodb for table 'adaptive_office'.

Set charset to one of 'utf8mb4' for table' adaptive_office'.

Set comments for table 'adaptive_office'.

Column 'id' in table' adaptive_office' have no comments.

Column 'id' in table' adaptive_office' is not allowed to been nullable.

Set Default value for column 'id' in table' adaptive_office'

Set a primary key for table 'adaptive_office'. | |

From the returned results, you can see that the audit and execution information of each row of statements is printed at the front, indicating that the column name information of the returned result set of Inception includes a total of ten columns. The following is the corresponding result of each column, because there are only two statements, then there are only two rows. From the first column of the result set, we can see that there are only two rows with sequence numbers 1 and 2. For the specific meaning of each column, this will be discussed in this chapter. You can only see what it is here.

In addition, Inception supports auditing and execution of statements, which can be turned on or off by itself, depending on the details.

For more details, please see the official website. Finally, I would like to thank the author of inception here.

IV. Introduction to Yearning

The visual sql audit platform based on Inception, and the Yearning SQL audit platform is based on the complete sql audit platform solution of Vue.js and Django, which provides SQL detection and execution based on Inception.

Source code address: https://github.com/cookieY/Yearning

Note:

1. To initialize the database, otherwise 500

Python3 manage.py makemigrations & & python3 manage.py migrate initialization database echo "from core.models import Account;Account.objects.create_user (username='admin', password='Yearning_admin', group='admin',is_staff=1)" | python3 manage.py shell adds initialization user

2. To modify the configuration file to support pymysql

Because Inception does not support pymysql So you need to change the relevant pymysql source code to modify the connections.py and cursors.py files under $PYTHON_HOME / lib / python3.6 / site-packages / pymysql to find the connections.py 1108 line if int (self.server_version.split (', 1) [0]) > = 5: self.client_flag | = CLIENT.MULTI_RESULTS change to try: if int (self.server_version.split ('.'. 1) [0]) > = 5: self.client_flag | = CLIENT.MULTI_RESULTS except: if self.server_version.split ('.' 1) [0] > = 'Inception2': self.client_flag | = CLIENT.MULTI_RESULTS finds cursors.py line 345 if self._result and (self._result.has_next or not self._result.warning_count): return changes to if self._result: return Note: modified connections.py and cursors.py can be replaced directly under the installation folder

The above is how to use Inception to build an automated operation and maintenance platform for MySQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.