Purging mysql-bin files, a.k.a. saving GBs of SSD space

My SSD was filling up and while searching for the cause I ended at MySQL binary log files. They used almost 40GB :-O while I don’t use the logs so often and especially not so backdated

$ sudo ls -lSh /var/lib/mysql |head 
total 7.8G
-rw-rw---- 1 mysql mysql 1.1G May 18 13:34 mysql-bin.000521
-rw-rw---- 1 mysql mysql 796M May 14 20:36 mysql-bin.000517
-rw-rw---- 1 mysql mysql 528M May 21 20:18 mysql-bin.000525
-rw-rw---- 1 mysql mysql 370M May 22 20:18 mysql-bin.000526
-rw-rw---- 1 mysql mysql 367M May 17 19:44 mysql-bin.000520
-rw-rw---- 1 mysql mysql 354M May 13 20:38 mysql-bin.000516
-rw-rw---- 1 mysql mysql 348M May 2 20:25 mysql-bin.000503
-rw-rw---- 1 mysql mysql 297M May 18 20:13 mysql-bin.000522
-rw-rw---- 1 mysql mysql 285M May 16 20:29 mysql-bin.000519

After some googling I found how to purge the files.

PURGE BINARY LOGS BEFORE '2017-05-01 00:00:00';

Because I don’t want to be bothered with this same issue a set expire_log_days to the 60 days in my.cnf, because the default is 0, so no automatic removal.

New path for MySQL query log on OSX

Sometimes during development I need to see what SQL queries are executed and listing them in Console.app is maybe the most convenient solution on OSX.

console.app

To see queries in Console

mysqld query log OSX

I just created /etc/mysql/my.cnf with:

[mysqld]
general_log = 1
# general_log_file = /usr/local/mysql/data/mysqld.query.log
general_log_file = /var/log/mysql/mysqld.query.log

slow_query_log = 1
long_query_time = 1 # second
# slow_query_log_file = /usr/local/mysql/data/mysqld.slow.log
slow_query_log_file = /var/log/mysql/mysqld.slow.log
log_queries_not_using_indexes = 0

MySQL server needs own permissions on the parent log dir so:

sudo mkdir /var/log/mysql
sudo chown -R _mysql:_mysql /var/log/mysql

Last step is server restart.

sudo launchctl unload -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
sudo launchctl load -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

On a Linux machine I use multitail as an alternative.

Enabling MySQL 5.7 query logging on OSX

I got used to it that sometimes I need to check the queries run by MySQL. After clean OSX upgrade I forgot to enable it.

So I added the following to /etc/mysql/my.cnf:

[mysqld]

sql_mode=

general_log = 1
general_log_file = /usr/local/mysql/data/mysqld.query.log

slow_query_log = 1
long_query_time = 1 # second
slow_query_log_file = /usr/local/mysql/data/mysqld.slow.log
log_queries_not_using_indexes = 0

The default one that comes with the package is at /usr/local/mysql/support-files/my-default.cnf

There’s also an option to set logging via SQL queries.

Link checking as cache warmup or integration testing

Sometimes I use wget awesome recursive spidering (crawling) feature (alternative to linkchecker) beside broken link check also for cache warmup or looking for PHP errors after commit.

On production for some projects I use PHP error logging into separate log directory per day:

if (!is_dir(DOC_ROOT.'/log')) {
    mkdir(DOC_ROOT.'/log');
}

ini_set('display_errors', 0);
ini_set('display_startup_errors', 0);
ini_set('log_errors', 1);
ini_set('error_log', DOC_ROOT.'/log/php-error-'.date('d').'.log');

So after crawling I check if there is a log file.
And the wget shell script that crawls the site is:

#!/bin/bash

timestamp=$(date +"%Y%m%d%H%M%S")

cd /tmp
time wget -4 --spider -r --delete-after \
     --no-cache --no-http-keep-alive --no-dns-cache \
     -U "Wget" \
     -X/blog \
     http://www.example.com -o"/tmp/wget-example-com-$timestamp.log"

During the crawling your site is hit and cache is generated if you have some implemented, for example phpFastCache.
I just only crawl via IPv4 without HTTP keep alive (only for better performance). Setting some unique user agent is good for parsing in access_log, too.
-X stands for excluding, also handy for improving performance on large sites.
-o outputs the wget status report where you can search for HTTP status codes as 404, 403, 500, etc.
Remember the excluded path, you might run another wget in parallel if you need. Unfortunately wget can’t run parallel threads as of writing.

Enable MySQL file logging

Wanted to know what queries are executed so I had to enable it for MariaDB.

SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql.log';
SET GLOBAL slow_query_log = 'On';
SET GLOBAL slow_query_log_file = '/var/log/mysql-slow.log';

To see the changes:

SHOW VARIABLES LIKE 'general_log';
SHOW VARIABLES LIKE 'slow_query_log';