When you need to synchronize data from MySQL to Elasticsearch, different synchronization schemes can be selected based on varying application scenarios, data scale, and synchronization delay requirements. This article introduces common synchronization schemes and their applicable scenarios to help you choose the appropriate scheme for data synchronization.
Synchronization Scheme | Basic Principle | Applicable Scenario | Use Limits | Reference |
Use Oceanus for Real-time Synchronization | Use Tencent Cloud's stream computing service Oceanus for real-time synchronization of MySQL data to Elasticsearch by parsing binlogs | Scenarios with high real-time requirements | 1. MySQL's binlog must be in ROW Mode (enabled by default in TencentDB for MySQL products) 2. The MySQL data table to be synchronized must contain a primary key 3. Modifying the structure of the MySQL table being synchronized is not supported during the synchronization process 4. You need to grant the account used to connect to MySQL the RELOAD and REPLICATION permission | |
Logstash enables full synchronization and incremental synchronization | The input-jdbc plugin of Logstash can achieve full data synchronization from MySQL to Elasticsearch by batching the data from MySQL tables and writing them in bulk to ES, thereby achieving full synchronization Additionally, when there is only new data in the MySQL table, a scheduled task can be set in Logstash's configuration to periodically query the newly added data within a recent period for simple incremental synchronization | Scenarios requiring full table data import into ES, and scenarios with low real-time performance requirements | 1. For incremental synchronization, the MySQL table needs to have an auto-increment field or a timestamp field, such as UpdateTime 2. Incremental synchronization does not support data deletion 3. The real-time performance of incremental synchronization is relatively poor |