Mysql Log, where is the mysql Log File?

Source: Internet
Author: User

Mysql Log, where is the mysql Log File?

Mysql logs are classified into binary logs, error logs, general query logs, and slow query logs. Binary log: records database operations in the form of binary files, but does not record query statements. Error Log: records information such as mysql server startup, shutdown time, and running errors. General query log: records slow query logs of user logon and query information: record the operation that exceeds the specified time. Note: Except binary logs, other logs are text files. Log files are usually stored in the database data directory. By default, only the error log function is enabled. The other three types of logs must be set by the database administrator. The log function reduces the database execution speed and takes a lot of time to record logs. It also occupies a large amount of hard disk space. 1. binary log: Also called update log, used to record database changes. The binary log function is disabled by default. You can use the log-bin option of the my. cnf or my. ini file to enable binary logs. Add the log-bin option to my. cnf or my. in the [mysqld] Group of the INI file, for example, # my. cnf (in Linux) or my. ini (in windows) [mysqld] log-bin [= Dir \ [filename], where the DIR parameter specifies the storage path of the binary file and the filename parameter specifies the file name of the binary file, format: filename. number, in the form of 000001, 000002, and so on. Every time you restart the mysql service, a new binary log file is generated, and the "number" of these log files is continuously increasing. The original log file exists, and a file named filename. index is generated. This file stores a list Of all binary log files. Note: Do not place binary logs on the same hard disk as the data files in the database. If the hard disk where the data files are stored is damaged, you can use binary logs on another hard disk to restore the database files. For example, in my. add the following statement [mysqld] log-bin in the [mysqld] Group of the INI file note: If the dir and filename parameters are not specified, the default file name is hostname-bin.number, hostname indicates the host name. After restarting the mysql server, you can see the hsf--bin.-000001 file in the data directory, colleagues also generated a hjh-bin.index file. Here, the mysql server host name is hsf. A. view the binary log format: mysqlbinlog filename. number Note: This command searches for the specified binary log in the current folder. Therefore, you must run this command in the directory where the binary log filename. number is located. For example, C:/log> mysqlbinlog mylog.000001, for example, mysqlbinlog C: \ log \ mylog.000001b. Delete all binary logs and delete all binary logs. Format: reset master; c. Delete binary logs by number. This command can delete logs before the specified binary log number. Format: purge master logs to 'filename. number'; for example, purge master logs to 'mylog. 123'. After execution, binary LOGS numbered 01, 02, and 03 will be deleted. D. Delete binary logs based on the Creation Time, and delete binary logs created before the specified time. For example, purge master logs to 'yyyy-mm-dd hh: mm: ss'; e. Use binary logs to restore the database (use the latest backup file TO restore the database, and then use binary logs to restore the database) format, such as mysqlbinlog filename. number | mysql-u root-p Note: Restore first if the number is small, for example, mysqlbinlog mylog.000001 | mysql-u root-pmysqlbinlog mylog.000002 | mysql-u root-pf, temporarily stop the binary log function, such as: set SQL _LOG_BIN = 0; restart the format: set SQL _LOG_BIN = 1; 2. The default error log function of error logs is enabled. The error log cannot be disabled. Stored in the data folder of the mysql database. It is usually named hostname. err. Hostname indicates the mysql server host name. You can set the storage location using the log-error option. Add the log-error option to the [mysqld] group of my. ini or my. inf file. For example: # my. cnf (in Linux) or my. ini (in windows) [mysqld] log-error = Dir/[filename] Note: the filename parameter is the name of the error log. If this parameter is not set, the default host name is used. Because it is a text file, you can view it directly. A. to delete an error log, run the mysqladmin command to enable the new error log. For example, mysqladmin-uroot-p flush-logs automatically creates a new error log after executing this command. The old error log is retained, but it has been renamed filename. err-old. Or use flush logs; after logging on to the database to enable new error logs. 3. The general query log is disabled by default. You can enable the general query log through the log option of my. cnf or my. ini file. Format: # my. cnf (in Linux) or my. ini (in windows) [mysqld] log [= Dir \ [filename] If no file name is specified, the default file name is hostname. log. Hostname is the host name of the mysql server. Because it is a text file, you can view it directly. The new general query log directly overwrites the old query log and does not need to be deleted manually. For example: mysqladmin-uroot-p flush-logs4, slow query log is disabled by default, through the my. cnf or my. ini file log-slow-queries option can enable slow query log. Use the long_query_time option to set the time value, in seconds. If this value is exceeded, the log will be recorded in the form of: # my. cnf (in Linux) or my. ini (windows OS) [mysqld] log-slow-queries [= Dir \ [filename] long_query_time = n Note: The generated log file name is a filename-slow.log. If you do not specify a file name, the default file name is hostname-slow.log, the default n is 10 seconds. The benchmark (count, expr) function can be used to test and execute the count expr operation. A. Run the "mysqladmin-u root-p flush-logs" command to delete slow query logs. The new slow query logs directly overwrite the old ones and do not need to be deleted manually. You can also manually delete the log and restart the service to generate a new slow query log. If you want to back up the old slow query log, you can rename the old log file and restart the service.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.