My MySQL learning experience (15) Log

Source: Internet
Author: User
Tags benchmark what sql mysql slow query log



This article "My MySQL Learning experience (15)" will explain the MySQL log



The logs in MySQL are divided into 4 categories, and using these log files, you can see what's happening inside MySQL.



respectively is



1, error log: Log the MySQL service start, run, stop the MySQL service problems occurred error log



2. Query log: Record the established client connection and execute the statement gerenal log



3. Binary log: A statement that records all change data and can be used for data replication binary log



4. Slow query log: Records all queries with execution time exceeding long_query_time or queries that do not use indexes slow log






By default, all logs are created in the MySQL data directory. By refreshing the log, you can force MySQL to close and reopen the log file (or, in some cases, switch to



A new log). When you execute a FLUSH logs statement or perform mysqladmin flush-logs or mysqladmin refresh, the log is refreshed






If you use the MySQL replication feature, more log files can be maintained on the replication server, which is called the replacement log






Other logging capabilities can degrade the performance of MySQL databases. For example, in a MySQL database system with very frequent queries, if the common query log and the slow query log are turned on,



The MySQL database will take a lot of time to log. At the same time, the log consumes a lot of disk space






Binary log



Binary log is what we often say Binlog, the main record of MySQL database changes.



Binary logs are in a valid format and are transaction-safe in a way that contains all the information that is available in the update log.






The binary log contains information about the execution time of each statement that updates the database. He does not contain statements that do not modify any data, such as a SELECT statement



The maximum purpose of using the binary log is to recover the database most likely because the binary log contains all the updates made after the backup






1. Start and set up binary log



By default, the binary log is turned off, and you can start and set up the binary log by modifying the MySQL configuration file



There are several settings in the My.ini [mysqld] group that are related to the binary log:


log-bin[=PATH/[FILENAME]]
expire_logs_days=10
max_binlog_size=100M


Log-bin defines the binary log, path indicates the directory path where the log file resides;



filename Specifies the name of the log file, such as the full name of the file is filename.0001,filename.0002, etc.



In addition to the above file, there is a file that becomes Filename.index, the file content is a list of all logs, you can use Notepad to open the file



Filename.index the contents of the file, Joe is my computer name and there is currently only one Binlog file:. \joe-bin.000001


. \joe-bin.000001





Expire_logs_days defines the time that MySQL clears an expired log, that is, the number of days that the binary log is automatically deleted.



The default value is 0, which means "no automatic deletion". The file may be deleted when MySQL starts or refreshes the binary log






Max_binlog_size defines the size limit for a single file, and if the content size written by the binary log exceeds the given value, the log will scroll



(Close the current file and reopen a new log file.) You cannot set this variable to be greater than 1GB or less than 4096 bytes. The default value is 1GB






If you are using large transactions, the binary log file size may also exceed the defined size of max_binlog_size.



In the My.ini configuration file, under the [Mysqld] group, add the following parameters and parameter values


[mysqld]
log-bin
expire_logs_days=10
max_binlog_size=100M


Once added, close and restart the MySQL service process to open the binary log and then use the show variables statement to query the log settings






To view log settings using the show VARIABLES statement


Show VARIABLES like  '%log_% ';








You can see that the Log_bin is on,max_binlog_size to 104857600 bytes, and the conversion to MB is 100MB.



After MySQL restarts, you can see that the newly generated file suffix is. 000001 and. Index two files, the file name defaults to the host name






If you want to change the directory location of the log file, you can modify the Log-bin parameter in My.ini



For example:


[mysqld]log-bin= "D:\mysql\log\binlog"


After you close and restart the MySQL service, the new binary log will appear under the "D:\mysql\log\binlog" path






Tip: The database file should not be placed on the same disk as the log file, so that when the database file is damaged, you can use the log to recover the data






2. View the binary log



MySQL binary logs are often used. When MySQL creates a binary log file, it first creates a file with the name filename and the index suffix;



Then create a file with the filename name, with ". 000001" as the suffix. When the MySQL service restarts, a file with the suffix ". 000001" is incremented by one,



The suffix is incremented by 1, and a new log file is created if the log length exceeds the upper limit of max_binlog_size (1GB by default)






The show binary logs statement can view the current number of binary log files and file names. MySQL binary logs cannot be viewed directly, if you want to view the log contents,



Can be viewed through the Mysqlbinlog command






To view the number of binary log files and file names using the show binary logs statement


SHOW BINARY LOGS;





As you can see, there are currently two binary log files, because I restarted the MySQL service and the number of log files was the same as the number of MySQL services started.



Each time the MySQL service is started, a new log file will be generated






Viewing binary logs using Mysqlbinlog



Mysqlbinlog is a separate exe that needs to be executed on the command line we export the contents of the Binlog file to Binlog.txt


Mysqlbinlog  "D:\Program Files (x86) \mysql\mysql server5.5\data\joe-bin.000002" >c:\binlog.txt
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @[email protected]@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#140731  7:49:30 server id 1  end_log_pos 107     Start: binlog v 4, server v 5.5.20-log created 140731  7:49:30 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG ‘
ioTZUw8BAAAAZwAAAGsAAAABAAQANS41LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACKhNlTEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
‘/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET [email protected]_COMPLETION_TYPE*/;





3. Delete binary log



MySQL binary logs can be configured for automatic deletion, while MySQL also provides a secure way to manually delete binary logs



Delete all binary log files using reset MASTER;


RESET MASTER;


Execute the statement, all binary logs will be deleted, MySQL will re-create the binary log, the new log file extension should be numbered again starting with 000001






Only delete part of the binary log files using purge MASTER LOGS;


PURGE MASTER LOGS;


The syntax is as follows


PURGE {MASTER | BINARY} LOGS to ' Log_name ' PURGE {MASTER | BINARY} LOGS before ' date '


The first method specifies the file name, and executing the command deletes all log files with a file name number that is smaller than the specified file name



The second method specifies a date, and executing the command deletes all log files before the specified date









Use purge MASTER LOGS; Delete all log files that were created earlier than binlog.000003



First of all, in order to demonstrate the statement operation process, to prepare multiple log files, the reader can perform multiple restarts of the MySQL service



For example, there are 10 log files






Execute Delete command


PURGE MASTER LOGS to "joe-bin.000003";


When execution is complete, use show binary logs; view binary log






You can see that joe-bin.000001 and joe-bin.000002 two log files were deleted.






Use PURGE MASTER LOGS to delete all log files created before March 30, 2013, execute the following command


PURGE MASTER LOGS before ' 20130330 '


After execution, the log files before March 30, 2013 are deleted, but the March 30, 2013 log is retained






4. Check the operation record in binary log


Show Binlog events;


For example, if you want to see the records in a binary log, but do not want to use Mysqlbinlog, you can use show Binlog events



For example, I want to see the contents of the ' joe-bin.000006 ' Binlog file, execute the following command


Show Binlog events in ' joe-bin.000006 ';


The contents are as follows



Log_name: joe-bin.000006
Pos: 202 
Event_type: Query 
Server_id: 1 
End_log_pos: 304 
Info: use `test`; insert into bin(name) values (‘orange‘) 


You can see what SQL commands the ' joe-bin.000006 ' this binlog file records






If you want to know when the Binlog file was created, you need to mysqlbinlog the tool to see


C:\ProgramData\MySQL\MySQL Server 5.5\data>mysqlbinlog mysql_bin.000001 
/*!40019 SET @@session.max_insert_delayed_threads=0*/; 
/*!50003 SET @[email protected]@COMPLETION_TYPE,COMPLETION_TYPE=0*/; 
DELIMITER /*!*/; 
# at 4 
#131015 16:35:56 server id 1  end_log_pos 106   


Where 131015 is the log creation time, which is October 15, 2013






5. Restore the database using the binary log



If the MySQL server has binary logging enabled, you can use the Mysqlbinlog tool to start at a specified point in time when data is unexpectedly lost in the database



(for example, the last backup) until now, or another specified point in time, to recover data from the log






To recover data from a binary log, you need to know the path and file name of the current binary log file. Typically from a configuration file (i.e. my.cnf or My.ini, the file name depends on the MySQL



The server's operating system) find the path






Mysqlbinlog the syntax for recovering data is as follows:


mysqlbinlog [option] filename |mysql-uuser-ppass


option is optional and filename is the log file name



The more important two-pair option parameter is



--start-datetime 、--Stop-datetime



--start-position 、--stop--position



--start-date 、--Stop-date can specify the starting point and end point of the recovery database



--start-position 、--Stop--position can specify the starting and ending positions of the recovery data






Use Mysqlbinlog to restore the MySQL database to the July 2, 2014 15:27:48 state, execute the following command


Mysqlbinlog--stop-datetime= "2014-7-2 15:27:48" D:\mysql\log\binlog\binlog.000008 |mysql-u user-p Password


After the command executes successfully, all operations before 15:27:48 July 2, 2014 are restored according to the binlog.000008 log file.



This method is more effective for deleting data that is mistakenly manipulated.






6. Temporarily stop the binary log



If the configuration file configuration in MySQL starts the binary log, MySQL will always record the binary log, modify the configuration file, can stop the binary log,



However, the MySQL database needs to be restarted. MySQL provides the ability to temporarily stop the binary log. The SET sql_log_bin statement allows MySQL to pause or start the binary log



The syntax is as follows


SET Sql_log_bin={0|1}


Execute the following statement to pause the binary log


SET sql_log_bin=0;


Execute the following statement to restore the record binary log


SET sql_log_bin=1;


In fact, the Binlog file is a bit like the LDF file of SQL Server, everyone has saved the database operation log, can be based on this log to recover the database



But there are different, MySQL Binlog can not be opened, because the MySQL redo log is placed in the Ib_logfile file, and the undo log with the data files are put together



So this is very different from SQL Server.






When copying, MySQL must turn on the Binlog function, slave read binlog, and SQL Server's subscribers read the LDF file on the publisher



So just said: The Binlog file is somewhat similar to the LDF file of SQL Server



Error log



The error log file contains information about when Mysqld starts and stops, and when the server is running with any serious errors.



In MySQL, the error log is also very important, MySQL will start and stop the database information and some error messages logged in the error log






1. Starting and setting error logs



By default, the error log is logged to the database's data directory. If you do not specify a file name in the configuration file, the file name defaults to Hostname.err.



For example: MySQL is hosting a server host named Mysql-db, and the file name for logging error messages is Mysql-db.err. If flush LOGS is executed, the error log file is reloaded






The start and stop of the error log, as well as the log file name, can be configured by modifying My.ini (or MY.CNF). The configuration entry for the error log is log-error.



Under [mysqld], configure Log-error to start the error log. If you need to specify a file name, the configuration is as follows:


[Mysqld]log-error=[path/[file_name]]


Path is the directory path where the log files are located, and filename is the log file name. After modifying the configuration item, the MySQL service needs to be restarted before it takes effect






2. Check the error log



The error log can monitor the running state of the system, so as to detect faults and repair faults in time. The MySQL error log is stored as a text file, and you can use a text editor to directly



View MySQL error log






If you do not know the log file's storage path, you can use show variables to view the storage path for the error log.



The statements are as follows


Show variables like ' log_error ';








Use Notepad to view MySQL error logs



View the path to the error log through the statement above show variables like ' Log_error ', and then open the file with Notepad



We can see the error log contents as follows


View Code





3. Delete error log



The MySQL error log is stored as a text file in the file system and can be deleted directly



For previous versions of mysql5.5.7, flush logs can rename the error log file to Filename.err_old,



and create a new log file. But starting with mysql5.5.7, flush logs just re-opens the log file, does not do log backups and create operations.



If the log file does not exist, a new log file is created when MySQL starts or executes flush logs






After you delete the error log file in the running state, MySQL does not automatically create the log file. Flush logs When the log is reloaded, if the file does not exist,



is created automatically. So after deleting the error log, if you need to rebuild the log file, you need to execute the following command on the server side:


Mysqladmin-u root-p Flush-logs


or log in to the MySQL database at the client, execute the FLUSH logs statement


Flush logs;





Delete the Err file and rebuild the Log-error file with the Flush logs statement



Delete files manually






Manually execute flush logs;, err file restored






Open the Err file, there's nothing inside






General Query Log






The general query log records all user actions for MySQL, including starting and shutting down services, executing queries and updating statements, etc.






1. Start and set up the general query log



The MySQL server does not turn on the universal query log by default. If you need a generic query log, you can modify the My.ini or my.cnf configuration file to



Open. Add the Log option under the [mysqld] group of My.ini or MY.CNF



form as follows


[Mysqld]log[=path/[filename]]


Path is the directory path where the log files are located, and filename is the log file name. If you do not specify a directory and file name, the Universal query log is stored by default in the MySQL data directory.



The Hostname.log file. Hostname is the host name of the MySQL database



This adds the option log below [mysqld] and does not specify a parameter value later


[Mysqld]log





2. View the General query log



All operations used are logged in the Universal query log. You can see what the user is doing with MySQL by looking at the general query log. The General query log is



stored in a file system as a text file, you can use a text editor to open a common log file directly for viewing, and you can use Notepad under Windows



Vim, gedit, etc. can be used under Linux



Use Notepad to view the MySQL Universal query log






The file contents are as follows


E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld, Version: 5.5.19-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time                 Id Command    Argument
140801 23:39:33        1 Connect    [email protected] on 
            1 Query    SHOW VARIABLES
            1 Query    SHOW WARNINGS
            1 Query    select timediff( curtime(), utc_time() )
            1 Query    SHOW COLLATION
            1 Query    SET NAMES utf8
            1 Query    SET character_set_results=NULL
            1 Query    SELECT * FROM `emp`
140801 23:39:44        1 Query    SELECT * FROM `emp`
            1 Query    SELECT * FROM `emp`
140801 23:39:55        1 Query    USE test;

SELECT * FROM `emp`
            1 Init DB    test


You can see the MySQL boot information and the user root connection server and the record that executes the query statement






3. Delete the general query log



The universal query log is stored as a text file in the file system. General query logging all actions of the user



Therefore, in the case of user query, update frequently, the general query log will grow fast. DBAs can periodically delete older, common logs to conserve disk space






You can delete a common query log by deleting the log file directly. To reestablish a new log file, you can use the statement


Mysqladmin-flush logs


Delete log file directly






Execute Flush Logs









Log file was regenerated






Slow query log






A slow query log is a day that records queries longer than a specified time. The slow query log is primarily used to record query statements that have a long execution time



By slowly querying the log, you can find statements that take longer to execute, perform less efficiently, and then optimize






1. Start and set slow query log



In MySQL, the slow query log is turned off by default and can be opened via the log-slow-queries option in the config file My.ini or my.cnf, or in the MySQL service



Start with--log--slow-queries[=file_name] to start the slow query log. When you start a slow query log, you need to My.ini or my.cnf files



Configure the Long_query_time option to specify the logging threshold, if the query time of a query statement exceeds this value, the query process will be logged to the slow query log



File.






To turn on the slow query log in the My.ini or my.cnf file, configure the following:


[mysqld]

log-slow-queries[=path/[filename]]
long_query_time=n


Path is the directory path where the log files are located, and filename is the log file name. If you do not specify a directory and file name, it is stored in the data directory by default



The file name Hostname-slow.log,hostname is the host name of the MySQL server. The parameter n is the time value, in seconds.



If the Long-query_time option is not set, the default time is 10 seconds






Turn on slow query log


[mysqld]
log-slow-queries
long_query_time=1





2. View slow query log



MySQL's slow query log is stored as text and can be viewed directly using a text editor. In the slow query log, a query statement that takes a long time to execute is recorded.



Users can get less efficient query statements from the slow query log, which provides an important basis for query optimization.






View some parameters of the slow query log


Show variables like '%slow% ';








To view the contents of the slow query log file, open the Win7u-20130414z-slow.log file in the data directory using a text editor





E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld, Version: 5.5.19-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time                 Id Command    Argument
# Time: 140802  0:02:29
# [email protected]: root[root] @ localhost [::1]
# Query_time: 7.578125  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use test;
SET timestamp=1406908949;
SELECT BENCHMARK (10000000,PASSWORD (‘newpwd‘));


You can see a slow query log recorded here. The account executing the statement is root @ localhost



Query time is query_time:7.578125 seconds



The query statement is SELECT BENCHMARK (10000000,password (' newpwd '));



The statement query time significantly exceeds the set value of 1 seconds, so it is logged in the slow query log file



Introduction to the benchmark function: http://database.51cto.com/art/201010/229366.htm






3. Delete Slow query log



As with the general query log, the slow query log can also be deleted directly. After deletion, without restarting the server, you need to perform a


Mysqladmin-u root-p Flush Logs


Rebuild the log file, or log on to the server to execute flush logs;






The official MySQL slow query log here has a flaw, that is, the query threshold can only be 1 seconds or more, if you want to set less than one second can do nothing



At this point, if you want to find the slow query SQL less than 1 seconds, you can use the Microslow-patch provided by Percona to break the limit and reduce the slow query time threshold to the millisecond level



Which logs should be opened normally



Logs can affect both MySQL performance and disk space. Therefore, if it is not necessary, you should open the log as little as possible.



Consider opening different logs depending on the environment you are using.



For example, a statement that optimizes query efficiency in the development environment can turn on slow query logging , or some SQL execution in the production environment is particularly slow or can be turned on



If the disk space is not sufficient, it can be turned on during peak hours, and then close the slow query log after capturing a slow SQL query






If you need to set up a replication environment, then you must open the binary log , if the data is particularly important also recommended to open the binary log, so that the database can be corrupted by the binary log



Salvage part of the data






General logs in either case, it is generally not recommended to open



Summarize



This article simply elaborated the MySQL log surface content, the MySQL log system is still quite perfect, hoped this article has the help to everybody






If there is a wrong place, welcome everyone to shoot brick O (∩_∩) o



2014-11-27 Supplemental Write transaction log process





My MySQL learning experience (15) Log


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.