How do I configure the binlog_format variable of a MySQL database to be backed up?

Updated at: 2025-02-17 10:03

Database Backup (DBS) provides the full backup, incremental backup, and data restoration capabilities. To ensure the smooth backup of a database, you must configure the database and database account as required when you configure the backup schedule. This topic describes how to configure the binlog_format variable of a MySQL database to be backed up.

Problem description

In the precheck step of configuring a backup schedule in the DBS console, information about precheck failure is displayed, which indicates that the precheck failed because the value of the binlog_format variable of the source database is not ROW. For more information, see Configure a backup schedule.

Solution

  • You must set the binlog_format variable to ROW. In ROW mode, the images of the involved rows before and after DML operations are performed are logged. This facilitates data restoration.

  • We recommend that you do not set the binlog_format variable to STATEMENT OT MIXED. ROW mode is more stable and reliable.

  • If you set the binlog_format variable to ROW, only the binary logs are changed and the database queries are not affected. However, we recommend that you kill all current database connections to ensure that ROW mode takes effect for all database connections.

Procedure

  1. Use a privileged account to run the following command in the source database to set binlog_format to ROW.

    SET GLOBAL binlog_format = 'ROW';

    You can run the following command to query the value of binlog_format of a MySQL database:

    SHOW GLOBAL VARIABLES LIKE 'binlog_format';

  2. Kill all connections to the database. Otherwise, connected processes may continue to write data in non-ROW mode, which results in inconsistent incremental data.

References

For more information about how to fix other exceptions and errors related to backup and restoration tasks, see How do I fix errors for an abnormal backup schedule in DBS? and Common errors and troubleshooting.

  • On this page (1, O)
  • Problem description
  • Solution
  • Procedure
  • References
Feedback