Big Data 264 - MySQL Binlog Research

Configure MySQL’s Binlog

Basic Introduction

MySQL’s Binary Log (binlog) is a log file type in MySQL that records all change operations performed on the database (excluding SELECT and SHOW queries). It is primarily used for data recovery, replication, and auditing scenarios.

Binlog’s Role

  • Data recovery: When data is lost due to database crashes or misoperations, data can be recovered by replaying binlog.
  • Master-slave replication: Binlog is the core of MySQL’s master-slave replication mechanism. By transmitting the master’s binlog to the slave and replaying it, data synchronization is achieved.
  • Data auditing: Records the specific time and operator of all data changes, facilitating auditing and analysis.
  • Incremental backup: Binlog supports recording incremental data changes. Combined with snapshot backups, you can quickly restore to any point in time.

Binlog’s Working Principle

  • Event recording: Binlog records each data change operation as an “Event”, stored in chronological order.
  • Log format: The recorded data includes transaction ID, table name, change type (INSERT, UPDATE, DELETE), specific change content, etc.
  • Write process: When users execute a transaction, data changes are first written to the binlog buffer. After the transaction commits, it’s flushed to the binlog file.
  • Log rotation: Binlog files generate new log files periodically based on configured size or time, and delete old logs (according to configuration).

Binlog’s Log Format

STATEMENT Mode

Records SQL statements.

Advantages: Smaller logs.

Disadvantages: Depends on environment; some SQL execution results may be inconsistent.

ROW Mode

Records specific row changes.

Advantages: Safe and reliable, suitable for replication.

Disadvantages: Larger logs.

MIXED Mode

Mixed mode, automatically selects the most appropriate mode (generally ROW-based).

Common Commands

Check if binlog is enabled

show variables like 'log_bin';

View binlog type

show global variables like 'binlog_format';

View detailed log configuration information

show global variables like '%log%';

MySQL data storage directory

show variables like '%dir%';

View binlog directory

show global variables like "%log_bin%";

View current server binlog files and sizes

show binary logs;

View latest binlog file name and Position

show master status;

Query binlog change information

show binlog events;

Modify MySQL

In MySQL, you need to first enable binlog writing and configure binlog-format to ROW mode.

vim /etc/my.cnf.d/mariadb-server.cnf

Configuration content:

[mysqld]
# Configure MySQL replication, need to define, don't duplicate with Canal's slaveId
server-id=1
# Enable binlog
log-bin=mysql-bin
# Choose ROW mode
binlog-format=ROW
# dwshow is the database name
binlog-do-db=dwshow

Restart MySQL

After modification, MySQL needs to be restarted. Only after restart will the configuration take effect.

systemctl restart mariadb
cd /var/lib/mysql
ll

Configure Permissions

Authorize the Canal connection MySQL account with MySQL Slave permissions. If an account already exists, you can grant directly.

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%' IDENTIFIED BY 'canal' ;

Import Data

Next, import business data into the dwshow database for testing. After importing data, observe whether there are mysql-bin.* files in the /var/lib/mysql directory.