目录

1.binlog基本概念

2.MySQL开启binlog

3.使用binlog日志恢复数据

3.1.恢复前准备工作

3.2.数据恢复

3.2.1.通过mysqlbinlog将binlog转为sql，以方便查询具体位置

3.2.2.查看生成的backuptmp.sql，最终确定需要恢复的起始位置为219，结束位置为982

3.2.3.通过mysqlbinlog执行恢复操作

前言：mysqldump和binlog都可以作为MySQL数据库备份的方式：

• mysqldump用于将整个或部分数据库导出为可执行的SQL文件，也可以用于导入或还原数据库，它可以创建一个数据库的逻辑备份，包括表结构和数据
• binlog（二进制日志）是一种事务日志，记录了对数据库进行的每个更改操作，如插入、更新、删除等。它用于增量备份和数据恢复，可以重放这些更改以还原到之前的状态

  mysqldump和binlog的主要区别如下：

  1. 数据格式：mysqldump生成的备份文件是以文本形式保存的SQL语句，可以读取和修改。binlog则是以二进制格式保存的事务日志，不能直接查看或修改，需要专门的工具（如mysqlbinlog）进行解析和分析。
  2. 备份范围：mysqldump可以备份整个数据库或特定的表，以及备份时可以选择备份的数据内容（例如只备份表结构、只备份数据等）。而binlog会记录所有的更改操作，包括对表结构和数据的更改，因此可以用来恢复到任何一个时间点之前的状态。
  3. 使用场景：mysqldump适用于定期完整备份数据库或移植数据库，以便将数据导入到不同的MySQL服务器。binlog则适用于增量备份和恢复，可以用于故障恢复、数据同步、主从复制等场景。

  使用mysqldump备份数据和还原传送门：使用mysqldump备份与恢复数据https://blog.csdn.net/DreamEhome/article/details/133580992

1.binlog基本概念

        二进制日志(binnary log)以事件形式记录了对MySQL数据库执行更改的所有操作。

        binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE、DROP等）以及表数据修改（INSERT、UPDATE、DELETE、TRUNCATE等）的二进制日志。不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但可以通过查询通用日志来查看MySQL执行过的所有语句。

binlog有两个常用的使用场景：

• 主从复制：master节点开启binlog，master把它的二进制日志传递给slaves来达到master-slave数据一致的目的（详见MySQL实操(二)——MySQL主从同步实战_醉酒的戈多的博客-CSDN博客）。
• 数据恢复：通过mysqlbinlog工具来恢复数据（本文主要讲解功能）。

  2.MySQL开启binlog

          MySQL安装完成后，MySQL5.7版本binlog默认不开启，MySQL8默认开启binlog；登录MySQL后，查看binlog状态sql如下：

  show variables like '%log_bin%';

  

  如未开启binlog日志，则可按以下步骤开启binlog日志

   开启binlog日志

  修改MySQL配置文件，linux中配置文件为my.conf，window下问my.ini，下面以centos为例演示

  • 编辑配置文件

    vim /etc/my.cnf

    • 添加配置项

      log-bin=mysql-bin
      server-id=1

      • 重启MySQL服务

        systemctl restart mysqld

        • 进入MySQL查看binlog日志是否开启成功

          

          log_bin为ON说明这个参数是开启的，就是说系统是记录了bin log的

          log_bin_basename配置了bin log的文件路径及文件前缀名

          log_bin_index配置了bin log索引文件的路径

          • 查看日志列表

            show master logs;

            

            • 根据log_bin_basename的路径查看binlog具体文件

              

               3.使用binlog日志恢复数据

              原理：当数据库发生变化时，binlog会记录数据库中的所有变化；需要恢复的时候可以根据binlog中的开始位置和结束位置还原本部分操作；结束位置一般是数据被破坏或删除之前的位置。

               3.1.恢复前准备工作

              开启binlog之后，创建测试数据库，在测试数据库中创建测试表，并写入数据：

              create database test;
              use test;
              CREATE TABLE `testuser` (
                `id` int(11) NOT NULL,
                `name` varchar(255) DEFAULT NULL,
                `age` decimal(18,2) DEFAULT NULL,
                PRIMARY KEY (`id`)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
              insert  into `testuser`(`id`,`name`,`age`) values (1,'张三',24.00);

              

               此时假如误删了test数据库，就可以用binlog来进行回复数据库、库中的表及表中的数据；

              drop database test;

              3.2.数据恢复

              当需要恢复数据时，为了防止恢复数据后影响最新业务，需要执行flush logs，产生一个新的binlog文件，此时旧的binlog文件不会再有写入；

              

              下面具体通过mysql-bin.000001来进行数据恢复 

              恢复时需要在binlog中找到两个位置：

              • 数据恢复的起始位置
              • 数据恢复的结束位置

                如在数据准备中的drop操作，需要在binlog中找到该位置，并将该位置作为数据恢复的结束位置

                3.2.1.通过mysqlbinlog将binlog转为sql，以方便查询具体位置

                mysqlbinlog --set-charset=utf-8 /var/lib/mysql/mysql-bin.000001>backuptmp.sql

                

                3.2.2.查看生成的backuptmp.sql，最终确定需要恢复的起始位置为219，结束位置为982

                

                 

                3.2.3.通过mysqlbinlog执行恢复操作

                mysqlbinlog -v /var/lib/mysql/mysql-bin.000001 --start-position=219 --stop-position=982 | mysql -uroot -p123456

                /var/lib/mysql/mysql-bin.000001  要操作binlog文件

                --start-position=219                      数据恢复的起始位置

                --stop-position=982                      数据恢复的结束位置

                mysql -uroot -p123456                 数据恢复需要登录数据库

                

                 
                 金水区手机网站建设公司  建网站公司排名  制作图片用什么软件好用  企业平台建设  武汉手机模板建站  深圳市住房和建设局网站