How to Enable Binary Log in Linux

Below is a short tutorial on how to enable binary log in Linux.

Enable the binary log file via SqlBak

If SqlBak is installed on your server and a connection is established to your MySQL Server, then you can enable binary logs via one command:

sudo sqlbak --configure-mysql --connection-id=1

If you have set several connections, then in the –connection-id parameter, you have to specify the connection ID to the database for which you want to enable binary logs. To find the connection ID, use the following command:

sudo sqlbak -sc

Enable the binary log file manually

  1. Locate the configuration file of MySQL Server my.cnf or mysql.cnf

As a rule, this file is located along the path:

/etc/mysql.cnf

or

/etc/my.cnf

If you cannot find it, please use the following command:

sudo find / -type f -name mysql.cnf
  1. Edit the configuration file of MySQL

Run:

sudo nano /etc/mysql.cnf

and add the following text to the end of the file:

[mysqld]  
server-id        = 1 
expire_logs_days = 10 
binlog_format    = row 
log_bin          = /var/log/mysql/mysql-bin
  1. Restart MySQL Server
sudo service mysql restart

 

11 thoughts on “How to Enable Binary Log in Linux”

  1. in mariadb, the bin_log will not have .log extention, it will be .000001 .000002 (six digit suffix), how to configure sqlbak in order to read these bin_log files ? Because Mariadb will ignore any extention for bin_log in my.cnf.

    Reply
    • Hi prast,

      Yes, you are right, files with bin logs will have extension 00001, 00002, etc.

      If you specify /var/log/mysql/mysql-bin.log or / var / log / mysql / mysql-bin bin log files will be named mysql-bin.00001, mysql-bin.00002, etc.

      Therefore, you can choose any option.

      Reply
  2. Hi, I’m getting the error “unknown command” when I try to run:

    sudo sqlbak —configure-mysql —connection-id=1

    Sqlbak is installed and a connection has been established.

    Reply
  3. Hello,

    Is necessary to specify ROW format for binlog (binlog_format = row)? Or mixed format is also possible for successful incremental backups of MySQL with SqlBak?

    Reply
    • Hi Sergei

      Binlogs have an unpleasant feature.

      All transactions that change data in all databases are written to one file. To restore a specific database, it is necessary to extract from this file only those records that refer specifically to this database. mysqlbinlog utility is used for this purpose. However, this utility does not pick up records if the database name was specified directly in the SQL command. For example

      INSERT INTO db_name.table_name ……

      If the database name was specified previously using “USE db_name”, then everything is OK.

      Thus, if you are sure that in SQL statements the database name is always indicated in the USE directive and never used directly in SQL statements, then you can use the MIXED format. Otherwise, you may encounter that when restoring data, not everything will be restored.

      Reply
  4. why my incremental backup doesnt work? whereas i have enabled the binary log using this command:

    sudo sqlbak –configure-mysql –connection-id=1

    Reply
  5. Hi Alexander,

    Incremental backup does not work 🙁

    “To create an incremental backup, you must enable a binary log”.

    job id: 44926

    Server version: 10.11.2-MariaDB-1:10.11.2+maria~ubu2004-log mariadb.org binary distribution
    binlog_expire_logs_seconds= 259200
    binlog_format = ROW
    binlog_row_image = FULL
    binlog_row_metadata = NO_LOG
    encrypt_binlog = OFF
    log_bin = ON
    log_bin_compress = ON
    sql_log_bin = ON

    Reply

Leave a Comment