Making Backups with Binary Log

Enable binary logging

nano /etc/mysql/mariadb.conf.d/50-server.cnf

Find this part log_bin = /var/log/mysql/mysql-bin.log

Change it to

log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M binlog_format = mixed
Restart mariadb

Restart mariadb service

systemctl restart mariadb

Your backups will live here ls /var/log/mysql

Using mysqlbinlog utility we can view the binlog file content

If it’s a single file you can recover using:

mysqlbinlog /var/lib/mysql-bin.000016 | mysql –uroot –pReset123

If it’s multiple files then just extract all content to one .sql file and directly restore it:

mysqlbinlog /var/lib/mysql-bin.000016 > /logs/allbinlog.sql

But still i recommend you have rclone backups that run via cronjob because if mariadb gets corrupted you cannot run mysqlbinlog utility.