如何高效實現 MySQL 與 elasticsearch 的資料同步

MySQL 自身簡單、高效、可靠，是又拍雲內部使用最廣泛的資料庫。但是當資料量達到一定程度的時候，對整個 MySQL 的操作會變得非常遲緩。而公司內部 robin/logs 表的資料量已經達到 800w，後續又有全文檢索的需求。這個需求直接在 MySQL 上實施是難以做到的。

原資料庫的同步問題

由於傳統的 mysql 資料庫並不擅長海量資料的檢索，當資料量到達一定規模時（估算單表兩千萬左右），查詢和插入的耗時會明顯增加。同樣，當需要對這些資料進行模糊查詢或是資料分析時，MySQL作為事務型關聯式資料庫很難提供良好的效能支援。使用適合的資料庫來實現模糊查詢是解決這個問題的關鍵。

但是，切換資料庫會迎來兩個問題，一是已有的服務對現在的 MySQL 重度依賴，二是 MySQL 的事務能力和軟體生態仍然不可替代，直接遷移資料庫的成本過大。我們綜合考慮了下，決定同時使用多個資料庫的方案，不同的資料庫應用於不同的使用場景。而在支援模糊查詢功能的資料庫中，elasticsearch 自然是首選的查詢資料庫。這樣後續對業務需求的切換也會非常靈活。

那具體該如何實現呢？在又拍雲以往的專案中，也有遇到相似的問題。之前採用的方法是在業務中編寫程式碼，然後同步到 elasticsearch 中。具體是這樣實施的：每個系統編寫特定的程式碼，修改 MySQL 資料庫後，再將更新的資料直接推播到需要同步的資料庫中，或推播到佇列由消費程式來寫入到資料庫中。

但這個方案有一些明顯的缺點：

• 系統高耦合，侵入式程式碼，使得業務邏輯複雜度增加

• 方案不通用，每一套同步都需要額外客製化，不僅增加業務處理時間，還會提升軟體複復雜度

• 工作量和複雜度增加

在業務中編寫同步方案，雖然在專案早期比較方便，但隨著資料量和系統的發展壯大，往往最後會成為業務的大痛點。

解決思路及方案

調整架構

既然以往的方案有明顯的缺點，那我們如何來解決它呢？優秀的解決方案往往是 「通過架構來解決問題「，那麼能不能通過架構的思想來解決問題呢？

答案是可以的。我們可以將程式偽裝成 「從資料庫」，主庫的增量變化會傳遞到從庫，那這個偽裝成 「從資料庫」 的程式就能實時獲取到資料變化，然後將增量的變化推播到訊息佇列 MQ，後續消費者消耗 MQ 的資料，然後經過處理之後再推播到各自需要的資料庫。

這個架構的核心是通過監聽 MySQL 的 binlog 來同步增量資料，通過基於 query 的查詢舊錶來同步舊資料，這就是本文要講的一種異構資料庫同步的實踐。

改進資料庫

經過深度的調研，成功得到了一套異構資料庫同步方案，並且成功將公司生產環境下的 robin/logs 的表同步到了 elasticsearch 上。

首先對 MySQL 開啟 binlog，但是由於 maxwell 需要的 binlog_format=row 原本的生產環境的資料庫不宜修改。這裡請教了海楊前輩，他提供了」從庫聯級「的思路，在從庫中監聽 binlog 繞過了操作生產環境重啟主庫的操作，大大降低了系統風險。

後續操作比較順利，啟動 maxwell 監聽從庫變化，然後將增量變化推播到 kafka ，最後設定 logstash 消費 kafka中的資料變化事件資訊，將結果推播到 elasticsearch。設定 logstash需要結合表結構，這是整套方案實施的重點。

這套方案使用到了kafka、maxwell、logstash、elasticsearch。其中 elasticsearch 與 kafka已經在生產環境中有部署，所以無需單獨部署維護。而 logstash 與 maxwell 只需要修改組態檔和啟動命令即可快速上線。整套方案的意義不僅在於成本低，而且可以大規模使用，公司內有 MySQL 同步到其它資料庫的需求時，都可以上任。

成果展示前後對比

使用該方案同步和業務實現同步的對比

寫入到 elasticsearch 效能對比 (8核4G記憶體)

經過對比測試，800w 資料量全量同步，使用 logstash 寫到 elasticsearch，實際需要大概 3 小時，而舊方案的寫入時間需要 2.5 天。

方案實施細節

接下來，我們來看看具體是如何實現的。

本方案無需編寫額外程式碼，非侵入式的，實現 MySQL 資料與 elasticsearch 資料庫的同步。

下列是本次方案需要使用所有的元件：

• MySQL

• Kafka

• Maxwell（監聽 binlog）

• Logstash（將資料同步給 elasticsearch）

• Elasticsearch

1. MySQL設定

本次使用 MySQL 5.5 作示範，其他版本的設定可能稍許不同需要

首先我們需要增加一個資料庫唯讀的使用者，如果已有的可以跳過。

-- 建立一個 使用者名稱為 maxwell 密碼為 xxxxxx 的使用者
CREATE USER 'maxwell'@'%' IDENTIFIED BY 'XXXXXX';
GRANT ALL ON maxwell.* TO 'maxwell'@'localhost';
GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'maxwell'@'%';

開啟資料庫的 binlog，修改 mysql 組態檔，注意 maxwell 需要的 binlog 格式必須是row。

# /etc/mysql/my.cnf

[mysqld]
# maxwell 需要的 binlog 格式必須是 row
binlog_format=row

# 指定 server_id 此設定關係到主從同步需要按情況設定，
# 由於此mysql沒有開啟主從同步，這邊預設設定為 1
server_id=1

# logbin 輸出的檔名， 按需設定
log-bin=master

重啟 MySQL 並檢視設定是否生效：

sudo systemctl restart mysqld

select @@log_bin;
-- 正確結果是 1
select @@binlog_format;
-- 正確結果是 ROW

如果要監聽的資料庫開啟了主從同步，並且不是主資料庫，需要再從資料庫開啟 binlog 聯級同步。

# /etc/my.cnf

log_slave_updates = 1

需要被同步到 elasticsearch 的表結構。

-- robin.logs
show create table robin.logs;

-- 表結構
CREATE TABLE `logs` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `content` text NOT NULL,
  `user_id` int(11) NOT NULL,
  `status` enum('SUCCESS','FAILED','PROCESSING') NOT NULL,
  `type` varchar(20) DEFAULT '',
  `meta` text,
  `created_at` bigint(15) NOT NULL,
  `idx_host` varchar(255) DEFAULT '',
  `idx_domain_id` int(11) unsigned DEFAULT NULL,
  `idx_record_value` varchar(255) DEFAULT '',
  `idx_record_opt` enum('DELETE','ENABLED','DISABLED') DEFAULT NULL,
  `idx_orig_record_value` varchar(255) DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=8170697 DEFAULT CHARSET=utf8

2. Maxwell 設定

本次使用 maxwell-1.39.2 作示範, 確保機器中包含 java 環境， 推薦 openjdk11

下載 maxwell 程式

wget https://github.com/zendesk/maxwell/releases/download/v1.39.2/maxwell-1.39.2.tar.gz
tar zxvf maxwell-1.39.2.tar.gz **&&**  cd maxwell-1.39.2

maxwell 使用了兩個資料庫：

• 一個是需要被監聽binlog的資料庫(只需要讀許可權)

• 另一個是記錄maxwell服務狀態的資料庫，當前這兩個資料庫可以是同一個

重要引數說明：

• host 需要監聽binlog的資料庫地址

• port 需要監聽binlog的資料庫埠

• user 需要監聽binlog的資料庫使用者名稱

• password 需要監聽binlog的密碼

• replication_host 記錄maxwell服務的資料庫地址

• replication_port 記錄maxwell服務的資料庫埠

• replication_user 記錄maxwell服務的資料庫使用者名稱

• filter 用於監聽binlog資料時過濾不需要的資料庫資料或指定需要的資料庫

• producer 將監聽到的增量變化資料提交給的消費者 (如 stdout、kafka)

• kafka.bootstrap.servers kafka 服務地址

• kafka_version kafka 版本

• kafka_topic 推播到kafka的主題

啟動 maxwell

注意，如果 kafka 設定了禁止自動建立主題，需要先自行在 kafka 上建立主題，kafka_version 需要根據情況指定, 此次使用了兩張不同的庫

./bin/maxwell 
        --host=mysql-maxwell.mysql.svc.cluster.fud3 
        --port=3306 
        --user=root 
        --password=password 
        --replication_host=192.168.5.38 
        --replication_port=3306 
        --replication_user=cloner 
        --replication_password=password
        --filter='exclude: *.*, include: robin.logs' 
        --producer=kafka 
        --kafka.bootstrap.servers=192.168.30.10:9092 
        --kafka_topic=maxwell-robinlogs --kafka_version=0.9.0.1

3. 安裝 Logstash

Logstash 包中已經包含了 openjdk，無需額外安裝。

wget https://artifacts.elastic.co/downloads/logstash/logstash-8.5.0-linux-x86_64.tar.gz
tar zxvf logstash-8.5.0-linux-x86_64.tar.gz

刪除不需要的組態檔。

rm config/logstash.yml

修改 logstash 組態檔，此處語法參考官方檔案（https://www.elastic.co/guide/en/logstash/current/input-plugins.html） 。

# config/logstash-sample.conf

input {
 kafka {
    bootstrap_servers => "192.168.30.10:9092"
    group_id => "main"
    topics => ["maxwell-robinlogs"]
 }
}

filter {
  json {
    source => "message"
  }

  # 將maxwell的事件型別轉化為es的事件型別
  # 如增加 -> index 修改-> update
  translate {
    source => "[type]"
    target => "[action]"
    dictionary => {
      "insert" => "index"
      "bootstrap-insert" => "index"
      "update" => "update"
      "delete" => "delete"
    }
    fallback => "unknown"
  }

  # 過濾無效的資料
  if ([action] == "unknown") {
    drop {}
  }

  # 處理資料格式
  if [data][idx_host] {
    mutate {
      add_field => { "idx_host" => "%{[data][idx_host]}" }
    }
  } else {
    mutate {
      add_field => { "idx_host" => "" }
    }
  }

  if [data][idx_domain_id] {
    mutate {
      add_field => { "idx_domain_id" => "%{[data][idx_domain_id]}" }
    }
  } else {
    mutate {
      add_field => { "idx_domain_id" => "" }
    }
  }

  if [data][idx_record_value] {
    mutate {
      add_field => { "idx_record_value" => "%{[data][idx_record_value]}" }
    }
  } else {
    mutate {
      add_field => { "idx_record_value" => "" }
    }
  }
  
   if [data][idx_record_opt] {
    mutate {
      add_field => { "idx_record_opt" => "%{[data][idx_record_opt]}" }
    }
  } else {
    mutate {
      add_field => { "idx_record_opt" => "" }
    }
  }
 
  if [data][idx_orig_record_value] {
    mutate {
      add_field => { "idx_orig_record_value" => "%{[data][idx_orig_record_value]}" }
    }
  } else {
    mutate {
      add_field => { "idx_orig_record_value" => "" }
    }
  }
 
  if [data][type] {
    mutate {
      replace => { "type" => "%{[data][type]}" }
    }
  } else {
    mutate {
      replace => { "type" => "" }
    }
  }
 
  mutate {
    add_field => {
      "id" => "%{[data][id]}"
      "content" => "%{[data][content]}"
      "user_id" => "%{[data][user_id]}"
      "status" => "%{[data][status]}"
      "meta" => "%{[data][meta]}"
      "created_at" => "%{[data][created_at]}"
    }
    remove_field => ["data"]
  }

  mutate {
    convert => {
      "id" => "integer"
      "user_id" => "integer"
      "idx_domain_id" => "integer"
      "created_at" => "integer"
    }
  }

  # 只提煉需要的欄位
  mutate {
    remove_field => [
      "message",
      "original",
      "@version",
      "@timestamp",
      "event",
      "database",
      "table",
      "ts",
      "xid",
      "commit",
      "tags"
    ]
   }
}

output {
  # 結果寫到es
  elasticsearch {
    hosts => ["http://es-zico2.service.upyun:9500"]
    index => "robin_logs"
    action => "%{action}"
    document_id => "%{id}"
    document_type => "robin_logs"
  }

  # 結果列印到標準輸出
  stdout {
    codec => rubydebug
  }
}

執行程式：

# 測試組態檔*
bin/logstash -f config/logstash-sample.conf --config.test_and_exit

# 啟動*
bin/logstash -f config/logstash-sample.conf --config.reload.automatic

4. 全量同步

完成啟動後，後續的增量資料 maxwell 會自動推播給 logstash 最終推播到 elasticsearch ，而之前的舊資料可以通過 maxwell 的 bootstrap 來同步，往下面表中插入一條任務，那麼 maxwell 會自動將所有符合條件的 where_clause 的資料推播更新。

INSERT INTO maxwell.bootstrap 
        ( database_name, table_name, where_clause, client_id ) 
values 
        ( 'robin', 'logs', 'id > 1', 'maxwell' );

後續可以在 elasticsearch 檢測資料是否同步完成，可以先檢視數量是否一致，然後抽樣對比詳細資料。

# 檢測 elasticsearch  中的資料量
GET robin_logs/robin_logs/_count