How to effectively track online MySQL instance tables and permission changes, mysql instances

How to effectively track online MySQL instance tables and permission changes, mysql instances
Introduction

From the perspective of the system administrator or DBA, it is always expected to limit various online changes to a controllable range to reduce some uncertain factors. This has the following benefits:

1. Record Online Database and table changes;

2. Have a global understanding of online database and table changes;

3. If there is a problem, it is convenient to roll back the operation;

From these three points of view, there are many ways to achieve this. For example, using tools such as migrate to force all operations to be executed in a uniform manner, this requires more cooperation from developers, therefore, this type of tool is difficult to implement in non-scale business scenarios. In addition, administrators or DBAs can record changes in similar ways, such as the knowledge base, redmine, but there are many uncontrollable factors, it is especially dependent on the online process and is prone to leakage. this document introduces how to track changes to online database tables. The following describes how to track changes to MySQL databases.

Tracking Method

In Postgresql, because triggers support various operations, we can use triggers to record changes to all DDL statements. in contrast, MySQL is relatively weak, and we can only achieve similar goals in other ways. the following describes how to use middleware, log, binlog, and register slave and mysqldiff.

1. Middleware

Existing middleware atlas, kingshard, and mycat are all deployed between the program and MySQL as a proxy, and all SQL statements sent to MySQL are forwarded through the proxy. as shown in, we can add DDL and DML statement records at the proxy level to track changes.

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

 

This method has a high degree of freedom and can be customized at will. however, some development capabilities are required. In addition, SQL filtering also affects query performance. Using Middleware to directly modify table structures is also a highly risky method.

2. log

This method is simple. Open MySQL's general log or audit log to record all SQL statements. this method is suitable for the development environment. If the online environment is enabled, it will generate a lot of logs, which far outweigh the disadvantages and is not conducive to maintenance;

3. binlog

The Administrator or DBA can also parse MySQL binlog to filter table or permission changes. this method is essentially the second method. The binlog option must be enabled for online databases, and parsing binlog is a resource-consuming operation. this method is especially unavailable if there are many online instances.

4. Register slave

Registering slave means forging a fake slave through the MySQL master/slave Protocol, so that the master will send all updates and then perform some filtering operations. this method is especially suitable for data synchronization or incremental consumption scenarios. Here, it is only used to record changes to tables or permissions, but it is not applicable if there are many online instances. typical tools include myreplication, tunsten-replicator, and Alibaba's canal.

5. mysqldiff

In fact, permission and table change are low-frequency operation events. Although the above four methods can achieve the goal, they are essentially resource-consuming operations. with this in mind, we can track permissions and table structure changes by comparing them. For details, see sys-mysql-diff. considering the versatility, the sys-mysql-diff tool needs to obtain the definition statements of all the tables in the specified database every time and generate the corresponding DDL statements through comparison. mysqldiff is an encapsulation of the sys-mysql-diff tool. You can track multiple instances in batches.

How to Use mysqldiff

The mysqldiff tool is encapsulated on the basis of the sys-mysql-diff tool. Therefore, it essentially uses the sys-mysql-diff tool to track online database changes. in practice, pay attention to the following points:

1. Configuration File

For the configuration required by mysqldiff, refer to the following:

[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. Permission

All change results are saved to the mysql_diff table in the specified MySQL database.[backend]For this tableselect, insert, updateRelated permissions. The instance to be tracked is[testXXXX]To view the table structure and user permissions, select and grant option permissions are required.[backend]User_mysqldiff[testXXXX]For example, some users need to grant them the following permissions:

grant select,insert,update on mysqldiff.* to user_mysqlmon@`10.0.21.%`;grant select on *.* to user_mysqldiff@`10.0.21.%` with grant option;

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

3. Run

Run the mysqldiff command for tracking:

# ./mysqldiff -conf conf.cnf -verbose2017/03/20 16:31:27 ---------------------------changes from 10.0.21.5:3301 changes from 10.0.21.7:3306 DROP TABLE `emp`;SET GLOBAL wait_timeout = 1000;2017/03/20 16:31:27 insert 10.0.21.17:3306/percona ok2017/03/20 16:31:27 ---------------------------

insert ... okA row inserts the result[backend]Medium.

Summary

It is relatively simple and convenient to use mysqldiff to track changes in database tables and permissions. It is lighter than other methods. in addition, it is not restricted by business scenarios and administrator habits, and is relatively common. however, it also has its own shortcomings. It is difficult to track tables that change frequently in a short period of time. mysqldiff can only record the last change. in addition, the Administrator must strictly restrict the permissions of the configuration file. It is best to grant 0600 of the permissions only to the current user. however, to track online database and table permission changes, mysqldiff is a suitable and common tool.