Page 1 of 1
Forum

Welcome to the Tweaking4All community forums!
When participating, please keep the Forum Rules in mind!

Topics for particular software or systems: Start your topic link with the name of the application or system.
For example “MacOS X – Your question“, or “MS Word – Your Tip or Trick“.

Please note that switching to another language when reading a post will not bring you to the same post, in Dutch, as there is no translation for that post!



MySQL/MariaDB - Pur...
 
Share:
Notifications
Clear all

MySQL/MariaDB - Purging mysql-bin.xxxxxx files the proper way ...

1 Posts
1 Users
0 Likes
432 Views
 Hans
(@hans)
Famed Member Admin
Joined: 9 years ago
Posts: 2236
Topic starter  

When running MariaDB on your QNAP, you may find tons of files name "mysql-bin.xxxxxx" files (where xxxxxx is a number) in your data dir (for example: /share/CACHEDEV1_DATA/.mariadb10/data).

You should never just randomly delete those, as these are binary logs generated by mysqld - and there are proper SQL statements to clean these up.

PURGE BINARY LOGS TO 'mysql-bin.xxxxxx';
PURGE BINARY LOGS BEFORE 'datetimestamp';

 

These statement will clear all bin-log files up to the given number (mysql-bin.xxxxxx) or up to a given date (datetimestamp).

For example, the following statement will remove all files numbered prior to "mysql-bin.000123"

PURGE BINARY LOGS TO 'mysql-bin.000123';

You can also use something like this, to remove all bin-log files older than 5 days:

PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 5 DAY) + INTERVAL 0 SECOND;

 

You can have MySQL/MariaDB clear these bin-log files automatically by sayin, for example, that it should only keep the bin-log files of the last 5 days:

SET GLOBAL expire_logs_days = 5;

 

You may want to add this to the settings file though (/etc/my.cnf - this will be different on your QNAP, maybe /share/CACHEDEV1_DATA/.qpkg/MariaDB10/etc/mariadb.conf)

[mysqld]
expire_logs_days=3

ReplyQuote
Share: