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
- 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
- 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
- Restart MySQL Server
sudo service mysql restart
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.
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.
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.
Hi,
There was a typo in the example, we have fixed it. Could you please try the following one:
sudo sqlbak --configure-mysql --connection-id=1I cannot use it, as we backup a AWS database and we cannot change the needed settings 🙁
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?
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.
why my incremental backup doesnt work? whereas i have enabled the binary log using this command:
sudo sqlbak –configure-mysql –connection-id=1
Hi dita,
To resolve the issue, we need more details. Could you please contact our support team https://sqlbak.com/support?
Thank you!
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
Hello Serg,
Could you please try to execute these two different queries and check the results:
SHOW VARIABLES LIKE ‘log_bin’
SHOW VARIABLES LIKE ‘binlog_format’
Thank you!