Enable MariaDB (MySQL) binary log on Debian

This howto is tested on:

• Debian 10.0 Buster

Requirements

This howto requires:

• a MySQL or MariaDB server, as described by Install MariaDB (MySQL) on Debian.

This howto recommends:

• Adjusting the filesystem mount options, as described by Adjust filesystem options for MariaDB (MySQL).

Environment setup

Detect if sudo is available (“command” is used if not):

cmdProxy='command'
command type -f 'sudo' &>'/dev/null' && cmdProxy='sudo'

Configuration

Enable MySQL binary log to allow for replication and to ensure data recovery in case of crash, and make sure data is written to disk at commit:

${cmdProxy} tee '/etc/mysql/conf.d/binary-log.cnf' <<< "[mysqld]
# Enable binary log for point in time recovery.
# see : https://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html
# Note: set sync_binlog to 0 for better performances at the cost of perfect data integrity.
log_bin = /var/log/mysql/mysql-bin.log
sync_binlog=1

# Limit binary log size to prevent databases lock ups at binary log rotation.
# see : http://www.mysqlperformanceblog.com/2012/05/24/binary-log-file-size-matters/
max_binlog_size = 100M

# Write InnoDB data to disk at commit.
# Note: set to 2 for better performance at the cost of perfect data integrity.
# See https://serverfault.com/questions/363355/io-wait-causing-so-much-slowdown-ext4-jdb2-at-99-io-during-mysql-commit
innodb_flush_log_at_trx_commit = 1"

Reload the database server configuration:

${cmdProxy} systemctl 'restart' 'mariadb'

Thanks

• Thanks to JinnKo for his reply to IO Wait causing so much slowdown (EXT4 JDB2 at 99% IO ) During Mysql Commit (en).