CDC系列（二）、Maxwell_v1.27.1 监控MySQL操作日志实时同步到Kafka

Myth丶恋晨 2022-12-09 14:54 328阅读 0赞

**目录**

前言

安装

配置

使用Maxwell监控MySQL操作并写入Kafka

尾巴

--------------------

**CDC系列：**

[CDC系列（一）、Canal 集群部署及使用（带WebUI）][CDC_Canal _WebUI]

--------------------

# 前言 #

在上一篇我们介绍了CDC工具，以及Canal的集群安装和使用，本篇我们来讲解另一个CDC工具：Maxwell。和Canal一样，Maxwell也是将自己伪装成MySQL的slave节点，通过监控MySQL的binlog来将数据操作日志同步到kafka等消息队列中供异构数据源使用。

本篇我们会介绍Maxwell的安装和使用。

# 安装 #

和Canal一样，一定要至少准备一个[MySQL库][MySQL]用于Maxwell的管理库存放状态信息以及用来监控的MySQL库，监控到的binlog导出到kafka，因此也需要准备[kafka][] 和[zookeeper ][zookeeper]，Maxwell也是java开发的因此需要JDK1.8，这些在之前的文章中都有介绍安装方式，因此本文不再赘述。

下载Maxwell\_v1.27.1： [https://github.com/zendesk/maxwell/releases/download/v1.27.1/maxwell-1.27.1.tar.gz][https_github.com_zendesk_maxwell_releases_download_v1.27.1_maxwell-1.27.1.tar.gz]

解压下载的tar包：

tar -zxvf maxwell-1.27.1.tar.gz -C /opt/app/

# 配置 #

和Canal一样，被监控的MySQL一定要开启Binlog，具体步骤可参考上一篇内容：[MySQL开启Binlog][MySQL_Binlog]。

其次在Maxwell的管理数据库上创建用户maxwell并授权，由于这里演示的被监控的mysql和maxwell在同一个mysql实例上，因此这里直接使用maxwell用户来监控mysql binlog，在实际生产应用中一般是将这两个库分开的，在下面的config\_csdn.properties 配置中也可以体现出来。

-- 创建maxwell管理库的连接账户
    CREATE USER 'maxwell'@'%' IDENTIFIED BY 'maxwell_pwd';
    GRANT ALL ON maxwell.* TO 'maxwell'@'%';
    GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'maxwell'@'%';

修改配置文件，详细选项可参考：[http://maxwells-daemon.io/config/][http_maxwells-daemon.io_config]

cd /opt/app/maxwell-1.27.1
    cp config.properties.example config_csdn.properties
    vim config_csdn.properties
    # 修改下面这些配置，其他配置选项可以默认或按需配置，可参考：http://maxwells-daemon.io/config/
    -------------------------------------------
    log_level=info
    
    # 该ID不能与mysql my.cnf中的一样，也不能与其他监控同一个库的CDC中配置id一样
    replica_server_id=9
    
    # 将监控的log导出到kafka
    producer=kafka
    kafka.bootstrap.servers=wyk01:9092,wyk02:9092,wyk03:9092
    # 增删改操作和ddl操作分别写到下面两个topic内
    kafka_topic=wyk_csdn_maxwell
    ddl_kafka_topic=wyk_csdn_maxwell
    #kinesis_stream=maxwell
    #include_dbs=test_csdn
    kafka_version=0.11.0
    kafka.compression.type=snappy
    kafka.retries=0
    kafka.acks=-1
    
    # 配置maxwell的管理库连接
    # mysql login info
    host=wyk01
    port=3306
    user=maxwell
    password=maxwell_pwd
    schema_database=maxwell
    jdbc_options=autoReconnect=true
    
    # 配置监控binlog的mysql库连接信息，如果没配置此内容的话，默认被监控的mysql和maxwell管理库是同一个mysql，即上面配置的内容。  这里被监控的mysql的连接用户需要有该mysql的binlog复制权限
    # maxwell can optionally replicate from a different server than where it stores
    # schema and binlog position info.  Specify that different server here:
    replication_host=wyk01
    replication_port=3306
    replication_user=maxwell
    replication_password=maxwell_pwd
    
    # 对log的过滤条件, include/exclude，具体请参考：http://maxwells-daemon.io/filtering
    # 下面表示只监控test_csdn库下的所有表，不监控maxwell库的变更操作
    filter=include: test_csdn.*,exclude: maxwell.*

# 使用Maxwell监控MySQL操作并写入Kafka #

上面的配置文件写完之后我们就可以直接启动maxwell监控mysql的操作，然后将监控的操作日志写入到kafka了，相关命令如下：

# 启动maxwell进程
    bin/maxwell --config config_csdn.properties
    # 后台启动maxwell进程
    bin/maxwell --config config_csdn.properties --daemon
    
    # 开启一个kafka的消费端验证效果
    bin/kafka-console-consumer.sh --bootstrap-server wyk01:9092,wyk02:9092,wyk03:9092 --topic wyk_csdn_maxwell
    
    # 全量推送指定表（test_csdn.wyk_csdn）的数据到kafka内
    bin/maxwell-bootstrap --config config_csdn.properties --database test_csdn --table wyk_csdn --client_id=csdn_full --bootstrapper=sync

> **注意：**
> 
> **1**. 当启动多个Maxwell，需要为每个实例配置不同的*client\_id 字符串型*（例如上面的第4个命令），以存储不同的binlog位点，如：
> 
> *bin/maxwell-bootstrap --config config\_csdn.properties --database test\_csdn --table wyk\_csdn \--client\_id=csdn\_full  \--bootstrapper=sync* 
> 
> **2**. Maxwell支持对指定的表全量同步，当参数*--bootstrapper=sync *时，在处理bootstrap时，会阻塞正常的binlog解析，当参数--*bootstrapper=async*时，不会阻塞。
> 
> 触发全量同步也可以直接在maxwell库下插入一条记录：
> 
> *insert into maxwell.bootstrap (database\_name, table\_name) values ('fooDB', 'barTable');*
> 
> 或指定client\_id：
> 
> *insert into maxwell.bootstrap (database\_name, table\_name, client\_id) values ('fooDB', 'barTable', 'custom\_maxwell\_client\_id');*
> 
> 当全量同步时，消息结构以bootstrap-start 声明开始，然后是bootstrap-insert 全量同步数据，最后以bootstrap-complete 结尾。
> 
> 若在全量同步过程中出现故障，下次重新启动时会从头重新开始，若重启遇到问题，可以到maxwell管理库将 bootstrap表的is\_completed字段更新为true即可。
> 
> 更加详细的Maxwell Bootstrap请参考：[http://maxwells-daemon.io/bootstrapping/][http_maxwells-daemon.io_bootstrapping]

启动maxwell及kafka消费者之后，在mysql中执行下面的命令，验证是否会被maxwell发送到kafka内：

create database test_csdn;
    use test_csdn;
    -- 建表
    create table wyk_csdn(id int,name varchar(20),ins_ts timestamp);
    
    -- 插入2条数据
    insert into wyk_csdn values(1,'wyk1',current_timestamp());
    insert into wyk_csdn values(2,'wyk2',current_timestamp());
    
    -- 更新id=2的数据
    update wyk_csdn set name='wyk2_new' where id=2;
    
    -- 删除id=1的数据
    delete from wyk_csdn where id=1;
    
    -- 清空表
    truncate table wyk_csdn;
    
    -- 删除表
    drop table wyk_csdn;
    
    -- 修改表结构
    create table wyk_csdn2(id int,name varchar(20),ins_ts timestamp);
    alter table wyk_csdn2 add column(add_c text);
    
    -- 修改表名
    rename table wyk_csdn2 to wyk_csdn2_new;
    
    drop table wyk_csdn2_new;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70][]

![20200922181549716.png][]

Maxwell监控binlog发送的json消息格式：

{
    	"type": "table-create",
    	"database": "test_csdn",
    	"table": "wyk_csdn",
    	"def": {
    		"database": "test_csdn",
    		"charset": "latin1",
    		"table": "wyk_csdn",
    		"columns": [
    			{
    				"type": "int",
    				"name": "id",
    				"signed": true
    			},
    			{
    				"type": "varchar",
    				"name": "name",
    				"charset": "latin1"
    			},
    			{
    				"type": "timestamp",
    				"name": "ins_ts",
    				"column-length": 0
    			}
    		],
    		"primary-key": []
    	},
    	"ts": 1600769161000,
    	"sql": "create table wyk_csdn(id int,name varchar(20),ins_ts timestamp)"
    }
    {
    	"database": "test_csdn",
    	"table": "wyk_csdn",
    	"type": "insert",
    	"ts": 1600769172,
    	"xid": 74698,
    	"commit": true,
    	"data": {
    		"id": 1,
    		"name": "wyk1",
    		"ins_ts": "2020-09-22 10:06:12"
    	}
    }
    {
    	"database": "test_csdn",
    	"table": "wyk_csdn",
    	"type": "insert",
    	"ts": 1600769175,
    	"xid": 74713,
    	"commit": true,
    	"data": {
    		"id": 2,
    		"name": "wyk2",
    		"ins_ts": "2020-09-22 10:06:15"
    	}
    }
    {
    	"database": "test_csdn",
    	"table": "wyk_csdn",
    	"type": "update",
    	"ts": 1600769181,
    	"xid": 74739,
    	"commit": true,
    	"data": {
    		"id": 2,
    		"name": "wyk2_new",
    		"ins_ts": "2020-09-22 10:06:21"
    	},
    	"old": {
    		"name": "wyk2",
    		"ins_ts": "2020-09-22 10:06:15"
    	}
    }
    {
    	"database": "test_csdn",
    	"table": "wyk_csdn",
    	"type": "delete",
    	"ts": 1600769184,
    	"xid": 74756,
    	"commit": true,
    	"data": {
    		"id": 1,
    		"name": "wyk1",
    		"ins_ts": "2020-09-22 10:06:12"
    	}
    }
    {
    	"type": "table-drop",
    	"database": "test_csdn",
    	"table": "wyk_csdn",
    	"ts": 1600769190000,
    	"sql": "DROP TABLE `wyk_csdn` /* generated by server */"
    }
    {
    	"type": "table-create",
    	"database": "test_csdn",
    	"table": "wyk_csdn2",
    	"def": {
    		"database": "test_csdn",
    		"charset": "latin1",
    		"table": "wyk_csdn2",
    		"columns": [
    			{
    				"type": "int",
    				"name": "id",
    				"signed": true
    			},
    			{
    				"type": "varchar",
    				"name": "name",
    				"charset": "latin1"
    			},
    			{
    				"type": "timestamp",
    				"name": "ins_ts",
    				"column-length": 0
    			}
    		],
    		"primary-key": []
    	},
    	"ts": 1600769193000,
    	"sql": "create table wyk_csdn2(id int,name varchar(20),ins_ts timestamp)"
    }
    {
    	"type": "table-alter",
    	"database": "test_csdn",
    	"table": "wyk_csdn2",
    	"old": {
    		"database": "test_csdn",
    		"charset": "latin1",
    		"table": "wyk_csdn2",
    		"columns": [
    			{
    				"type": "int",
    				"name": "id",
    				"signed": true
    			},
    			{
    				"type": "varchar",
    				"name": "name",
    				"charset": "latin1"
    			},
    			{
    				"type": "timestamp",
    				"name": "ins_ts",
    				"column-length": 0
    			}
    		],
    		"primary-key": []
    	},
    	"def": {
    		"database": "test_csdn",
    		"charset": "latin1",
    		"table": "wyk_csdn2",
    		"columns": [
    			{
    				"type": "int",
    				"name": "id",
    				"signed": true
    			},
    			{
    				"type": "varchar",
    				"name": "name",
    				"charset": "latin1"
    			},
    			{
    				"type": "timestamp",
    				"name": "ins_ts",
    				"column-length": 0
    			},
    			{
    				"type": "text",
    				"name": "add_c",
    				"charset": "latin1"
    			}
    		],
    		"primary-key": []
    	},
    	"ts": 1600769196000,
    	"sql": "alter table wyk_csdn2 add column(add_c text)"
    }
    {
    	"type": "table-alter",
    	"database": "test_csdn",
    	"table": "wyk_csdn2",
    	"old": {
    		"database": "test_csdn",
    		"charset": "latin1",
    		"table": "wyk_csdn2",
    		"columns": [
    			{
    				"type": "int",
    				"name": "id",
    				"signed": true
    			},
    			{
    				"type": "varchar",
    				"name": "name",
    				"charset": "latin1"
    			},
    			{
    				"type": "timestamp",
    				"name": "ins_ts",
    				"column-length": 0
    			},
    			{
    				"type": "text",
    				"name": "add_c",
    				"charset": "latin1"
    			}
    		],
    		"primary-key": []
    	},
    	"def": {
    		"database": "test_csdn",
    		"charset": "latin1",
    		"table": "wyk_csdn2_new",
    		"columns": [
    			{
    				"type": "int",
    				"name": "id",
    				"signed": true
    			},
    			{
    				"type": "varchar",
    				"name": "name",
    				"charset": "latin1"
    			},
    			{
    				"type": "timestamp",
    				"name": "ins_ts",
    				"column-length": 0
    			},
    			{
    				"type": "text",
    				"name": "add_c",
    				"charset": "latin1"
    			}
    		],
    		"primary-key": []
    	},
    	"ts": 1600769199000,
    	"sql": "rename table wyk_csdn2 to wyk_csdn2_new"
    }
    {
    	"type": "table-drop",
    	"database": "test_csdn",
    	"table": "wyk_csdn2_new",
    	"ts": 1600769202000,
    	"sql": "DROP TABLE `wyk_csdn2_new` /* generated by server */"
    }

Maxwell bootstrap全量同步表数据的json消息：

{
    	"database": "test_csdn",
    	"table": "wyk_csdn",
    	"type": "bootstrap-start",
    	"ts": 1600769648,
    	"data": {}
    }
    {
    	"database": "test_csdn",
    	"table": "wyk_csdn",
    	"type": "bootstrap-insert",
    	"ts": 1600769648,
    	"data": {
    		"id": 1,
    		"name": "wyk1",
    		"ins_ts": "2020-09-23 07:13:02"
    	}
    }
    {
    	"database": "test_csdn",
    	"table": "wyk_csdn",
    	"type": "bootstrap-insert",
    	"ts": 1600769648,
    	"data": {
    		"id": 2,
    		"name": "wyk2",
    		"ins_ts": "2020-09-23 07:13:02"
    	}
    }
    {
    	"database": "test_csdn",
    	"table": "wyk_csdn",
    	"type": "bootstrap-complete",
    	"ts": 1600769648,
    	"data": {}
    }

# 尾巴 #

通过上面的使用，我们可以看出，maxwell能够监控到数据库的alter table&view /create table&view/insert/update/delete/truncate/drop/rename等动作，但并不能监控到truncate动作，相比于Canal是个劣势，但相比于canal，maxwell bootstrap支持指定表进行全量同步，在做初始化切换以及重刷全量数据的时候可以保证不会有数据丢失的风险，而canal对于此场景的处理会麻烦许多（通过canal adapter的etl功能实现）。

希望本文对你有帮助，请点个赞鼓励一下作者吧~ 谢谢！

[CDC_Canal _WebUI]: https://blog.csdn.net/wsdc0521/article/details/108727632
[MySQL]: https://blog.csdn.net/wsdc0521/article/details/82864901
[kafka]: https://blog.csdn.net/wsdc0521/article/details/108597755
[zookeeper]: https://blog.csdn.net/wsdc0521/article/details/108597288
[https_github.com_zendesk_maxwell_releases_download_v1.27.1_maxwell-1.27.1.tar.gz]: https://github.com/zendesk/maxwell/releases/download/v1.27.1/maxwell-1.27.1.tar.gz
[MySQL_Binlog]: https://blog.csdn.net/wsdc0521/article/details/108727632#MySQL%E5%BC%80%E5%90%AFBinlog
[http_maxwells-daemon.io_config]: http://maxwells-daemon.io/config/
[http_maxwells-daemon.io_bootstrapping]: http://maxwells-daemon.io/bootstrapping/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70]: /images/20221123/5d5f350cd97741e09222a4b69d4c31b7.png
[20200922181549716.png]: /images/20221123/557e8ed082954706b5ee316522900c0d.png