DocsICP FilingConsole
官方文档
首页

Collect MySQL binary logs (to be deprecated)

更新时间：
复制 MD 格式
产品详情
我的收藏

You can use Logtail to collect MySQL binary logs and upload the parsed data to Simple Log Service for query, analysis, and downstream processing.

Important

Simple Log Service no longer supports the Logtail plug-in for collecting MySQL binary logs. New users can no longer create Logtail configurations to collect MySQL binary logs, but existing Logtail configurations remain unaffected. We recommend that you use DataWorks or Flink to collect MySQL binary logs. For more information, see MySQL connector and MySQL data source.

Principle

Logtail acts as a secondary MySQL node to communicate with the primary MySQL node as follows:

  1. Logtail acts as a secondary MySQL node and sends a dump request to the primary MySQL node.

  2. After the primary MySQL node receives the dump request, the node sends binary logs to Logtail in real time.

  3. Logtail performs operations such as event parsing, event filtering, and data parsing on the binary logs. Then, Logtail uploads the parsed data to Simple Log Service.

实现原理

Features

  • Supports incremental collection of binary logs to capture database update operations. Compatible with MySQL databases, including Relational Database Service (RDS) databases that use the MySQL protocol.

  • Supports multiple methods for filtering database data.

  • Supports configurable binary log checkpoints.

  • Supports checkpoint-based synchronization of data storage status.

Limits

  • Logtail V1.0.31 and later can collect binary logs from MySQL 8.0 databases.

  • The binary logging feature must be enabled for your MySQL database, and the binlog_format parameter must be set to ROW for the database. By default, the feature is enabled for an RDS database. 

    # Check if binlog is enabled
mysql> show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.02 sec)
# Check the binlog format
mysql> show variables like "binlog_format";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.03 sec)

  • The ID of the secondary MySQL node as which Logtail acts must be unique for your database.

  • Limits on RDS databases:

    • You cannot install Logtail on the server that hosts your RDS instance. You must install Logtail on a server that can connect to the instance.

    • You cannot collect binary logs from a secondary RDS database. You must configure your primary RDS database to collect binary logs.

  • Database version upgrades, table schema changes, or disk changes can interrupt data synchronization. If this occurs, delete the Logtail checkpoint directory and restart Logtail. If the issue persists, consider using DataWorks or Flink for data collection. The default checkpoint directory is /etc/ilogtail/checkpoint.

Scenarios

Logtail is suitable for scenarios that require high-performance synchronization of large volumes of data.

  • Real-time query and analysis of incremental database data.

  • Database operation auditing.

  • Downstream processing of database change data through Simple Log Service, including custom query and analysis, visualization, stream processing via downstream nodes, batch processing in MaxCompute, and long-term storage in Object Storage Service (OSS).

Usage notes

We recommend that you relax the Logtail resource usage limits to handle traffic spikes and mitigate data risks. Logtail may be forcefully restarted if these limits are exceeded.

You can modify the related parameters in the /usr/local/ilogtail/ilogtail_config.json file. For more information, see Configure the startup parameters of Logtail.

For example, you can set the CPU utilization limit to two cores and the memory usage limit to 2,048 MB: 

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

    ...
}

Data reliability

We recommend that you enable the global transaction identifier (GTID) feature on your MySQL server and upgrade Logtail to V0.16.15 or later. This prevents duplicate data collection after a primary/secondary switchover and ensures data reliability.

  • Incomplete data collection: If Logtail is disconnected from your MySQL server for an extended period, some data may not be collected.

    When Logtail is disconnected from your primary MySQL node, the primary node continues generating binary logs and deleting expired ones. After Logtail reconnects, it uses a local checkpoint to request binary logs from the primary node. However, if the disconnection lasts long enough, the logs generated after the checkpoint may have been deleted. In this case, a recovery mechanism identifies the most recent binary log on the primary node and resumes collection from there. The logs between the checkpoint and the most recent binary log are lost, resulting in incomplete data collection.

  • Repeated data collection: If a primary/secondary switchover occurs when binary log sequence numbers are inconsistent between the primary and secondary MySQL nodes, binary logs may be collected more than once.

    If you configure primary/secondary synchronization for your MySQL database, the primary node automatically synchronizes binary logs to the secondary node, which stores them in local binary log files. If the sequence numbers are inconsistent between the primary and secondary nodes and a switchover occurs, logs may be collected more than once because the checkpoint mechanism is based on binary log file names and offsets.

    For example, a segment of data is located between (binlog.100, 4) and (binlog.105, 4) on the MySQL primary node, and between (binlog.1000, 4) and (binlog.1005, 4) on the MySQL secondary node. Logtail has already collected this data from the MySQL primary node and updated the local checkpoint to (binlog.105, 4). If a failover occurs and proceeds without errors, Logtail will continue to use the local checkpoint (binlog.105, 4) to collect binlogs from the new MySQL primary node. However, because the positions of this data on the new MySQL primary node, from (binlog.1000, 4) to (binlog.1005, 4), are greater than the position requested by Logtail, the primary node returns the data to Logtail, resulting in duplicate collection.

Create a Logtail configuration

  1. Log on to the Simple Log Service console.

  2. In the Import Data section, select MySQL BinLog - Plug-in.

  3. Select the destination project and Logstore, and then click Next.

  4. Create a machine group.

    • If you have a machine group, click Use Existing Machine Groups.

    • If you do not have a machine group, follow these steps to create one. This example uses an ECS instance.

      1. On the ECS Instance tab, select the target ECS instance and click Create.

        For more information, see Install Logtail on ECS instances.

        Important

        If your server is an ECS instance from a different Alibaba Cloud account, a server from another cloud provider, or a server in your own data center, you must install Logtail manually. For more information, see Install Logtail on a Linux server or Install Logtail on a Windows server.

        After you manually install Logtail, you must configure a user identifier on the server. For more information, see Configure a user identifier.

      2. After the installation is complete, click Complete Installation.

      3. On the Create Machine Group page, enter a Name and click Next.

        Log Service supports IP address-based machine groups and custom identifier-based machine groups. For more information about the parameters, see Create an IP address-based machine group and Create a custom identifier-based machine group.

  5. Confirm that the machine group is displayed in the Applied Server Groups section and click Next.

    Important

    If you apply a machine group immediately after you create the machine group, the heartbeat status of the machine group may be FAIL. This issue occurs because the machine group is not connected to Simple Log Service. To resolve this issue, you can click Automatic Retry. If the issue persists, see What do I do if no heartbeat connections are detected on Logtail?

  6. Configure a data source and click Next.

    You can specify a data source by using form configuration or editor configuration in JSON. For more information, see Logtail configuration details.

  7. Preview the data, create indexes, and then click Next.

    By default, Log Service enables full-text indexing. You can also manually create field indexes based on the collected logs, or click Automatic Index Generation to have Log Service create them automatically. For more information, see Create indexes.

    Important

    To query and analyze logs, you must enable either full-text indexing or a field index. If you enable both, queries use the field indexes.

  8. Click Query Log to go to the Query & Analysis page for the Logstore.

    You may need to wait about one minute for the indexes to become effective. You can then view the collected logs on the Raw Logs tab. For more information, see Quick start for log query and analysis.

Logtail configuration details

You can configure a data source by using form configuration or JSON editor configuration.

Form configuration

In the Configure Data Source step, configure the following parameters.

Parameter

Description

Configuration Name

The name of the Logtail configuration.

Database Host

The address of the host on which the database resides.

Database Port

The port number of the database.

Database Username

The username of the account that is used to log on to the database.

Make sure that the account has read permissions on the database and the MySQL REPLICATION permission. Example: 

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

Database Password

The password of the account that is used to log on to the database.

For enhanced security, set the username and password to a placeholder value such asxxx. After the configuration is synchronized to the Logtail server, update the credentials in the local file at/usr/local/ilogtail/user_log_config.json. For more information, see Modify local configurations.

Important

If you modify this parameter in the Simple Log Service console, the parameter setting in the Logtail configuration on the Logtail server is overwritten after the modification is synchronized to the server.

ServerID

The ID of the secondary MySQL node as which Logtail acts.

Important

The value of the ServerID parameter must be unique for your database. Otherwise, data fails to be collected.

Included Tables

The names of the tables from which data is collected. When you specify the exact name of a table, you must include the name of the database to which the table belongs and the name of the table. Example: test_db.test_table. You can also specify a regular expression for this parameter.

  • If you want to implement exact match, add ^ to the start of the regular expression and append $ to the end. Example: ^test_db\\.test_table$.

  • If you want to collect data from all tables, specify .*\\..*.

  • If a table does not match any rules in Included Tables, Logtail does not collect its data.

Ignored Tables

The names of the tables from which data is not collected. When you specify the exact name of a table, you must include the name of the database to which the table belongs and the name of the table. Example: test_db.test_table. You can also specify a regular expression for this parameter.

  • If you want to implement exact match, add ^ to the start of the regular expression and append $ to the end. Example: ^test_db\\.test_table$.

  • If a table matches any rules in Ignored Tables, Logtail does not collect its data.

Binary Log File Name of First Collection

The name of the binary log file from which data is collected for the first time. If you do not configure this parameter, Logtail starts to collect data from the current point in time.

To start collection from a specific position, check the current Binlog file and offset, and then set the Binary Log File Name of First Collection and Binary Log File Offset of First Collection parameters accordingly. Example: 

# Set Initial Binlog File Name to mysql-bin.000063 and Initial Binlog File Offset to 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)
Note

If you specify Binary Log File Name of First Collection, the initial collection may generate a large volume of network traffic.

Binary Log File Offset of First Collection

The offset of the binary log file from which data is collected for the first time.

Add GTID

Specifies whether to add GTIDs to the data that is uploaded to Simple Log Service. For more information, see GTID Format and Storage.

Collect INSERT Events

Specifies whether to collect the data on INSERT events.

Collect UPDATE Events

Specifies whether to collect the data on UPDATE events.

Collect DELETE Events

Specifies whether to collect the data on DELETE events.

Collect DDL Events

Specifies whether to collect the data on data definition language (DDL) events.

Note

DDL events do not support table filtering with Included Tables and Ignored Tables.

Encoding Method

The encoding format of data.

Convert Text to String

Specifies whether to convert the data of the text type to the string type.

Compress Events into JSON

Specifies whether to pack event data in the JSON format. If you select Compress Events into JSON, Logtail packs event data into the data and old_data fields in the JSON format. The old_data field is available only for ROW_UPDATE events.

For example, a table has three columns: c1, c2, and c3. If you do not select Compress Events into JSON, an insert event contains three separate fields:c1,c2, andc3. If you select Compress Events into JSON, the columns are packed into a singledata field with a value of{"c1":"...", "c2": "...", "c3": "..."}.

Important

This parameter is available only for Logtail V0.16.19 and later.

Collect Event Metadata

Specifies whether to collect the metadata of events. The metadata of binary log events includes event_time, event_log_position, event_size, and event_server_id.

Important

This parameter is available only for Logtail V0.16.21 and later.

Data Processing

The configuration that is used to process data. For example, you can configure this parameter to extract fields, extract log time, mask data, and filter logs. This parameter is optional. You can specify one or more data processing methods. For more information, see Use Logtail plug-ins to process data.

Editor configuration in JSON

In the Plug-in Configuration field, enter your Logtail configuration in JSON format. The following example shows a basic configuration.

  • inputs is required and is used to configure the data source settings for the Logtail configuration.

    Important

    You can specify only one type of data source in inputs.

  • processors is optional and is used to configure the data processing settings for the Logtail configuration to parse data. You can specify one or more processing methods.

    If your logs cannot be parsed based only on the setting of inputs, you can configure processors in the Plug-in Configuration field to add plugins for data processing. For example, extract fields, extract log time, mask data, and filter logs. For more information, see Logtail plugins for data processing. 

{
 "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
         }
     }
 ]
}

Parameter

Type

Required

Description

type

string

Yes

The type of the data source. Set the value to service_canal.

Host

string

No

The address of the host on which the database resides. Default value: 127.0.0.1.

Port

int

No

The port number of the database. Default value: 3306.

User

string

No

The username of the account that is used to log on to the database. Default value: root.

Make sure that the account has read permissions on the database and the MySQL REPLICATION permission. Example: 

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

No

The password of the account that is used to log on to the database. This parameter is empty by default.

For enhanced security, set the username and password to a placeholder value such asxxx. After the configuration is synchronized to the Logtail server, update the credentials in the local file at/usr/local/ilogtail/user_log_config.json. For more information, see Modify local configurations.

Important

If you modify this parameter in the Simple Log Service console, the parameter setting in the Logtail configuration on the Logtail server is overwritten after the modification is synchronized to the server.

ServerID

int

No

The ID of the secondary MySQL node as which Logtail acts. Default value: 125.

Important

The value of the ServerID parameter must be unique for your database. Otherwise, data fails to be collected.

IncludeTables

string array

Yes

The names of the tables from which data is collected. When you specify the exact name of a table, you must include the name of the database to which the table belongs and the name of the table. Example: test_db.test_table. You can also specify a regular expression for this parameter.

  • If you want to implement exact match, add ^ to the start of the regular expression and append $ to the end. Example: ^test_db\\.test_table$.

  • If you want to collect data from all tables, specify .*\\..*.

  • If the name of a table does not meet one of the conditions that are specified by the IncludeTables parameter, the data in the table is not collected.

ExcludeTables

string array

No

The names of the tables from which data is not collected. When you specify the exact name of a table, you must include the name of the database to which the table belongs and the name of the table. Example: test_db.test_table. You can also specify a regular expression for this parameter.

  • If you want to implement exact match, add ^ to the start of the regular expression and append $ to the end. Example: ^test_db\\.test_table$.

  • If the name of a table meets one of the conditions that are specified by the ExcludeTables parameter, the data in the table is not collected.

StartBinName

string

No

The name of the binary log file from which data is collected for the first time. By default, Logtail starts to collect data from the current point in time.

If you want Logtail to collect data from a specific position, set the StartBinName parameter to the name of the binary log file from which you want Logtail to collect data and set the StartBinlogPos parameter to the offset of the file. Example: 

# Set StartBinName to "mysql-bin.000063" and StartBinlogPos to 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)
Note

If you configure the StartBinName parameter, a large volume of traffic is generated the first time data is collected from the file.

StartBinlogPos

int

No

The offset of the binary log file from which data is collected for the first time. Default value: 0.

EnableGTID

bool

No

Specifies whether to add GTIDs to the data that is uploaded to Simple Log Service. For more information, see GTID Format and Storage. Valid values:

  • true (default)

  • false

EnableInsert

bool

No

Specifies whether to collect the data on INSERT events. Valid values:

  • true (default)

  • false

EnableUpdate

bool

No

Specifies whether to collect the data on UPDATE events. Valid values:

  • true (default)

  • false

EnableDelete

bool

No

Specifies whether to collect the data on DELETE events. Valid values:

  • true (default)

  • false

EnableDDL

bool

No

Specifies whether to collect the data on DDL events. Valid values:

  • true

  • false (default)

Note

If you set the EnableDDL parameter to true, the values of the IncludeTables and ExcludeTables parameters do not take effect. The values are used for data filtering.

Charset

string

No

The encoding format of data. Default value: utf8.

TextToString

bool

No

Specifies whether to convert the data of the text type to the string type. Valid values:

  • true

  • false (default)

PackValues

bool

No

Specifies whether to pack event data in the JSON format. If you set the PackValues parameter to true, Logtail packs event data into the data and old_data fields in the JSON format. The old_data field is available only for ROW_UPDATE events. Valid values:

  • true

  • false (default)

For example, a table has three columns: c1, c2, and c3. If you setPackValues tofalse, an insert event contains three separate fields:c1,c2, andc3. If you setPackValues totrue, these columns are packed into a singledata field with a value of{"c1":"...", "c2": "...", "c3": "..."}.

Important

This parameter is available only for Logtail V0.16.19 and later.

EnableEventMeta

bool

No

Specifies whether to collect the metadata of events. The metadata of binary log events includes event_time, event_log_position, event_size, and event_server_id.

  • true

  • false (default)

Important

This parameter is available only for Logtail V0.16.21 and later.

Modify the configurations on a Logtail server

If you did not provide values for the Host, User, and Password parameters in Plug-in Configuration, you can edit the configuration file manually on the server.

  1. Log on to the server on which Logtail is installed.

  2. Open the /usr/local/ilogtail/user_log_config.json file, find the service_canal keyword, and then modify the parameters such as Host, User, and Password.

  3. Run the following command to restart Logtail: 

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

Troubleshooting

If no data appears on the preview or query page after you configure Logtail to collect logs, see Troubleshoot Logtail collection failures.

Sample database table and logs

The following examples show INSERT, UPDATE, and DELETE operations on the specialalarm table in the user_info database, along with the table schema, sample operations, and resulting logs.

  • Table schema

    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;

  • Database operations

    Perform the INSERT, DELETE, and UPDATE operations. 

    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";

    Create an index for zc.specialalarm.

    ALTER TABLE `zc`.`specialalarm` 
ADD INDEX `time_index` (`time` ASC);

  • Collected logs

    You can view the collected logs for each operation on the query and analysis page of the specified Logstore. Examples:

    • INSERT statement 

      __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 statement 

      __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 statement 

      __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 statement 

      __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:

    Field

    Description

    _host_

    The hostname of the database.

    _db_

    The name of the database.

    _table_

    The name of the table.

    _event_

    The type of the event.

    _id_

    The auto-increment ID. IDs start from 0 and increment by 1 each time the data on a binary log event is collected.

    _gtid_

    The GTID.

    _filename_

    The name of the binary log file.

    _offset_

    The offset of the binary log file. The value is updated only when a COMMIT operation is performed.

Previous：Collect MySQL query resultsNext：Collect PostgreSQL query results