How to Enable Binary Log in Linux

by

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

 

9 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

  4. 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

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

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

    Reply

Leave a Comment