6

I'm running mysql Ver 15.1 Distrib 5.5.46-MariaDB in CentOS 6.7 server. I'm trying to get queries logged and I'm doing by redirecting log to files as shown below:

#/etc/my.cnf.d/server.cnf
# this is only for the mysqld standalone daemon
[mysqld]
#
# * Fine Tuning
#
key_buffer              = 16M
max_allowed_packet      = 512M
thread_stack            = 192K
thread_cache_size       = 8

log-output = FILE

#General Query Log
general_log
general_log_file        = /var/log/mysql/mysql_query.log

#Error Log
log_error               = /var/log/mysql/mysql_error.log

#Slow Query Log
log-slow-queries
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/mysql_slow.log
long_query_time         = 2
log-queries-not-using-indexes

collation-server = utf8_general_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

Before restart MySQL service I did run the following command to create each file and assign proper permissions:

mkdir -p /var/log/mysql && touch /var/log/mysql/mysql_query.log && touch /var/log/mysql/mysql_error.log && touch /var/log/mysql/mysql_slow.log && chown -R mysql:mysql /var/log/mysql/

I have restarted the MySQL daemon:

service mysql restart

After restart the MySQL server I can see the files but with 0 size so MySQL is not sending anything to those files. I did run the following query on MySQL console SHOW VARIABLES LIKE '%log%' and below is the result and as you may notice changes are not made at server:

+-------------------------------------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name                             | Value                                                                                                        |
+-------------------------------------------+--------------------------------------------------------------------------------------------------------------+
| aria_checkpoint_log_activity              | 1048576                                                                                                      |
| aria_log_file_size                        | 1073741824                                                                                                   |
| aria_log_purge_type                       | immediate                                                                                                    |
| aria_sync_log_dir                         | NEWFILE                                                                                                      |
| back_log                                  | 50                                                                                                           |
| binlog_annotate_row_events                | OFF                                                                                                          |
| binlog_cache_size                         | 32768                                                                                                        |
| binlog_checksum                           | NONE                                                                                                         |
| binlog_direct_non_transactional_updates   | OFF                                                                                                          |
| binlog_format                             | STATEMENT                                                                                                    |
| binlog_optimize_thread_scheduling         | ON                                                                                                           |
| binlog_stmt_cache_size                    | 32768                                                                                                        |
| expire_logs_days                          | 0                                                                                                            |
| general_log                               | OFF                                                                                                          |
| general_log_file                          | pdone-prod.log                                                                                               |
| innodb_flush_log_at_trx_commit            | 1                                                                                                            |
| innodb_locks_unsafe_for_binlog            | OFF                                                                                                          |
| innodb_log_block_size                     | 512                                                                                                          |
| 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                                                                                                            |
| innodb_recovery_update_relay_log          | OFF                                                                                                          |
| innodb_use_global_flush_log_at_trx_commit | ON                                                                                                           |
| log                                       | OFF                                                                                                          |
| log_bin                                   | OFF                                                                                                          |
| log_bin_trust_function_creators           | OFF                                                                                                          |
| log_error                                 | /var/lib/mysql/pdone-prod.pdi-inc.com.err                                                                    |
| log_output                                | FILE                                                                                                         |
| log_queries_not_using_indexes             | OFF                                                                                                          |
| log_slave_updates                         | OFF                                                                                                          |
| log_slow_filter                           | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |
| log_slow_queries                          | OFF                                                                                                          |
| log_slow_rate_limit                       | 1                                                                                                            |
| log_slow_verbosity                        |                                                                                                              |
| log_warnings                              | 1                                                                                                            |
| max_binlog_cache_size                     | 18446744073709547520                                                                                         |
| max_binlog_size                           | 1073741824                                                                                                   |
| 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                       | pdone-prod-slow.log                                                                                          |
| sql_log_bin                               | ON                                                                                                           |
| sql_log_off                               | OFF                                                                                                          |
| sync_binlog                               | 0                                                                                                            |
| sync_relay_log                            | 0                                                                                                            |
| sync_relay_log_info                       | 0                                                                                                            |
+-------------------------------------------+--------------------------------------------------------------------------------------------------------------+

What could be wrong here? Why logs are not being sent to those files?

UPDATES

@rick-james: I did check permissions by running:

# ls -ld /var/log/mysql/
drwxrwxrwx 2 mysql mysql 4096 Nov 30 16:44 /var/log/mysql/

And apparently are good (777).

@ypercube: Below is the result of the command you want me to run.

# ls -l /var/log/mysql/
total 0
-rw-r--r-- 1 mysql mysql 0 Nov 30 16:44 mysql_error.log
-rw-r--r-- 1 mysql mysql 0 Nov 30 16:44 mysql_query.log
-rw-r--r-- 1 mysql mysql 0 Nov 30 16:44 mysql_slow.log

In first place all files was chmod as 644 but I change that to be 775 now result looks like:

# ls -l /var/log/mysql/
total 0
-rwxrwxr-x 1 mysql mysql 0 Nov 30 16:44 mysql_error.log
-rwxrwxr-x 1 mysql mysql 0 Nov 30 16:44 mysql_query.log
-rwxrwxr-x 1 mysql mysql 0 Nov 30 16:44 mysql_slow.log

But not change at all, logs are not beeing saved in those files. Why?

2

Ok, finally I found where the issue was! For some reason I delete /etc/my.cnf file (perhaps when I uninstall MySQL or something else) and that's the file including everything under /etc/my.cnf.d because of that changes don't take effect. My bad. Thanks for your time

1

Check under /var/log/mysql/ like your my.cnf says.

SHOW VARIABLES LIKE '%log%'; -- to see if the settings really took effect.

Is there a /etc/my.cnf file?

Look at the arguments on mysqld that is running to see if they override anything.

Did you restart mariadb?

  • Nop, there is nothing at that path, even mysql directory isn't created under /var/ logs. Yes, there is a /etc/my.cnf` with this content: [client-server] # include all files from the config directory !includedir /etc/my.cnf.d1, how do I see the arguments? Yes I have restarted MariaDB and even the whole server – ReynierPM Apr 25 '15 at 20:32
  • To do the SHOW, connect on the commandline "mysql" and execute the command. – Rick James Apr 25 '15 at 23:47
  • You said /var/log/mysql/, not /var/mysql/, nor /var/logs/mysql/. – Rick James Apr 25 '15 at 23:48
  • I have added SHOW command output to the main post but still not having that file, see the output of tail command: tailf /var/log/mysql/mysql-general.log result tailf: cannot stat "/var/log/mysql/mysql-general.log": No such file or directory – ReynierPM Apr 26 '15 at 0:08
  • Are there are any related errors in the error file? If the user that the mysql service is running under, does not have write permission for the /var/log/mysql/ directory, it will show in the error file. And is there such a directory in the first place? What does ls /var/log/mysql/ show? – ypercubeᵀᴹ Apr 26 '15 at 0:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.