All Categories
Database Backup
How to Enable Binary Log for MySQL or MariaDB

How to Enable Binary Log for MySQL or MariaDB

Incremental MySQL/MariaDB backups using Bin Logs

To enable binary logs, you need to update MySQL config, ensure your MySQL user is capable of dealing with binary logs by adding needed privileges, then restart your MySQL server.

Step 1 - MySQL Config

Edit MySQL config file, usually located at /etc/my.cnf or /etc/mysql/my.cnf

Ensure you set the following (under [mysqld] section)

server_id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
max_binlog_size         = 100M
binlog_format           = mixed

For MySQL 8+, also add this

binlog_expire_logs_seconds = 864000

For MySQL 5, add

expire_log_days = 10

Step 2 - Create a user with replication capabilities

Ensure you give your MySQL user (mybackupsuser in this example) the REPLICATION SLAVE privilege from the MySQL shell.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'mybackupsuser'@'%';

Step 3 - Restart MySQL

sudo service mysql restart
Did this answer your question?
😞
😐
🤩

Last updated on August 4, 2021

How to dump stored procedures / functions How to create a Passwordless MySQL Backup