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

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

1 Posts
1 Users
0 Likes
1,683 Views
 Hans
(@hans)
Famed Member Admin
Joined: 11 years ago
Posts: 2660
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

Like what you see and you'd like to help out? 

The best way to help is of course by assisting others with their questions here in the forum, but you can also help us out in other ways:

- Do your shopping at Amazon, it will not cost you anything extra but may generate a small commission for us,
- send a cup of coffee through PayPal ($5, $10, $20, or custom amount),
- become a Patreon,
- donate BitCoin (BTC), or BitCoinCash (BCH).

Share: