Abstract:

I participated in ACOUG Forum for the first time on December 5, 2015 in Guangzhou, ACOUG Asia Tour. At the meeting, Mr. Gai shared that “DBA in cloud era will be from the back to the front, DevOps is imperative — the best practice is SQL audit”. At that time, I was so enthusiastic that DATABASE can still be played in this way.

One, the origin

ACOUG Asia Tour Guangzhou Station, Dec. 2015

At the ACOUG forum for the first time, Mr. Gei shared that “IN the cloud era, DBAs will be from the back to the front, DevOps is imperative — the best practice is SQL audit.” At that time, I was so enthusiastic that database can be played in this way.

Later, I tried SQL auditing in the company, developed SQL development specifications, trained developers, conducted human auditing, and released human auditing, thinking it was the legendary best practice of database DevOps. The whole SQL audit process is manual and inefficient, and the funny thing is that the developers also have the same DB permissions. What do you want your DBA to do? It was naive to think that as long as DBAs were more specialized in SQL than in development, they would follow the rules of the game. Looking back now, I was too high, too ideal, not practical enough.

2017.5.11-13, Beijing, DTCC- Database Technology Conference

I attended DTCC for the first time, which should be the most productive 3 days since I became a full-time DBA. More than half of the lecturers talked about DB automatic operation and maintenance, which planted a seed in my heart.

Later, I tried to use Python to develop several small tools for automatic operation and maintenance in the company. I personally felt the convenience brought by automation. Automation can indeed avoid a lot of low-value, repetitive and tedious labor. At the same time, I have been thinking, can we use automation to achieve SQL audit? By chance, I found archer, an OPEN source project with SQL reviews, on Github. After a trial run, the enthusiasm of two years ago reappeared.

Ii. Platform introduction

archer

An automated SQL operation platform based on inception, supporting work order, audit, authentication, email, OSC and other functions.

Making address: https://github.com/jly8866/archer

If archer is decomposed, I think it can be divided into Inception and Django

  • Inception is internal and subject to review

  • Django is external and takes care of presentation

Ps: This kind of understanding, I don’t know the original author will be very depressed, ha ha



inception

A set of audit, execute, backup and generate a rollback statement in a MySQL automated operation and maintenance tool

Github address: https://github.com/mysql-inception/inception



Django

Django is an open source Web application framework written in Python. The MT’V frame pattern is adopted, that is, model M, template T and view V.

Third, secondary development

Some simple secondary development was also done based on Archer:

  • Mask single sign-on

  • Fix email sending bug

  • Display full Chinese name

  • Assign the specified database instance to the engineer

Going forward, more features are planned for Archer:

  • MSDB

  • Data archiving

  • Database Backup

  • Performance report

  • Inspection report

Four, summary

At present, Archer has been deployed to the production environment, and DB scripts have been successfully released for a newly launched X project. The following preparations have been gradually rolled out.

In general, I think the effect is ok, at least I have made a step in database automation and DevOps. Compared with the manual audit of SQL two years ago, I have summed up two experiences that I feel the most:

  • Always use automated tools/platforms, purely manual is inefficient

  • Must find the right audit point, let everyone comply with, for SQL audit, is to put DB operation rights in the hands of the DBA, can not be open to the outside world, this must be strangled!! Yes, strangle it, kill it!! Ha ha

V. Appendix:

Finally, attached are some screenshots of the platform:




The original post was published on January 29, 2018

Author: LAN Jianfeng

This article is from the cloud habitat community partner “Lao Ye Teahouse”. For relevant information, you can follow the wechat public account of “Lao Ye Teahouse”

If you find any content suspected of plagiarism in our community, you are welcome to send an email to [email protected] to report and provide relevant evidence. Once verified, our community will immediately delete the content suspected of infringement.

The original link