【Maxwell】03 定向监听&全量输出
一、定向监听
定向监听,即只监听某一个特定的表,或者库
1、创建样本案例
-- 创建监听的库(演示样本) CREATE DATABASE `test-db-2` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci'; -- 和需要监听的表 (演示样本) CREATE TABLE `test-db-2`.`sample` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `NAME` varchar(64) DEFAULT NULL, `TYPE` int(12) DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
2、定向监听的参数项:
cd /usr/local/maxwell-1.29.2 ./bin/maxwell \ --user='maxwell' \ --password='123456' \ --host='192.168.2.225' \ --port='3308' \ --producer='stdout' \ --filter="exclude:*.*, include:test-db-2.sample" \ --jdbc_options='useSSL=false&serverTimezone=Asia/Shanghai' # 定向监听,通过过滤器参数实现 --filter="exclude:*.*, include:test-db-2.sample" # exclude,表示排除 # include,表示包含
针对该库的sample表进行操作,是能够监听的
[root@localhost maxwell-1.29.2]# ./bin/maxwell \ > --user='maxwell' \ > --password='123456' \ > --host='192.168.2.225' \ > --port='3308' \ > --producer='stdout' \ > --filter="exclude:*.*, include:test-db-2.sample" \ > --jdbc_options='useSSL=false&serverTimezone=Asia/Shanghai' Using kafka version: 1.0.0 17:01:57,280 INFO Maxwell - Starting Maxwell. maxMemory: 247332864 bufferMemoryUsage: 0.25 17:01:57,493 INFO Maxwell - Maxwell v1.29.2 is booting (StdoutProducer), starting at Position[BinlogPosition[mysql-bin.000005:656973], lastHeartbeat=1642495761716] 17:01:57,651 INFO MysqlSavedSchema - Restoring schema id 1 (last modified at Position[BinlogPosition[mysql-bin.000005:16191], lastHeartbeat=0]) 17:01:57,819 INFO BinlogConnectorReplicator - Setting initial binlog pos to: mysql-bin.000005:656973 17:01:57,856 INFO BinaryLogClient - Connected to 192.168.2.225:3308 at mysql-bin.000005/656973 (sid:6379, cid:254) 17:01:57,856 INFO BinlogConnectorReplicator - Binlog connected. 17:01:58,149 INFO AbstractSchemaStore - storing schema @Position[BinlogPosition[mysql-bin.000005:657429], lastHeartbeat=1642495761716] after applying "CREATE DATABASE `test-db-2` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci'" to test-db-2, new schema id is 2 17:01:58,223 INFO AbstractSchemaStore - storing schema @Position[BinlogPosition[mysql-bin.000005:657658], lastHeartbeat=1642495761716] after applying "CREATE TABLE `sample` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `NAME` varchar(64) DEFAULT NULL, `TYPE` int(12) DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4" to test-db-2, new schema id is 3 {"database":"test-db-2","table":"sample","type":"insert","ts":1642496453,"xid":87865,"commit":true,"data":{"ID":1,"NAME":"1","TYPE":11}}
如果在其他库操作,Maxwell则是不会监听的
INSERT INTO `test-db`.`day_sale` (`ID`, `PRODUCT`, `CHANNEL`, `AMOUNT`, `SALE_DATE`) VALUES (NULL, '产品A', '拼多多', 2497.0000, NOW()), (NULL, '产品B', '京东', 2497.0000, NOW()), (NULL, '产品C', '淘宝', 2497.0000, NOW())
二、全量输出
全量输出,通过主库存放的maxwell库的元数据的更改,实现数据初始化
(除了这种方法还有其他3种,未列举出来)
需求:
将 test-db-2 库下的 sample 表的四条数据,全量导入到 maxwell 控制台进行打印。
操作:
INSERT INTO `maxwell`.`bootstrap` (`id`, `database_name`, `table_name`, `where_clause`, `is_complete`, `inserted_rows`, `total_rows`, `created_at`, `started_at`, `completed_at`, `binlog_file`, `binlog_position`, `client_id`, `comment`) VALUES (NULL, 'test-db-2', 'sample', NULL, 0, 0, 0, NULL, NULL, NULL, NULL, 0, 'maxwell', NULL);
输出日志
# 不关闭maxwell也能执行,maxwell先监听了maxwell库的bootstrap表的操作,发现了初始化操作,然后执行同步 {"database":"maxwell","table":"bootstrap","type":"insert","ts":1642497749,"xid":90142,"commit":true,"data":{"id":2,"database_name":"test-db-2","table_name":"sample","where_clause":null,"is_complete":0,"inserted_rows":0,"total_rows":0,"created_at":null,"started_at":null,"completed_at":null,"binlog_file":null,"binlog_position":0,"client_id":"maxwell","comment":null}} {"database":"test-db-2","table":"sample","type":"bootstrap-start","ts":1642497347,"data":{}} 17:15:47,541 INFO SynchronousBootstrapper - bootstrapping started for test-db-2.sample {"database":"test-db-2","table":"sample","type":"bootstrap-insert","ts":1642497347,"data":{"ID":1,"NAME":"1","TYPE":11}} {"database":"test-db-2","table":"sample","type":"bootstrap-insert","ts":1642497347,"data":{"ID":2,"NAME":"2","TYPE":22}} {"database":"test-db-2","table":"sample","type":"bootstrap-insert","ts":1642497347,"data":{"ID":3,"NAME":"3","TYPE":33}} {"database":"test-db-2","table":"sample","type":"bootstrap-insert","ts":1642497347,"data":{"ID":4,"NAME":"4","TYPE":44}} {"database":"test-db-2","table":"sample","type":"bootstrap-insert","ts":1642497347,"data":{"ID":5,"NAME":"5","TYPE":55}} {"database":"test-db-2","table":"sample","type":"bootstrap-complete","ts":1642497347,"data":{}} 17:15:47,592 INFO SynchronousBootstrapper - bootstrapping ended for #2 test-db-2.sample
同步完成的结果:
SELECT * FROM `maxwell`.`bootstrap`; +----+---------------+------------+--------------+-------------+---------------+------------+------------+---------------------+---------------------+-------------+-----------------+-----------+---------+ | id | database_name | table_name | where_clause | is_complete | inserted_rows | total_rows | created_at | started_at | completed_at | binlog_file | binlog_position | client_id | comment | +----+---------------+------------+--------------+-------------+---------------+------------+------------+---------------------+---------------------+-------------+-----------------+-----------+---------+ | 2 | test-db-2 | sample | NULL | 1 | 5 | 0 | NULL | 2022-01-18 17:22:30 | 2022-01-18 17:22:30 | NULL | 0 | maxwell | NULL | +----+---------------+------------+--------------+-------------+---------------+------------+------------+---------------------+---------------------+-------------+-----------------+-----------+---------+
当数据全部初始化完成以后,Maxwell 的元数据会变化
is_complete 字段从 0 变为 1
started_at 字段从 null 变为具体时间(数据同步开始时间)
completed_at 字段从 null 变为具体时间(数据同步结束时间)