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?
Best Answer
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