【微服务】mysql + elasticsearch数据双写设计与实现

from pymysql import Connection
from elasticsearch import Elasticsearch, helpers
 
# MySQL连接配置
mysql_config = {
    "host": "localhost",
    "user": "root",
    "password": "your_password",
    "database": "your_database"
}
 
# Elasticsearch连接配置
es_config = {
    "hosts": ["localhost:9200"]
}
 
# 初始化MySQL和Elasticsearch连接
mysql_conn = Connection(**mysql_config)
es = Elasticsearch(es_config["hosts"], timeout=100)
 
# 获取MySQL中的数据并同步到Elasticsearch
def sync_data_to_es(index_name, mysql_query, es_id_field, es_doc_type='doc'):
    # 执行MySQL查询
    with mysql_conn.cursor() as cursor:
        cursor.execute(mysql_query)
        rows = cursor.fetchall()
    
    # 准备Elasticsearch的actions
    actions = []
    for row in rows:
        action = {
            "_index": index_name,
            "_type": es_doc_type,
            "_id": row[es_id_field],
            "_source": row
        }
        actions.append(action)
    
    # 批量索引到Elasticsearch
    helpers.bulk(es, actions)
 
# 使用示例
sync_data_to_es(
    "your_index",
    "SELECT id, name, description FROM your_table",
    "id"
)

这段代码展示了如何使用Python连接MySQL和Elasticsearch，并将MySQL中的数据同步到Elasticsearch中。代码中的sync_data_to_es函数接受索引名、MySQL查询语句、Elasticsearch文档ID字段和文档类型作为参数，执行查询并批量导入数据到Elasticsearch。