In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
Preface
Using open source Yearning and inception open source software, build an audit platform for MYSQL audit and online MYSQL statement update.
Function description
Yearning: a complete mysql-sql audit platform solution based on Vue.js and Django. Provide Inception-based SQL detection and execution, provide visualization for Inception.
Inception: it 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 the SQL statements of MySQL. 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.
Operating system and IP information
Serial number
Server name
IP
Operating system
one
Centos_6
10.21.88.51
CentOS release 6.8 (Final)
Software version and installation path
Serial number
Software
Version information
Installation path
Code directory
one
Python
3.6.6
/ usr/local/python
two
Yearning
None (latest)
/ opt/Yearning
three
Inception
2.1.50
/ usr/local/inception
four
Nginx
1.10.2
Yum installation
/ usr/share/nginx/html
five
Mysql
5.7.21
/ usr/local/mysql
Installation process
Dependent package installation
Yum-y install cmake bison ncurses-devel gcc gcc-c++ openssl-devel
Inception installation
Cd / optwget https://github.com/mysql-inception/inception/archive/master.zipmkdir-p / usr/local/inceptionmkdir-p / inception/ {data Logs} unzip master.zip-d / inceptioncd / inception/inception-mastercmake-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" make & & make install
Configuration
Edit the parameter file vim / etc/ inc.cnf [inception] general_log=1 # which is the parameter of the native MySQL and is used to record which statements have been executed on the Inception service Used to locate some problems, such as general_log_file=/usr/local/inception/data/inception.log # set the file path written by general log # Inception service port socket=/usr/local/inception/data/inc.socket # Inception socket file storage location 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 # when creating a table, the value of the self-incrementing column is not specified as 1 Then report an error inception_check_autoincrement_name=1 # when building a table, if the name of the specified self-incrementing column is not ID, the error is reported, which means it is meaningful. When prompting inception_check_column_comment=1 # to build a table, the column has no comments and inception_check_column_default_value=0 # check whether the new column attribute should have a default value when creating a table, modifying a column, or adding a column. Inception_check_dml_limit=1 # when LIMIT is used in the DML statement Do you want to report an error inception_check_dml_orderby=1 # when you use Order By in the DML statement, do you want to report an error inception_check_dml_where=1 # when there is no WHERE condition in the DML statement, do you want to report an error inception_check_identifier=1 # turn on and off Inception's checking of various names in the SQL statement, if set to ON, if you find that there are characters other than numbers, letters and underscores in the name Will be reported Identifier "invalidname" is invalid, valid options: [a valid options ZMagol 0-9 inception_check_index_prefix=1 # is to check whether the prefix of the index name is "idx_" and whether the unique index prefix is "uniq_" inception_check_insert_field=1 # is to check the existence of the linked list of columns in the insert statement inception_check_primary_key=1 # if there is no primary key, it will report an error inception_check_table_comment=0 # when building the table There is no comment on the table inception_check_timestamp_default=0 # when building a table, if you do not specify a default value for the timestamp type, you will report an error if the inception_enable_autoincrement_unsigned=1 # self-increment column is to check whether the BLOB field is supported for the unsigned inception_enable_blob_type=0 #. Including table creation, column modification, and new column operations are enabled by default inception_enable_column_charset=0 # allows columns to set their own character set inception_enable_enum_set_bit=0 # whether the enum,set,bit data type inception_enable_foreign_key=0 # supports foreign key inception_enable_identifer_keyword=0 # check whether there is an identifier written as the keyword MySQL in the SQL statement. The default value is alarm. Inception_enable_not_innodb=0 # the storage engine specified for table creation is not Innodb, and does not report errors if inception_enable_nullable=0 # is listed as NULL when creating or adding columns. Do not report errors when inception_enable_orderby_rand=0 # orderby rand? is inception_enable_partition_table=0 # supported when partitioning table inception_enable_select_star=0 # Select*? is inception_enable_sql_statistic=1 # set to support statistics of statements executed by Inception? What is the proportion of various statements? if this parameter is turned on, each execution will store the statistics of this operation as a record 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, it is prompted to convert it to VARCHARinception_max_key_parts=5 # the maximum number of columns in an index, more than this number will report an error inception_max_keys=16 # the maximum number of indexes in a table, inception_max_update_rows=10000 # the maximum number of rows expected to be affected in a modification statement If you exceed this number, inception_merge_alter_table=1 # will report an error in multiple statements that change the same table, prompting you to synthesize a # inception supporting OSC parameter inception_osc_bin_dir=/user/bin # to specify the location of the pt-online-schema-change script, which cannot be modified. Set inception_osc_check_interval=5 # corresponding to the OSC parameter-check-interval in the configuration file. The meaning is that Sleep time between checks for-max-lag.inception_osc_chunk_size=1000 # corresponds to OSC parameter-chunk-sizeinception_osc_chunk_size_limit=4 # corresponds to OSC parameter-chunk-size-limitinception_osc_chunk_time=0.1 # corresponds to OSC parameter-chunk-timeinception_osc_critical_thread_connected=1000 # corresponds to parameter-thread_connected part inception_osc_critical_thread_running=80 # in critical-load corresponds to parameter-thread_ in critical-load Running part inception_osc_drop_new_table=1 # corresponding parameter-[no] drop-new-tableinception_osc_drop_old_table=1 # corresponding parameter-[no] drop-old-tableinception_osc_max_lag=3 # corresponding parameter-max-laginception_osc_max_thread_connected=1000 # corresponding parameter-thread_connected part inception_osc_max_thread_running=80 # corresponding parameter in max-load-thread_running part inception_osc in max-load The parameter _ min_table_size=0 # is actually a switch for OSC If set to 0, all ALTER statements go OSC, and if set to non-0, the OSC mode is used when the table occupies a space larger than this value. In M, the table size 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'". A global OSC switch, which is turned on by default, is set to OFF if you want to turn it off, so that inception_osc_print_none=1 # is directly modified to set in the Inception return result set. As to whether the standard output information of the original OSC in the execution process should be printed to the error message column corresponding to 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 print the corresponding parameter inception_osc_print_sql=1 #-print# backup server information, pay attention to change it to your machine. Used for rollback. Inception_remote_system_password=P@ssw0rdinception_remote_system_user=incep_rwinception_remote_backup_port=3306inception_remote_backup_host=10.10.3.70inception_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 and test
Nohup / usr/local/inception/bin/Inception-- defaults-file=/etc/inc.cnf & netstat-tulpn | the default port of grep 6669 / / inception is 6669/usr/local/inception/bin/mysql-uroot-h227.0.0.1-P6669 / / Connect inception, inception get variables; / / View all parameters of inception in the same way as mysql
Install PYTHON
Cd / usr/local/srctar-xvf Python-3.6.6.tgzmkdir-p / usr/local/python/3.6.6/lib./configure-- enable-shared-- prefix=/usr/local/python/3.6.6 LDFLAGS= "- Wl -rpath / usr/local/python/3.6.6/lib "make & & make install soft connection cp / usr/bin/python / usr/bin/python2.6.6ln-fs / usr/local/python/3.6.6/bin/python3.6 / usr/bin/pythonln-fs / usr/local/python/3.6.6/bin/pip3 / usr/bin/pippython-vYum modify vi / usr/bin/yum to change the header #! / usr/bin/python to #! / usr/bin/python2.6.6
Yearning installation
Cd / optgit clone https://github.com/cookieY/Yearning.git
Edit Yearning/src/deploy.conf
[mysql] db = created library name address = database address port = database port password = database password username = database user [host] ipaddress = server ip address (platform address is displayed when messages are pushed)
Since Inception does not support pymysql natively, you need to change the source code related to pymysql. Note: connections.py and cursors.py that have been modified under the install folder can be replaced directly.
Pip3 install-r requirements.txt # install the corresponding python dependency library python3 manage.py makemigrations core & & python3 manage.py migrate core # initialize database echo "from core.models import Account;Account.objects.create_user (username='admin', password='Yearning_admin', group='admin',is_staff=1)" | python manage.py shell # add initialization user echo "from core.models import grained Grained.objects.get_or_create (username='admin', permissions= {'person': [],' ddl':'1, 'ddlcon': [],' dml':'1, 'dmlcon': [],' dic':'1), 'diccon': [],' dicedit':'0, 'user':' 1, 'base':' 1, 'dicexport':' 0, 'query':' 1' 'querycon': []}) "| python manage.py shell # initialization permission echo" from core.models import globalpermissions Globalpermissions.objects.get_or_create (authorization='global', inception= {'host':', 'port':', 'user':', 'password':', 'back_host':', 'back_port':', 'back_user':', 'back_password':'}, ldap= {'type':', 'host':', 'sc':'' 'domain':', 'user':', 'password':'}, message= {'webhook':', 'smtp_host':', 'smtp_port':', 'user':', 'password':', 'to_user':', 'mail': False,' ding': False}, other= {'limit':', 'con_room': [' AWS', 'Aliyun' 'Own',' Other'], 'foce':', 'multi': False,' query': False, 'sensitive_list': [] 'sensitive':'}) "| python manage.py shell # initialization dynamic configuration information cp-rf Yearning/webpage/dist/* $NGINX_HOME/html/ # copy the compiled static file to the nginx html directory (if you change the Nginx static path address, copy the static file to the corresponding static file directory) systemctl start nginx # start nginx$PYTHON_HOME/bin/gunicorn settingConf.wsgi:application-c gunicorn.conf # startup Django default account: admin password: Yearning_admin
instructions
You need to create a user first (the permissions of Yearning are divided into administrator and user. By default, only admin administrator account is used for audit. Create: user account is used for MYSQL audit submitted), and enter user name and password to log in:
User
Click "manage" after logging in, and the red circle is shown below.
Click "user" to add users. (example: an appuser user has been added as follows)
Database settings (configure database information for connection auditing, set a test connection as follows)
Settings (inception related settings, other settings slightly)
After the "users", "database" and "settings" under the relevant "management" above, the data audit operation can be carried out.
Submission and review of work orders
Work order submission
Create a work order after the user logs in, as shown in the following figure: (after filling in the information, check it correctly before submitting)
Audit (log in with the administrator account and enter the following interface for audit)
Click the "Action" of the work order to be reviewed to enter the following interface:
Performing the operation has completed the audit operation.
Rollback operation: (performed by the administrator and must be set up for the inception backup server)
After the work order is approved, there is a "View rollback statement" in the following figure. Click submit work order after entering the following interface, and the rollback operation is completed after re-approval.
The above is the MySQL audit platform built by Yearning and inception, and a simple introduction to its use. For more information on the use of Yearning, please refer to its official documentation.
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.
Views: 230
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.