Mysql enable error log - Исправление ошибок и поиск оптимальных решений проблем

If you are a web developer, you need to refer to various log
files, in order to debug your application or improve its
performance. Logs is the best place to start troubleshooting.
Concerning the famous MySQL database server (or MariaDB server), you need
to refer to the following log files:

The Error Log. It contains information about
errors that occur while the server is running (also server start
and stop)
The General Query Log. This is a general record
of what mysqld is doing (connect, disconnect, queries)
The Slow Query Log. Ît consists of “slow” SQL
statements (as indicated by its name).

This article does not refer to The Binary Log. This requires very high standards
server hardware and is useful only in special cases (e.g.
replication, master – slaves setup, certain data recovery
operations). Îtherwise, it is just a “performance killer”.

The official documentation about MySQL logs is available
here.

Enable logs from MySQL configuration

Logging parameters are located under [mysqld]
section.

Edit MySQL configuration file:

This is the default setup for Logging and Replication (in
Debian server). In
other distributions the structure may be different, but you can
always use MySQL server parameters:

# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration
#log_slow_queries       = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name

All log files are NOT enabled by default MySQL setup (except the
error log on Windows). Default Debian setup sends Error log to
syslog. The other log files are not enabled.

Error Log

Error Log goes to syslog due to
/etc/mysql/conf.d/mysqld_safe_syslog.cnf, which contains
the following:

This is the recommended method. If, for some
reason, you do not want Error log to go to syslog, comment the
above lines in /etc/mysql/conf.d/mysqld_safe_syslog.cnf
or completely remove this file. Then, add in
/etc/mysql/my.cnf the following lines:

[mysqld_safe]
log_error=/var/log/mysql/mysql_error.log

[mysqld]
log_error=/var/log/mysql/mysql_error.log

General Query Log

To enable General Query Log, uncomment (or add) the relevant
lines

general_log_file        = /var/log/mysql/mysql.log
general_log             = 1

Slow Query Log

To enable Slow Query Log, uncomment (or add) the relevant
lines

log_slow_queries       = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes

Restart MySQL server after changes

This method requires a server restart.

or, using systemd

systemctl restart mysql.service

Enable logs at runtime

Since MySQL 5.1 you can enable and disable logs at runtime.

To enable logs at runtime, login to mysql client (mysql -u root -p ) and give:

SET GLOBAL general_log = 'ON';
SET GLOBAL slow_query_log = 'ON';

To disable logs at runtime, login to mysql client (mysql -u root -p ) and give:

SET GLOBAL general_log = 'OFF';
SET GLOBAL slow_query_log = 'OFF';

This method works on any platform and does not require a server
restart.

Display log results

Error log

With the above settings, you can display Error log using

REMARK: If you do not specify Error log file, MySQL keeps Error
log at data dir (usually /var/lib/mysql in a file named
{host_name}.err).

General Query log

With the above settings, you can display General log using

tail -f /var/log/mysql/mysql.log

REMARK: If you do not define General log file, MySQL keeps
General log at data dir (usually /var/lib/mysql in a
file named {host_name}.log).

Slow Query log

With the above settings, you can display Slow Query log
using

tail -f /var/log/mysql/mysql-slow.log

REMARK: If you do not specify Slow Query log file, MySQL keeps
Slow Query log at data dir (usually /var/lib/mysql in a
file named {host_name}-slow.log).

Log rotation

Do NOT ever forget to rotate logs. Otherwise, log files may
become huge.

In Debian (and
Debian derivatives as Ubuntu etc) log rotation using
logrotate is already present after initial server setup
(“Debian packages pre-configuration”).

nano /etc/logrotate.d/mysql-server

in other distributions, some changes may be needed

# - I put everything in one block and added sharedscripts, so that mysql gets
#   flush-logs'd only once.
#   Else the binary logs would automatically increase by n times every day.
# - The error log is obsolete, messages go to syslog now.
/var/log/mysql.log /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log {
        daily
        rotate 7
        missingok
        create 640 mysql adm
        compress
        sharedscripts
        postrotate
                test -x /usr/bin/mysqladmin || exit 0
                # If this fails, check debian.conf!
                MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"
                if [ -z "`$MYADMIN ping 2>/dev/null`" ]; then
                  # Really no mysqld or rather a missing debian-sys-maint user?
                  # If this occurs and is not a error please report a bug.
                  #if ps cax | grep -q mysqld; then
                  if killall -q -s0 -umysql mysqld; then
                    exit 1
                  fi
                else
                  $MYADMIN flush-logs
                fi
        endscript
}

Check out your server configuration

TIP: Use show variables like '%log%'; to examine
your server variables related to log files

root@cosmos ~ # mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 144332
Server version: 5.5.31-0+wheezy1 (Debian)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> show variables like '%log%';
+-----------------------------------------+--------------------------------+
| Variable_name                           | Value                          |
+-----------------------------------------+--------------------------------+
| back_log                                | 50                             |
| binlog_cache_size                       | 32768                          |
| binlog_direct_non_transactional_updates | OFF                            |
| binlog_format                           | STATEMENT                      |
| binlog_stmt_cache_size                  | 32768                          |
| expire_logs_days                        | 10                             |
| general_log                             | OFF                            |
| general_log_file                        | /var/lib/mysql/cosmos.log      |
| 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                              |
| log                                     | OFF                            |
| log_bin                                 | OFF                            |
| log_bin_trust_function_creators         | OFF                            |
| log_error                               |                                |
| log_output                              | FILE                           |
| log_queries_not_using_indexes           | OFF                            |
| log_slave_updates                       | OFF                            |
| log_slow_queries                        | OFF                            |
| log_warnings                            | 1                              |
| max_binlog_cache_size                   | 18446744073709547520           |
| max_binlog_size                         | 104857600                      |
| max_binlog_stmt_cache_size              | 18446744073709547520           |
| max_relay_log_size                      | 0                              |
| relay_log                               |                                |
| relay_log_index                         |                                |
| relay_log_info_file                     | relay-log.info                 |
| relay_log_purge                         | ON                             |
| relay_log_recovery                      | OFF                            |
| relay_log_space_limit                   | 0                              |
| slow_query_log                          | OFF                            |
| slow_query_log_file                     | /var/lib/mysql/cosmos-slow.log |
| sql_log_bin                             | ON                             |
| sql_log_off                             | OFF                            |
| sync_binlog                             | 0                              |
| sync_relay_log                          | 0                              |
| sync_relay_log_info                     | 0                              |
+-----------------------------------------+--------------------------------+
41 rows in set (0.00 sec)

Server variables official documentation is available here.

Using default MySQL setup, all log files are NOT enabled (except
the error log on Windows). Default Debian setup sends Error log to
syslog.

Actually, there many situations where log files can provide
solutions to critical problems:

Always enable Error log.
Enable General query log (preferably at runtime) when you want
to
- check that your application handles correctly MySQL database
  connection (a common mistake is to connect multiple times to MySQL
  from a single script)
- monitor executed queries from your application
- test memcached (or similar software), checking out if a
  query was executed or memcached has handled the request
Enable Slow Query log (preferably from MySQL configuration file
for a short period of time, e.g. 2-3 days) when your application
performance is reduced for some reason and you should detect the
slow queries.

Example

Here is an example of MySQL General query log:

131021 17:43:50    43 Connect   root@localhost as anonymous on pnet_blog
                   43 Init DB   pnet_blog
                   43 Query     SELECT count(id) as total_posts FROM posts WHERE date_published is not null AND date_published <= '20131021144350'
                   43 Query     SELECT * FROM posts WHERE date_published is not null AND date_published <= '20131021144350' ORDER BY date_published DESC LIMIT 0,10
                   44 Connect   root@localhost as anonymous on pnet_blog
                   44 Query     SELECT id, title, impressions FROM tips WHERE date_published IS NOT NULL AND date_published <= '20131021144350' ORDER BY date_published DESC LIMIT 0, 10
                   44 Quit
                   43 Quit
131021 17:44:28    45 Connect   root@localhost as anonymous on pnet_blog
                   45 Init DB   pnet_blog
                   45 Query     SELECT * FROM posts WHERE url='how-and-when-to-enable-mysql-logs'
                   45 Query     UPDATE posts SET impressions=impressions+1 WHERE id='41'
                   45 Query     SELECT url, post_title FROM posts WHERE date_published IS NOT NULL AND date_published < '20131020150000' ORDER BY date_published DESC LIMIT 0,1
                   45 Query     SELECT url, post_title FROM posts WHERE date_published IS NOT NULL AND date_published > '20131020150000' ORDER BY date_published ASC LIMIT 0,1
                   45 Query     SELECT * FROM posts WHERE date_published is not null AND date_published <= '20131021144428' AND date_published >= '20130421144428' ORDER BY impressions DESC LIMIT 0,10
                   46 Connect   root@localhost as anonymous on pnet_blog
                   46 Query     SELECT id, title, impressions FROM tips WHERE date_published IS NOT NULL AND date_published <= '20131021144428' ORDER BY date_published DESC LIMIT 0, 10
                   46 Quit
                   45 Quit

Entrepreneur | Full-stack developer | Founder of MediSign Ltd. I have over 15 years of professional experience designing and developing web applications. I am also very experienced in managing (web) projects.

Источник

This tutorial shows you how to configure and view different MySQL logs. MySQL is
an open-source relational database based on SQL (Structured Query Language).
MySQL offers various built-in logs. In general, a database is the cornerstone of
almost every backend, and because of that administrators want to log this
service.

MySQL has multiple logs with different purpose. We will focus on following four
logs:

Error log: Records problems encountered starting, running, or stopping
mysqld. This log is stored by default in the /var/log directory. It could
be useful if you want to analyze the server itself.
General query logs: Records every connection established with each client.
This log records everything that client sent to the server. This log is useful
to determine client problems.
Binary logs: Record each event that manipulates data in the database.
These log records operations such as a table creating, modification of schema,
inserting new values, or querying tables. These logs are used to backup and
recover the database.
Slow query log: Record of each query, which execution took too much time.
This log could be useful for the optimisation of slow SQL queries.

In this tutorial, you will do the following actions:

You will install the MySQL server and view default error log.
You will connect to MySQL server, view metadata about general query logs
and view these logs.
You will understand the concept of the MySQL binary logs and where to find
them.
You will enable and configure a slow query log, simulate some slow query
and check this incident in the new log.

🔭 Want to centralize and monitor your MySQL logs?

Head over to Logtail and start ingesting your logs in 5 minutes.

Prerequisites

You will need:

Ubuntu 20.04 distribution including the non-root user with sudo access.
Basic knowledge of SQL languages (understanding of simple select query
statement).

Step 1 — Viewing Error Log

The MySQL server is maintained by the command-line program mysqld. This
program manages access to the MySQL data directory that contains databases and
tables. The problems encountered during mysqld starting, running, or stopping
are stored as a custom log in the directory /var/log/mysql. This log doesn’t
include any information about SQL queries. It is useful for the analysis of the
MySQL server.

First of all, let’s install the MySQL server. Ubuntu 20.04 allows to install the
MySQL from default packages with the apt install (installation requires sudo
privilege):

$ sudo apt update
$ sudo apt install mysql-server

The first command will update Ubuntu repositories, and the second will download
and install required packages for the MySQL server.

Now, the server is installed. The new service already creates a default error
log. You can list the directory /var/log and find a new subdirectory mysql
with ls:

You’ll see the program’s output appear on the screen:

Output
alternatives.log       bootstrap.log           kern.log.4.gz
alternatives.log.1     btmp                    lastlog
alternatives.log.2.gz  btmp.1                  letsencrypt
alternatives.log.3.gz  cups                    my-custom-app
apache2                dist-upgrade            mysql
apport.log             dmesg                   openvpn
apport.log.1           dmesg.0                 private
apport.log.2.gz        dmesg.1.gz              speech-dispatcher
apport.log.3.gz        dmesg.2.gz              syslog
apport.log.4.gz        dmesg.3.gz              syslog.1
apport.log.5.gz        dmesg.4.gz              syslog.2.gz
apport.log.6.gz        dpkg.log                syslog.3.gz
apport.log.7.gz        dpkg.log.1              syslog.4.gz
apt                    dpkg.log.2.gz           syslog.5.gz
auth.log               dpkg.log.3.gz           syslog.6.gz
auth.log.1             faillog                 syslog.7.gz
auth.log.2.gz          fontconfig.log          teamviewer15
auth.log.3.gz          gdm3                    test.log
auth.log.4.gz          gpu-manager.log         ubuntu-advantage.log
boot.log               gpu-manager-switch.log  unattended-upgrades
boot.log.1             hp                      wtmp
boot.log.2             installer               wtmp.1
boot.log.3             journal                 Xorg.0.log
boot.log.4             kern.log                Xorg.0.log.old
boot.log.5             kern.log.1              Xorg.1.log
boot.log.6             kern.log.2.gz           Xorg.1.log.old
boot.log.7             kern.log.3.gz

The output shows also directory mysql. This directory contains by default
single log error.log. Let’s view the content of file error.log with cat:

cat /var/log/mysql/error.log

You’ll see the program’s output appear on the screen:

Output
2021-04-28T09:36:19.040254Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.23-0ubuntu0.20.04.1) initializing of server in progress as process 62373
2021-04-28T09:36:19.046865Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-04-28T09:36:20.482915Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-04-28T09:36:23.709432Z 6 [Warning] [MY-010453] [Server] [email protected] is created with an empty password ! Please consider switching off the --initialize-insecure option.
2021-04-28T09:36:28.971810Z 6 [System] [MY-013172] [Server] Received SHUTDOWN from user boot. Shutting down mysqld (Version: 8.0.23-0ubuntu0.20.04.1).
2021-04-28T09:36:33.851492Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.23-0ubuntu0.20.04.1) starting as process 62437
2021-04-28T09:36:33.870257Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-04-28T09:36:34.114770Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-04-28T09:36:34.222753Z 0 [ERROR] [MY-011292] [Server] Plugin mysqlx reported: 'Preparation of I/O interfaces failed, X Protocol won't be accessible'
2021-04-28T09:36:34.222924Z 0 [ERROR] [MY-011300] [Server] Plugin mysqlx reported: 'Setup of socket: '/var/run/mysqld/mysqlx.sock' failed, can't create lock file /var/run/mysqld/mysqlx.sock.lock'
2021-04-28T09:36:34.354295Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-04-28T09:36:34.354479Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
...

The output shows that the file stores plain text records about the mysqld
server initialisation, and running.

Step 2 — Viewing General Query Logs

The server writes records about each client event during connection to the
general query log. Basically, it keeps the information about all SQL statements
that happens. This log is useful when the administrators want to know what
clients exactly execute.

Connecting to Server and Checking General Query Log Status

First of all, let’s check the status of the general query log because this
logging feature can be turned off.

You can connect to MySQL server as a root client:

You will be redirected to MySQL command-line.

Now, you can view system variables related to the general query log by executing
command show variables:

mysql> show variables like '%general%';

The clause specifies a pattern that should match the variable. In our case, the
pattern '%general%' specifies to show variables that contain the string
general. You’ll see the program’s output appear on the screen:

Output
+------------------+-----------------------------+
| Variable_name    | Value                       |
+------------------+-----------------------------+
| general_log      | ON                          |
| general_log_file | /var/lib/mysql/alice.log    |
+------------------+-----------------------------+
2 rows in set (0.01 sec)

The output shows two variables:

general_log: the variable holds value ON (general log enable), or OFF
(general log disabled).
general_log_file: the variable defines where is the log stored in the file
system.

As you can see, the general query log is by default enabled. We can disconnect
from the server by executing the exit command:

You will be redirected back to the terminal.

Viewing General Query Log

Now, you can view the content of this log with a cat (the sudo is required
because this file is maintained by the system):

$ sudo cat /var/lib/mysql/alice.log

You’ll see the program’s output appear on the screen:

Output
/usr/sbin/mysqld, Version: 8.0.23-0ubuntu0.20.04.1 ((Ubuntu)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
2021-04-28T10:47:28.713271Z    10 Connect   [email protected] on  using Socket
2021-04-28T10:47:28.713625Z    10 Query select @@version_comment limit 1
2021-04-28T10:53:50.778598Z    10 Query show variables like '%general%'
2021-04-28T13:35:41.944309Z    10 Quit

The output shows all statement executed on the server. You can see the time
stamp and the specific command that was executed. There is also the executed
command show variables like '%general%'.

Step 3 — Listing Binary Logs

The binary log contains events that manipulated the database. If you want to
recover the database, you need a backup and a binary log relevant to this
backup. There are multiple binary logs because they are versioned.

By default, the binary logs are enabled. You can check where are they stored.
Let’s connect to the MySQL server as a root client:

You will be redirected to MySQL prompt.

Now, you can check the binary logs status by executing show binary logs:

The command will list the binary log files on the server:

Output
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       179 | No        |
| binlog.000002 |       403 | No        |
| binlog.000003 |       770 | No        |
| binlog.000004 |       179 | No        |
| binlog.000005 |       403 | No        |
| binlog.000006 |       892 | No        |
+---------------+-----------+-----------+
6 rows in set (0.00 sec)

The output shows all binary logs. Now, we can find out where are this logs
stored.

We can show logs location by executing command show variables:

mysql> show variables like '%log_bin%';

We already use this show clause in the previous step. This time, the clause
shows variables that contain the string log_bin. You’ll see the program’s
output appear on the screen:

Output
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| 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                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
6 rows in set (0.00 sec)

The output shows that the binary logs are stored in directory /var/lib/mysql,
and they are labelled as binlog.index (for example binlog.000001).

We can disconnect from the server by executing the exit command:

You will be redirected back to the terminal.

Now, let’s list the directory /var/lib/mysql but only as a root because it is
owned and maintained by the system:

You’ll see the program’s output appear on the screen:

Output
auto.cnf         ca.pem                 ib_logfile0    private_key.pem
binlog.000001    client-cert.pem      ib_logfile1      public_key.pem
binlog.000002    client-key.pem       ibtmp1             server-cert.pem
binlog.000003    debian-5.7.flag      '#innodb_temp' server-key.pem
binlog.000004    EXAMPLE_DB           alice.log      sys
binlog.000005    '#ib_16384_0.dblwr'  alice.pid      undo_001
binlog.000006    '#ib_16384_1.dblwr'  mysql            undo_002
binlog.index     ib_buffer_pool       mysql.ibd
ca-key.pem     ibdata1              performance_schema

The output shows that the directory /var/lib/mysql contains the binary log
files.

Step 4 — Configuring Slow Query Log

MySQL allows to log queries, which took too much time. This mechanism is called
a slow query log.

Enabling Slow Query Logging

By default, the slow query log is disabled. You can enable it by editing MySQL
configuration file /etc/mysql/mysql.conf.d/mysqld.cnf (sudo required):

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

The file contains following lines that holds configuration variables (by default
commented out):

...
# Here you can see queries with especially long duration
# slow_query_log          = 1
# slow_query_log_file     = /var/log/mysql/mysql-slow.log
# long_query_time = 10
...

These three lines holds following three configuration variables:

slow_query_log: the slow query logging is disable (value 0) or enabled
(value 1).
slow_query_log_file: the slow query log is stored in the file
/var/log/mysql/mysql-slow.log. You can specify your own file.
long_query_time: by default, the slow query logs record each SQL query that
takes more than 10 seconds. You can change this minimal time interval to
another value. The value can be specified as a floating-point number where the
value 1.0 refers to 1 second.

You can enable slow query log by uncommenting this three lines. Also, you can
set your own long_query_time value:

...
# Here you can see queries with especially long duration
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/mysql-slow.log
long_query_time = 5
...

In our example, we change the default long_query_time value to 5 seconds. Now,
you can save the file.

If you want immediately apply the new configuration rules then you must restart
the MySQL server with systemctl (sudo required):

$ sudo systemctl restart mysql.service

Now, the MySQL server enables slow query log.

Checking Slow Query Log Status

You can check that the log is enabled if you login into the MySQL server as a
root client:

You will be redirected to MySQL prompt.

Let’s check the slow query log status by executing command show variables:

mysql> show variables like '%slow_query_log%';

Once again, we use the show clause. This time, the clause shows variables that
contain the string slow_query_log. You’ll see the program’s output appear on
the screen:

Output
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| slow_query_log      | ON                               |
| slow_query_log_file | /var/log/mysql/mysql-slow.log   |
+---------------------+----------------------------------+
2 rows in set (0.00 sec)

The output shows that the slow query log is enabled (the variable
slow_query_log holds the value ON). The log is stored in the file
/var/log/mysql/mysql-slow.log. You can see that it is the same file as the
file specified in the configuration script.

Let’s view actual slow query time interval by executing the command
show variables:

mysql> show variables like '%long_query_time%';

You’ll see the program’s output appear on the screen:

Output
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time |  5.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

The output shows that the variable long_query_time holds the value 5 seconds
(as we define in the configuration script).

Viewing Slow Query Log

At last, we can check that the MySQL records slow queries to the new log. You
can execute the following select query that takes 6 seconds:

The select will wait 6 seconds and then return 0:

Output
+----------+
| sleep(6) |
+----------+
|        0 |
+----------+
1 row in set (6.01 sec)

The output shows that this query takes 6 seconds. As a result, it should be
recorded in a slow query log.

We can disconnect from the server by executing the exit command:

You will be redirected back to the terminal.

At last, we can print content of the slow query log
/var/log/mysql/mysql-slow.log (the sudo is required because the file is
maintained by system):

$ sudo cat /var/log/mysql/mysql-slow.log

You’ll see the program’s output appear on the screen:

/usr/sbin/mysqld, Version: 8.0.23-0ubuntu0.20.04.1 ((Ubuntu)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
# Time: 2021-04-29T06:28:55.445053Z
# [email protected]: root[root] @ localhost []  Id:    15
# Query_time: 6.000443  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1619677729;
select sleep(6);

You can see that the output shows record about execution query
select sleep(6).

Conclusion

In this tutorial, you configured and viewed different MySQL logs. You installed
the MySQL server and viewed the error log. You connected to the server,
viewed the general query logs and their configuration. You listed binary
logs. At last, you enabled, configured and viewed a slow query log.

Centralize all your logs into one place.

Analyze, correlate and filter logs with SQL.

Create actionable

dashboards.

Share and comment with built-in collaboration.

Got an article suggestion?
Let us know

How To Start Logging With PostgreSQL

Learn how to start logging with PostgreSQL and go from basics to best practices in no time.

→

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.

Источник

I am running MySQL 5.0.45 on OS X 10.6.1 and I cannot get MySQL to log anything. I am debugging an application and need to see all queries and errors.

I added to the etc/my.cnf:

[mysqld]
bind-address = 127.0.0.1
log = /var/log/mysqld.log
log-error = /var/log/mysqld.error.log

I used sudo to create two log files, set permissions to 644, then restarted MySQL.

I referred to Where is my mysql log on OS X? in order to troubleshoot.

Running:

ps auxww|grep [m]ysqld

Returns:

_mysql      71   0.0  0.8   646996  15996   ??  S     7:31PM   0:01.10 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --pid-file=/usr/local/mysql/var/Macintosh-41.local.pid
_mysql      46   0.0  0.0   600336    744   ??  Ss    7:30PM   0:00.03 /bin/sh /usr/local/mysql/bin/mysqld_safe

And running:

$(ps auxww|sed -n '/sed -n/d;/mysqld /{s/.* ([^ ]*mysqld) .*/1/;p;}') --verbose --help|grep '^log'

Returns:

log                               /var/log/mysqld.log
log-bin                           (No default value)
log-bin-index                     (No default value)
log-bin-trust-function-creators   FALSE
log-bin-trust-routine-creators    FALSE
log-error                         /var/log/mysqld.error.log
log-isam                          myisam.log
log-queries-not-using-indexes     FALSE
log-short-format                  FALSE
log-slave-updates                 FALSE
log-slow-admin-statements         FALSE
log-slow-queries                  (No default value)
log-tc                            tc.log
log-tc-size                       24576
log-update                        (No default value)
log-warnings                      1

Running:

mysql> show variables like '%log%';

Returns:

+---------------------------------+---------------------------+
| Variable_name                   | Value                     |
+---------------------------------+---------------------------+
| back_log                        | 50                        | 
| binlog_cache_size               | 32768                     | 
| expire_logs_days                | 0                         | 
| innodb_flush_log_at_trx_commit  | 1                         | 
| innodb_locks_unsafe_for_binlog  | OFF                       | 
| innodb_log_arch_dir             |                           | 
| innodb_log_archive              | OFF                       | 
| innodb_log_buffer_size          | 1048576                   | 
| innodb_log_file_size            | 5242880                   | 
| innodb_log_files_in_group       | 2                         | 
| innodb_log_group_home_dir       | ./                        | 
| innodb_mirrored_log_groups      | 1                         | 
| log                             | ON                        | 
| log_bin                         | OFF                       | 
| log_bin_trust_function_creators | OFF                       | 
| log_error                       | /var/log/mysqld.error.log | 
| log_queries_not_using_indexes   | OFF                       | 
| log_slave_updates               | OFF                       | 
| log_slow_queries                | OFF                       | 
| log_warnings                    | 1                         | 
| max_binlog_cache_size           | 4294967295                | 
| max_binlog_size                 | 1073741824                | 
| max_relay_log_size              | 0                         | 
| relay_log_purge                 | ON                        | 
| relay_log_space_limit           | 0                         | 
| sync_binlog                     | 0                         | 
+---------------------------------+---------------------------+
26 rows in set (0.00 sec)

Any help on how I can get MySQL to log?

asked Oct 4, 2009 at 0:11

Even though you used chmod 644, make sure mysql is the owner of the logs.

touch /var/log/mysql.log
chown mysql:mysql /var/log/mysql.log
touch /var/log/mysql.error.log
chown mysql:mysql /var/log/mysql.error.log

Next, restart mysqld.

Then, log into mysql and run:

mysql> show variables like '%log%';

Look at the values for general_log, general_log_file, log, log_error, etc.

Switch them on as needed and run mysqladmin flushlogs as needed.

answered Oct 5, 2009 at 3:27

randomxrandomx

1,0041 gold badge8 silver badges14 bronze badges

To enable the log files, you need to make sure that one or more of the following directives are in the [mysqld] section of your mysql server’s main configuration file (/etc/my.cnf on mine):

[mysqld]
log-bin
log
log-error
log-slow-queries

The logs will be created, by default, in the same data directory that holds the database subdirectories themselves (typically /var/lib/mysql) and the log file names default to the hostname followed by a suffix that matches the directive names above (eg. -bin, -slow-queries, etc).

To keep your logs in a different path, or using different filenames, specify the base name following the log= directive, and any path and filename you like e.g.:

[mysqld]
log-bin=/var/log/mysql-bin.log
general-log=/var/log/mysql.log
log-error=/var/log/mysql-error.log
slow-query-log=/var/log/mysql-slowquery.log

You need to restart mysqld for these changes to take effect.

answered Mar 30, 2011 at 14:33

gigawattgigawatt

1891 silver badge6 bronze badges

The previous answers were out dated. You can look the configuration file in /etc/my.cnf or on windows ./mysql/my.ini.

In the [mysqld] section add

log_bin
general_log
log_error

The documentation for windows states that the logs get stored in ProgramData/MySQL/MySQL Server 5.6/. Ignore it because it’s a lie.

In addition the recommended log option is depreciated and results in the error

ambiguous option '--log' (log-bin, log_slave_updates)

being logged in ./mysql/data/host.err and the daemon silently dying. The correct setting is general-log.

Also log-slow-queries is not valid and caused the error

C:mysqlbinmysqld.exe: unknown option '--log-slow-queries'

So be certain to stay away from that also.

masegaloeh

18.1k10 gold badges56 silver badges105 bronze badges

answered Apr 28, 2015 at 15:49

user3338098user3338098

401 gold badge3 silver badges13 bronze badges

Try different log settings in my.cnf. In my case I needed:

general_log = on

general_log_file=/var/log/mysql/mysql.log

HBruijn

73.5k23 gold badges132 silver badges194 bronze badges

answered Aug 5, 2016 at 12:34

Enter mysql command line with super privileges and perform:

SET GLOBAL general_log_file = '/var/log/mysql/mysql.log';

In my case, it didn’t matter if I had this variable already set with the same value! Also, I changed the permissions and the owner of the file as mentioned in the other posts.

answered Sep 7, 2011 at 12:13

LuciaLucia

1451 silver badge7 bronze badges

welcome back to the StackExchange!
Beyond clustering architectures, data protection also includes disk mirroring, snapshots, and redundant networks.
While the tools available to system administrators have evolved, the general approach to data protection has always been one of resilience through redundancy.
Thank you very much all.

answered Nov 27, 2022 at 11:47

Источник

Журналы событий — первый и самый простой инструмент для определения статуса системы и выявления ошибок. Основных логов в MySQL четыре:

Error Log — стандартный лог ошибок, которые собираются во время работы сервера (в том числе start и stop);
Binary Log — лог всех команд изменения БД, нужен для репликации и бэкапов;
General Query Log — основной лог запросов;
Slow Query Log — лог медленных запросов.

Лог ошибок

Этот журнал содержит все ошибки, которые произошли во время работы сервера, включая критические ошибки, а также остановки, включения сервера и предупреждения (warnings). С него нужно начать в случае сбоя системы. По умолчанию все ошибки выводятся в консоль (stderr), также можно записывать ошибки в syslog (по умолчанию в Debian) или отдельный лог-файл:

log_error=/var/log/mysql/mysql_error.log

Рекомендуем держать этот журнал включенным для быстрого определения ошибок. А для понимания, что значит та или иная ошибка, в MySQL присутствует утилита perror:

shell> perror 13 64

OS error code 13: Permission denied

OS error code 64: Machine is not on the network

Бинарный (он же двоичный) лог

В бинарный лог записываются все команды изменения базы данных, пригодится для репликации и восстановления.

Включается так:

log_bin = /var/log/mysql/mysql-bin.log

expire_logs_days = 5

max_binlog_size = 500M

Учтите, что если вы не собираетесь масштабировать систему и реализовывать отказоустойчивость, то бинарный лог лучше не включать. Он требователен к ресурсам и снижает производительность системы.

Лог запросов

В этом журнале содержатся все полученные SQL-запросы, информация о подключениях клиентов. Может пригодиться для анализа индексов и оптимизации, а также выявления ошибочных запросов:

general_log_file = /var/log/mysql/mysql.log

general_log = 1

Также его можно включить/отключить во время работы сервера MySQL:

SET GLOBAL general_log = ‘ON‘;

SET GLOBAL general_log = ‘OFF‘;

Лог медленных запросов

Журнал пригодится для определения медленных, то есть неэффективных запросов. Подробнее читайте в этой статье.

Просмотр логов

Для просмотра логов на Debian (Ubuntu) нужно выполнить:

# Лог ошибок

tail -f /var/log/syslog

#Лог запросов

tail -f /var/log/mysql/mysql.log

# Лог медленных запросов

tail -f /var/log/mysql/mysql-slow.log

Ротация логов

Не забывайте сжимать (архивировать, ротировать) файлы логов, чтобы они занимали меньше места на сервере. Для этого используйте утилиту logrotate, отредактировав файл конфигурации /etc/logrotate.d/mysql-server:

# — I put everything in one block and added sharedscripts, so that mysql gets

# flush-logs’d only once.

# Else the binary logs would automatically increase by n times every day.

# — The error log is obsolete, messages go to syslog now.

/var/log/mysql.log /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log {

daily

rotate 7

missingok

create 640 mysql adm

compress

sharedscripts

postrotate

test -x /usr/bin/mysqladmin || exit 0

# If this fails, check debian.conf!

MYADMIN=«/usr/bin/mysqladmin —defaults-file=/etc/mysql/debian.cnf»

if [ -z «`$MYADMIN ping 2>/dev/null`» ]; then

# Really no mysqld or rather a missing debian-sys-maint user?

# If this occurs and is not an error please report a bug.

#if ps cax | grep -q mysqld; then

if killall -q -s0 -umysql mysqld; then

exit 1

else

$MYADMIN flush-logs

endscript

}

DDL Log

MySQL также ведет лог языка описания данных. В него собираются данные операций типа DROP_TABLE and ALTER_TABLE. Лог используется для восстановления после сбоев, которые произошли во время выполнения таких операций. DDL Log — бинарный файл, не предназначенный для чтения пользователем, поэтому не модифицируйте и не удаляйте его.

Самое главное

Всегда включайте лог ошибок, используйте лог запросов для проверки соединения приложения с базой данных, проверки запросов и работы memcached. Лог медленных запросов пригодится для оптимизации работы MySQL.

https://github.com/midnight47/

Источник