Real-time Monitoring of mysql database data changes

Warning: fopen (home1vhostvh499565wwwblogwp-contentpluginsdevformattergeshigeshimysql.php) [function. fopen]: failedtoopenstream: Nosuchfileordirectoryinhome1vhostvh499565wwwwww_shaozhuqingwp-contentpluginsdevfor

Warning: fopen (/home1/vhost/vh499565/www/blog/wp-content/plugins/devformatter/geshi/mysql. php) [function. fopen]: failed to open stream: No such file or directory in/home1/vhost/vh499565/www/www_shaozhuqing/wp-content/plugins/devfor

Warning: Fopen (/home1/vhost/vh499565/www/blog/wp-content/plugins/devformatter/geshi/mysql. php) [function. fopen]: failed to open stream: No such file or directory in /Home1/vhost/vh499565/www/www_shaozhuqing/wp-content/plugins/devformatter/devgeshi. phpOn line 103

Warning: Fopen (/home1/vhost/vh499565/www/blog/wp-content/plugins/devformatter/geshi/mysql. php) [function. fopen]: failed to open stream: No such file or directory in /Home1/vhost/vh499565/www/www_shaozhuqing/wp-content/plugins/devformatter/devgeshi. phpOn line 103

Warning: Fopen (/home1/vhost/vh499565/www/blog/wp-content/plugins/devformatter/geshi/mysql. php) [function. fopen]: failed to open stream: No such file or directory in /Home1/vhost/vh499565/www/www_shaozhuqing/wp-content/plugins/devformatter/devgeshi. phpOn line 103

Warning: Fopen (/home1/vhost/vh499565/www/blog/wp-content/plugins/devformatter/geshi/mysql. php) [function. fopen]: failed to open stream: No such file or directory in /Home1/vhost/vh499565/www/www_shaozhuqing/wp-content/plugins/devformatter/devgeshi. phpOn line 103
For secondary development, a large part of it is looking for files and database changes for database changes. No useful monitoring software for database changes has been found. Today, I will introduce you to how to monitor database changes using the functions provided by mysql. 1. Open the database configuration file my. ini (usually in the database installation directory) (D: \ MYSQL) 2. Add the logtail log.txt code in the last row of the database. 3. Restart the mysql database. 4. Go to the database data directory. I am (D: \ MYSQL \ data). You will find another log.txt file in C: \ Documents and Settings \ All Users \ Application Data \ MySQL Server 5.5 \ data test: 1、for data warehouse operation 2、 log.txt file content if any change is found, you can monitor mysql database changes, database queries, deletions, updates, and inserts can be found. Hope this article can help you with faster secondary development ^_^

Log File Type Overview :?? 1. Error Log ?? Record the problems that occur when you start, run, or stop mysqld. My. ini configuration information: # Enter a name for the error log file .?? Otherwise a default name will be used. # log-error = d:/mysql_log_err.txt2. query the log ???? Record the established client connection and executed statements. My. ini configuration information: # Enter a name for the query log file. Otherwise a default name will be used. # log = d:/mysql_log.txt3. Update log ?? Statement used to record data changes. This log is not supported. My. ini configuration information: # Enter a name for the update log file. Otherwise a default name will be used. # log-update = d:/mysql_log_update.txt4. binary log ???? Records all statements for changing data. It is also used for replication. My. ini configuration information: # Enter a name for the binary log. Otherwise a default name will be used. # log-bin = d:/mysql_log_bin5. Slow log ???? Record all queries whose execution time exceeds long_query_time seconds or where no index is used. My. ini configuration information: # Enter a name for the slow query log file. Otherwise a default name will be used. # long_query_time = 1 # log-slow-queries = d:/mysql_log_slow.txt

In linux: SQL code ??? 1. # merge in [mysqld ?? 2. # log ?? 3. log-error =/usr/local/mysql/log/error. log ?? 4. log =/usr/local/mysql/log/mysql. log ?? 5. long_query_time = 2 ?? 6. log-slow-queries =/usr/local/mysql/log/slowquery. log? # Commit in [mysqld] # log-error =/usr/local/mysql/log/error. log =/usr/local/mysql/log/mysql. log long_query_time = 2 log-slow-queries =/usr/local/mysql/log/slowquery. log ?? In windows: SQL code ??? 1. # merge in [mysqld ?? 2. # log ?? 3. log-error = "E:/PROGRA ~ 1/EASYPH ~ 1.0B1/mysql/logs/error. log "?? 4. log = "E:/PROGRA ~ 1/EASYPH ~ 1.0B1/mysql/logs/mysql. log "?? 5. long_query_time = 2 ?? 6. log-slow-queries = "E:/PROGRA ~ 1/EASYPH ~ 1.0B1/mysql/logs/slowquery. log "? # Commit in [mysqld] # log-error = "E:/PROGRA ~ 1/EASYPH ~ 1.0B1/mysql/logs/error. log "log =" E:/PROGRA ~ 1/EASYPH ~ 1.0B1/mysql/logs/mysql. log "long_query_time = 2 log-slow-queries =" E:/PROGRA ~ 1/EASYPH ~ 1.0B1/mysql/logs/slowquery. log "??? Enable slow query long_query_time = 2 -- indicates how long the SQL statement will be logged. Here, log-slow-queries =/usr/local/mysql/log/slowquery is 2 seconds. log -- Record slow statements returned by the query? Log-queries-not-using-indexes = nouseindex. log -- it is a literal meaning that no index query is used in log? Log = mylog. log -- Record all execution statements

Log storage: by default, When enabled, all logs are stored in the DataDir directory. if no name is specified, it will later host name. if the host name is songcomputer, the related log is songcomputer. log File. Close and enable Mysql logs:Run the following command to check whether logs are enabled: mysql> show variables like 'Log _ % '; if the Value is OFF, the service is not enabled, to enable this function, you only need. write ini configuration information (my. ini is in the mysql installation directory), remove the "#" number, and then restart the mysql service. OK. The specified log file has been created. To stop mysql Log service, you only need to remove the corresponding configuration information in my. ini. >>> Use slow log query to manually read slow logs and modify the slow log time. show variables like 'long % 'will get the slow log time and set the slow log value set long_query_time = 2. binary files Binary log:From the overview, I can see that the log-bin of my. ini configuration information does not specify the file extension, because it is not used even if you specify the extension. When mysql creates a binary log file, it first creates a file named "mysql_log_bin" and named ". index, and create a file named "mysql_log_bin" with the suffix ". 000001. When the mysql service is restarted once, A file with a suffix of 000001 is added, and the suffix name is incremented by 1. If the log length exceeds the upper limit of max_binlog_size (1 GB by default), a new log file is created; using flushlogs (mysql Command Line) or executing mysqladmin-u-p flush-logs (windows Command Prompt) will also create a new log file. Since binary data is written, normal data cannot be seen when you open a file in Notepad. How can we view it? Use the mysqlbinlog command in the BIN directory, for example, Bin> mysqlbinlog d:/mysql_log/logs> mysqlbinlog d:/mysql_log/mysql_bin.000002Bin> mysqlbinlog d:/mysql_log/logs> mysqlbin: /mysql_log/mysql_bin.000004Bin> mysqlbinlog d:/mysql_log/mysql_bin.20.05 use SQL statements to view the binary file directory created by mysql: Mysql> show master logs; view the current binary file status: mysql> show master status; as for the accurate understanding of log files, you need to carefully read and deeply understand the log files. I will not describe them here!

Original article address: real-time monitoring of mysql database data changes. Thanks to the original author for sharing.