MySQL——binlog复制和恢复数据

Spethen_cao

已于 2024-03-14 08:53:33 修改

阅读量900

点赞数 21

文章标签： mysql 数据库

于 2024-01-15 10:05:27 首次发布

本文链接：https://blog.csdn.net/weixin_43845075/article/details/135593430

版权

基本概念

binlog是Mysql sever层维护的一种二进制日志，与innodb引擎中的redo/undo log是完全不同的日志；其主要是用来记录对mysql数据更新或潜在发生更新的SQL语句，并以"事务"的形式保存在磁盘中

使用场景：

复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves并回放来达到master-slave数据一致的目的
数据恢复：通过mysqlbinlog工具恢复数据
增量备份

开启binlog

MySQL安装完成后，MySQL5.7版本binlog默认不开启，MySQL8默认开启binlog；

登录MySQL：（docker部署的先进入docker容器）

mysql -uroot -p

查看binlog状态sql如下：

show variables like '%log_bin%';

show variables like '%log_bin%';
+---------------------------------+-------------------------------------+
| Variable_name                   | Value                               |
+---------------------------------+-------------------------------------+
| log_bin                         | OFF                                 |
| log_bin_basename                |                                     |
| log_bin_index                   |                                     |
| log_bin_trust_function_creators | OFF                                 |
| log_bin_use_v1_row_events       | OFF                                 |
| sql_log_bin                     | ON                                  |
+---------------------------------+-------------------------------------+

如未开启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日志是否开启成功

binlog信息查询binlog开启后，可以在配置文件中查看其位置信息，也可以在myslq命令行中查看：
show variables like '%log_bin%';
+---------------------------------+-------------------------------------+
| Variable_name                   | Value                               |
+---------------------------------+-------------------------------------+
| log_bin                         | ON                                  |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                 |
| log_bin_use_v1_row_events       | OFF                                 |
| sql_log_bin                     | ON                                  |
+---------------------------------+-------------------------------------+

binlog文件开启binlog后，会在数据目录（默认）生产host-bin.n（具体binlog信息）文件及host-bin.index索引文件（记录binlog文件列表）。当binlog日志写满(binlog大小max_binlog_size，默认1G),或者数据库重启才会生产新文件，但是也可通过手工进行切换让其重新生成新的文件（flush logs）；另外，如果正使用大的事务，由于一个事务不能横跨两个文件，因此也可能在binlog文件未满的情况下刷新文件
mysql> show binary logs; //查看binlog文件列表,
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       177 |
| mysql-bin.000003 |  10343266 |
| mysql-bin.000004 |  10485660 |
| mysql-bin.000005 |     53177 |
| mysql-bin.000006 |      2177 |
| mysql-bin.000007 |      1383 |
+------------------+-----------+

查看binlog的状态：show master status可查看当前二进制日志文件的状态信息，显示正在写入的二进制文件，及当前position
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

使用binlog日志恢复数据

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

数据恢复

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

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

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

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

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

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

mysqlbinlog --no-defaults -vv --base64-output=decode-rows mysql-bin.000001 > backuptmp.sql

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

通过mysqlbinlog执行恢复操作

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

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

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

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

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

使用binlog进行数据库主从复制

复制是mysql最重要的功能之一，mysql集群的高可用、负载均衡和读写分离都是基于复制来实现的；从5.6开始复制有两种实现方式，基于binlog和基于GTID（全局事务标示符）；本文接下来将介绍基于binlog的一主一从复制；其复制的基本过程如下：

a.Master将数据改变记录到二进制日志(binary log)中
b.Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容
c.Master接收到来自Slave的IO进程的请求后，负责复制的IO进程会根据请求信息读取日志指定位置之后的日志信息，返回给Slave的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置
d.Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master从某个bin-log的哪个位置开始往后的日志内容
e.Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行

接下来使用实例演示基于binlog的主从复制：

a.配置master
        主要包括设置复制账号，并授予REPLICATION SLAVE权限，具体信息会存储在于master.info文件中，及开启binlog；
        mysql> CREATE USER 'test'@'%' IDENTIFIED BY '123456';
        mysql> GRANT REPLICATION SLAVE ON *.* TO 'test'@'%';
        mysql> show variables like "log_bin";
            +---------------+-------+
            | Variable_name | Value |
            +---------------+-------+
            | log_bin       | ON    |
            +---------------+-------+
        查看master当前binlogmysql状态：mysql> show master status;
            +------------------+----------+--------------+------------------+-------------------+
            | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
            +------------------+----------+--------------+------------------+-------------------+
            | mysql-bin.000003 |      120 |              |                  |                   |
            +------------------+----------+--------------+------------------+-------------------+
        建表插入数据：
            CREATE TABLE `tb_person` (
        	   `id` int(11) NOT NULL AUTO_INCREMENT,
               `name` varchar(36) NOT NULL,                           
               `address` varchar(36) NOT NULL DEFAULT '',    
               `sex` varchar(12) NOT NULL DEFAULT 'Man' ,
        	   `other` varchar(256) NOT NULL ,
               PRIMARY KEY (`id`)
             ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
             
        	 insert into tb_person  set name="name1", address="beijing", sex="man", other="nothing";
        	 insert into tb_person  set name="name2", address="beijing", sex="man", other="nothing";
        	 insert into tb_person  set name="name3", address="beijing", sex="man", other="nothing";
        	 insert into tb_person  set name="name4", address="beijing", sex="man", other="nothing";
    b.配置slave
        Slave的配置类似master，需额外设置relay_log参数，slave没有必要开启二进制日志，如果slave为其它slave的master，须设置bin_log
    c.连接master
        mysql> CHANGE MASTER TO
           MASTER_HOST='10.108.111.14',
           MASTER_USER='test',
           MASTER_PASSWORD='123456',
           MASTER_LOG_FILE='mysql-bin.000003',
           MASTER_LOG_POS=120;
    d.show slave status;
        mysql> show slave status\G
        *************************** 1. row ***************************
                       Slave_IO_State:   ---------------------------- slave io状态，表示还未启动
                          Master_Host: 10.108.111.14  
                          Master_User: test  
                          Master_Port: 20126  
                        Connect_Retry: 60   ------------------------- master宕机或连接丢失从服务器线程重新尝试连接主服务器之前睡眠时间
                      Master_Log_File: mysql-bin.000003  ------------ 当前读取master binlog文件
                  Read_Master_Log_Pos: 120  ------------------------- slave读取master binlog文件位置
                       Relay_Log_File: relay-bin.000001  ------------ 回放binlog
                        Relay_Log_Pos: 4   -------------------------- 回放relay log位置
                Relay_Master_Log_File: mysql-bin.000003  ------------ 回放log对应maser binlog文件
                     Slave_IO_Running: No
                    Slave_SQL_Running: No
                  Exec_Master_Log_Pos: 0  --------------------------- 相对于master从库的sql线程执行到的位置
                Seconds_Behind_Master: NULL
        Slave_IO_State, Slave_IO_Running, 和Slave_SQL_Running为NO说明slave还没有开始复制过程。
    e.启动复制
        start slave
    f.再次观察slave状态
        mysql> show slave status\G
        *************************** 1. row ***************************
                       Slave_IO_State: Waiting for master to send event -- 等待master新的event
                          Master_Host: 10.108.111.14
                          Master_User: test
                          Master_Port: 20126
                        Connect_Retry: 60
                      Master_Log_File: mysql-bin.000003
                  Read_Master_Log_Pos: 3469  ---------------------------- 3469  等于Exec_Master_Log_Pos，已完成回放
                       Relay_Log_File: relay-bin.000002                    ||
                        Relay_Log_Pos: 1423                                ||
                Relay_Master_Log_File: mysql-bin.000003                    ||
                     Slave_IO_Running: Yes                                 ||
                    Slave_SQL_Running: Yes                                 ||
                  Exec_Master_Log_Pos: 3469  -----------------------------3469  等于slave读取master binlog位置，已完成回放
                Seconds_Behind_Master: 0
        可看到slave的I/O和SQL线程都已经开始运行，而且Seconds_Behind_Master=0。Relay_Log_Pos增加，意味着一些事件被获取并执行了。
        
        最后看下如何正确判断SLAVE的延迟情况，判定slave是否追上master的binlog：
        1、首先看 Relay_Master_Log_File 和 Maser_Log_File 是否有差异；
        2、如果Relay_Master_Log_File 和 Master_Log_File 是一样的话，再来看Exec_Master_Log_Pos 和 Read_Master_Log_Pos 的差异，对比SQL线程比IO线程慢了多少个binlog事件；
        3、如果Relay_Master_Log_File 和 Master_Log_File 不一样，那说明延迟可能较大，需要从MASTER上取得binlog status，判断当前的binlog和MASTER上的差距；
        4、如果以上都不能发现问题，可使用pt_heartbeat工具来监控主备复制的延迟。
        
    g.查询slave数据，主从一致
        mysql> select * from tb_person;
            +----+-------+---------+-----+---------+
            | id | name  | address | sex | other   |
            +----+-------+---------+-----+---------+
            |  5 | name4 | beijing | man | nothing |
            |  6 | name2 | beijing | man | nothing |
            |  7 | name1 | beijing | man | nothing |
            |  8 | name3 | beijing | man | nothing |
            +----+-------+---------+-----+---------+
关于mysql复制的内容还有很多，比如不同的同步方式、复制格式情况下有什么区别，有什么特点，应该在什么情况下使用....这里不再一一介绍。

Spethen_cao

关注

21
点赞
踩
18

收藏

觉得还不错? 一键收藏
0
评论
MySQL——binlog复制和恢复数据

复制是mysql最重要的功能之一，mysql集群的高可用、负载均衡和读写分离都是基于复制来实现的；binlog是Mysql sever层维护的一种二进制日志，与innodb引擎中的redo/undo log是完全不同的日志；结束位置一般是数据被删除之前的位置。当需要恢复数据时，为了防止恢复数据后影响最新业务，需要执行flush logs，产生一个新的binlog文件，此时旧的binlog文件不会再有写入；如在数据准备中的drop操作，需要在binlog中找到该位置，并将该位置作为数据恢复的结束位置。
复制链接

扫一扫