MySQL Log Files in CentOS

I am having a heck of a time getting this web server to log MySQL errors for me. I just started having issues with MySQL crashing every night and having to restart the service. I am running MySQL on CentOS release 7.6.1810 MySQL version is 15.1 (distrib 10.1.37-MariaDB).

I am trying to pinpoint the cause but I am unable to get the log files to generate.

If I go to:

/etc/my.cnf

then edit it in nano it does not have the [mysql] line in the file. This is where you would add in the general_log stuff.

However, in /etc/my.cnf.d/ I do have a mysql-clients.cnf and a server.cnf which do show the [mysql] line in the file to add in the logs.

If I add the line in manually to my.cnf when I stop the service I cannot restart it.

I get an error:

Job for mariadb.service failed because the control process exited with error code.

The same thing happens if I add the code for the log files in the server.cnf.

If I add the code in on the mysql-clients.cnf I can stop and start the server with no issues, but nothing is being written to any of the files.

I also created the files and gave them 777 permissions and chown to mysql:mysql.

Does anyone have any idea on what I can do to get this logging?

The following lines are what I have been trying to add:

[mysqld_safe]
log_error=/var/log/mysql/mysql_error.log
[mysqld]
log_error=/var/log/mysql/mysql_error.log
general_log_file = /var/log/mysql/mysql.log
general_log = 1
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes

Thank you for any and all help!

1 Answer

So, what you describe sounds like (good faith) desperation. The reality is you only ever need to edit one config file to do what you have set out to do and that is (typically) the /etc/my.cnf file.

With that established, I would recommend you rewind and undo all of the adjustments you made to others files, and start from “tabula rasa.”

Once you are at a stable state, I recommend logging into MySQL and running this command to check the values of current MySQL server settings:

SHOW VARIABLES;

That will show you all variable names and settings. Make sure the values you are adjusting are indeed named the way the MySQL install expects them to be named.

Once you confirm those values, set them as you have outlined. Just note that you will only ever be changing the mysqld settings since those are the MySQL server settings; mysqld equates to “MySQL Daemon” which is a classic name for a Linux/Unix server.

Once your /etc/my.cnf is set, restart MySQL. But be sure to run it as sudo with something like sudo service mysqld restart; might be different for MariaDB. After the restart completes, the new settings should be set… Which you can then confirm my logging into MySQL and running that SHOW VARIABLES; command again.