Discussion on Mysql DATA Status Monitoring)

Application scenarios:

To run the program for a long time, you need to query Mysql in almost the whole table and respond to data changes within the tolerable range.

 

Solution 1: update the time through the Mysql built-in table

Query scheme: SELECT TABLE_NAME, UPDATE_TIME FROM INFORMATION_SCHEMA.tables WHERE TABLE_SCHEMA = 'palas _ V4 ';

Problem: innodb does not support the database engine. It only supports table-level judgment (acceptable ).

Advantage: Fast query speed.

Variant solutions: 1. Create a trigger and update UPDATE_TIME to the current timestamp when the table executes update/insert/delete. 2. Add the program logic to update the timestamp at the database access interface.

 

Solution 2: add the updated timestamp field to all rows

Query solution: to determine whether to modify the data, you only need to query the maximum updatetime of the table. For incremental update, you only need to query data later than the last query time.

There is a problem: each row of the table increases the space consumption by 4 bytes. The timestamp update problem can be solved by mysql itself. The delete operation is not supported. to delete the table, you need to add valid fields to change the status.

Advantage: Row-level update is supported.

 

Solution 3: system modification time for reading Mysql files

Query solution: Obtain the system modification time of E: \ SqlData \ data \ Palas_V4 \ Media. frm

Problem: You need to operate on the server where Mysql is located, or you need to remotely access it, which may cause security problems. Only table-level judgment is supported.

Advantage: high speed, no extra tablespace or increased mysql load.

Variant solutions: 1. Create a monitoring service on the server where Mysql is located to provide database status information.

 

Solution selection:

Advantages and disadvantages:

Solution 1 and solution 2 must make corresponding changes to the database. The modifications do not affect the business logic, but the Mysql operations will be doubled (the actual modification requires 8 (may be 4) data size per byte) No network transmission; simple program implementation logic; when the amount of data modified is small, there is almost no impact on performance, but after modification, the query overhead is large. Solution 2: The growth of table space will increase, which has a small impact on the business logic (deletion is not supported). Some tables also need to add fields to determine the status. After modification, the query overhead is small. The performance of solution 3 is not much different from that of solution 1, but it requires additional programming to provide status query. The time and hard disk IO will increase (not significant ).

Type Selection:

Based on the actual situation, the priority is listed as follows:

Solution 3-> solution 2-> solution 1 (solution 1: Too Many triggers will make table maintenance difficult, and the engine will reduce database performance)

 

Enable independent tablespace for Mysql:

1. stop mysql:/etc/init. d/mysqld stop

2. Change my. cnf configuration file: innodb-file-per-table = 1

3. Back up databases using the innodb Engine: mysqldump-u tg-p tg>/home/6fan/tg. SQL;

4. Delete innodb databases and log files. If the innodb database folder is not deleted, the innodb engine cannot be started.

5. start mysql:/etc/init. d/mysqld start

6. Import the database: mysql-u root-p

If you have a graphical management tool, you can directly back up the database to be listened to after modifying the configuration file, and then restore the database after deletion.

 

Build File listening and data to provide services:

1. Enable the file listening service on the mysql server and provide the get put method (only the status is provided and the data needs to be checked by yourself)

2. The Get method is called when global data is provided for Service Startup. The Put method declares which synchronization is required for the primary receiver.

 

Build global data to provide services: (Web Api supports json and xml formats)

1. Use get to get the status of all files at startup;

2. Accept put data to enable data update (you can switch to get when querying data );

3. Data is returned when a caller exists;

4. Compress and save the data for Transmission