MySQL logs
MySQL logs record routine operations and error information about MySQL. These logs help you know what happens in MySQL and provide necessary information for MySQL optimization and management.
The MySQL logs include binary logs, general query logs, slow query logs, error logs, and transaction logs.
Binary log
Binary logs are used to record write operations (including add, delete, or modify, but not query) in the MySQL database. Operation statements are saved in the form of events to describe data changes.
Binary has two main functions:
- Replication: When master/slave replication is configured, the master server sends binary logs to the slave server. The slave server uses the binary log information to perform local replication for master/slave synchronization.
- Restore, because binary logs contain all updates since the backup, they can be used to maximize database recovery. Therefore, you are advised to save binary logs to a separate disk for data recovery if the disk is damaged.
Check whether binary logs are enabled
Binaries can be shown as show variables like ‘log_bin’; Check whether it has been opened.
The following return representation is closed:
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
Copy the codeConfigure binary MySQL logs in Windows
- The MySQL Server is installed on Windows10. The MySQL Server is a 64-bit service named MySQL57.
MySQL > install MySQL
C:\>mysql -V
mysql Ver 14.14 Distrib 5.7.17, for Win64 (x86_64)
Copy the code- Open the
C: \ ProgramData/MySQL/MySQL Server 5.7Ini file in the my.ini directory[mysqld]Add the following configuration to the configuration group:
MySQL binary log configuration
# bin log Output directory and file
log-bin="C:/1workspace/develop/db/mysql/mysql57/logs/binlog"
Bin log Clearing time
expire_logs_days=7
Size of each bin log file
max_binlog_size=200m
#binlog Cache size
binlog_cache_size=10m
# Maximum binlog cache size
max_binlog_cache_size=20m
Copy the code- Open CMD in administrator mode and restart MySQL service:
C:\>net stop MySQL57 MySQL57 service is stopping. The MySQL57 service has been stopped successfully. C:\>net start MySQL57 MySQL57 service is being started.Copy the code- Connect to MySQL server:
C:\>mysql -uroot -pmysqltao
Copy the code- MySQL bin log enabled
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
Copy the codeAt this point you can open your configured binlog path and see the generated binary log file: binlog.000001,
And a binary log index file: binlog.index.
Check the bin log
Every time you restart MySQL, a new binlog file is generated. Run the show binary logs command to view all binlog files.
There are now three binglog files:
mysql> show binary logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000001 | 177 |
| binlog.000002 | 177 |
| binlog.000003 | 154 |
+---------------+-----------+
Copy the codeExecute an UPDATE statement:
update demo_java_db_1.t_user set name='Jack' where id=1;
Copy the codeMysqlbinlog = mysqlbinlog;
C:\1workspace\develop\db\mysql\mysql57\logs>mysqlbinlog binlog.000003 /*! 50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; / *! 50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*! * /. # at 4 #190704 17:19:28 server id 1 end_log_pos 123 CRC32 0xaf193fc6 Start: Binlog V 4, Server V 5.7.17-log created 190704 17:19:28 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*! * /. BINLOG ' oMQdXQ8BAAAAdwAAAHsAAAABAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAACgxB1dEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AcY/Ga8= '/*! * /. # at 123 #190704 17:19:29 server id 1 end_log_pos 154 CRC32 0xc916f3aa Previous-GTIDs # [empty] # at 154 #190704 17:39:43 server id 1 end_log_pos 219 CRC32 0xcecca4bf Anonymous_GTID last_committed=0 sequence_number=1 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*! * /. # at 219 #190704 17:39:43 server id 1 end_log_pos 287 CRC32 0xb6a3de97 Query thread_id=7 exec_time=0 error_code=0 SET TIMESTAMP=1562233183/*! * /. SET @@session.pseudo_thread_id=7/*! * /. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*! * /. SET @@session.sql_mode=1344274432/*! * /. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*! * /. / *! \C utf8 *//*! * /. SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*! * /. SET @@session.lc_time_names=0/*! * /. SET @@session.collation_database=DEFAULT/*! * /. BEGIN /*! * /. # at 287 #190704 17:39:43 server id 1 end_log_pos 358 CRC32 0xd1ce52d6 Table_map: `demo_java_db_1`.`t_user` mapped to number 115 # at 358 #190704 17:39:43 server id 1 end_log_pos 445 CRC32 0x7a57aa7a Update_rows: table id 115 flags: STMT_END_F BINLOG ' X8kdXRMBAAAARwAAAGYBAAAAAHMAAAAAAAEADmRlbW9famF2YV9kYl8xAAZ0X3VzZXIABQgPDw8P CGAAYABgAGAAHtZSztE= X8kdXR8BAAAAVwAAAL0BAAAAAHMAAAAAAAEAAgAF///wAQAAAAAAAAAFZmlyc3QGcHdkMTIzA01B TvABAAAAAAAAAARKYWNrBnB3ZDEyMwNNQU56qld6 '/ *! * /. # at 445 #190704 17:39:43 server id 1 end_log_pos 476 CRC32 0x6e49c227 Xid = 42 COMMIT/*! * /. SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*! * /. DELIMITER ; # End of log file /*! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; / *! 50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;Copy the codeBinlog shows that the t_user table of the demo_JAVA_DB_1 library was changed at 17:39:43.
Restore data using binlog
To view the events recorded in binlog, run the show binlog events command.
mysql> show binlog events in 'binlog.000003'\G
*************************** 1. row ***************************
Log_name: binlog.000003
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 123
Info: Server ver: 5.7.17-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: binlog.000003
Pos: 123
Event_type: Previous_gtids
Server_id: 1
End_log_pos: 154
Info:
*************************** 3. row ***************************
Log_name: binlog.000003
Pos: 154
Event_type: Anonymous_Gtid
Server_id: 1
End_log_pos: 219
Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
Log_name: binlog.000003
Pos: 219
Event_type: Query
Server_id: 1
End_log_pos: 287
Info: BEGIN
*************************** 5. row ***************************
Log_name: binlog.000003
Pos: 287
Event_type: Table_map
Server_id: 1
End_log_pos: 358
Info: table_id: 115 (demo_java_db_1.t_user)
*************************** 6. row ***************************
Log_name: binlog.000003
Pos: 358
Event_type: Update_rows
Server_id: 1
End_log_pos: 445
Info: table_id: 115 flags: STMT_END_F
*************************** 7. row ***************************
Log_name: binlog.000003
Pos: 445
Event_type: Xid
Server_id: 1
End_log_pos: 476
Info: COMMIT /* xid=42 */
*************************** 8. row ***************************
Log_name: binlog.000003
Pos: 476
Event_type: Anonymous_Gtid
Server_id: 1
End_log_pos: 541
Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 9. row ***************************
Log_name: binlog.000003
Pos: 541
Event_type: Query
Server_id: 1
End_log_pos: 609
Info: BEGIN
*************************** 10. row ***************************
Log_name: binlog.000003
Pos: 609
Event_type: Table_map
Server_id: 1
End_log_pos: 680
Info: table_id: 115 (demo_java_db_1.t_user)
*************************** 11. row ***************************
Log_name: binlog.000003
Pos: 680
Event_type: Update_rows
Server_id: 1
End_log_pos: 765
Info: table_id: 115 flags: STMT_END_F
*************************** 12. row ***************************
Log_name: binlog.000003
Pos: 765
Event_type: Xid
Server_id: 1
End_log_pos: 796
Info: COMMIT /* xid=69 */
*************************** 13. row ***************************
Log_name: binlog.000003
Pos: 796
Event_type: Anonymous_Gtid
Server_id: 1
End_log_pos: 861
Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 14. row ***************************
Log_name: binlog.000003
Pos: 861
Event_type: Query
Server_id: 1
End_log_pos: 929
Info: BEGIN
*************************** 15. row ***************************
Log_name: binlog.000003
Pos: 929
Event_type: Table_map
Server_id: 1
End_log_pos: 1000
Info: table_id: 115 (demo_java_db_1.t_user)
*************************** 16. row ***************************
Log_name: binlog.000003
Pos: 1000
Event_type: Update_rows
Server_id: 1
End_log_pos: 1085
Info: table_id: 115 flags: STMT_END_F
*************************** 17. row ***************************
Log_name: binlog.000003
Pos: 1085
Event_type: Xid
Server_id: 1
End_log_pos: 1116
Info: COMMIT /* xid=101 */
*************************** 18. row ***************************
Log_name: binlog.000003
Pos: 1116
Event_type: Anonymous_Gtid
Server_id: 1
End_log_pos: 1181
Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 19. row ***************************
Log_name: binlog.000003
Pos: 1181
Event_type: Query
Server_id: 1
End_log_pos: 1249
Info: BEGIN
*************************** 20. row ***************************
Log_name: binlog.000003
Pos: 1249
Event_type: Table_map
Server_id: 1
End_log_pos: 1320
Info: table_id: 115 (demo_java_db_1.t_user)
*************************** 21. row ***************************
Log_name: binlog.000003
Pos: 1320
Event_type: Update_rows
Server_id: 1
End_log_pos: 1405
Info: table_id: 115 flags: STMT_END_F
*************************** 22. row ***************************
Log_name: binlog.000003
Pos: 1405
Event_type: Xid
Server_id: 1
End_log_pos: 1436
Info: COMMIT /* xid=107 */
22 rows in set (0.00 sec)
Copy the code- Log_name: this log is stored in the same file as mysql_bin.000001.
- Pos: indicates the start position of the log in the bin-log
- Event_type:log type information
- Server_id: You can view the Server_id in the configuration, indicating which server logs are generated. Server_id can be set in my.ini
- End_log_pos: indicates the end position of the log in bin-log
- Info: some remarks in the log. You can intuitively see what operations are performed
Data can be restored using the mysqlbinlog command, which supports adding parameters to restore specified events:
mysqlbinlog --start-date="The 2019-07-04 18:00:00" --stop-date="The 2019-07-04 18:10:00" binlog.000003 |mysql -uroot -pmysqltao
Copy the codeThe error log
MySQL’s error log records any serious errors that occur when mysqld is started and stopped, as well as when the server is running.
As with binlog, configure in the my.ini file:
# error log Output directory and file
log-error="C:/1workspace/develop/db/mysql/mysql57/logs/errorlog"
Copy the codeGeneral query log
The general query log can be saved in a text file or table. All connections and statements are recorded in this log file or table. The log function is disabled by default.
Ini file [mysqld] group to configure the generic query log:
# general query log output format [none | file | table | file, the table]
log_output=file
# whether to enable the general query log [on | off]
general_log=on
Generic query log location and name
general_log_file="C:/1workspace/develop/db/mysql/mysql57/logs/generallog"
Copy the codeSlow Query logs
Slow query logs record slow SQL statements and can be used to optimize the performance of SQL statements that take a long time and are not executed efficiently.
- Temporarily enable slow query logs:
set global slow_query_log = on;
Copy the codeTo disable slow query logs, run the following command:
set global slow_query_log = off;
Copy the code2. Set the critical point of slow query time temporarily
The query time higher than this threshold is recorded in the slow query log.
All SQL that takes more than 1 second to execute will be logged as a slow query:
set long_query_time = 1;
Copy the code- Set the slow storage query mode
set globle log_output = file;
Copy the codeThe default value is None. We can also set it to table. If it is table, slow query information will be stored in the slow_log table in mysql library.
- Query Indicates the status of the slow query log and the location of the slow query log
show variables like '%quer%';
Copy the codeAdded: transaction logs
Transaction logging is unique to the InnoDB engine and can help improve the efficiency of transactions.
With transaction logging, the storage engine only needs to modify the in-memory copy of a table’s data and then record the changes to the transaction log that persists on disk, rather than persisting the data itself to disk each time.
Transaction logging is appending, so the operation of logging is sequential I/O in a small area of the disk, unlike random I/O, which requires moving the head in multiple places on the disk, so transaction logging is relatively faster.
After the transaction log is persisted, the modified data in memory can be slowly flushed back to disk in the background. Most storage engines today implement this, which is commonly referred to as write-ahead logging, where modifying data requires two writes to disk.
If the data changes have been recorded in the transaction log and persisted, but the data itself has not been written back to disk, the system crashes and the storage engine automatically recovers the modified data when it restarts. The recovery mode available depends on the storage engine.
View transaction log parameters:
mysql> SHOWGLOBAL VARIABLES LIKE '%log%';
+-----------------------------------------+-----------------------------------+
| Variable_name | Value |
+-----------------------------------------+-----------------------------------+
| innodb_flush_log_at_trx_commit | 1 |
|innodb_locks_unsafe_for_binlog |OFF |
| innodb_log_buffer_size | 8388608 |
|innodb_log_file_size |5242880 |
| innodb_log_files_in_group | 2 |
|innodb_log_group_home_dir |./ |
|innodb_mirrored_log_groups |1 |
Copy the codeconclusion
- MySQL logs include:
Error logs, general query logs, slow query logs, transaction logs, binary logs
- There are three ways to output MySQL logs:
File, table, none(not saved)
- MySQL logs are used for:
Exception monitoring, performance optimization, data recovery, and primary/secondary synchronization
