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.

Connect to localhost MySQL from Docker container

To test connection to MySQL/MariaDB server running on localhost from Docker container try:

docker run --rm -it --net=host mariadb mysql -u root -h
--rm  Automatically remove the container when it exits https://docs.docker.com/engine/reference/run/#clean-up-rm
-i    Keep STDIN open even if not attached https://docs.docker.com/engine/reference/run/#foreground
-t    Allocate a pseudo-TTY
--net Connect a container to a network https://docs.docker.com/engine/reference/run/#network-settings

Getting ‘doesn’t have a default value’ from MySQL (MariaDB)?

Solution for me from ideas at https://stackoverflow.com/questions/15438840/mysql-error-1364-field-doesnt-have-a-default-values and https://www.farbeyondcode.com/Solution-for-MariaDB-Field–xxx–doesn-t-have-a-default-value-5-2720.html was to remove ‘STRICT_TRANS_TABLES’ from my.cnf.

So I ended up with the following in /usr/local/mysql/my.cnf: