160411. Real-time monitoring of MySQL database changes

For two development, a large part of the search for files and the change of database

for database changes. has not found the more useful monitoring database change monitoring software.

Today, I'll show you how to monitor database changes using MySQL's own features

1. Open the database configuration file My.ini (typically in the database installation directory) (D:\MYSQL)

2. Add the last line of the database

Log=log.txt

Code

3. Restart MySQL Database

4, go to the database data directory my is (D:\MYSQL\data) you will find a Log.txt file

Mine is in C:\Documents and Settings\All Users\Application Data\mysql\mysql Server 5.5\data

Test:
1, the database operation
2. View Log.txt file contents If you find a change, you can monitor the MySQL database changes.
Database query Delete Update Insert can be found

Hopefully this article will help you develop faster two times ^_^

Overview of log file types:   1. Error logging   issues 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 would be used. #log-error=d:/mysql_log_err.txt2. Query logging    establishes client connections and statements that are executed. My.ini configuration information: #Enter a name for the query log file. Otherwise a default name would be used. #log =d:/mysql_log.txt3. The update log   records the statement that changes the data. The use of the log is not supported. My.ini configuration information: #Enter a name for the update log file. Otherwise a default name would be used. #log-UPDATE=D:/MYSQL_LOG_UPDATE.TXT4. The binary log    records all statements that change data. Also used for replication. My.ini configuration information: #Enter a name for the binary log. Otherwise a default name would be used. #log-bin=d:/mysql_log_bin5. Slow logging all queries that have    performed longer than long_query_time seconds or queries that do not use indexes. My.ini configuration information: #Enter a name for the slow query log file. Otherwise a default name would be used. #long_query_time =1#log-slow-queries= D:/mysql_log_slow.txt

Under Linux: SQL code 1. # enter 2 in [Mysqld]. #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 # Enter #log in [mysqld] Log-error=/usr/local/mysql/log/error.log Log=/usr/local/mysql/log/mysql.log long_query_time=2 log-slow-queries=/usr/local/mysql/log/slowquery.logwindows : SQL code 1. # enter 2 in [Mysqld]. #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" # in [mysqld] input #log 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" Turn on slow query long_query_time = 2-- It means that SQL executed for more than a long time will be log down, here is 2 seconds log-slow-queries=/usr/local/mysql/log/slowquery.log-- Log the query back to a slower statement Log-queries-not-usiNg-indexes = nouseindex.log--literally, log down without using the index Querylog=mylog.log--record all executed statements 

Log storage: By default, when turned on, all logs are stored in the DataDir directory. If you do not specify a name, it will be the name of the host name later. If the host name is Songcomputer, then the related log is the Songcomputer.log file.

MySQL Log off and on:

Use the following command to see if the log is enabled

1. Mysql>show variables

Where value is off, the service is not turned on, to enable only the My.ini configuration information to be written to (My.ini as the MySQL installation directory), and then remove the previous "#"
Restart the MySQL service. OK, you will now see that the specified log file has been created. Conversely, to stop the MySQL log service, simply remove the corresponding configuration information from the My.ini
Can

>>>> corresponding use slow log query

Manually read slow logs and modify slow log times

Show variables like ' long% '

Will get a slow log time

To set the value of the slow log

Set long_query_time = 2;

Focus on the binary file

Binary log:

From the overview I can see that the log-bin of the My.ini configuration information does not specify a file name extension, because even if you specify it on the extension it is not used. When MySQL creates binary log files
, first create a file with a name of "Mysql_log_bin" and a ". Index" suffix, and then create a "mysql_log_bin" name to
". 000001" is the suffix of the file. When the MySQL service restarts a file with a suffix of ". 000001", the suffix name is incremented by 1, and if the log length exceeds
The upper limit of the Max_binlog_size (default is 1G) also creates a new log file, using flush
A new log file is also created by logs (MySQL command) or by performing mysqladmin–u–p flush-logs (Windows command Prompt).

Since the write is binary data, open the file with Notepad is not see the normal data, then how to view it?

Use the Mysqlbinlog command in the bin directory, such as:

Bin>mysqlbinlog d:/mysql_log/mysql_bin.000001

Bin>mysqlbinlog d:/mysql_log/mysql_bin.000002

Bin>mysqlbinlog d:/mysql_log/mysql_bin.000003

Bin>mysqlbinlog d:/mysql_log/mysql_bin.000004

Bin>mysqlbinlog d:/mysql_log/mysql_bin.000005

You can also view the binary file directories created by MySQL using the SQL statement:

1. Mysql> show master logs;

To view the current binary file status:

1. Mysql> Show master status;

As for the accurate understanding of log files, but also need readers to read carefully, deep understanding, here is no longer an Austrian!

160411. Real-time monitoring of MySQL database changes