MySQL Server Log Files on CentOS

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:

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*/;

Table of Contents

 About This Book

 Introduction to Linux Systems

 Cockpit - Web Portal for Administrator

 Process Management

 Files and Directories

 Users and Groups

 File Systems

 Installing CentOS

 SELinux - Security-Enhanced Linux

 Network Connection on CentOS

 Software Package Manager on CentOS - DNF and YUM

 Running Apache Web Server (httpd) on Linux Systems

 Running PHP Scripts on Linux Systems

Running MySQL Database Server on Linux Systems

 Install MySQL Database Server on CentOS

 Manage MySQL Server 'mysqld' on CentOS

 Set MySQL Server "root" Password on CentOS

 MySQL Server File Locations on CentOS

 MySQL Server Data Backups on CentOS

MySQL Server Log Files on CentOS

 MySQL Server Socket Connection on CentOS

 MySQL Server TCP/IP Connection on CentOS

 Open Firewall for MySQL Server on CentOS

 "Server sent charset unknown to the client" Error

 Performance of Inserting Integers to MySQL Database

 Performance Comparison of Inserting Integers vs. Strings

 Running Python Scripts on Linux Systems

 vsftpd - Very Secure FTP Daemon

 Postfix - Mail Transport Agent (MTA)

 Dovecot - IMAP and POP3 Server

 Email Client Tools - Mail User Agents (MUA)

 GCC - C/C++ Compiler

 Conda - Environment and Package Manager

 Tools and Utilities

 References

 Full Version in PDF/EPUB