This morning I was doing some troubleshooting on a PHP program that couldn’t seem to access the locally running MySQL database server. Upon searching for some logging to figure out what was going on, I came across the fact the MySQL as installed from the Arch repositories, doesn’t enable much along theses lines.<\/p>\n
MySQL supports error logging to \/var\/lib\/mysql directory by default. The file is usually in the form Enabling general logging for MySQL<\/strong><\/p>\n The first step is to figure out where you want MySQL to store its general log. In our example, we will put it in the \/var\/log\/mysql directory, in file called general.log. This directory and file must be created, and permissions set so that mysql can write to it. MySQL will not<\/em> create this file on its own. So lets create the directory and file with the following commands:<\/p>\n Then, in order tell MySQL to enable general logging, a couple of lines so be added to the \/etc\/mysql\/my.cnf file in the [mysqld] section – see last two lines in the example my.cnf file below: That’s all it takes! Save the my.cnf file, and restart mysqld with:<\/p>\n You should now the general.log file in \/var\/log\/mysql. An example might look like this (showing some client connections and select statements):<\/p>\n The above snip shows MySQL starting and an application hitting a database.<\/p>\n Configuring MySQL to log errors to the system log<\/strong><\/p>\n That last thing I want to cover is getting MySQL to log to the system log, instead of its own log in \/var\/lib\/mysql. I mentioned above that MySQL logs errors to \/var\/lib\/mysql\/ This one is pretty simple – add the following section to the \/etc\/mysql\/my.cnf file:<\/p>\n And then restart MySQL with:<\/p>\n You should see messages in the daemon.log file that look something like this:<\/p>\n In summary, the above changes will allow you to “see” what MySQL is doing both in terms of the daemon itself, and the actual activity being processed by the database server. These changes could also work on other distros, but in particular ArchLinux by default enables only error logging to the MySQL directory only. <\/p>\n Up next – configuring syslog-ng as syslog server for network devices.<\/p>\n","protected":false},"excerpt":{"rendered":" This morning I was doing some troubleshooting on a PHP program that couldn’t seem to access the locally running MySQL database server. Upon searching for some logging to figure out what was going on, I came across the fact the MySQL as installed from the Arch repositories, doesn’t enable much along theses lines. MySQL supports […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[10],"tags":[13,24,44,46,61],"_links":{"self":[{"href":"https:\/\/www.linuxpoweruser.com\/index.php?rest_route=\/wp\/v2\/posts\/388"}],"collection":[{"href":"https:\/\/www.linuxpoweruser.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.linuxpoweruser.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.linuxpoweruser.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.linuxpoweruser.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=388"}],"version-history":[{"count":0,"href":"https:\/\/www.linuxpoweruser.com\/index.php?rest_route=\/wp\/v2\/posts\/388\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.linuxpoweruser.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=388"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.linuxpoweruser.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=388"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.linuxpoweruser.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=388"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}sudo mkdir \/var\/log\/mysql
\nsudo touch \/var\/log\/mysql\/general.log
\nsudo chown mysql:mysql -R \/var\/log\/mysql
\n<\/code><\/p>\n
\n
\n# The MySQL server
\n[mysqld]
\nport = 3306
\nsocket = \/var\/run\/mysqld\/mysqld.sock
\ndatadir = \/var\/lib\/mysql
\nskip-external-locking
\nkey_buffer_size = 16M
\nmax_allowed_packet = 1M
\ntable_open_cache = 64
\nsort_buffer_size = 512K
\nnet_buffer_length = 8K
\nread_buffer_size = 256K
\nread_rnd_buffer_size = 512K
\nmyisam_sort_buffer_size = 8M
\ngeneral_log = 1
\ngeneral_log_file = \/var\/log\/mysql\/general.log<\/code>
\n<\/strong><\/p>\n\/etc\/rc.d\/mysqld restart<\/code><\/p>\n\/usr\/bin\/mysqld, Version: 5.5.21-log (Source distribution). started with:
\nTcp port: 3306 Unix socket: \/var\/run\/mysqld\/mysqld.sock
\nTime Id Command Argument
\n120318 10:10:26 1 Connect UNKNOWN_USER@localhost as on
\n 1 Quit
\n120318 10:10:39 2 Connect root@localhost on
\n 2 Init DB webcalendar
\n 2 Init DB webcalendar
\n 2 Query SELECT COUNT( cal_value ) FROM webcal_config
\n 2 Query SELECT cal_permissions FROM webcal_access_function
\n WHERE cal_login = 'admin'
\n 2 Query SELECT cal_permissions FROM webcal_access_function
\n WHERE cal_login = '__default__'
\n 2 Init DB webcalendar
\n 2 Query SELECT cal_login, cal_lastname, cal_firstname, cal_is_admin, cal_email, cal_passwd FROM webcal_user ORDER BY cal_lastname, cal_firstname, cal_login
\n<\/code><\/p>\n
\n[mysqld_safe]
\nsyslog<\/code><\/p>\n\/etc\/rc.d\/mysqld restart<\/code><\/p>\nMar 18 10:14:54 localhost mysqld_safe: Starting mysqld daemon with databases from \/var\/lib\/mysql
\nMar 18 10:14:54 localhost mysqld: 120318 10:14:54 InnoDB: The InnoDB memory heap is disabled
\nMar 18 10:14:54 localhost mysqld: 120318 10:14:54 InnoDB: Mutexes and rw_locks use GCC atomic builtins
\nMar 18 10:14:54 localhost mysqld: 120318 10:14:54 InnoDB: Compressed tables use zlib 1.2.6
\nMar 18 10:14:54 localhost mysqld: 120318 10:14:54 InnoDB: Initializing buffer pool, size = 128.0M
\nMar 18 10:14:54 localhost mysqld: 120318 10:14:54 InnoDB: Completed initialization of buffer pool
\nMar 18 10:14:54 localhost mysqld: 120318 10:14:54 InnoDB: highest supported file format is Barracuda.
\nMar 18 10:14:54 localhost mysqld: 120318 10:14:54 InnoDB: Waiting for the background threads to start
\nMar 18 10:14:55 localhost mysqld: 120318 10:14:55 InnoDB: 1.1.8 started; log sequence number 2218816
\nMar 18 10:14:55 localhost mysqld: 120318 10:14:55 [ERROR] \/usr\/bin\/mysqld: Can't find file: '.\/performance_schema\/events_waits_current.frm' (errno: 13)
\n<\/code><\/p>\n