mysql+canal+kafka+elasticsearch構(gòu)建訂單搜索平臺(canal搭建)

1.相關(guān)背景：

在各個互聯(lián)網(wǎng)公司的項(xiàng)目架構(gòu)中腻暮。有一個系統(tǒng)必不可少屉更，那就是各類的搜索系統(tǒng)徙融。不管是b端商家端還是c端用戶。都有各種各樣的搜索需求偶垮。我們今天就基于mysql张咳，canal，kafka似舵，elasticsearch搭建一個簡單的訂單搜索平臺脚猾。

大致流程如下：

1)當(dāng)一條新的訂單數(shù)據(jù)存入到數(shù)據(jù)庫

2)canal監(jiān)聽到數(shù)據(jù)變更

3)數(shù)據(jù)變更存入到kafka中

4)消息監(jiān)聽系統(tǒng)監(jiān)聽kafka消息將數(shù)據(jù)存入到elasticsearch當(dāng)中

5)構(gòu)建搜索api提供用戶查詢

第一部分：

系統(tǒng)搭建階段：

1.數(shù)據(jù)準(zhǔn)備過程(mysql已經(jīng)安裝完成):

testdb.sql

DROP SCHEMA IF EXISTS demo_ds;

DROP SCHEMA IF EXISTS demo_ds_0;

DROP SCHEMA IF EXISTS demo_ds_1;

CREATE SCHEMA IF NOT EXISTS demo_ds;

CREATE SCHEMA IF NOT EXISTS demo_ds_0;

CREATE SCHEMA IF NOT EXISTS demo_ds_1;

CREATE TABLE IF NOT EXISTS demo_ds.t_order (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));

CREATE TABLE IF NOT EXISTS demo_ds.t_order_item (order_item_id BIGINT NOT NULL AUTO_INCREMENT, order_id BIGINT NOT NULL, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_item_id));

CREATE TABLE IF NOT EXISTS demo_ds_0.t_order_0 (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));

CREATE TABLE IF NOT EXISTS demo_ds_0.t_order_1 (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));

CREATE TABLE IF NOT EXISTS demo_ds_0.t_order_item_0 (order_item_id BIGINT NOT NULL AUTO_INCREMENT, order_id BIGINT NOT NULL, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_item_id));

CREATE TABLE IF NOT EXISTS demo_ds_0.t_order_item_1 (order_item_id BIGINT NOT NULL AUTO_INCREMENT, order_id BIGINT NOT NULL, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_item_id));

CREATE TABLE IF NOT EXISTS demo_ds_1.t_order_0 (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));

CREATE TABLE IF NOT EXISTS demo_ds_1.t_order_1 (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));

CREATE TABLE IF NOT EXISTS demo_ds_1.t_order_item_0 (order_item_id BIGINT NOT NULL AUTO_INCREMENT, order_id BIGINT NOT NULL, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_item_id));

CREATE TABLE IF NOT EXISTS demo_ds_1.t_order_item_1 (order_item_id BIGINT NOT NULL AUTO_INCREMENT, order_id BIGINT NOT NULL, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_item_id));

通過mysql客戶端導(dǎo)入(source腳本):

導(dǎo)入完成后，查看數(shù)據(jù)庫:

相關(guān)表:

2.canal安裝：

canal相關(guān)介紹:

https://github.com/alibaba/canal

quick start:https://github.com/alibaba/canal/wiki/QuickStart

使用場景:

基于日志增量訂閱和消費(fèi)的業(yè)務(wù)包括

數(shù)據(jù)庫鏡像

數(shù)據(jù)庫實(shí)時備份

索引構(gòu)建和實(shí)時維護(hù)(拆分異構(gòu)索引砚哗、倒排索引等)

業(yè)務(wù) cache 刷新

帶業(yè)務(wù)邏輯的增量數(shù)據(jù)處理

下面安裝過程:

首先我們需要配置mysql.cnf

對于自建 MySQL , 需要先開啟 Binlog 寫入功能龙助，配置 binlog-format 為 ROW 模式，my.cnf 中配置如下

然后需要重啟蛛芥，使用如下命令:

mysql.server restart

接下來需要授權(quán):

授權(quán) canal 鏈接 MySQL 賬號具有作為 MySQL slave 的權(quán)限, 如果已有賬戶可直接 grant

CREATE USER canalI DENTIFIED BY 'canal';

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENTON*.*TO'canal'@'%';--GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%' ;

FLUSH PRIVILEGES;

接下來下載最新的canal發(fā)布包:

wget https://github.com/alibaba/canal/releases/download/canal-1.1.3/canal.deployer-1.1.3.tar.gz

mkdir /tmp/canal

tar zxvf canal.deployer-1.1.3.tar.gz? -C /tmp/canal

解壓目錄如下：

配置修改

vi conf/example/instance.properties

## mysql serverId

canal.instance.mysql.slaveId = 1234

#position info提鸟，需要改成自己的數(shù)據(jù)庫信息

canal.instance.master.address = 127.0.0.1:3306

canal.instance.master.journal.name =

canal.instance.master.position =

canal.instance.master.timestamp =

#canal.instance.standby.address =

#canal.instance.standby.journal.name =

#canal.instance.standby.position =

#canal.instance.standby.timestamp =

#username/password，需要改成自己的數(shù)據(jù)庫信息

canal.instance.dbUsername = canal?

canal.instance.dbPassword = canal

canal.instance.defaultDatabaseName =

canal.instance.connectionCharset = UTF-8

#table regex

canal.instance.filter.regex = .\*\\\\..\*

canal.instance.connectionCharset 代表數(shù)據(jù)庫的編碼方式對應(yīng)到 java 中的編碼類型仅淑，比如 UTF-8称勋，GBK , ISO-8859-1

如果系統(tǒng)是1個 cpu，需要將 canal.instance.parser.parallel 設(shè)置為 false

啟動

sh bin/startup.sh

關(guān)閉

sh bin/stop.sh