This topic describes how to configure Logtail in the Log Service console to collect MySQL binary logs.
Prerequisites
Description
- Logtail acts as a secondary server and sends dump requests to the primary server.
- After the primary server receives the dump requests, it sends binary logs to Logtail in real time.
- Logtail parses events in the binary logs, filters and parses data, and then uploads the parsed data to Log Service.

Features
- Allows you to collect incremental data of MySQL databases, such as ApsaraDB for RDS databases, by using binary logs. This data collection mechanism provides excellent performance.
- Supports multiple methods that filter data in databases.
- Allows you to set binary log positions.
- Allows you to record synchronization statuses by using the checkpoint mechanism.
Limits
- Binary logs of MySQL 8.0 or later cannot be collected.
- Row-based binary logs must be enabled for MySQL databases. By default, row-based binary
logs are enabled for RDS databases.
# Check whether binary logging is enabled. mysql> show variables like "log_bin"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.02 sec) # View the binary log 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 server whose role Logtail assumes must be unique.
- Limits on RDS instances
- Logtail cannot be installed on a server where an RDS instance resides. You must install Logtail on a server that can communicate with the RDS instance.
- You can collect binary logs only from a primary RDS database. Collection of binary logs from secondary RDS databases is not supported.
Scenarios
- Query the incremental data of databases in real time.
- Audit operations that are performed on databases.
- Use Log Service to query database updates, visualize search and analysis results, transform data for stream computing, export log data to MaxCompute for offline computing, and export log data to OSS for long-term storage.
Precautions
We recommend that you increase resource limits on Logtail to accommodate traffic surges and avoid risks to your data. If the limits are exceeded, Logtail may be forced to restart.
You can set parameters in the /usr/local/ilogtail/ilogtail_config.json file. For more information, see Configure the startup parameters of Logtail.
{
...
"cpu_usage_limit":2,
"mem_usage_limit":2048,
...
}
Data reliability
- Incomplete collection of data: If the network between Logtail and the MySQL server
is disconnected for a long period of time, data loss may occur.
If the network between Logtail and the primary server is disconnected, the primary server still generates binary logs and deletes expired binary logs. When the network connection between Logtail and the primary MySQL server is recovered, Logtail uses a checkpoint to request more binary logs from the primary MySQL server. However, if the network has been disconnected for a long period of time, the data that is generated after the checkpoint may be deleted. In this case, the recovery mechanism is triggered. The mechanism locates the last binary log file position from which Logtail resumes binary log collection. The data between the checkpoint and the collection resumption position is not collected. This leads to incomplete data collection.
- Repeated data collection: If the binary log file positions on the primary and secondary
servers are different and a primary/secondary server switchover occurs, binary logs
may be repeatedly collected.
If the MySQL primary-secondary server synchronization is configured, the primary MySQL server synchronizes binary logs to the secondary MySQL server. The secondary server receives the log data, and stores the log data to its local binary log files. If the binary log file positions on the primary and secondary are different and a primary-secondary server switchover occurs, data may be repeatedly collected. This is because the checkpoint mechanism uses a binary log file name and an offset for data collection.
For example, a piece of data ranges from
(binlog.100, 4)
to(binlog.105, 4)
on the primary server, and ranges from(binlog.1000, 4)
to(binlog.1005, 4)
on the secondary server. Logtail has obtained the data from the primary server and updated the checkpoint to(binlog.105, 4)
. If a primary-secondary server switchover occurs, Logtail continues to obtain binary logs from the new primary server based on the local checkpoint(binlog.105, 4)
. However, the binary log position on the new primary MySQL server ranges from(binlog.1000, 4)
to(binlog.1005, 4)
. This position range is greater than the range that Logtail requests, resulting in repeated collection.
Procedure
Modify the configurations on the server where Logtail is installed
If you did not enter real information for the Host, User, Password, and other parameters under the inputs field, you can modify the parameters after the configurations are synchronized to the local server.
What to do next
After Logtail sends MySQL binary logs to Log Service, you can view the logs in the
Log Service console. For example, after you perform the INSERT
, UPDATE
, DELETE
operations on the SpecialAlarm
table of the user_info
database, binary logs are collected and uploaded to Log Service. This section describes
the schema of the table. This section also lists the INSERT, DELETE, and UPDATE operations
on the table, and describes sample logs that are introduced by the three operations.
- Create a 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 on the database.
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 forzc.specialalarm
.ALTER TABLE `zc`.`specialalarm` ADD INDEX `time_index` (`time` ASC);
- Sample log data
You can view the log data that is introduced by each operation on the Search & Analysis page of a Logstore. The following examples show log data that is introduced by different operations:
- Log data that is introduced by the INSERT operation
__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 name of the host where the database resides. _db_
The name of the RDS database. _table_
The name of the table. _event_
The type of the system event. _id_
The ID of the current collection. The value starts from 0 and increments by 1 each time a binary log event is collected. _gtid_
The global transaction ID. _filename_
The name of the binary log file. _offset_
The offset of the binary log file. The value is updated after each COMMIT operation. - Log data that is introduced by the INSERT operation