MariaDB 开启 binlog 日志文件

1. 数据库版本

MariaDB [(none)]> select version();
+--------------------+
| version()          |
+--------------------+
| 10.8.6-MariaDB-log |
+--------------------+
1 row in set (0.000 sec)

2. 配置文件

# 在[mysqld]下添加
log-bin = /data/mariadb-binlog/mariadb-log # binlog日志路径，格式为mariadb-log.00000*,递增
log-bin-index = /data/mariadb-binlog/mariadb-log.index
expire-logs-days = 15 # binlog日志保留天数
server-id = 1
binlog-format = ROW # binlog日志有三种格式，分别是Statement、MiXED、ROW
# max-binlog-size = 100M   # binlog每个日志文件大小
log_bin_compress=1

3. 查看配置信息

MariaDB [(none)]> show variables like '%log_bin%';
+---------------------------------+----------------------------------------+
| Variable_name                   | Value                                  |
+---------------------------------+----------------------------------------+
| log_bin                         | ON                                     |
| log_bin_basename                | /data/mariadb-binlog/mariadb-log       |
| log_bin_compress                | ON                                     |
| log_bin_compress_min_len        | 256                                    |
| log_bin_index                   | /data/mariadb-binlog/mariadb-log.index |
| log_bin_trust_function_creators | OFF                                    |
| sql_log_bin                     | ON                                     |
+---------------------------------+----------------------------------------+
7 rows in set (0.001 sec)

MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-log.000002 |   102935 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SHOW BINARY LOGS;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-log.000001 |    172133 |
| mariadb-log.000002 |    978072 |
+--------------------+-----------+
2 rows in set (0.000 sec)

MariaDB [(none)]> show binlog events in 'mariadb-log.000002' limit 10;
+--------------------+-----+-------------------+-----------+-------------+-----------------------------------------------------+
| Log_name           | Pos | Event_type        | Server_id | End_log_pos | Info                                                |
+--------------------+-----+-------------------+-----------+-------------+-----------------------------------------------------+
| mariadb-log.000002 |   4 | Format_desc       |         1 |         256 | Server ver: 10.8.6-MariaDB-log, Binlog ver: 4       |
| mariadb-log.000002 | 256 | Start_encryption  |         1 |         296 |                                                     |
| mariadb-log.000002 | 296 | Gtid_list         |         1 |         339 | [0-1-368]                                           |
| mariadb-log.000002 | 339 | Binlog_checkpoint |         1 |         384 | mariadb-log.000002                                  |
| mariadb-log.000002 | 384 | Gtid              |         1 |         426 | BEGIN GTID 0-1-369                                  |
| mariadb-log.000002 | 426 | Annotate_rows     |         1 |         500 | update weather_count set nums=nums+1 where id=16570 |
| mariadb-log.000002 | 500 | Table_map         |         1 |         570 | table_id: 18 (weather_ical.weather_count)           |
| mariadb-log.000002 | 570 | Update_rows_v1    |         1 |         638 | table_id: 18 flags: STMT_END_F                      |
| mariadb-log.000002 | 638 | Xid               |         1 |         669 | COMMIT /* xid=4 */                                  |
| mariadb-log.000002 | 669 | Gtid              |         1 |         711 | BEGIN GTID 0-1-370                                  |
+--------------------+-----+-------------------+-----------+-------------+-----------------------------------------------------+
10 rows in set (0.000 sec)

4. 恢复数据

# 先把sql_log_bin设置为off,要不然会产生很多binlog日志
mysql> set sql_log_bin=off;
# 查看是否修改成功
show variables like '%log_bin%';
# 恢复数据方法一（mysqlbinlog在数据库安装目录bin目录下）
[root@localhost /]# mysqlbinlog /data/mariadb-binlog/mariadb-log.000002 | mysql -u root database_name

# 恢复数据方法二
[root@localhost /]# mysqlbinlog --start-datetime="2023-01-01 00:00:00"  --stop-datetime="2023-01-31 23:59:59" /data/mariadb-binlog/mariadb-log.000002 > /var/opt/test.sql
mysqlbinlog --start-position=100  --stop-position=389 --database=database_name  /data/mariadb-binlog/mariadb-log.000002 > /var/opt/test.sql
mysql> source /var/opt/test.sql
# 开启sql_log_bin
mysql> set sql_log_bin=on;