binlog is a binary file saved by mysql, which can be used to recover data, synchronize data, etc.

If you want Mysql to generate binlog automatically, you need to turn it on in the configuration file.

If you don’t know where your mysql default configuration file is? You can find it with the following command.

1
2
3

mysql --help | grep 'Default options' -A 1

/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

vim /etc/my.cnf:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

[mysqld]
# Set the log path, note that the route requires mysql users to have permission to write
log_bin=/var/lib/mysql/mysql-bin
# Set binlog cleanup time
expire_logs_days = 7
# Size of each log file
max_binlog_size = 100m
# Cache Size
binlog_cache_size = 4m
# Maximum binlog cache size
max_binlog_cache_size = 512m
server-id=1 
# Set the log in three formats: STATEMENT, ROW, and MIXED.
binlog_format=row

Restart mysql after completing the changes.

Exporting binlog files

First determine which binlog file is currently in use.

1
2
3
4
5
6
7
8

mysql -uroot -p 
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1259 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Export:

1

mysqlbinlog /var/lib/mysql/mysql-bin.000001 -v > bak.sql # bak.sql is my custom file name

Parameters:

  • -database Specify database
  • -v Binlog log in row format
  • -start-position Specify the start position
  • -stop-position Specify the end position
  • -start-datetime Specify the extraction start time
  • -set-charset Set character encoding
  • -read-from-remote-server Read from remote server
  • -h Get Help

Importing binlog files

Use the exported binlog file to recover data in another database.

1

mysql -uroot -p <Here you can specify the table> < bak.sql

The binlog file only records the SQL modification operations, not the SELECT operations.