binglog备份数据

最新推荐文章于 2025-09-14 14:06:43 发布

原创

最新推荐文章于 2025-09-14 14:06:43 发布 · 718 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#大数据 #mysql #数据库

一、开启binlog日志功能

1）编辑打开mysql配置文件/etc/mysql/my.cnf

┌──(root㉿kali)-[~]
└─# vim /etc/mysql/my.cnf

在[mysqld] 区块添加 log-bin=mysql-bin 确认是打开状态(mysql-bin 是日志的基本名或前缀名)

注意：每次服务器（数据库）重启，服务器会调用flush logs;，新创建一个binlog日志！

2）重启mysqld服务使配置生效

┌──(root㉿kali)-[~]
└─# service mysql restart

3）查看binlog日志是否开启

MariaDB [(none)]> show variables like 'log%';
+---------------------------------+---------------------------------------------------------------------------------+
| Variable_name                   | Value                                                                           |
+---------------------------------+---------------------------------------------------------------------------------+
| log_bin                         | ON

常用的binlog日志操作命令

1）查看所有binlog日志列表

MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       351 |
| mysql-bin.000002 |       328 |
| mysql-bin.000003 |       328 |
| mysql-bin.000004 |       328 |
+------------------+-----------+

2）查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      328 |              |                  |
+------------------+----------+--------------+------------------+

3）flush刷新log日志，自此刻开始产生一个新编号的binlog日志文件

MariaDB [(none)]> flush logs;
Query OK, 0 rows affected (0.007 sec)

MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       351 |
| mysql-bin.000002 |       328 |
| mysql-bin.000003 |       328 |
| mysql-bin.000004 |       375 |
| mysql-bin.000005 |       371 |
+------------------+-----------+

注意：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；

4）重置(清空)所有binlog日志

MariaDB [(none)]> reset master;
Query OK, 0 rows affected (0.009 sec)

MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       328 |
+------------------+-----------+

二、查看binlog日志内容，常用有两种方式

1）使用mysqlbinlog自带查看命令法：

注意：

-->binlog是二进制文件，普通文件查看器cat、more、vim等都无法打开，必须使用自带的mysqlbinlog命令查看

-->binlog日志与数据库文件在同目录中

-->在MySQL5.5以下版本使用mysqlbinlog命令时如果报错，就加上 “--no-defaults”选项

“ps -ef|grep mysql”查看mysql的数据存放目录，可知是/var/lib//mysql

┌──(root㉿kali)-[~]
└─# cd /var/lib/mysql
┌──(root㉿kali)-[/var/lib/mysql]
└─# ls   
aria_log.00000001  ib_logfile0        mysql-bin.000002    oupeng
aria_log_control   ibtmp1             mysql-bin.000003    performance_schema
debian-10.5.flag   multi-master.info  mysql-bin.000004
ib_buffer_pool     mysql              mysql-bin.index
ibdata1            mysql-bin.000001   mysql_upgrade_info

使用mysqlbinlog命令查看binlog日志内容，下面截取其中的一个片段分析：

┌──(root㉿kali)-[/var/lib/mysql]
└─# mysqlbinlog mysql-bin.000004
......
# at 459
#220717 23:28:36 server id 1  end_log_pos 609 CRC32 0xb14f9973  Query   thread_id=32   exec_time=0     error_code=0

SET @@session.collation_database=DEFAULT/*!*/;
insert into changan(name,sec,age) values('zhangsan','m','20'),('小红','w','20')   #执行的sql语句
/*!*/;
# at 609
#220717 23:28:36 server id 1  end_log_pos 640 CRC32 0xcc1b628a  Xid = 190   #执行的时间
......

解释：

server id 1 ：数据库主机的服务号，

end_log_pos 609： sql结束时的pos节点，

thread_id=32：线程号

2）上面这种办法读取出binlog日志的全文内容比较多，不容易分辨查看到pos点信息下面介绍一种更为方便的查询命令：

命令格式

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
#IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件)
#FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
#LI