MySQL Server Log Files on CentOS

MySQL Tutorials - Herong's Tutorial Examples

∟MySQL Server Log Files on CentOS

This section provides a tutorial example on how to review log file settings and locations for MySQL 8.0 server on CentOS 8 systems.

Log files are very useful to monitor MySQL Server performance and troubleshoot any issues. You should learn how to manage MySQL log files as shown in this tutorial.

1. Remember that MySQL 8 supports 7 types of log files:

Error Log - Log errors. Controlled by %log_error% variables.
Slow Query Log - Logs queries that took longer times. Controlled by %slow_query_log% variables.
Binary Log - Log statements that change data (also used for replication). Controlled by %binlog% and %log_bin% variables.
General Query Log - Log all queries. Controlled by %general_log% variables.
DDL Log - Log DDL (Data Definition Language) operations. Controlled by %metadata% variables.
Relay Log - Log data changes received from a replication master server. Controlled by %relay_log% variables.
InnoDB Log - Log Controlled by %innodb_log% variables.

2. Review "Error Log" file settings and locations. Remember that "Error Log" is always turned on.

herong$ mysqladmin -u root -p variables grep log_error
| binlog_error_action          ABORT_SERVER
| log_error                    /var/log/mysql/mysqld.log
| log_error_services           log_filter_internal; log_sink_internal
| log_error_suppression_list   |
| log_error_verbosity          2

herong$ sudo ls -l /var/log/mysql/mysqld.log
-rw-r-----. 1 mysql mysql 8735 Mar 28 08:20 /var/log/mysql/mysqld.log

herong$ sudo head -100 /var/log/mysql/mysqld.log
[Server] /usr/libexec/mysqld (mysqld 8.0.17) initializing of server
   in progress as process 18690
[Server] root@localhost is created with an empty password ! Please
   consider switching off the --initialize-insecure option.
[Server] /usr/libexec/mysqld (mysqld 8.0.17) initializing of server
   has completed
[Server] /usr/libexec/mysqld (mysqld 8.0.17) starting as process 18739
[Server] CA certificate ca.pem is self signed.
[Server] /usr/libexec/mysqld: ready for connections. Version: '8.0.17'
   socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution.
[Server] X Plugin ready for connections.
   Socket: '/var/lib/mysql/mysqlx.sock' bind-address: '::' port: 33060
...
[Server] /usr/libexec/mysqld: Forcing close of thread 32  user: 'root'.
[Server] /usr/libexec/mysqld: Forcing close of thread 9  user: 'root'.
[Server] /usr/libexec/mysqld: Shutdown complete (mysqld 8.0.17)
   Source distribution.

3. Turn on "General Query Log", if you want to debug some MySQL applications.

herong$ mysql -u root -p

mysql> show variables like 'general_log%';
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | OFF                          |
| general_log_file | /var/lib/mysql/localhost.log |
+------------------+------------------------------+

mysql> set global general_log = on;

(Run your MySQL application)

herong$ sudo head -100 /var/lib/mysql/localhost.log
/usr/libexec/mysqld, Version: 8.0.17 (Source distribution). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
...
... 34 Connect  herong@localhost on mydb using Socket
... 34 Query  SELECT COUNT(*) FROM Cases
... 34 Query  SELECT * FROM Cases  ORDER BY ID DESC LIMIT 0, 30
... 34 Quit

... 35 Connect  herong@localhost on mydb using Socket
... 35 Query  SELECT * FROM Cases WHERE ID = 10001
... 35 Quit

4. Review "Binary Log" file settings and locations. Remember that "Binary Log" is turned on by default. "Binary Log" files are stored in binary format. So you need to use "mysqlbinlog" command to read it.

herong$ mysqladmin -u root -p variables grep log_bin
| log_bin                           ON
| log_bin_basename                  /var/lib/mysql/binlog
| log_bin_index                     /var/lib/mysql/binlog.index
| log_bin_trust_function_creators   OFF
| log_bin_use_v1_row_events         OFF

herong$ sudo ls -l /var/lib/mysql/binlog*
mysql mysql 18315580 Mar 27 05:49 /var/lib/mysql/binlog.000001
mysql mysql      178 Mar 27 06:13 /var/lib/mysql/binlog.000002
mysql mysql     9995 Mar 28 08:14 /var/lib/mysql/binlog.000003
mysql mysql     2785 Mar 28 08:33 /var/lib/mysql/binlog.000004
mysql mysql       64 Mar 28 08:15 /var/lib/mysql/binlog.index

herong$ sudo tail /var/lib/mysql/binlog.000004
**4
?D???^#???Z???A^"O??E???8U???A^??  : ?Estd???SYSTEM
....

herong$ sudo mysqlbinlog /var/lib/mysql/binlog.000004
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
...
# at 2785
#200329  3:50:28 server id 1  end_log_pos 2864 CRC32 0x394cd7a3 ...
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1585468228619899 (2020-03-29 ...)
# immediate_commit_timestamp=1585468228619899 (2020-03-29 ...)
/*!80001 SET @@session.original_commit_timestamp=1585468228619899*//*!*/;
/*!80014 SET @@session.original_server_version=80017*//*!*/;
/*!80014 SET @@session.immediate_server_version=80017*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 2864
#200329  3:50:28 server id 1  end_log_pos 2949 CRC32 0x47303c51 ...
SET TIMESTAMP=1585468228/*!*/;
BEGIN
/*!*/;

# at 2949
#200329  3:50:28 server id 1  end_log_pos 3022 CRC32 0x5454ba1a
   Table_map: `mydb`.`Cases` mapped to number 117

# at 3022
#200329  3:50:28 server id 1  end_log_pos 3819 CRC32 0xd293a8cf
   Update_rows: table id 117 flags: STMT_END_F

BINLOG '
RFOAXhMBAAAA....................................................
...GrpUVA==
RFOAXh8BAAAA....................................................
................................................................
...Zs+ok9I=
'/*!*/;

# at 3819
#200329  3:50:28 server id 1  end_log_pos 3850 CRC32 0xe1c421ea
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*/;