環境介紹：

主機IP：192.168.10.13

Canal和MySQL同時安裝到這臺機器上

安裝配置 Mysql5.7.36

下載mysql 5.7.36軟體包

# cd /usr/local

# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz

解壓縮

# tar zxvf mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz

# mv mysql-5.7.36-linux-glibc2.12-x86_64 mysql

編輯~/.bash_profile

# vi ~/.bash_profile

PATH=/usr/local/mysql/bin:$PATH:$HOME/bin

export PATH

# groupadd mysql

# useradd -g mysql mysql

編輯/etc/passwd

# vi /etc/passwd

mysql:x:1000:1000::/home/mysql:/sbin/nologin

# mkdir -p /data/mysql/data

# mkdir -p /data/mysql/log

# mkdir -p /data/mysql/log/binlog

# chown -R mysql.mysql /data/mysql

編輯/etc/my.cnf

# vi /etc/my.cnf

[mysqld]

gtid_mode = on

enforce_gtid_consistency = 1

master_info_repository=TABLE

relay_log_info_repository=TABLE

##base info

basedir=/usr/local/mysql

datadir=/data/mysql/data

socket=/data/mysql/data/mysql.sock

pid-file=/data/mysql/log/mysqld.pid

symbolic-links=0

server-id = 10135

port = 3306

user = mysql

character_set_server=utf8mb4

skip_name_resolve = 1

max_connections = 1000

max_connect_errors = 1000

log-error=/data/mysql/log/mysqld.err

lower_case_table_names=1

log-slave-updates

skip-slave-start

##binlog

expire_logs_days = 3

binlog_format=ROW

log_bin = /data/mysql/log/binlog/mysql_binlog

binlog_cache_size =32768

sync_binlog = 0

##other cache

join_buffer_size = 2M

tmp_table_size = 64M

table_open_cache=6000

table_definition_cache=3400

tmpdir = /tmp

max_allowed_packet = 64M

interactive_timeout = 900

wait_timeout = 1200

read_buffer_size = 8M

read_rnd_buffer_size = 8M

sort_buffer_size = 8M

innodb_sort_buffer_size = 8M

##slow log

slow_query_log = 1

long_query_time = 2

slow_query_log_file = /data/mysql/log/slow.log

log_queries_not_using_indexes = 1

log_slow_admin_statements = 1

log_slow_slave_statements = 1

log_throttle_queries_not_using_indexes = 10

min_examined_row_limit = 1000

innodb_print_all_deadlocks = 1

##buffer_pool

innodb_buffer_pool_size = 4G

innodb_buffer_pool_instances = 8

innodb_page_cleaners = 4

innodb_buffer_pool_load_at_startup = 1

innodb_buffer_pool_dump_at_shutdown = 1

innodb_io_capacity = 1000

innodb_io_capacity_max = 8000

innodb_lock_wait_timeout = 30

innodb_file_format = Barracuda

innodb_file_format_max = Barracuda

##redo

innodb_log_file_size = 512M

innodb_log_buffer_size = 64M

innodb_log_files_in_group = 4

##undo

innodb_undo_log_truncate = 1

innodb_undo_logs = 128

innodb_max_undo_log_size = 512M

innodb_purge_rseg_truncate_frequency = 128

innodb_undo_tablespaces= 3

innodb_purge_threads = 4

##other

log_timestamps=system

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

[client]

socket=/data/mysql/data/mysql.sock

初始化MySQL:

# mysqld --initialize-insecure --user=mysql

啟動MySQL:

# mysqld_safe --user=mysql &

登入MySQL:

mysql -uroot -p

預設空密碼

修改密碼:

mysql> alter user root@localhost identified by '123456';

建立新使用者:

mysql> create user root@'%' identified by '123456';

mysql> grant all on *.* to root@'%' with grant option;

mysql> flush privileges;

重新登入:

mysql -uroot -p123456

在demo庫上建立MySQL測試表:

mysql> create database demo;

mysql> use demo

mysql> CREATE TABLE `test_cdc` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`sex` tinyint(1) DEFAULT NULL,

`name` varchar(20) DEFAULT NULL,

`address` varchar(255) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

安裝配置canal:

下載canal-1.1.5:

#wget

解壓Canal到指定目錄：

# mkdir /root/cacal

# cd /root/cacal

# tar zxvf canal.deployer-1.1.5.tar.gz

編輯配置檔案:

# mkdir -p canal/conf/demo

# cp conf/example/instance.properties conf/demo/

編輯配置檔案:

# vi conf/demo/instance.properties

按照如下內容進行配置

#################################################

## mysql serverId , v1.0.26+ will autoGen

canal.instance.mysql.slaveId=1013

# enable gtid use true/false

canal.instance.gtidon=false

# position info

canal.instance.master.address=192.168.10.13:3306

canal.instance.master.journal.name=

canal.instance.master.position=

canal.instance.master.timestamp=

canal.instance.master.gtid=

# rds oss binlog

canal.instance.rds.accesskey=

canal.instance.rds.secretkey=

canal.instance.rds.instanceId=

# table meta tsdb info

canal.instance.tsdb.enable=true

#canal.instance.tsdb.url=jdbc:mysql://127.0.0.1:3306/canal_tsdb

#canal.instance.tsdb.dbUsername=canal

#canal.instance.tsdb.dbPassword=canal

#canal.instance.standby.address =

#canal.instance.standby.journal.name =

#canal.instance.standby.position =

#canal.instance.standby.timestamp =

#canal.instance.standby.gtid=

# username/password

canal.instance.dbUsername=canal

canal.instance.dbPassword=canal

canal.instance.connectionCharset = UTF-8

# enable druid Decrypt database password

canal.instance.enableDruid=false

#canal.instance.pwdPublicKey=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALK4BUxdDltRRE5/zXpVEVPUgunvscYFtEip3pmLlhrWpacX7y7GCMo2/JM6LeHmiiNdH1FWgGCpUfircSwlWKUCAwEAAQ==

# table regex

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

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

# table black regex

canal.instance.filter.black.regex=

# table field filter(format: schema1.tableName1:field1/field2,schema2.tableName2:field1/field2)

#canal.instance.filter.field=test1.t_product:id/subject/keywords,test2.t_company:id/name/contact/ch

# table field black filter(format: schema1.tableName1:field1/field2,schema2.tableName2:field1/field2)

#canal.instance.filter.black.field=test1.t_product:subject/product_image,test2.t_company:id/name/contact/ch

# mq config

canal.mq.topic=example

# dynamic topic route by schema or table regex

#canal.mq.dynamicTopic=mytest1.user,mytest2\\..*,.*\\..*

canal.mq.partition=0

# hash partition config

#canal.mq.partitionsNum=3

#canal.mq.partitionHash=test.table:id^name,.*\\..*

#canal.mq.dynamicTopicPartitionNum=test.*:4,mycanal:6

#################################################

啟動Canal

# sh bin/startup.sh

驗證是否啟動成功

# tail -200f logs/demo/demo.log

出現如下資訊說明啟動成功:

2022-03-01 16:26:37.547 [canal-instance-scan-0] INFO c.a.otter.canal.instance.spring.CanalInstanceWithSpring - start CannalInstance for 1-demo

2022-03-01 16:26:37.549 [canal-instance-scan-0] WARN c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert - --> init table filter : ^.*\..*$

2022-03-01 16:26:37.549 [canal-instance-scan-0] WARN c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert - --> init table black filter : ^mysql\.slave_.*$

2022-03-01 16:26:37.549 [canal-instance-scan-0] INFO c.a.otter.canal.instance.core.AbstractCanalInstance - start successful....

2022-03-01 16:26:37.552 [destination = demo , address = /192.168.10.13:3306 , EventParser] WARN c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - ---> begin to find start position, it will be long time for reset or first position

2022-03-01 16:26:37.552 [destination = demo , address = /192.168.10.13:3306 , EventParser] WARN c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - prepare to find start position just show master status

2022-03-01 16:26:37.766 [destination = demo , address = /192.168.10.13:3306 , EventParser] WARN c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - ---> find start position successfully, EntryPosition[included=false,journalName=mysql_binlog.000002,position=8331,serverId=1013,gtid=,timestamp=1646025301000] cost : 208ms , the next step is binlog dump

2022-03-01 16:26:37.886 [MultiStageCoprocessor-other-demo-0] WARN c.a.o.canal.parse.inbound.mysql.tsdb.DatabaseTableMeta - dup apply for sql : DROP TABLE `test_cdc` /* generated by server */

2022-03-01 16:26:37.899 [MultiStageCoprocessor-other-demo-0] WARN c.a.o.canal.parse.inbound.mysql.tsdb.DatabaseTableMeta - dup apply for sql : CREATE TABLE `test_cdc` (

`id` int NOT NULL AUTO_INCREMENT,

`sex` TINYINT(1) DEFAULT NULL,

`name` varchar(20) DEFAULT NULL,

`address` varchar(255) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB

2022-03-01 16:46:13.128 [New I/O server worker #1-1] INFO c.a.otter.canal.instance.core.AbstractCanalInstance - subscribe filter change to demo.test_cdc

2022-03-01 16:46:13.128 [New I/O server worker #1-1] WARN c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert - --> init table filter : ^demo.test_cdc$

2022-03-01 17:41:03.366 [New I/O server worker #1-2] INFO c.a.otter.canal.instance.core.AbstractCanalInstance - subscribe filter change to demo.test_cdc

2022-03-01 17:41:03.366 [New I/O server worker #1-2] WARN c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert - --> init table filter : ^demo.test_cdc$

注意：

建立同步任務之前，首先要在fe.conf裡配置enable_create_sync_job=true，這個值預設是false不啟用，否則就不能建立同步任務。

不想重啟fe的話，這個引數也可以手動動態修改:

mysql>ADMIN SET FRONTEND CONFIG ("dynamic_partition_enable" = "true");`

開始同步資料步驟:

建立Doris目標表

使用者需要先在Doris端建立好與Mysql端對應的目標表：

mysql -uroot -p -h 192.168.10.13 -P 9030

mysql>create database demo

mysql>use demo

mysql>CREATE TABLE `doris_mysql_binlog_demo` (

`id` int(11) NOT NULL COMMENT "",

`sex` tinyint(4) NULL COMMENT "",

`name` varchar(20) NULL COMMENT "",

`address` varchar(255) NULL COMMENT ""

) ENGINE=OLAP

UNIQUE KEY(`id`, `sex`)

COMMENT "OLAP"

DISTRIBUTED BY HASH(`sex`) BUCKETS 1

PROPERTIES (

"replication_allocation" = "tag.location.default: 3",

"in_memory" = "false",

"storage_format" = "V2"

);

手動啟用同步(create_sync_job)功能:

mysql> ADMIN SET FRONTEND CONFIG ("enable_create_sync_job" = "true");

Query OK, 0 rows affected (0.00 sec)

mysql> ADMIN SET FRONTEND CONFIG ("dynamic_partition_enable" = "true");

Query OK, 0 rows affected (0.00 sec)

檢視同步功能對應的值:

mysql> ADMIN SHOW FRONTEND CONFIG like 'enable_create_sync_job';

+------------------------+-------+---------+-----------+------------+---------+

+------------------------+-------+---------+-----------+------------+---------+

+------------------------+-------+---------+-----------+------------+---------+

1 row in set (0.01 sec)

開始建立同步任務:

mysql> CREATE SYNC `demo`.`job1`

(

FROM `demo`.`test_cdc` INTO `doris_mysql_binlog_demo`

)

FROM BINLOG

(

"type" = "canal",

"canal.server.ip" = "192.168.10.13",

"canal.server.port" = "11111",

"canal.destination" = "demo",

"canal.username" = "canal",

"canal.password" = "canal"

);

檢視同步任務狀態:

mysql> SHOW SYNC JOB from demo \G

*************************** 1. row ***************************

JobId: 1045572

JobName: job1

Type: CANAL

State: RUNNING

Channel: demo.test_cdc->doris_mysql_binlog_demo

Status: position:[mysql_binlog.000002:5946], executeTime:[2022-02-28 10:23:30], delay:2902ms

JobConfig: address:192.168.10.13:11111,destination:demo,batchSize:8192

CreateTime: 2022-03-01 10:20:42

LastStartTime: 2022-03-01 10:20:45

LastStopTime: NULL

FinishTime: NULL

Msg: NULL

2 rows in set (0.01 sec)

往mysql庫裡插入記錄:

mysql -uroot -p123456

mysql> use demo

mysql> insert into test_cdc (sex,name,address) values(10,'dsf','hangzhou');

Query OK, 1 row affected (0.04 sec)

mysql> insert into test_cdc (sex,name,address) values(20,'lisi','zhangzhou');

Query OK, 1 row affected (0.08 sec)

mysql> insert into test_cdc (sex,name,address) values(30,'chenfeng','zhengzhou');

Query OK, 1 row affected (0.05 sec)

過幾秒鐘，檢視doris庫這一端mysql剛剛插入的記錄:

# mysql -uroot -p -h 192.168.10.13 -P 9030

mysql> use demo

mysql> select * from doris_mysql_binlog_demo;

+------+------+----------+-----------+

| id | sex | name | address |

+------+------+----------+-----------+

| 3 | 10 | dsf | hangzhou |

| 5 | 20 | lisi | zhangzhou |

| 6 | 30 | chenfeng | zhengzhou |

+------+------+----------+-----------+

3 rows in set (0.01 sec)

MySQL端刪除一條記錄：

mysql> delete from test_cdc where name='lisi';

Query OK, 1 row affected (0.07 sec)

Doris端檢視:

mysql> select * from doris_mysql_binlog_demo;

+------+------+----------+-----------+

| id | sex | name | address |

+------+------+----------+-----------+

| 3 | 10 | dsf | hangzhou |

| 6 | 30 | chenfeng | zhengzhou |

+------+------+----------+-----------+

2 rows in set (0.00 sec)

MySQL端更新一條記錄:

mysql> update test_cdc set id=4 where name='chenfeng';

Query OK, 1 row affected (0.05 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Doris端檢視：

mysql> select * from doris_mysql_binlog_demo;

+------+------+----------+-----------+

| id | sex | name | address |

+------+------+----------+-----------+

| 3 | 10 | dsf | hangzhou |

| 4 | 30 | chenfeng | zhengzhou |

+------+------+----------+-----------+

2 rows in set (0.01 sec)

資料已更新。

停止同步任務命令:

mysql> stop sync job1;

檢視stop後的狀態：

mysql> SHOW SYNC JOB from demo \G

*************************** 1. row ***************************

JobId: 10013

JobName: job1

Type: CANAL

State: CANCELLED

Channel: demo.test_cdc->doris_mysql_binlog_demo

Status: position:[mysql_binlog.000002:9959], executeTime:[2022-03-01 16:47:57], delay:846ms

JobConfig: address:192.168.10.13:11111,destination:demo,batchSize:8192

CreateTime: 2022-03-01 16:46:08

LastStartTime: 2022-03-01 16:46:13

LastStopTime: 2022-03-01 17:37:31

FinishTime: 2022-03-01 17:37:31

Msg: SyncFailMsg [type=USER_CANCEL, msg=user cancel]

再次啟用的話，需要重新create sync job：

mysql> CREATE SYNC `demo`.`job1`

(

FROM `demo`.`test_cdc` INTO `doris_mysql_binlog_demo`

)

FROM BINLOG

(

"type" = "canal",

"canal.server.ip" = "192.168.10.13",

"canal.server.port" = "11111",

"canal.destination" = "demo",

"canal.username" = "canal",

"canal.password" = "canal"

);

檢視同步任務:

mysql> SHOW SYNC JOB from demo \G

*************************** 1. row ***************************

JobId: 10013

JobName: job1

Type: CANAL

State: CANCELLED

Channel: demo.test_cdc->doris_mysql_binlog_demo

Status: position:[mysql_binlog.000002:9959], executeTime:[2022-03-01 16:47:57], delay:846ms

JobConfig: address:192.168.10.13:11111,destination:demo,batchSize:8192

CreateTime: 2022-03-01 16:46:08

LastStartTime: 2022-03-01 16:46:13

LastStopTime: 2022-03-01 17:37:31

FinishTime: 2022-03-01 17:37:31

Msg: SyncFailMsg [type=USER_CANCEL, msg=user cancel]

*************************** 2. row ***************************

JobId: 10015

JobName: job1

Type: CANAL

State: RUNNING

Channel: demo.test_cdc->doris_mysql_binlog_demo

Status: position:[mysql_binlog.000002:10528], executeTime:[2022-03-01 17:43:23], delay:6736ms

JobConfig: address:192.168.10.13:11111,destination:demo,batchSize:8192

CreateTime: 2022-03-01 17:40:58

LastStartTime: 2022-03-01 17:41:03

LastStopTime: NULL

FinishTime: NULL

Msg: NULL

2 rows in set (0.01 sec)

多表同步例子:

多表同步只需要像下面這樣寫法就可以了,例如：

CREATE SYNC test_2.doris_mysql_binlog_demo_job

(

FROM demo.test_cdc INTO doris_mysql_binlog_demo,

FROM demo.test_cdc_1 INTO doris_mysql_binlog_demo,

FROM demo.test_cdc_2 INTO doris_mysql_binlog_demo,

FROM demo.test_cdc_3 INTO doris_mysql_binlog_demo

)

MySQL端大批次寫入資料例子：

mysql端建表:

mysql> create database test;

mysql>use test

mysql> CREATE TABLE `test` (

-> `id` int(11) NOT NULL,

-> `a` int(11) DEFAULT NULL,

-> `b` int(11) DEFAULT NULL,

-> PRIMARY KEY (`id`),

-> KEY `a` (`a`),

-> KEY `b` (`b`)

-> ) ENGINE=InnoDB;

批次寫入資料儲存過程：

mysql>delimiter ;;

mysql>create procedure idata()

begin

declare i int;

set i=1;

while(i<=1000000)do

insert into test values(i, i, i);

set i=i+1;

end while;

end;;

mysql>delimiter ;

mysql>call idata();

mysql>select count(*) from test;

doris端建表:

mysql>create database test;

mysql> use test

mysql>

CREATE TABLE test

(

id INT(11) not null,

a int(11) null,

b int(11) null

)

ENGINE=olap

UNIQUE KEY(id,a,b)

COMMENT "測試表"

DISTRIBUTED BY HASH(a) BUCKETS 32

PROPERTIES ("storage_type"="column");

同步資料:

連線doris:

# mysql -uroot -pxxxxx -h 192.168.10.13 -P 9030

mysql>use test

mysql>CREATE SYNC `test`.`job1`

(

FROM `test`.`test` INTO `test`

)

FROM BINLOG

(

"type" = "canal",

"canal.server.ip" = "127.0.0.1",

"canal.server.port" = "11111",

"canal.destination" = "example",

"canal.username" = "",

"canal.password" = ""

);

檢視同步任務:

mysql> show sync job from test \G

*************************** 1. row ***************************

JobId: 11820

JobName: job1

Type: CANAL

State: RUNNING

Channel: test.t->t

Status: position:[mysql_binlog.000002:11705270], executeTime:[2022-03-03 16:18:25], delay:774ms

JobConfig: address:127.0.0.1:11111,destination:example,batchSize:8192

CreateTime: 2022-03-03 16:01:33

LastStartTime: 2022-03-03 16:01:33

LastStopTime: NULL

FinishTime: NULL

Msg: NULL

1 row in set (0.01 sec)

MySQL端檢視資料：

mysql -uroot -pxxxxx test -e "select count(*) from test.test;"

Doris端檢視資料：

mysql -uroot -pxxxxx -h 192.168.10.14 -P 9030 test -e "select count(*) from test.test;"

Doris 0.15 binlog load for centos 7測試詳細步驟

相關文章