MySQL 开启配置binlog以及通过binlog恢复数据

目录

• • 一、binlog日志基本概念
  • 二、开启binlog日志记录
  • • 2.1、查看binlog日志记录启用状态
    • 2.2、开启配置binlog日志
    • 三、制作测试数据（可以先不执行，这里是为后续数据恢复做准备，先看数据恢复流程）
    • 四、使用binlog日志恢复数据
    • • 4.1、前置准备
      • 4.2、恢复全部数据
      • 4.3、通过指定位置区间恢复

        一、binlog日志基本概念

           binlog是MySQL sever层维护的一种二进制日志，binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE、DROP等）以及表数据修改（INSERT、UPDATE、DELETE、TRUNCATE等）的二进制日志。不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改。

        作用主要有：

        • 主从复制：在MySQL的Master节点开启binlog，Master把它的二进制日志传递给slaves并回放来达到master-slave数据一致的目的。
        • 数据恢复：通过mysqlbinlog工具来恢复数据

          二、开启binlog日志记录

          2.1、查看binlog日志记录启用状态

          MySQL安装完成后，MySQL5.7版本binlog默认是不开启的，MySQL8默认开启binlog，登录MySQL后，可以通过SHOW VARIABLES LIKE '%log_bin%';命令查看是否开启binlog。

          # 登录 mysqlmysql -h127.0.0.1 -P3306 -uroot -p123456

          # 查看是否开启binlogmysql> SHOW VARIABLES LIKE '%log_bin%';

          log_bin 的Value如果为ON代表开启，如果为OFF代表关闭，我这里使用的是MySQL8.0默认是开启的，如果没有开启可以通过下面方式开启：

          2.2、开启配置binlog日志

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

          • 编辑配置文件

            # 在centos中mysql的配置文件一般都在/etc/mysql目录下，如果不在可以通过 find / -name "my.cnf" 查找vi /etc/mysql/my.cnf

            • 添加配置

              # 服务IDserver-id=1# binlog 配置 只要配置了log_bin地址 就会开启log_bin = /var/lib/mysql/mysql_bin# 日志存储天数 默认0 永久保存# 如果数据库会定期归档，建议设置一个存储时间不需要一直存储binlog日志，理论上只需要存储归档之后的日志expire_logs_days = 30# binlog最大值max_binlog_size = 1024M# 规定binlog的格式，binlog有三种格式statement、row、mixad，默认使用statement，建议使用row格式binlog_format = ROW# 在提交n次事务后，进行binlog的落盘，0为不进行强行的刷新操作，而是由文件系统控制刷新日志文件，如果是在线交易和账有关的数据建议设置成1，如果是其他数据可以保持为0即可sync_binlog = 1

              • 重启MySQL服务使配置生效

                systemctl restart mysqld

                • 登录MySQL查看配置是否生效

                  mysql> SHOW VARIABLES LIKE '%log_bin%';

                  log_bin为ON代表MySQL已经开启binlog日志记录

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

                  log_bin_index配置了binlog索引文件的路径

                  • 查看日志列表

                    mysql> SHOW MASTER LOGS;

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

                      ls -l /var/lib/mysql/mysql_bin.*

                      三、制作测试数据（可以先不执行，这里是为后续数据恢复做准备，先看数据恢复流程）

                      登录MySQL后创建库、创建表、在插入一些数据

                      • 登录数据库

                        mysql -h127.0.0.1 -P3306 -uroot -p123456

                        • 创建库

                          CREATE DATABASE binlog_test_db;

                          • 切换到自己创建的数据库

                            USE binlog_test_db;

                            • 创建表

                              DROP TABLE IF EXISTS `binlog_test_table`;CREATE TABLE `binlog_test_table`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `nick_name` varchar(255) DEFAULT NULL,  `create_time` datetime(0) NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB ;

                              • 插入数据

                                INSERT INTO `binlog_test_table` VALUES (1, 'Alia', NOW());INSERT INTO `binlog_test_table` VALUES (2, 'Kerwin', NOW());INSERT INTO `binlog_test_table` VALUES (3, 'Hilaria', NOW());INSERT INTO `binlog_test_table` VALUES (4, 'Coco', NOW());

                                • 查看创建的库

                                  mysql> SHOW DATABASES;

                                  • 查看创建的表和数据

                                    # 切换到binlog_test_db库mysql> USE binlog_test_db;# 查看库中所有的表mysql> SHOW TABLES;# 查看表中的数据mysql> SELECT * FROM binlog_test_table;

                                    四、使用binlog日志恢复数据

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

                                    4.1、前置准备

                                    自己测试的话先要确保有一个干净的环境，避免出现一些奇怪问题导致浪费时间，这里先做一些前置准备，也就是说自己的binlog文件中最好只有当前测试的数据记录，避免出现干扰。

                                    • 1、登录MySQL执行下面命令刷新log日志，自此刻开始产生一个新编号的binlog日志文件

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

                                    • 2、查看当前的binlog文件

                                      ls -l /var/lib/mysql/mysql_bin.*

                                      我这里在刷新binlog之前是mysql_bin.000003，刷新后新生成了一个mysql_bin.000004，后续日志都会记录在这个新的文件中。

                                      4.2、恢复全部数据

                                      在恢复数据前首先要制作好测试数据，恢复全部数据就是将一个完整的binlog都执行，比如我们在制作测试数据时创建库、创建表、插入数据等操作都会记录在binlog中，使用这个binlog恢复数据等于将我们上面的操作又执行了一次。

                                      数据恢复演示流程：

                                      • 1、执行4.1前置准备中的操作，生成一个新的binlog文件，假设我这里生成新的文件叫做mysql_bin.000004，后续数据的操作日志都会记录在这个日志文件中。

                                      • 2、准备数据，直接执行本文第三点制作测试数据中的SQL即可。

                                      • 3、将mysql_bin.000004这个binlog日志归档，因为我们需要通过mysql_bin.000004这个日志恢复全部数据，所以要在删除数据库之前归档，如果删除数据库语句也被记录在mysql_bin.000004中，那么执行完mysql_bin.000004库又会被删除了。

                                        # 登录数据库执行刷新log日志，自此刻开始产生一个新编号的binlog日志文件mysql> FLUSH LOGS;

                                      • 4、删除数据库

                                        mysql> DROP DATABASE binlog_test_db;

                                        这里可以看到binlog_test_db库已经被删除了。

                                      • 5、通过mysqlbinlog执行恢复全部

                                        # mysqlbinlog是MySQL自带的一个工具，一般在安装MySQL时mysqlbinlog也会被放入可执行目录下，可以直接执行# 如果不能直接使用mysqlbinlog命令，可以通过 find / -name "mysqlbinlog" 查找一下这个工具在什么位置# 然后指定全路径执行即可：如 /usr/bin/mysqlbinlog --no-defaults ...mysqlbinlog --no-defaults /var/lib/mysql/mysql_bin.000004 | mysql -h127.0.0.1 -P3306 -uroot -p123456

                                      • 6、查看数据恢复情况

                                        # 查看全部库mysql> SHOW DATABASES;# 切换到binlog_test_db库mysql> USE binlog_test_db;# 查看库中所有的表mysql> SHOW TABLES;# 查看表中的数据mysql> SELECT * FROM binlog_test_table;

                                        到这里可以看到数据已经全部恢复，但是还存在几个问题：

                                        • 1、如果存在多个binlog文件如何恢复全部数据
                                          • 存在多个binlog文件依次恢复即可，必须按照日志序号来恢复，假设有三个日志文件序号是000001、000002、000003，那么第一个恢复的一定是000001，第二个恢复000002，最后恢复000003。
                                          • 2、如果我们在同一个日志文件中也进行了删库操作怎么恢复
                                            • 全部恢复原理就是将之前做过的操作全做一次，有删库操作自然也会在操作一次，这样肯定是违背我们的需求的，解决方法其实就是不去执行某个节点后的操作就行，比如删库操作是在节点5执行，那么我们只恢复到节点4即可，在后续会详细说明。

                                              4.3、通过指定位置区间恢复

                                              和恢复全部数据不同的是可以指定一个位置区间恢复数据，这样如果删除库操作和数据插入操作都在同一个日志文件中也可以进行数据恢复，而且很灵活。

                                              数据恢复演示流程：

                                              • 1、执行4.1前置准备中的操作，生成一个新的binlog文件，假设我这里生成新的文件叫做mysql_bin.000006，后续数据的操作日志都会记录在这个日志文件中。

                                              • 2、准备数据，直接执行本文第三点制作测试数据中的SQL即可。

                                              • 3、删除数据库

                                                mysql> DROP DATABASE binlog_test_db;

                                                这里可以看到binlog_test_db库已经被删除了。

                                              • 4、将mysql_bin.000006这个binlog日志归档，为了防止恢复数据后影响最新业务，需要执行flush logs，产生一个新的binlog文件，此时旧的binlog文件不会再有写入。

                                                # 登录数据库执行刷新log日志，自此刻开始产生一个新编号的binlog日志文件mysql> FLUSH LOGS;

                                              • 5、通过mysqlbinlog将binlog转为sql，以方便查询具体位置

                                                mysqlbinlog --set-charset=utf-8 /var/lib/mysql/mysql_bin.000006>binlog_000006.sql

                                              • 6、查看生成的binlog_000006.sql，确定需要恢复的起始位置结束位置还有起始时间和结束时间

                                                # 打开 binlog_000006.sql 文件，搜索自己的创建库语句确认起始位置和时间，在搜索删除库语句确认结束位置和时间vi binlog_000006.sql

                                                确认开始位置为232，开始时间为 23年9月27日 17:17:23

                                                确认结束位置为2220，结束时间为 23年9月27日 17:18:36

                                              • 7、通过mysqlbinlog执行恢复操作

                                                # 通过位置区间恢复 mysqlbinlog --no-defaults /var/lib/mysql/mysql_bin.000006 --start-position=232 --stop-position=2220 | mysql -h127.0.0.1 -P3306 -uroot -p123456

                                                /var/lib/mysql/mysql_bin.000006 ： 要操作binlog文件

                                                –start-position=232 ：数据恢复的起始位置

                                                –stop-position=2220 ：数据恢复的结束位置

                                                mysql -h127.0.0.1 -P3306 -uroot -p123456 ： 数据恢复需要登录数据库