采集MySQL Binlog(计划弃用)

本文介绍通过Logtail采集MySQL Binlog的操作步骤。

重要

日志服务后续计划不再支持MySQL Binlog采集插件,当前已不支持新用户新建采集配置,原有的采集配置仍能正常使用,建议您使用DataWorks或Flink进行采集。具体操作,请参见MySQLMySQL数据源

原理

Logtail内部实现了MySQL Slave节点的交互协议,具体流程如下所示。

  1. Logtail将自己伪装为MySQL Slave节点向MySQL master节点发送dump请求。

  2. MySQL master节点收到dump请求后,会将自身的Binlog实时发送给Logtail。

  3. Logtail对Binlog进行事件解析、过滤、数据解析等操作,并将解析好的数据上传到日志服务。

实现原理

功能特点

  • 通过Binlog增量采集数据库的更新操作数据,性能优越。支持RDS等MySQL协议的数据库。

  • 支持多种数据库过滤方式。

  • 支持设置Binlog位点。

  • 支持通过Checkpoint机制同步保存状态。

使用限制

  • Logtail 1.0.31及以上版本支持采集MySQL 8.0的Binlog。

  • MySQL必须开启Binlog,且Binlog必须为row模式(RDS默认已开启Binlog)。

    # 查看是否开启Binlog
    mysql> show variables like "log_bin";
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | log_bin       | ON    |
    +---------------+-------+
    1 row in set (0.02 sec)
    # 查看Binlog类型
    mysql> show variables like "binlog_format";
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | binlog_format | ROW   |
    +---------------+-------+
    1 row in set (0.03 sec)
  • ServerID唯一,即需要同步的MySQL的Slave ID唯一。

  • RDS限制

    • 无法直接在RDS服务器上安装Logtail,您需要将Logtail安装在能连通RDS实例的服务器上。

    • RDS备库不支持Binlog采集,您需要配置RDS主库进行采集。

  • 包括数据库版本升级、表结构变更、磁盘变更等操作,可能造成数据同步中断。如遇此场景,可尝试删除logtail的checkpoint目录后,重启logtail。如果问题仍然存在,推荐您使用DataWorks或Flink进行采集。checkpoint目录位置默认为/etc/ilogtail/checkpoint

应用场景

适用于数据量较大且性能要求较高的数据同步场景。

  • 增量订阅数据库变动,进行实时查询与分析。

  • 数据库操作审计。

  • 使用日志服务对数据库更新信息进行自定义查询分析、可视化、对接下游流计算、导入MaxCompute离线计算、导入OSS长期存储等操作。

注意事项

建议您适当放开对Logtail的资源限制以应对流量突增等情况,避免Logtail因为资源超限被强制重启,对您的数据造成不必要的风险。

您可以通过/usr/local/ilogtail/ilogtail_config.json文件修改相关参数。更多信息,请参见设置Logtail启动参数

如下示例表示将CPU的资源限制放宽到双核,将内存资源的限制放宽到2048MB。

{
    ...
    "cpu_usage_limit":2,
    "mem_usage_limit":2048,

    ...
}

数据可靠性

建议您启用MySQL服务器的全局事务ID(GTID)功能,并将Logtail升级到0.16.15及以上版本以保证数据可靠性,避免因主备切换造成的数据重复采集。

  • 数据漏采集:Logtail与MySQL服务器之间的网络长时间中断时,可能会产生数据漏采集情况。

    如果Logtail和MySQL master节点之间的网络发生中断,MySQL master节点仍会不断地产生新的Binlog数据并且回收旧的Binlog数据。当网络恢复,Logtail与MySQL master节点重连成功后,Logtail会使用自身的checkpoint向MySQL master节点请求更多的Binlog数据。但由于长时间的网络中断,它所需要的数据很可能已经被回收,这时会触发Logtail的异常恢复机制。在异常恢复机制中,Logtail会从MySQL master节点获取最近的Binlog位置,以它为起点继续采集,这样就会跳过checkpoint和最近的Binlog位置之间的数据,导致数据漏采集。

  • 数据重复采集:当MySQL master节点和slave节点之间的Binlog序号不同步时,发生了主备切换事件,可能会产生数据重复采集情况。

    在MySQL主备同步的设置下,MySQL master节点会将产生的Binlog同步给MySQL slave节点,MySQL slave节点收到后存储到本地的Binlog文件中。当MySQL master节点和slave节点之间的Binlog序号不同步时,发生了主备切换事件,以Binlog文件名和文件大小偏移量作为checkpoint的机制将导致数据重复采集。

    例如,有一段数据在MySQL master节点上位于(binlog.100, 4)(binlog.105, 4)之间,而在MySQL slave节点上位于(binlog.1000, 4)(binlog.1005, 4)之间,并且Logtail已经从MySQL master节点获取了这部分数据,将本地checkpoint更新到了(binlog.105, 4)。如果此时发生了主备切换且无任何异常发生,Logtail将会继续使用本地checkpoint(binlog.105, 4)去向新的MySQL master节点采集binlog。但是因为新的MySQL master上的(binlog.1000, 4)(binlog.1005, 4)这部分数据的序号都大于Logtail所请求的序号,MySQL master将它们返回给Logtail,导致重复采集。

创建Logtail配置

  1. 登录日志服务控制台

  2. 接入数据区域,选择MySQL BinLog-插件

  3. 选择目标Project和Logstore,单击下一步
  4. 创建机器组。
    • 如果您已有可用的机器组,请单击使用现有机器组
    • 如果您还没有可用的机器组,请执行以下操作(以ECS为例)。
      1. ECS机器页签中,通过手动选择实例方式选择目标ECS实例,单击创建

        具体操作,请参见安装Logtail(ECS实例)

        重要 如果您的服务器是与日志服务属于不同账号的ECS、其他云厂商的服务器和自建IDC时,您需要手动安装Logtail。更多信息,请参见安装Logtail(Linux系统)安装Logtail(Windows系统)

        手动安装Logtail后,您必须在该服务器上手动配置用户标识。具体操作,请参见配置用户标识

      2. 安装完成后,单击确认安装完毕
      3. 创建机器组页面,输入名称,单击下一步

        日志服务支持创建IP地址机器组和用户自定义标识机器组,详细参数说明请参见创建IP地址机器组创建用户自定义标识机器组

  5. 确认目标机器组已在应用机器组区域,单击下一步

    重要

    创建机器组后立刻应用,可能因为连接未生效,导致心跳为FAIL,您可单击自动重试。如果还未解决,请参见Logtail机器组无心跳进行排查。

  6. 设置数据源,然后单击下一步

    您可以通过表单配置方式或JSON配置方式完成数据源设置。更多信息,请参见Logtail配置详情

  7. 预览数据及创建索引,然后单击下一步
    日志服务默认开启全文索引。您也可以根据采集到的日志,手动创建字段索引,或者单击自动生成索引,日志服务将自动生成字段索引。更多信息,请参见创建索引
    重要 如果您要查询和分析日志,那么全文索引和字段索引必须至少启用一种。同时启用时,以字段索引为准。
  8. 单击查询日志,系统将跳转至Logstore查询分析页面。
    您需要等待1分钟左右,待索引生效后,才能在原始日志页签中,查看已采集到的日志。更多信息,请参见查询和分析日志

Logtail配置详情

您可以通过表单配置方式或JSON配置方式完成数据源设置。

表单配置方式

数据源设置步骤中,完成如下配置。

参数

说明

配置名称

Logtail配置名称。

数据库主机

数据库所在主机的地址。

数据库端口

数据库的端口号。

数据库用户名

登录数据库的用户名称。

需保证配置的用户具有数据库读权限以及MySQL REPLICATION权限,示例如下。

CREATE USER canal IDENTIFIED BY 'canal';  
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
-- GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%' ;
FLUSH PRIVILEGES;

数据库密码

登录数据库的账号密码。

如果安全需求较高,建议将访问用户名和密码配置为xxx,待配置同步至本地机器后,在本地文件/usr/local/ilogtail/user_log_config.json中找到对应配置进行修改。具体操作,请参见修改本地配置

重要

如果您在控制台上修改了此参数,同步至本地后会覆盖本地的配置。

ServerID

Logtail伪装成的MySQL Slave的ID。

重要

ServerID对于MySQL数据库必须唯一,否则会采集失败。

包含的表

包含的表名称(包括数据库名称,例如test_db.test_table),支持正则表达式。

  • 如果需要完全匹配,请加上前缀^,加上后缀$,例如^test_db\\.test_table$

  • 如果需要采集所有表,请设置为.*\\..*

  • 如果某个表名称不符合包含的表中的任一条件,则该表中的数据不会被采集。

忽略的表

忽略的表名称(包括数据库名称,例如test_db.test_table),支持正则表达式。

  • 如果需要完全匹配,请加上前缀^,加上后缀$,例如^test_db\\.test_table$

  • 如果某个表符合忽略的表中的任一条件,则该表中的数据不会被采集。

首次采集的Binlog文件名

首次采集的Binlog文件名。不设置时,默认从当前时间点开始采集。

如果想从指定位置开始采集,可以查看当前的Binlog文件以及文件大小偏移量,并将首次采集的Binlog文件名首次采集的Binlog文件的偏移量设置成对应的值,示例如下。

# 设置首次采集的Binlog文件名为mysql-bin.000063, 设置首次采集的Binlog文件的偏移量为0。
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000063 |       241 |
| mysql-bin.000064 |       241 |
| mysql-bin.000065 |       241 |
| mysql-bin.000066 |     10778 |
+------------------+-----------+
4 rows in set (0.02 sec)
说明

指定首次采集的Binlog文件名后,第一次采集会产生较大流量。

首次采集的Binlog文件的偏移量

首次采集的Binlog文件的偏移量。

是否附加全局事务ID

选中该选项,则上传的数据中将附加全局事务ID

是否采集insert事件的数据

选中该选项,则Logtail将采集insert事件数据。

是否采集update事件的数据

选中该选项,则Logtail将采集update事件数据。

是否采集delete事件的数据

选中该选项,则Logtail将采集delete事件数据。

是否采集DDL事件数据

选中该选项,则Logtail将采集DDL(data definition language)事件数据。

说明

该选项不支持通过包含的表忽略的表过滤。

编码方式

数据的编码方式。

是否将text类型的数据转换成字符串

选中该选项,则Logtail会将text类型的数据转换成字符串。

是否将事件数据打包成JSON格式

选中该选项,则Logtail会将事件数据以JSON格式集中打包到dataold_data两个字段中,其中old_data字段仅在row_update事件中有意义。

例如数据表有三列数据c1、c2、c3,并且取消选中是否将事件数据打包成JSON格式,则row_insert事件数据中会有c1c2c3三个字段。而选中是否将事件数据打包成JSON格式时,c1,c2,c3会被统一打包为data字段,值为{"c1":"...", "c2": "...", "c3": "..."}

重要

Logtail 0.16.19及以上版本支持该功能。

是否采集事件的元数据

选中该选项,则Logtail将采集事件的元数据。Binlog事件的元数据包括event_time、event_log_position、event_size和event_server_id。

重要

Logtail 0.16.21及以上版本支持该功能。

数据处理

处理配置,用于解析数据,例如提取字段、提取日志时间、脱敏数据、过滤日志等。可选项,您可以配置一种或多种处理方式。更多信息,请参见使用Logtail插件处理数据

JSON配置方式

插件配置中填写您的Logtail配置信息,示例如下所示。

  • inputs为数据源配置,必选项。

    重要

    一个inputs中只允许配置一个类型的数据源。

  • processors为处理配置,用于解析数据。可选项,您可以配置一种或多种处理方式。

    如果当前的inputs配置无法满足日志解析需求,您可以在插件配置中添加processors配置,即添加Logtail插件处理数据。例如提取字段、提取日志时间、脱敏数据、过滤日志等。更多信息,请参见使用Logtail插件处理数据

{
 "inputs": [
     {
         "type": "service_canal",
         "detail": {
             "Host": "************.mysql.rds.aliyuncs.com",
             "Port": 3306,
             "User" : "user1",
             "ServerID" : 56321,
             "Password": "*******",
             "IncludeTables": [
                 "user_info\\..*"
             ],
             "ExcludeTables": [
                 ".*\\.\\S+_inner"
             ],
             "TextToString" : true,
             "EnableDDL" : true
         }
     }
 ]
}

参数

类型

是否必须

说明

type

string

数据源类型,固定为service_canal

Host

string

数据库所在主机地址,默认值为127.0.0.1

Port

int

数据库端口,默认值为3306

User

string

登录数据库用户名称,默认值为root

需保证配置的用户具有数据库读权限以及MySQL REPLICATION权限,示例如下。

CREATE USER canal IDENTIFIED BY 'canal';  
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
-- GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%' ;
FLUSH PRIVILEGES;

Password

string

登录数据库的用户密码,默认值为空。

如果安全需求较高,建议将访问用户名和密码配置为xxx,待配置同步至本地机器后,在本地文件/usr/local/ilogtail/user_log_config.json中找到对应配置进行修改。具体操作,请参见修改本地配置

重要

如果您在控制台上修改了此参数,同步至本地后会覆盖本地的配置。

ServerID

int

Logtail伪装成的Mysql Slave的ID。默认值为125

重要

ServerID对于MySQL数据库必须唯一,否则会采集失败。

IncludeTables

string数组

包含的表名称(包括数据库名称,例如test_db.test_table),支持正则表达式。

  • 如果需要完全匹配,请加上前缀^,加上后缀$,例如^test_db\\.test_table$

  • 如果需要采集所有表,请设置为.*\\..*

  • 如果某个表名称不符合IncludeTables中的任一条件,则该表中的数据不会被采集。

ExcludeTables

string 数组

忽略的表名称(包括数据库名称,例如test_db.test_table),支持正则表达式。

  • 如果需要完全匹配,请加上前缀^,加上后缀$,例如^test_db\\.test_table$

  • 如果某个表符合ExcludeTables中的任一条件,则该表中的数据不会被采集。

StartBinName

string

首次采集的Binlog文件名。默认从当前时间点开始采集。

如果想从指定位置开始采集,可以查看当前的Binlog文件以及文件大小偏移量,并将StartBinNameStartBinlogPos设置成对应的值,示例如下。

# 设置StartBinName为"mysql-bin.000063", 设置StartBinlogPos为0
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000063 |       241 |
| mysql-bin.000064 |       241 |
| mysql-bin.000065 |       241 |
| mysql-bin.000066 |     10778 |
+------------------+-----------+
4 rows in set (0.02 sec)
说明

指定StartBinName后,第一次采集会产生较大流量。

StartBinlogPos

int

首次采集的Binlog文件的偏移量,默认值为0

EnableGTID

bool

上传的数据中是否附加全局事务ID

  • true(默认值):附加全局事务ID。

  • false:不附加全局事务ID。

EnableInsert

bool

是否采集insert事件的数据。

  • true(默认值):采集。

  • false:不采集。

EnableUpdate

bool

是否采集update事件的数据。

  • true(默认值):采集。

  • false:不采集。

EnableDelete

bool

是否采集delete事件的数据。

  • true(默认值):采集。

  • false:不采集。

EnableDDL

bool

是否采集DDL(data definition language)事件数据。

  • true:采集。

  • false(默认值):不采集。

说明

该选项不支持通过IncludeTablesExcludeTables过滤。

Charset

string

数据的编码方式。默认值为utf8

TextToString

bool

是否将text类型的数据转换成字符串。

  • true:转换。

  • false(默认值):不转换。

PackValues

bool

是否将事件数据以JSON格式集中打包到dataold_data两个字段中,其中old_data仅在row_update事件中有意义。

  • true:打包。

  • false(默认值):不打包。

例如数据表有三列数据c1、c2、c3,并且设置PackValuesfalse,则row_insert事件数据中会有c1c2c3三个字段。而设置PackValuestrue时,c1、c2、c3会被统一打包为data字段,值为{"c1":"...", "c2": "...", "c3": "..."}

重要

Logtail 0.16.19及以上版本支持该功能。

EnableEventMeta

bool

是否采集事件的元数据。 Binlog事件的元数据包括event_time、event_log_position、event_size和event_server_id。

  • true:采集。

  • false(默认值):不采集。

重要

Logtail 0.16.21及以上版本支持该功能。

修改本地配置

如果您没有在插件配置中输入真实的Host、User、Password等信息,可以在插件配置下发到本地后进行手动修改。

  1. 登录Logtail所在服务器。

  2. 打开/usr/local/ilogtail/user_log_config.json文件,找到service_canal关键字,修改HostUserPassword等字段。

  3. 执行以下命令重启Logtail。

    sudo /etc/init.d/ilogtaild stop; sudo /etc/init.d/ilogtaild start

问题排查

使用Logtail采集日志后,如果预览页面或查询页面无数据,您可以参见Logtail采集日志失败的排查思路进行排查。

数据库表和日志样例

例如对user_info数据库下的specialalarm表分别执行INSERTUPDATEDELETE操作,对应的数据库表结构、数据库操作及日志样例如下所示。

  • 表结构样例

    CREATE TABLE `specialalarm` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `time` datetime NOT NULL,
    `alarmtype` varchar(64) NOT NULL,
    `ip` varchar(16) NOT NULL,
    `count` int(11) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    KEY `time` (`time`) USING BTREE,
    KEY `alarmtype` (`alarmtype`) USING BTREE
    ) ENGINE=MyISAM AUTO_INCREMENT=1;
  • 数据库操作

    执行INSERT、DELETE和UPDATE三种操作。

    insert into specialalarm (`time`, `alarmType`, `ip`, `count`) values(now(), "NO_ALARM", "10.10.**.***", 55);
    delete from specialalarm where id = 4829235  ;
    update specialalarm set ip = "10.11.***.**" where id = "4829234";

    zc.specialalarm创建一个索引。

    ALTER TABLE `zc`.`specialalarm` 
    ADD INDEX `time_index` (`time` ASC);
  • 日志样例

    在查询分析页面,查看每种操作对应的日志,日志样例如下所示。

    • INSERT语句

      __source__:  10.30.**.**  
      __tag__:__hostname__:  iZbp145dd9fccu*****  
      __topic__:    
      _db_:  zc  
      _event_:  row_insert  
      _gtid_:  7d2ea78d-b631-11e7-8afb-00163e0eef52:536  
      _host_:  *********.mysql.rds.aliyuncs.com  
      _id_:  113  
      _table_:  specialalarm  
      alarmtype:  NO_ALARM  
      count:  55  
      id:  4829235  
      ip:  10.10.***.***  
      time:  2017-11-01 12:31:41
    • DELETE语句

      __source__:  10.30.**.**  
      __tag__:__hostname__:  iZbp145dd9fccu****
      __topic__:    
      _db_:  zc  
      _event_:  row_delete  
      _gtid_:  7d2ea78d-b631-11e7-8afb-00163e0eef52:537  
      _host_:  *********.mysql.rds.aliyuncs.com  
      _id_:  114  
      _table_:  specialalarm  
      alarmtype:  NO_ALARM  
      count:  55  
      id:  4829235  
      ip:  10.10.**.***
      time:  2017-11-01 12:31:41
    • UPDATE语句

      __source__:  10.30.**.**  
      __tag__:__hostname__:  iZbp145dd9fccu****  
      __topic__:    
      _db_:  zc  
      _event_:  row_update  
      _gtid_:  7d2ea78d-b631-11e7-8afb-00163e0eef52:538  
      _host_:  *********.mysql.rds.aliyuncs.com  
      _id_:  115  
      _old_alarmtype:  NO_ALARM  
      _old_count:  55  
      _old_id:  4829234  
      _old_ip:  10.10.22.133  
      _old_time:  2017-10-31 12:04:54  
      _table_:  specialalarm  
      alarmtype:  NO_ALARM  
      count:  55  
      id:  4829234  
      ip:  10.11.***.***
      time:  2017-10-31 12:04:54
    • DDL(data definition language)语句

      __source__:  10.30.**.**  
      __tag__:__hostname__:  iZbp145dd9fccu****  
      __topic__:    
      _db_:  zc  
      _event_:  row_update  
      _gtid_:  7d2ea78d-b631-11e7-8afb-00163e0eef52:539  
      _host_:  *********.mysql.rds.aliyuncs.com  
      ErrorCode:  0
      ExecutionTime:  0
      Query:  ALTER TABLE `zc`.`specialalarm` 
      ADD INDEX `time_index` (`time` ASC)
      StatusVars:

    字段

    说明

    _host_

    数据库host名称。

    _db_

    数据库名称。

    _table_

    表的名称。

    _event_

    事件类型。

    _id_

    本次采集的自增ID,从0开始,每次采集一个binlog事件后加1。

    _gtid_

    全局事务ID。

    _filename_

    Binlog文件名。

    _offset_

    Binlog文件大小偏移量,该值只会在每次commit后更新。