業務背景
寫任何工具都不能脫離實際業務的背景。開始這個專案的時候是因為現有的專案中資料分佈太零碎，零零散散的分佈在好幾個資料庫中，沒有統一的資料庫來收集這些資料。這種情況下想做一個大而全的會員中心繫統比較困難。（這邊是一個以網際網路保險為中心的專案，保單，會員等資料很零散的儲存在好幾個專案之中，並且專案之間的資料基本上是隔離的）。
現有的專案資料庫是在騰訊雲中儲存，雖然騰訊提供了資料同步功能，但是這樣必須要表結構相同才行，並不符合我們的需求。所以需要自行開發。
專案在這裡：https://github.com/hjx601496320/miner。
需求
1：需要能靈活配置。
2：實時資料10分鐘內希望可以完成同步。
3：來源資料與目標資料可能結構，欄位名稱不同。
4：增刪改都可以同步。
技術選擇
這個任務交給了我和另外一個同事來做。
同事的
同事希望可以通過ETL工具Kettle來做，這個東西我沒有研究過，是同事自己在研究。具體過程不是很清楚，但是最後是通過在mysql中設定更新，修改，刪除的觸發器，然後在Kettle中做了一個定時任務，實現了資料同步的功能，初步測試符合需求。但是必須要在資料庫中設定觸發器，並且會有一個臨時表，這一點我個人不是很喜歡。
我的
我是本著能自己寫就自己寫的原則，準備自己寫一個。剛開始使用的是定時任務比較兩個庫的資料差別，然後再同步資料。但是經過一定的資料測試後，發現在資料量大的時候，定時任務中的上一個任務沒有執行完畢，下一個任務就又開始了。這樣造成了兩邊資料不一致。最終這個方案廢棄了。
後來通過研究，發現mysql的資料操作會記錄在binlog中，這時就有了新的方案。可以通過逐行獲取binlog資訊，經過解析資料後，同步在目標庫中。
既然有了方案，那麼就開始做吧。
開始嘗試：1
首先要開啟資料庫的binlog功能，這一步比較簡單，修改mysql的配置檔案：/etc/mysql/mysql.conf.d/mysqld.cnf，新增：
server-id = 1 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M binlog_format = ROW
然後重啟mysql 就好了，具體每個引數的意思，搜尋一下就好了。這時候隨意的對某一個資料庫中的表做一下增刪改，對應的日誌就會記錄在/var/log/mysql/這個資料夾下了。我們看一下這個資料夾裡的東西：
這裡的檔案是沒有辦法正常檢視的，需要使用mysql提供的命令來檢視，命令是這個樣子的：
1：檢視 mysqlbinlog mysql-bin.000002 2：指定位置檢視 mysqlbinlog --start-position="120" --stop-position="332" mysql-bin.000002
因為我們現在的binlog_format指定的格式是ROW（就在上面寫的，還記得嗎？），所謂binlog檔案的內容沒有辦法正常檢視，因為他是這個樣子的：
這時，我們需要：
對輸出進行解碼 mysqlbinlog --base64-output=decode-rows -v mysql-bin.000001
這時候，顯示的結果就變成了：
雖然還不是正常的sql，但是好賴是有一定的格式了。
but自己來做解析的話還是很麻煩，so～放棄這種操作。
繼續嘗試：2
經過再次研究後，發現資料庫中執行sql也是可以檢視binlog的。主要有如下幾條命令：
重置binlog reset master; 檢視binlog的配置 show variables like '%binlog%'; 檢視所有的binlog show binary logs; 檢視正在寫入的binlog show master status; 檢視指定binlog檔案 show binlog events in 'mysql-bin.000001'; 檢視指定binlog檔案，並指定位置 show binlog events in 'mysql-bin.000001' from [pos] limit [顯示多少條];
按照上面的命令執行結果為：
發現sql還是不能正常顯示。這裡的原因應該是binlog_format配置的原因。將其修改為 binlog_format=Mixed後，完美解決。經過資料庫中一通增刪改後，顯示的sql類似這樣：
use `pay`; /* ApplicationName=DataGrip 2018.2.5 */ UPDATE `pay`.`p_pay_log` t SET t.`mark_0` = 'sdfsdf' WHERE t.`id` LIKE '342' ESCAPE '#'
現在似乎已經可以開始寫資料同步了，只要在啟動的時候獲取當正在使用的是哪一個日誌檔案，記錄binlog的位置，然後一點一點向下執行，解析sql就好了。但是在這個過程中，我發現阿里巴巴有一款開源的軟體可以用。就是標題上說道的：canal。看了一下網站上的介紹，簡直美滋滋。
它的文件和程式碼地址在這裡：https://github.com/alibaba/canal，大家可以看一下。現在就準備用這個來完成我所需要的功能。
正式開始寫
首先看一下介紹，canal是需要單獨執行一個服務的，這個服務具體的配置還是比較簡單的。它的作用我自己理解就是監控binlog，然後根據自己的需要獲取binlog中一定量的資料。這個資料是經過處理的，可以比較方便的知道里面的具體資訊。比如那些資料發生了變動，每列資料的列名是什麼，變動前和變動後的值是啥之類的。那麼開始。
1：我的想法
1）：專案啟動的時候，開啟canal的連結，以及初始化一些配置。
@Bean
public CanalConnector canalConnector() {
CanalConnector connector = CanalConnectors.newSingleConnector(
//對應canal服務的連結
new InetSocketAddress(canalConf.getIp(), canalConf.getPort()),
//連結的目標，這裡對應canal服務中的配置，需要查閱文件
canalConf.getDestination(),
//不知道是什麼使用者，使用“”
canalConf.getUser(),
//不知道是什麼密碼，使用“”
canalConf.getPassword()
);
return connector;
}
2）：先開啟一個執行緒，裡面寫一個死迴圈，用於從canal的服務中獲取binlog中的訊息。這個訊息類是：com.alibaba.otter.canal.protocol.Message。
Message message = connector.getWithoutAck(100); connector：canal連結的例項化物件。 connector.getWithoutAck(100)：從連線中獲取100條binlog中的資料。
3）：取出Message中的事件集合，就是binlog中的每一條資料。將型別為增刪改的資料取出，之後每一條資料放在一個執行緒中，用執行緒池去執行它。
List<Entry> entries = message.getEntries(); message.getEntries()：從連結中獲取的資料集合，每一條代表1條binlog資料
4）：在每一個執行緒中，取出Entry中的資料，根據其型別拼接各種sql，並執行。
Header header = entry.getHeader();
獲取發生變化的表名稱，可能會沒有
String tableName = header.getTableName();
獲取發生變化的資料庫名稱，可能會沒有
String schemaName = header.getSchemaName();
//獲取事件型別
EventType eventType = rowChange.getEventType();
這裡我們只是用其中的三種型別：
EventType.DELETE 刪除
EventType.INSERT 插入
EventType.UPDATE 更新
//獲取發生變化的資料
RowChange rowChange = CanalEntry.RowChange.parseFrom(entry.getStoreValue());
//遍歷其中的資料
int rowDatasCount = rowChange.getRowDatasCount();
for (int i = 0; i < rowDatasCount; i++) {
//每一行中的資料
RowData rowData = rowChange.getRowDatas(i);
}
//獲取修改前的資料
List<Column> before = rowData.getBeforeColumnsList();
//獲取修改後的資料
List<Column> after = rowData.getAfterColumnsList();
Column中有一系列方法，比如是否發生修改，時候為key，是否是null等，就不在細說了。
2：萬事具備，可以開始寫了
1）：這裡先寫一個執行緒，用於不停的從canal服務中獲取訊息，然後建立新的執行緒並讓其處理其中的資料。程式碼如下：
@Override
public void run() {
while (true) {
//主要用於在連結失敗後用於再次嘗試重新連結
try {
if (!run) {
//開啟連結，並設定 run=true
startCanal();
}
} catch (Exception e) {
System.err.println("連線失敗，嘗試重新連結。。。");
threadSleep(3 * 1000);
}
System.err.println("連結成功。。。");
//不停的從CanalConnector中獲取訊息
try {
while (run) {
//獲取一定數量的訊息，這裡為執行緒池數量×3
Message message = connector.getWithoutAck(batchSize * 3);
long id = message.getId();
//處理獲取到的訊息
process(message);
connector.ack(id);
}
} catch (Exception e) {
System.err.println(e.getMessage());
} finally {
//如果發生異常，最終關閉連線，並設定run=false
stopCanal();
}
}
}
void process(Message message) {
List<Entry> entries = message.getEntries();
if (entries.size() <= 0) {
return;
}
log.info("process message.entries.size:{}", entries.size());
for (Entry entry : entries) {
Header header = entry.getHeader();
String tableName = header.getTableName();
String schemaName = header.getSchemaName();
//這裡判斷是否可以取出資料庫名稱和表名稱，如果不行，跳過迴圈
if (StringUtils.isAllBlank(tableName, schemaName)) {
continue;
}
//建立新的執行緒，並執行
jobList.stream()
.filter(job -> job.isMatches(tableName, schemaName))
.forEach(job -> executorService.execute(job.newTask(entry)));
}
}
這裡的jobList是我自己定義List<Job>，程式碼如下：
package com.hebaibai.miner.job;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.otter.canal.protocol.CanalEntry;
import com.google.protobuf.InvalidProtocolBufferException;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import static com.alibaba.otter.canal.protocol.CanalEntry.Entry;
@Slf4j
@Data
public abstract class Job {
/**
* 資料庫連結
*/
protected JdbcTemplate jdbcTemplate;
/**
* 額外配置
*/
protected JSONObject prop;
/**
* 校驗目標是否為合適的資料庫和表
*
* @param table
* @param database
* @return
*/
abstract public boolean isMatches(String table, String database);
/**
* 例項化一個Runnable
*
* @param entry
* @return
*/
abstract public Runnable newTask(final Entry entry);
/**
* 獲取RowChange
*
* @param entry
* @return
*/
protected CanalEntry.RowChange getRowChange(Entry entry) {
try {
return CanalEntry.RowChange.parseFrom(entry.getStoreValue());
} catch (InvalidProtocolBufferException e) {
e.printStackTrace();
}
return null;
}
}
jobList裡面放的是Job的實現類。
3：寫一個Job的實現類，並用於同步表，並轉換欄位名稱。
因為需求中要求兩個同步的資料中可能欄位名稱不一致，所以我寫了一個josn用來配置兩個表的欄位對應關係：
別的配置
。。。
"prop": {
//來源資料庫
"database": "pay",
//來源表
"table": "p_pay_msg",
//目標表（目標庫在其他地方配置）
"target": "member",
//欄位對應關係
//key ：來源表的欄位名
//value：目標表的欄位名
"mapping": {
"id": "id",
"mch_code": "mCode",
"send_type": "mName",
"order_id": "phone",
"created_time": "create_time",
"creator": "remark"
}
}
。。。
別的配置
下面是全部的程式碼，主要做的就是取出變動的資料，按照對應的欄位名重新拼裝sql，然後執行就好了，不多解釋。
package com.hebaibai.miner.job;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
import static com.alibaba.otter.canal.protocol.CanalEntry.*;
/**
* 單表同步，表的欄位名稱可以不同，型別需要一致
* 表中需要有id欄位
*/
@SuppressWarnings("ALL")
@Slf4j
public class TableSyncJob extends Job {
/**
* 用於校驗是否適用於當前的配置
*
* @param table
* @param database
* @return
*/
@Override
public boolean isMatches(String table, String database) {
return prop.getString("database").equals(database) &&
prop.getString("table").equals(table);
}
/**
* 返回一個新的Runnable
*
* @param entry
* @return
*/
@Override
public Runnable newTask(final Entry entry) {
return () -> {
RowChange rowChange = super.getRowChange(entry);
if (rowChange == null) {
return;
}
EventType eventType = rowChange.getEventType();
int rowDatasCount = rowChange.getRowDatasCount();
for (int i = 0; i < rowDatasCount; i++) {
RowData rowData = rowChange.getRowDatas(i);
if (eventType == EventType.DELETE) {
delete(rowData.getBeforeColumnsList());
}
if (eventType == EventType.INSERT) {
insert(rowData.getAfterColumnsList());
}
if (eventType == EventType.UPDATE) {
update(rowData.getBeforeColumnsList(), rowData.getAfterColumnsList());
}
}
};
}
/**
* 修改後的資料
*
* @param after
*/
private void insert(List<Column> after) {
//找到改動的資料
List<Column> collect = after.stream().filter(column -> column.getUpdated() || column.getIsKey()).collect(Collectors.toList());
//根據表對映關係拼裝更新sql
JSONObject mapping = prop.getJSONObject("mapping");
String target = prop.getString("target");
List<String> columnNames = new ArrayList<>();
List<String> columnValues = new ArrayList<>();
for (int i = 0; i < collect.size(); i++) {
Column column = collect.get(i);
if (!mapping.containsKey(column.getName())) {
continue;
}
String name = mapping.getString(column.getName());
columnNames.add(name);
if (column.getIsNull()) {
columnValues.add("null");
} else {
columnValues.add("'" + column.getValue() + "'");
}
}
StringBuilder sql = new StringBuilder();
sql.append("REPLACE INTO ").append(target).append("( ")
.append(StringUtils.join(columnNames, ", "))
.append(") VALUES ( ")
.append(StringUtils.join(columnValues, ", "))
.append(");");
String sqlStr = sql.toString();
log.debug(sqlStr);
jdbcTemplate.execute(sqlStr);
}
/**
* 更新資料
*
* @param before 原始資料
* @param after 更新後的資料
*/
private void update(List<Column> before, List<Column> after) {
//找到改動的資料
List<Column> updataCols = after.stream().filter(column -> column.getUpdated()).collect(Collectors.toList());
//找到之前的資料中的keys
List<Column> keyCols = before.stream().filter(column -> column.getIsKey()).collect(Collectors.toList());
//沒有key,執行更新替換
if (keyCols.size() == 0) {
return;
}
//根據表對映關係拼裝更新sql
JSONObject mapping = prop.getJSONObject("mapping");
String target = prop.getString("target");
//待更新資料
List<String> updatas = new ArrayList<>();
for (int i = 0; i < updataCols.size(); i++) {
Column updataCol = updataCols.get(i);
if (!mapping.containsKey(updataCol.getName())) {
continue;
}
String name = mapping.getString(updataCol.getName());
if (updataCol.getIsNull()) {
updatas.add("`" + name + "` = null");
} else {
updatas.add("`" + name + "` = '" + updataCol.getValue() + "'");
}
}
//如果沒有要修改的資料，返回
if (updatas.size() == 0) {
return;
}
//keys
List<String> keys = new ArrayList<>();
for (Column keyCol : keyCols) {
String name = mapping.getString(keyCol.getName());
keys.add("`" + name + "` = '" + keyCol.getValue() + "'");
}
StringBuilder sql = new StringBuilder();
sql.append("UPDATE ").append(target).append(" SET ");
sql.append(StringUtils.join(updatas, ", "));
sql.append(" WHERE ");
sql.append(StringUtils.join(keys, "AND "));
String sqlStr = sql.toString();
log.debug(sqlStr);
jdbcTemplate.execute(sqlStr);
}
/**
* 刪除資料
*
* @param before
*/
private void delete(List<Column> before) {
//找到改動的資料
List<Column> keyCols = before.stream().filter(column -> column.getIsKey()).collect(Collectors.toList());
if (keyCols.size() == 0) {
return;
}
//根據表對映關係拼裝更新sql
JSONObject mapping = prop.getJSONObject("mapping");
String target = prop.getString("target");
StringBuilder sql = new StringBuilder();
sql.append("DELETE FROM `").append(target).append("` WHERE ");
List<String> where = new ArrayList<>();
for (Column column : keyCols) {
String name = mapping.getString(column.getName());
where.add(name + " = '" + column.getValue() + "' ");
}
sql.append(StringUtils.join(where, "and "));
String sqlStr = sql.toString();
log.debug(sqlStr);
jdbcTemplate.execute(sqlStr);
}
}
專案在這裡：https://github.com/hjx601496320/miner。