Article author: Liu Hanhua, senior operation and maintenance engineer of Beitchat
Below is my first demand analysis and prototype design work, here sorted out with the need of friends to share! 1.1 What Problems should be solved According to the unified standard of MySQL statement writing, manual audit is difficult. The more standards there are, the more difficult it is to implement effectively, and the efficiency is low. 1.2 Can the solution solve this problem? My proposal here is based on Inception, to develop a web system integrating SQL submission, query, and process customization functions. Automate the SQL submission review process, efficiently implement the unified standard of Mysql statement writing, and optimize SQL statements. Inception is an automated operation and maintenance system integrating audit, execution, and rollback. It is modified from MySQL code and can be used for explicit, detailed, and accurate audit of MySQL SQL statements. It works in exactly the same mode as MySQL and can be directly connected using MySQL client. It is a server relative to the application (upper level auditing process system, etc.) and only needs to specify the server address and Inception server port when connecting. It is a client relative to the online MySQL server corresponding to the statement to be audited or executed. Internally, it needs to connect to the database server in real time to obtain the required information, or directly execute the corresponding statement online and obtain binlog, etc. Inception is an intermediate service. However, Inception and some corresponding open source systems only have the functionality of a simple Inception table structural review, which cannot meet our requirements, so we have to propose corresponding requirements and prototype design ourselves, and deliver it to the platform team for development! 1. The SQL submission process is shown in figure 2
Online RDS submission process: SQL statements are submitted by the developer and must be checked for Inception for SQL syntax specification before they can be submitted for further project supervisor review. Finally, the DBA will decide whether to execute the SQL statements immediately based on the online RDS status. 2. Project Configuration Management Web interface supports RDS development, testing, online RDS project configuration query, creation, modification and other management functions. 2.1 Project Configuration Management Only DBA has the right to adjust, and can decide the process and configuration of SQL submission in various environments.
There is no need for content review and DBA review for development and test environment. You can publish development and test environment only after passing automatic review (Inception for SQL syntax specification inspection). Automatic auditing checks SQL syntax specification by calling “automatic auditing associated script” to access Inception (RDS and mysql configuration binding and connection support). SQL is executed when host is the RDS or mysql address, User is the account,pass is the password,port is the port, and execute is 1. Execute = 0; mysql_structure is a Python connection called by SQL statements; Inception only has a common script that accepts parameters and receives feedback on execution from Python scripts. Step 1: Developers select the database name and corresponding environment to be submitted, and input the following information.
When you click check, Inception will receive a execute value of 0, and Inception will be checked for SQL syntax specification.
After passing the check, click Submit and wait for the project manager to review the content
Step 2: The project manager will click “View” and decide whether to allow the approval according to the SQL content. If the approval is approved, the next step will be carried out
Step 3: Check by the DBA, click the check, and then decide whether to execute the SQL statement immediately according to the online RDS or MYSQL load
4, “Operation Record Query” can quickly export the operation records and SQL statements of a library, can locate THE SQL statements and the submitted person, time and submit audit records
Click here to see more records of the audit process
Below are the detailed SQL statements and related records that are exported
5, “SMTP send Settings” : support online email notification to the specified number of people
6. Associated Inception automatic auditing: Pass SQL statements through the Web, call the corresponding Python script to access the Inception interface, and submit THE SQL code to the development and test environment. SQL submitter account: developer, project leader, DBA Sql submission statements contain dangerous operation fields of DROP and TRUNCate. If these statements are found to be automatically submitted, they will not pass. Finally, thank you again for your support, and thank again Lin Yi and Li Haiwen for their help and support!