How to effectively track changes to MySQL instance tables and permissions on the line

Introduced

From the point of view of a system administrator or DBA, the total expectation is to limit the variety of changes on the line to a manageable range, reducing some of the uncertainties. There are several advantages to doing so:

1. Change of the library table on the record line; 2. a global understanding of the changes to the library tables on the line; 3. If there is a problem, convenient rollback operation;

From the three point of view, there are many ways to achieve, such as through the migrate and other tools to enforce all operations in a unified manner, which requires developers to do more work, so such tools in the non-scale business scenario is more difficult to achieve; In addition, administrators or DBAs can record changes in a similar way, such as Redmine, but there are many factors that are not controllable, especially depending on the on-line process, and are prone to flaws. This explains how to track changes to the online library table as described in this article, which is described in MySQL database below.

How to track

In Postgresql, because triggers are well supported for various operations, we can record all changes to DDL statements in the form of triggers. Compared to this, MySQL appears to be weaker, and we can only achieve similar goals in other ways. Below the middleware, log, binlog, register slave, Mysqldiff Five ways to introduce.

1. Middleware

Existing middleware, such as Atlas, Kingshard, Mycat, etc., are deployed between the program and MySQL in the role of proxy, and all SQL sent to MySQL is forwarded via proxy. As shown, we can add a number of DDL, DML-related statements at the proxy level, to track changes.

    +------+        +-------+        +-------+    | app  |  --->  | proxy |  --->  | MySQL |    +------+        +-------+        +-------+

This is a high degree of freedom and can be customized. However, some development capabilities are required, and SQL filtering also affects the performance of queries, and the use of middleware to directly modify the table structure is also a risky approach.

2. Log

This is a simple way to log all SQL statements by opening MySQL's general log or audit log. This way is more suitable for the development environment, the online environment if the opening will produce a lot of logs, the harm is far greater than the benefit, is not conducive to maintenance;

3. Binlog

Administrators or DBAs can also parse MySQL's binlog to filter changes to tables or permissions. This approach is essentially the second way, the online database needs to turn on the Binlog option, and parsing Binlog is also a resource-intensive operation. If there are many instances on the line, this method is particularly not available.

4. Register slave

Registering slave means to forge a fake slave through MySQL's master-slave protocol, so that master sends all updates and then does some filtering. This is especially true when synchronizing data or incremental consumption scenarios where only changes to the record table or permissions are really overqualified, and there are many instances on the line that are not available. Typical tools are myreplication, Tungsten-replicator, and Ali's canal.

5. Mysqldiff

In fact, the permissions and table changes themselves are low-frequency operation events, although all of the above four ways can achieve the goal, but in essence, are very resource-intensive operations. With this in mind, we can achieve the tracking of permissions and table structure changes in a comparative way, as described in the Sys-mysql-diff tool. For versatility, the Sys-mysql-diff tool needs to get the definition statements for all the tables in the specified library each time, and to generate the corresponding DDL statements by contrast. Mysqldiff is the encapsulation of sys-mysql-diff tools that can be used to track multiple instances in bulk.

How to use Mysqldiff

The Mysqldiff tool is a layer of encapsulation on the basis of the Sys-mysql-diff tool, so it essentially tracks changes in the online library through the Sys-mysql-diff tool. In the practical application, the following points need to be noted:

1. Configuration files

The configuration required by Mysqldiff is referenced below:

[Backend]dsn= User_mysqlmon:xxxxxxxx@tcp(10.0.21.17:3306)/mysqldiff?charset=Utf8[test3301]host=10.0.21.5Port=3301DB=testuser=User_mysqldiffpass=Xxxxxxxxtag=Host_location[test3306]host=10.0.21.7Port=3306DB=Perconauser=User_mysqldiffpass=Xxxxxxxxtag= Host_location

2. Permissions

All the change results are saved to the Mysql_diff table in the specified MySQL library, which is the above [backend] section, which requires the select, insert, update relevant permissions for the table. The tracked instance is part of the need to view the [testXXXX] table structure and user permissions so that select and Grant OPTION permission. We take User_mysqlmon user as [backend] the user, take User_mysqldiff as [testXXXX] part of the user as an example, need to give them the following permissions:

Grant Select,insert,update on mysqldiff.* to [e-mail protected] ' 10.0.21.% '; Grant SELECT On *. * to [email protected] ' 10.0.21 .% ' with GRANT option;

db = Information_schema In the configuration indicates that all databases are tracked;

3. Running

Run the Mysqldiff command to trace:

#./mysqldiff-conf Conf.cnf-verbose ./Geneva/ -  -: to: ----------------------------changes from10.0.21.5:3301changes from10.0.21.7:3306DROP TABLE ' emp '; SET GLOBAL wait_timeout= +; ./Geneva/ -  -: to: -Insert10.0.21.17:3306/Percona OK ./Geneva/ -  -: to: ----------------------------

insert ... okA row indicates that the result is inserted [backend] in.

Summarize

It is relatively simple and convenient to keep track of library tables and permissions in Mysqldiff mode, which is lighter than other methods. It is also not subject to business scenarios and administrator habits, and is relatively generic. But it also has its own flaws, the tables that change frequently in a short period of time are difficult to track, and Mysqldiff can only record the last change. In addition, the administrator needs to strictly restrict the permissions of the profile, it is best to give 0600 permissions to view only the current user. Overall, however, Mysqldiff is a more appropriate and versatile tool to track changes to the permissions on the online library table.

How to effectively track changes to MySQL instance tables and permissions on the line