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

This article introduces the relevant knowledge of "what is the method of building Yearning + Inception SQL audit platform". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Yearning installation:

Install Nginx

Yum install nginx-y

Install MySQL sequentially

Mysql-community-common-5.7.22-1.el6.x86_64.rpm

Mysql-community-libs-5.7.22-1.el6.x86_64.rpm

Mysql-community-client-5.7.22-1.el6.x86_64.rpm

Mysql-community-server-5.7.22-1.el6.x86_64.rpm

Python 3.6 installation

Decompression

Tar-xvf Python-3.6.4.tar.xz

Create a directory

Mkdir-p / usr/local/python/3.6.4/lib

Compile

Cd Python-3.6.4

. / configure-- enable-shared-- prefix=/usr/local/python/3.6.4 LDFLAGS= "- Wl,-rpath / usr/local/python/3.6.4/lib"

Make & & make install

Soft link

Cp / usr/bin/python / usr/bin/python2.6.6

Ln-fs / usr/local/python/3.6.4/bin/python3.6 / usr/bin/python

Ln-fs / usr/local/python/3.6.4/bin/pip3 / usr/bin/pip

Yum modification

Vi / usr/bin/yum

Change the header #! / usr/bin/python to #! / usr/bin/python2.6.6

Inception installation

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.

Dependent package installation

Yum install gcc gcc-c++ cmake bison openssl-devel ncurses-devel git

Download the source package

Git clone https://github.com/mysql-inception/inception.git (closed source, but packages before closed source can still be found online)

Compile and install Inception

Mkdir-p / usr/local/inception

Mkdir-p / inception/ {data,logs}

Unzip master.zip-d / inception

Cd / inception/inception-master

Cmake-DWITH_DEBUG=OFF-DCMAKE_INSTALL_PREFIX=/usr/local/inception-DMYSQL_DATADIR=/inception/data-DWITH_SSL=yes-DCMAKE_BUILD_TYPE=RELEASE-DWITH_ZLIB=bundled-DMY_MAINTAINER_CXX_WARNINGS= "- Wall-Wextra-Wunused-Wwrite-strings-Wno-strict-aliasing-Wno-unused-parameter-Woverloaded-virtual"-DMY_MAINTAINER_C_WARNINGS= "- Wall-Wextra-Wunused-Wwrite-strings-Wno-strict-aliasing-Wdeclaration-after-statement"

CMake Error: The source "/ inception/inception-master/CMakeLists.txt" does not match the source "/ opt/inception-master/CMakeLists.txt" used to generate cache

Rm-rf CMakeLists.txt re-execution

Make & & make install

Create an inception profile

-- Editing parameter files

Vim / etc/inc.cnf

[inception]

General_log=1 # is the parameter of the native MySQL, which is used to record which statements have been executed on the Inception service, to locate some problems, and so on.

General_log_file=/usr/local/inception/data/inception.log # sets the file path written by general log

Service port of port=6669 # Inception

Socket file location of socket=/usr/local/inception/data/inc.socket # Inception

Character-set-server=utf8 # mysql native parameters

# Inception Audit rules

Inception_check_autoincrement_datatype=1 # when creating a table, the type of self-incrementing column is not int or bigint Times error

Inception_check_autoincrement_init_value=1 # an error is reported if the value of the self-incrementing column is not specified as 1 when the table is created

Inception_check_autoincrement_name=1 # when creating a table, if the name of the specified self-incrementing column is not ID, an error will be reported, which is meaningful. Give a hint.

When inception_check_column_comment=1 # created the table, there was no comment in the column.

Inception_check_column_default_value=0 # check whether the new column properties should have default values when creating tables, modifying columns, and adding new columns

Does inception_check_dml_limit=1 # report an error when it uses LIMIT in the DML statement

Does inception_check_dml_orderby=1 # report an error when it uses Order By in the DML statement

Does inception_check_dml_where=1 # report an error when there is no WHERE condition in the DML statement?

Inception_check_identifier=1 # turns on and off Inception's checking of various names in SQL statements. If set to ON, if characters other than numbers, letters, or underscores are found in the name, it will report Identifier "invalidname" is invalid, valid options: [Identifier ZMague 0-9 invalidname _].

Does inception_check_index_prefix=1 # check that the index name is prefixed with "idx_" and that the unique index prefix is "uniq_"

Does inception_check_insert_field=1 # check the existence of column linked lists in insert statements?

Inception_check_primary_key=1 # when creating a table, if there is no primary key, an error will be reported

When inception_check_table_comment=0 # created the table, there was no comment on the table.

Inception_check_timestamp_default=0 # error is reported if no default value is specified for the timestamp type when creating the table

Does inception_enable_autoincrement_unsigned=1 # self-increment column want to be unsigned?

Inception_enable_blob_type=0 # checks whether BLOB fields are supported, including creating tables, modifying columns, and adding columns. Default is enabled.

Inception_enable_column_charset=0 # allows columns to set their own character sets

Does inception_enable_enum_set_bit=0 # support enum,set,bit data types?

Does inception_enable_foreign_key=0 # support foreign keys?

Inception_enable_identifer_keyword=0 # checks to see if there is an identifier written as a MySQL keyword in the SQL statement. The default value is alarm.

The storage engine specified in inception_enable_not_innodb=0 # table creation is not Innodb and does not report errors.

Inception_enable_nullable=0 # does not report an error if it is listed as NULL when creating or adding a column

Is there an error in inception_enable_orderby_rand=0 # orderby rand?

Does inception_enable_partition_table=0 # support partitioned tables?

Do you have to report an error when inception_enable_select_star=0 # Select*

Whether the inception_enable_sql_statistic=1 # setting supports statistics on the proportion of various statements executed by Inception. If this parameter is enabled, the statistics of this operation will be stored in the statistic table of the inception library in the backup database instance. Each operation corresponds to a record. The information contained in this record is the number of statements executed of various types.

Inception_max_char_length=16 # when the length of the char type is greater than this value, you are prompted to convert it to VARCHAR

Inception_max_key_parts=5 # the maximum number of columns in an index. If you exceed this number, an error will be reported.

Inception_max_keys=16 # the maximum number of indexes in a table. If you exceed this number, an error will be reported

Inception_max_update_rows=10000 # in a modification statement, the maximum number of rows expected to be affected is greater than this number, an error is reported

Inception_merge_alter_table=1 # appears in multiple statements that change the same table, reporting an error and prompting to synthesize one

# inception supports OSC parameters

Inception_osc_bin_dir=/user/bin # is used to specify the location of the pt-online-schema-change script, which cannot be modified, and is set in the configuration file

Inception_osc_check_interval=5 # corresponds to the OSC parameter-check-interval, which means Sleep time between checks for-- max-lag.

Inception_osc_chunk_size=1000 # corresponds to OSC parameter-chunk-size

Inception_osc_chunk_size_limit=4 # corresponds to OSC parameter-chunk-size-limit

Inception_osc_chunk_time=0.1 # corresponds to OSC parameter-chunk-time

Inception_osc_critical_thread_connected=1000 # corresponding parameter-- thread_connected part of critical-load

Inception_osc_critical_thread_running=80 # corresponding parameter-- thread_running part of critical-load

Inception_osc_drop_new_table=1 # corresponding parameter-- [no] drop-new-table

Inception_osc_drop_old_table=1 # corresponding parameter-- [no] drop-old-table

Inception_osc_max_lag=3 # corresponding parameter-max-lag

Inception_osc_max_thread_connected=1000 # corresponding parameter-- thread_connected part of max-load

Inception_osc_max_thread_running=80 # corresponding parameter-- thread_running part of max-load

The parameter inception_osc_min_table_size=0 # is actually a switch for OSC. If set to 0, all ALTER statements go OSC. If set to non-0, the OSC mode is used when the space occupied by the table is greater than this value. In M, the size of this table is calculated by the statement "select (DATA_LENGTH + INDEX_LENGTH) / 1024 INDEX_LENGTH 1024 from information_schema.tables where table_schema = 'dbname' and table_name =' tablename'"

Inception_osc_on=0 # A global OSC switch, which is on by default and set to OFF if you want to turn it off, so it can be modified directly.

Inception_osc_print_none=1 # is used to set the returned result set in Inception. If the standard output information of the original OSC is printed to the corresponding error information column of the result set, if it is set to 1, it will not be printed. If set to 0, it will be printed. If there is an error, it will be printed.

Inception_osc_print_sql=1 # corresponding parameter-print

# back up the server information and change it to your machine. Used for rollback.

Inception_remote_system_password=P@ssw0rd

Inception_remote_system_user=incep_rw

Inception_remote_backup_port=3306

Inception_remote_backup_host=10.10.3.70

Inception_support_charset=utf8 # indicates the character set supported when building a table or database. If more than one is needed, it is separated by a comma. The scope of influence is to create a table, set a session character set, modify the attributes of a table character set, etc.

Start the Inception program

Cd / usr/local/inception/bin

. / Inception-- defaults-file=/etc/inc.cnf &

[1] 11179

[root@b28-11-92 bin] # 2019-06-21 15:00:31 0 [Note] Welcome to use Inception2.1.50

2019-06-21 15:00:31 11179 [Note] Server hostname (bind-address):'*'; port: 6669

2019-06-21 15:00:31 11179 [Note] IPv6 is available.

2019-06-21 15:00:31 11179 [Note] -:: 'resolves to'::'

2019-06-21 15:00:31 11179 [Note] Server socket created on IP:':'

View Inception processes

Ps-ef | grep Inception

Root 11179 30011 0 15:00 pts/1 00:00:00. / Inception-- defaults-file=/etc/inc.cnf

test

Mysql-uroot-h227.0.0.1-P6669

Execute inception get variables after successful connection

Output all variables, indicating that the startup has been successful

Yearning installation

Git clone https://github.com/cookieY/Yearning.git

Log in to MySQL to create a library yearning library

Create database Yearning DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

Install the appropriate python dependent libraries

Cd Yearning/src

Pip install-r requirements.txt

Pip is configured with locations that require TLS/SSL, however the ssl module in Python is not available.

Collecting Django==2.1.5 (from-r requirements.txt (line 1))

Could not fetch URL https://pypi.python.org/simple/django/: There was a problem confirming the ssl certificate: Can't connect to HTTPS URL because the SSL module is not available. -skipping

Could not find a version that satisfies the requirement Django==2.1.5 (from-r requirements.txt (line 1)) (from versions:)

No matching distribution found for Django==2.1.5 (from-r requirements.txt (line 1))

Modify the Setup file

Python-3.6.4/Modules

Vim Setup

# Socket module helper for socket (2)

_ socket socketmodule.c timemodule.c

# Socket module helper for SSL support; you must comment out the other

# socket line above, and possibly edit the SSL variable:

SSL=/usr/local/ssl

_ ssl _ ssl.c\

-DUSE_SSL-I $(SSL) / include-I $(SSL) / include/openssl\

-L$ (SSL) / lib-lssl-lcrypto

Recompile

Cd Python-3.6.4

. / configure-- enable-shared-- prefix=/usr/local/python/3.6.4 LDFLAGS= "- Wl,-rpath / usr/local/python/3.6.4/lib"

Make

Make install

Configuration, editing Yearning/src/deploy.conf fil

[mysql]

Db = the name of the library created

Address = database address

Port = database port

Password = database password

Username = database user

[host]

Ipaddress = server ip address: Port (involving cross-domain is very important! If the settings are not correct, you will not be able to log in!)

If the local address is 192.168.137.13, nginx sets the port to 80.

It should be filled in as 192.168.137.13 and then access the platform through this address.

[Inception]

Ip = Inception address

Port = Inception port

User = Inception user name

Password = Inception password

Backupdb = backup database address

Backupport = backup database port

Backupuser = backup database user name

Backuppassword = backup database password

[LDAP] LDAP related settings

LDAP_SERVER = LDAP service address

LDAP_SCBASE = LDAP dc settings such as dc=xxx,dc=com

LDAP_DOMAIN = LDAP domain name such as xxx.com

[email] Settings related to mailbox push

Username = email account number such as xxxx@163.com

Password = account password sent by mailbox

Smtp_server = email stmp address. For specific address, please consult the corresponding email provider.

Initialize the database

Python manage.py makemigrations

Python manage.py migrate

If you report an error ModuleNotFoundError: No module named 'Crypto'

Pip install pycrypto

Reinitialize the database

Python manage.py makemigrations

Python manage.py migrate

Add initialization user

Echo "from core.models import Account;Account.objects.create_user (username='admin', password='admin123456', group='admin',is_staff=1)" | python manage.py shell

Initialize permissions

Echo "from core.models import grained Grained.objects.get_or_create (username='admin', permissions= {'ddl':' 1, 'ddlcon': [],' dml':'1, 'dmlcon': [],' dic':'1, 'diccon': [],' dicedit':'0, 'query':' 1, 'querycon': [],' user':'1, 'base':' 1' 'dicexport':' 0'}) "| python manage.py shell

Copy the compiled static files to the nginx html directory (the dist directory is generated by npm run build)

Cd Yearning/webpage/dist

Cp-rf * / usr/share/nginx/html/

/ etc/init.d/nginx restart

Start django

Cd Yearning/src

Python manage.py runserver 0.0.0.0:8000

Visit the home page http://192.168.17.77

Note:

Use the default account: admin password: admin123456 login can be used, may not be able to log in (unable to jump to the page), this is due to cross-domain problems, to solve this problem, please refer to: http://blog.csdn.net/apple9005/article/details/54427902

Matters needing attention

The default Super Admin only has access to each page, and other permissions need to be added on their own! For more information, please see the user management instructions.

Since Inception does not support pymysql natively, you need to change the source code related to pymysql

Modify $PYTHON_HOME/lib/python3.6/site-packages/pymysql

Connections.py and cursors.py two files

Find line connections.py 786

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

Find cursors.py.

If self._result and (self._result.has_next or not self._result.warning_count):

Return

Change to

If self._result:

Return

This is the end of the content of "what is the method of building Yearning + Inception SQL audit platform". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.