MySQL writes crazy error logs

A core business database, the version is MySQL 8.34
Community Server Edition. Since going online, the error log of this database server has increased very rapidly (as shown in the figure below), increasing to a capacity of more than 10
G
every 24
hours.

Because there is a fault alarm and it has not affected normal access to the business, the relevant personnel are not allowed to restart the MySQL
service. In view of this situation, I had to set up an automatic scheduled task to clean these logs at a fixed time every night. For specific operations, on the system command line, execute "crontab -e"
and add the following text line:

00 01 * * * echo > /data/mysql8/data/mysql_db/mysql.log

Save and exit the edit mode. If you need to verify the correctness and validity of the task, you can modify the execution time to a fairly recent time point, and then compare the error log file "mysql.log#" before and after the task execution. ##" size, and check whether the cron
log has executed this scheduled task, as shown in the figure below.

During the Spring Festival holiday, everyone goes home to celebrate the New Year, and this is the lowest period for visits. Taking this opportunity, I plan to completely solve this problem. Seek opinions from relevant personnel and ask if it is possible to modify the MySQL

option file to block useless warning output? The reply was "
How long does it take to restart"
? The answer: "
A few minutes are enough"
.

What does this defined error log record in large quantities? Open the "mysql.log"

large file and find that it is full of warning messages. Use the system command "tail -f mysql.log"
. The screen output rolls like a motor flywheel. The specific data is shown in the figure below.

These warning messages indicate that the user account uses "mysql_native_password"

that is inconsistent with the default authentication method "
caching_sha2_password"
. The solution is to either change the password authentication method of all user accounts to "
caching_sha2_password"
, or the error log file "mysql.log"
does not record these warning messages. Since there are many user accounts and multiple businesses are designed, it is easier not to record warning messages. These warning messages are useless anyway (let it record real error logs, which will help troubleshooting).

MySQL

The host system Centos 7 where the server is located,
Open the option file "/etc/my.cnf"
in the text editor, and append the following text line in the text block [mysqld
] .

log-error-verbosity=1

By default, the "log-error-verbosity"

value of MySQL 8
is "3"
, which means that all "
errors, warnings and comments are recorded in the error log "
. The number "2
" means logging "errors and warnings", while the number "1
" means logging only "errors".

It should be noted that before modifying the option file, remember to back up first. This is common sense and also a regret medicine. After checking that there are no writing errors, restart the MySQL

service, and then check whether the local MySQL
service is normal, whether the remote master-slave synchronization is normal and whether there is any delay.

After running for a few minutes, check the error log of MySQL

to see if it no longer grows rapidly. After observing for a period of time, it is indeed true that the warning log of MySQL
is no longer recorded, and the growth rate of the file has also been greatly reduced.

The above is the detailed content of MySQL writes crazy error logs. For more information, please follow other related articles on the PHP Chinese website!