How to enable binary logging in MySQL or MariaDB

Binary logging turns MySQL or MariaDB into a database flight recorder, capturing committed changes for replication and point-in-time recovery. When something goes sideways (bad deploy, accidental delete, “who ran that UPDATE?”), binary logs make it possible to replay or ship changes instead of guessing.

With binary logging enabled (log_bin), the server writes a sequence of binlog files (for example, mysql-bin.000001, mysql-bin.000002) plus an index file that tracks them. Replicas (or backup tools) read these events and apply them in order; the event format is controlled by binlog_format (commonly ROW for safer replication behavior).

Binary logs can grow quickly and fill disks, so retention settings matter (expire_logs_days or binlog_expire_logs_seconds depending on version). Enabling log_bin requires a server restart, and a stable, non-zero server_id is strongly recommended to avoid replication ambiguity.

1. Query the current log_bin and server_id values.

   $ sudo mysql -e "SHOW VARIABLES LIKE 'log_bin'; SHOW VARIABLES LIKE 'server_id';"
   +---------------+-------+
   | Variable_name | Value |
   +---------------+-------+
   | log_bin       | OFF   |
   +---------------+-------+
   +---------------+-------+
   | server_id     | 0     |
   +---------------+-------+

   If sudo mysql does not authenticate on the host, use mysql -u root -p instead.

2. Pick a unique, non-zero server_id integer for the instance.

   Each server in the same replication topology needs a distinct server_id to prevent event ownership confusion.

3. Open the MySQL server config file /etc/mysql/mysql.conf.d/mysqld.cnf in an editor.

   $ sudoedit /etc/mysql/mysql.conf.d/mysqld.cnf

   For MariaDB on Debian or Ubuntu, the common server config file is /etc/mysql/mariadb.conf.d/50-server.cnf.

4. Add binary logging settings under the [mysqld] section.

   [mysqld]
   server_id = 1
   log_bin = mysql-bin
   binlog_format = ROW
   expire_logs_days = 7

   Omitting retention (expire_logs_days or equivalent) can cause unchecked binlog growth that fills the filesystem backing /var/lib/mysql.

5. Restart the database service to apply log_bin.

   $ sudo systemctl restart mysql

   On hosts running MariaDB, the unit name is commonly mariadb (sudo systemctl restart mariadb).

6. Check the service status for a clean start.

   $ sudo systemctl status mysql
   ● mysql.service - MySQL Community Server
        Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
        Active: active (running) since Fri 2025-12-12 12:34:56 UTC; 8s ago
   ##### snipped #####

7. Confirm log_bin reports ON after the restart.

   $ sudo mysql -e "SHOW VARIABLES LIKE 'log_bin';"
   +---------------+-------+
   | Variable_name | Value |
   +---------------+-------+
   | log_bin       | ON    |
   +---------------+-------+

8. Display the current binary log file and position.

   $ sudo mysql -e "SHOW MASTER STATUS;"
   +------------------+----------+--------------+------------------+-------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
   +------------------+----------+--------------+------------------+-------------------+
   | mysql-bin.000001 |      157 |              |                  |                   |
   +------------------+----------+--------------+------------------+-------------------+

   A non-empty File value indicates binlogs are being created.

9. List the available binary logs recorded by the server.

   $ sudo mysql -e "SHOW BINARY LOGS;"
   +------------------+-----------+
   | Log_name         | File_size |
   +------------------+-----------+
   | mysql-bin.000001 |       157 |
   +------------------+-----------+

10. Verify retention is set to a non-zero value.

    $ sudo mysql -e "SHOW VARIABLES LIKE 'expire_logs_days';"
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | expire_logs_days| 7     |
    +-----------------+-------+

    MySQL 8 prefers binlog_expire_logs_seconds; keeping expire_logs_days for compatibility is common when standardizing across mixed MySQL and MariaDB fleets.