How to import MySQL Binlogs-阿里云帮助中心

You can import binlogs from a self-managed MySQL database or an RDS MySQL database to Simple Log Service to query, analyze, and process data. This topic describes how to import MySQL binlogs.

Prerequisites

You have a MySQL database with the row-based binary logging (binlog) feature enabled.
The binlog feature is enabled by default for RDS MySQL databases.
You have a database account that has the read and `REPLICATION` permissions on the target database.
For example, the database account is `sls_binlog` and the password is `sls_binlog`. Run the following commands to grant the `sls_binlog` user the `SELECT` and `REPLICATION` permissions on all tables in the target database.
```
CREATE USER sls_binlog IDENTIFIED BY 'sls_binlog';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'sls_binlog'@'%';
-- GRANT ALL PRIVILEGES ON *.* TO 'sls_binlog'@'%' ;
FLUSH PRIVILEGES;
```
You can add a whitelist to a MySQL database.
Add the IP addresses based on the region of the project for the data import task and the network environment of the database. For a list of IP addresses, see IP address whitelists.
You have created a project and a Logstore. For more information, see Manage Project and Create a basic Logstore.

Import data

Important

Currently, this feature is available only in the China (Heyuan) region.

Log on to the Simple Log Service console.
In the Projects section, click the one you want.
On the Log Storage > Logstores tab, click the > icon to the left of the target Logstore. Then, hover over Data Ingestion > Data Import and click +.
In the Quick Data Access dialog box, click MySQL Binlog - Data Import.

Configure the import settings.

In the Data Source Settings section, configure the following parameters.

If you use an RDS MySQL database in a virtual private cloud (VPC), select Use VPC Address and RDS Instance, and then set the following parameters.

Parameter	Description
VPC Instance ID	The ID of the VPC where the RDS MySQL database resides.
RDS Instance ID	Set this parameter to the VpcCloudInstanceId of the RDS instance. You can call the DescribeDBInstanceAttribute operation to obtain the VpcCloudInstanceId. For more information, see Obtain the VpcCloudInstanceId of an RDS instance.

If you use a self-managed MySQL database on an ECS instance in a VPC, select Use VPC Address and Self-managed Database on ECS, and then set the following parameters.
Parameter
Description
VPC Instance ID
The ID of the VPC where the ECS instance resides.
ECS Instance IP
The private IP address of the ECS instance.
If you use an RDS MySQL database over the public network or a self-managed MySQL database in other scenarios, clear Use VPC Address and set the following parameter.
Parameter
Description
Public IP Address or Domain Name of Database
The public IP address or domain name of the MySQL database.

The following table describes the common parameters.

Parameter	Description
Configuration Name	The name of the import configuration.
MySQL Port	The service port of the MySQL database. The default value is 3306.
Username	The username to log on to the MySQL database. Grant only read permissions to this account.
Password	The password for the account.
Database	The name of the MySQL database.
Advanced Configuration
Database Table Whitelist	Specifies the database tables from which to collect data. Enter a regular expression in the `database_name.table_name` format. Separate multiple tables with semicolons (;). The default value is `.\..`, which matches all tables in all databases.
Database Table Blacklist	Specifies the database tables that you do not want to collect data from. Enter a regular expression in the `database_name.table_name` format. Separate multiple tables with semicolons (;). The default value is `mysql\..*`, which excludes all tables in the `mysql` database.
Binlog Start Position	Specifies the position from which to start collecting Binlogs. Latest Position: Starts collecting Binlogs from the latest position. Specify Binlog Position: Starts collecting Binlogs from a specified position. Run the `show binary logs;` statement to view the Binlog filename and file size offset. If you do not set Start Binlog Filename and Start Binlog Position, collection starts from the latest position. Note If you specify only Start Binlog Filename, the first collection may generate a large amount of traffic. Specify GTID: Starts collecting from the specified global transaction identifier (GTID) position. If you do not set Start GTID, collection starts from the latest position.
Collect DDL	If you turn on the Collect DDL switch, Simple Log Service collects data definition language (DDL) event data. Important This option is not affected by the database table blacklists and whitelists.
Collect Insert	If you turn on the Collect Insert switch, Simple Log Service collects Insert event data.
Collect Update	If you turn on the Collect Update switch, Simple Log Service collects Update event data.
Collect Delete	If you turn on the Collect Delete switch, Simple Log Service collects Delete event data.
Collect Event Metadata	If you turn on the Collect Event Metadata switch, Simple Log Service collects event metadata, including the _event_size_ and _event_server_id_ fields.
Use event_time as Log Time	Specifies whether to use the value of the event_time field as the log time. If you turn on the Use event_time as Log Time switch, the value of the event_time field is used as the log time. If you turn off the Use event_time as Log Time switch, the system time is used as the log time.

Click Preview to preview the import results.
Note
If the RDS MySQL database does not generate binlog data in real time, a timeout message appears.
After you confirm the settings, click Next.

Preview data, configure indexes, and then click Next.
By default, full-text indexing is enabled for Log Service. You can also configure field indexes based on collected logs in manual mode or automatic mode. To configure field indexes in automatic mode, click Automatic Index Generation. This way, Log Service automatically creates field indexes. For more information, see Create an index.
Important
If you want to query and analyze logs, you must enable full-text indexing or field indexing. If you enable both full-text indexing and field indexing, the system uses only field indexes.
Click Query Logs to go to the query and analysis page and confirm that the MySQL binlogs are imported.
Wait for about one minute. The import is successful if the target MySQL binlogs are imported.

View import configurations and statistical reports

After you create an import configuration, you can view the configuration and its statistical reports in the console.

Click the target project.
Under the target Logstore, choose Data Ingestion > Data Import, and then click the target import configuration.
On the Import Configuration Overview page, view the basic information and statistical reports for the import configuration.

Related operations

On the Import Configuration Overview page, you can also perform the following operations.

Modify a configuration
Click Modify Configuration to modify the import configuration. For more information about the configuration parameters, see Import data.
Delete a configuration
Click Delete Configuration to delete the import configuration.
Warning
This operation cannot be undone. Proceed with caution.

FAQ

Why does binlog synchronization fail?
The main reasons are as follows:
- The binlog position is lost because the MySQL server was restarted or the binlogs were cleared. Restart the import task to retrieve the latest binlog position.
- A primary/secondary switchover of the MySQL server causes binlogs to be out of sync. For example, if the latest binlog position changes from (binlog.1005, 4) to (binlog.105, 4), the position is lost. Restart the import task to retrieve the latest binlog position.
Why does the unknown field appear?
The main reasons are as follows:
- The database account does not have the `SELECT` permission on the database tables, which causes the loss of table metadata.
- High network latency during the first connection between Simple Log Service and the MySQL server may cause the loss of table metadata.
- No primary key is created in the tables of the RDS MySQL database.
How do I prevent duplicate data collection?
Enable the Global Transaction Identifier (GTID) feature on your MySQL server to prevent duplicate data collection caused by primary/secondary switchovers.
In a primary/secondary synchronization setup, the MySQL master node synchronizes its generated binlogs to the MySQL slave node. The slave node receives the binlogs and stores them in its local binlog files. If the binlog sequence numbers on the master and slave nodes are out of sync when a primary/secondary switchover occurs, the checkpoint mechanism that uses the binlog filename and file size offset will cause duplicate data collection.
For example, a data segment is located between (binlog.100, 4) and (binlog.105, 4) on the master node, and between (binlog.1000, 4) and (binlog.1005, 4) on the slave node. Logtail has already collected this data from the master node and updated its local checkpoint to (binlog.105, 4). If a primary/secondary switchover occurs at this time, Logtail will continue to use the local checkpoint (binlog.105, 4) to collect binlogs from the new master node. However, the binlog files on the new master node have a different naming sequence, such as starting from binlog.1000. This discrepancy causes the new master to resend data that has already been collected, which results in duplicate collection.

Parameter	Description
Public IP Address or Domain Name of Database	The public IP address or domain name of the MySQL database.