Collect MySQL binlogs with Logtail-Simple Log Service(SLS)-阿里云帮助中心

This topic describes how to use Logtail to collect MySQL binary logs.

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. The following list describes the communication process:

Logtail acts as a secondary MySQL node and sends a dump request to the primary MySQL node.
After the primary MySQL node receives the dump request, the node sends binary logs to Logtail in real time.
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

Binary logs can be incrementally collected. This way, you can collect data related to the update operations that are performed on your databases. MySQL databases are supported, such as Relational Database Service (RDS) databases that use the MySQL protocol.
Multiple methods are provided to filter data in databases.
Binary log checkpoints can be configured.
Checkpoints can be used to synchronize the status of data storage.

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

If you want to synchronize large amounts of data and require high performance, you can use Logtail to collect MySQL binary logs.

Query and analyze the incremental data of databases in real time.
Audit the operations that are performed on databases.
Use Simple Log Service to perform operations on the update-related data of databases. For example, you can perform custom query and analysis on the data, visualize the data, push the data to downstream nodes for stream processing, import the data to MaxCompute for batch processing, and import the data to Object Storage Service (OSS) for long-term storage.

Usage notes

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

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.

You can relax the limit on CPU utilization to two cores and the limit on memory usage to 2,048 MB. Example:

{
    ...
    "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 data from being repeatedly collected after a primary/secondary switchover is triggered on your database and ensures data reliability.

Incomplete data collection: If Logtail is disconnected from your MySQL server for a long period of time, some data may not be collected.
When Logtail is disconnected from your primary MySQL node, the primary MySQL node keeps generating binary logs and deletes expired binary logs. After Logtail is re-connected to the primary MySQL node, Logtail uses a local checkpoint to request binary logs from the primary MySQL node. However, if the network disconnection lasts long, the logs that are generated after the checkpoint may be deleted. In this case, the recovery mechanism is triggered. The mechanism identifies the most recent binary log on the primary MySQL node to resume collection. The logs that are generated between the checkpoint and the most recent binary log are not collected. This leads to incomplete data collection.
Repeated data collection: If a primary/secondary switchover is triggered when the sequence numbers of binary logs are inconsistent between your primary and secondary MySQL node, binary logs may be repeatedly collected.

If you configure primary/secondary synchronization for your MySQL database, the primary MySQL node automatically synchronizes binary logs to the secondary node. The secondary node stores the logs to local binary log files. If the sequence numbers are inconsistent between the primary and secondary MySQL nodes and a primary/secondary switchover is triggered, logs may be repeatedly collected. This issue occurs because the checkpoint mechanism is based on the names of binary log files and the offsets of the files.

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

Log on to the Simple Log Service console.
In the Import Data section, select MySQL BinLog - Plug-in.
Select the destination project and Logstore, and then click Next.
Create a machine group.
- If you have an existing machine group, click Use Existing Machine Groups.
- If you do not have a machine group, perform the following 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
    You must manually install Logtail if your server is an ECS instance from a different Alibaba Cloud account, a server from another cloud provider, or a server in a data center. 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. In the Create Machine Group step, enter a Name and click Next.
    
    Log Service allows you to create machine groups based on IP addresses or custom identifiers. For more information about the parameters, see Create an IP address-based machine group and Create a custom identifier-based machine group.
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?
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.
Preview the data, create indexes, and then click Next.

By default, full-text indexing is enabled. 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 field indexes. If both are enabled, field indexes take precedence.
Click Query Log to go to the query and analysis page for the Logstore.

Wait about one minute for the indexes to take effect. Then, you can 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 specify a data source by using form configuration or editor configuration in JSON.

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 as`xxx`. 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 as`xxx`. 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.

Log on to the server on which Logtail is installed.
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.

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 page or the query page after you use Logtail to collect logs, see Troubleshoot Logtail log collection failures.

Sample database table and logs

This topic demonstrates INSERT, UPDATE, and DELETE operations on the specialalarm table in the user_info database. The following list provides the table schema, sample database operations, and the 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 logs that are collected for each operation on the query and analysis page of the Logstore that is specified in the Logtail configuration. 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.