首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Maxwell 系列(二)—— 入门(7000字的干货)

Maxwell 系列(二)—— 入门(7000字的干货)

作者头像
大数据最后一公里
发布2021-08-05 10:32:42
1.2K0
发布2021-08-05 10:32:42
举报

相关介绍 请看Maxwell 系列(一),下面开始进入实战环节。

一、mysql操作

0、安装Mysql

省略

1、修改mysql配置文件

vi /etc/my.cnf
#[mysqld]
binlog_format=row

2、创建maxwell数据库

create database maxwell;

3、创建maxwell用户

grant all on maxwell.* to 'maxwell'@'%' identified by 'sweet';

4、对maxwell用户授权

grant select,replication client ,replication slave on *.* to 'maxwell'@'%';

5、刷新权限

flush privileges;

创建maxwell用户和maxwell数据库,是maxwell用户会往maxwell数据库写入一些默认配置,具体配置如下

二、maxwell操作

1、从官网上下载tar.gz压缩包 http://maxwells-daemon.io/

2、producer

stdout:输出到控制台(在新版中改为Command line,也就是命令行输出)

kafka:作为kafka的生产者,数据发送到kafka中

Command line

bin/maxwell --user='maxwell' --password='sweet' --host='127.0.0.1' --producer=stdout

Kafka

bin/maxwell --user='maxwell' --password='XXXXXX' --host='127.0.0.1' \
   --producer=kafka --kafka.bootstrap.servers=localhost:9092 --kafka_topic=maxwell

#后台启动
[root@datanode1 ~]# nohup bin/maxwell --user='maxwell' --password='123456' --host='127.0.0.1' --producer=kafka  &

Redis

bin/maxwell --user='maxwell' --password='XXXXXX' --host='127.0.0.1' \
    --producer=redis --redis_host=redis.hostname

三、mysql插入和修改数据

maxwell采集的信息

1、插入数据

{
  "database": "lpc",
  "table": "student",
  "type": "insert",
  "ts": 1597310003,
  "xid": 3744150,
  "commit": true,
  "data": {
    "id": 1,
    "name": "hello"
  }
}

2、修改数据

{
  "database": "lpc",
  "table": "address",
  "type": "update",
  "ts": 1597628782,
  "xid": 35146026,
  "commit": true,
  "data": {
    "id": 2,
    "value": "深圳"
  },
  "old": {
    "value": "上海"
  }
}

四、kafka依赖包

maxwell中有kafka生产者,maxwell官网上对应版本支持的kafka版本。

maxwell目录下存放在kafka依赖包地址是:/lib/kafka-clients

如果生产环境下kafka的版本,maxwell不支持,可以将对应的kafka-clients的版本jar包放到该目录下。

说明:maxwell是作为kafka的生产者,通过客户端发送,所以包是客户端的包。

五、maxwell数据库中的表

1、bootstrap

mysql> desc bootstrap;
+-----------------+---------------------+------+-----+---------+----------------+
| Field           | Type                | Null | Key | Default | Extra          |
+-----------------+---------------------+------+-----+---------+----------------+
| id              | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| database_name   | varchar(255)        | NO   |     | NULL    |                |
| table_name      | varchar(255)        | NO   |     | NULL    |                |
| where_clause    | text                | YES  |     | NULL    |                |
| is_complete     | tinyint(1) unsigned | NO   |     | 0       |                |
| inserted_rows   | bigint(20) unsigned | NO   |     | 0       |                |
| total_rows      | bigint(20) unsigned | NO   |     | 0       |                |
| created_at      | datetime            | YES  |     | NULL    |                |
| started_at      | datetime            | YES  |     | NULL    |                |
| completed_at    | datetime            | YES  |     | NULL    |                |
| binlog_file     | varchar(255)        | YES  |     | NULL    |                |
| binlog_position | int(10) unsigned    | YES  |     | 0       |                |
| client_id       | varchar(255)        | NO   |     | maxwell |                |
| comment         | varchar(255)        | YES  |     | NULL    |                |
+-----------------+---------------------+------+-----+---------+----------------+

2、columns

mysql> desc columns;
+---------------+---------------------+------+-----+---------+----------------+
| Field         | Type                | Null | Key | Default | Extra          |
+---------------+---------------------+------+-----+---------+----------------+
| id            | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| schema_id     | int(10) unsigned    | YES  | MUL | NULL    |                |
| table_id      | int(10) unsigned    | YES  | MUL | NULL    |                |
| name          | varchar(255)        | YES  |     | NULL    |                |
| charset       | varchar(255)        | YES  |     | NULL    |                |
| coltype       | varchar(255)        | YES  |     | NULL    |                |
| is_signed     | tinyint(1) unsigned | YES  |     | NULL    |                |
| enum_values   | text                | YES  |     | NULL    |                |
| column_length | tinyint(3) unsigned | YES  |     | NULL    |                |
+---------------+---------------------+------+-----+---------+----------------+
mysql>select * from columns limit 10;
+----+-----------+----------+---------------------+---------+----------+-----------+-------------+---------------+
| id | schema_id | table_id | name                | charset | coltype  | is_signed | enum_values | column_length |
+----+-----------+----------+---------------------+---------+----------+-----------+-------------+---------------+
|  1 |         1 |        1 | permission_id       | NULL    | bigint   |         1 | NULL        |          NULL |
|  2 |         1 |        1 | permission_name     | utf8    | varchar  |         0 | NULL        |          NULL |
|  3 |         1 |        1 | resource_type_id    | NULL    | int      |         1 | NULL        |          NULL |
|  4 |         1 |        1 | permission_label    | utf8    | varchar  |         0 | NULL        |          NULL |
|  5 |         1 |        1 | principal_id        | NULL    | bigint   |         1 | NULL        |          NULL |
|  6 |         1 |        1 | sort_order          | NULL    | smallint |         1 | NULL        |          NULL |
|  7 |         1 |        2 | principal_id        | NULL    | bigint   |         1 | NULL        |          NULL |
|  8 |         1 |        2 | principal_type_id   | NULL    | int      |         1 | NULL        |          NULL |
|  9 |         1 |        3 | principal_type_id   | NULL    | int      |         1 | NULL        |          NULL |
| 10 |         1 |        3 | principal_type_name | utf8    | varchar  |         0 | NULL        |          NULL |
+----+-----------+----------+---------------------+---------+----------+-----------+-------------+---------------+

3、databases

//databases是关键字用`号括起来
mysql> desc `databases`;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| schema_id | int(10) unsigned | YES  | MUL | NULL    |                |
| name      | varchar(255)     | YES  |     | NULL    |                |
| charset   | varchar(255)     | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> select * from `databases` limit 10;
+----+-----------+-----------------+---------+
| id | schema_id | name            | charset |
+----+-----------+-----------------+---------+
|  1 |         1 | ambari_dianxin  | utf8    |
|  2 |         1 | ambari_liantong | utf8    |
|  3 |         1 | authority       | utf8    |
|  4 |         1 | bigdatahouse    | utf8    |
|  5 |         1 | datagovernance  | utf8    |
|  6 |         1 | dataswap        | utf8    |
|  7 |         1 | hive_dianxin    | utf8    |
|  8 |         1 | hive_liantong   | utf8    |
|  9 |         1 | hive_zijian     | utf8    |
| 10 |         1 | maxwell         | utf8    |
+----+-----------+-----------------+---------+
""
databases中的数据包含mysql中所有的数据库,会重复出现
""

4、heartbeats

mysql> desc heartbeats;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| server_id | int(10) unsigned | NO   | PRI | NULL    |       |
| client_id | varchar(255)     | NO   | PRI | maxwell |       |
| heartbeat | bigint(20)       | NO   |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from heartbeats;
+-----------+-----------+---------------+
| server_id | client_id | heartbeat     |
+-----------+-----------+---------------+
|         7 | maxwell   | 1597629983601 |
|         8 | maxwell   | 1597395041145 |
+-----------+-----------+---------------+
""
heartbeat是maxwell和当前服务器建立连接的时间
""

5、positions

mysql> desc positions;
+---------------------+------------------+------+-----+---------+-------+
| Field               | Type             | Null | Key | Default | Extra |
+---------------------+------------------+------+-----+---------+-------+
| server_id           | int(10) unsigned | NO   | PRI | NULL    |       |
| binlog_file         | varchar(255)     | YES  |     | NULL    |       |
| binlog_position     | int(10) unsigned | YES  |     | NULL    |       |
| gtid_set            | varchar(4096)    | YES  |     | NULL    |       |
| client_id           | varchar(255)     | NO   | PRI | maxwell |       |
| heartbeat_at        | bigint(20)       | YES  |     | NULL    |       |
| last_heartbeat_read | bigint(20)       | YES  |     | NULL    |       |
+---------------------+------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> select * from positions limit 10;
+-----------+------------------+-----------------+----------+-----------+--------------+---------------------+
| server_id | binlog_file      | binlog_position | gtid_set | client_id | heartbeat_at | last_heartbeat_read |
+-----------+------------------+-----------------+----------+-----------+--------------+---------------------+
|         7 | mysql-bin.000004 |       303376653 | NULL     | maxwell   |         NULL |       1597630552439 |
|         8 | mysql-bin.000004 |       168979418 | NULL     | maxwell   |         NULL |       1597395041031 |
+-----------+------------------+-----------------+----------+-----------+--------------+---------------------+

图上的两个时间在变,因为当前mysql连接了集成平台,会有数据不断写入,写入时,都会导致binlog文件滚动

需要在静止化的mysql测试heartbeat和binlog_position

6、schemas

mysql> desc `schemas`;
+---------------------+----------------------+------+-----+---------+----------------+
| Field               | Type                 | Null | Key | Default | Extra          |
+---------------------+----------------------+------+-----+---------+----------------+
| id                  | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| binlog_file         | varchar(255)         | YES  |     | NULL    |                |
| binlog_position     | int(10) unsigned     | YES  |     | NULL    |                |
| last_heartbeat_read | bigint(20)           | YES  |     | 0       |                |
| gtid_set            | varchar(4096)        | YES  |     | NULL    |                |
| base_schema_id      | int(10) unsigned     | YES  |     | NULL    |                |
| deltas              | mediumtext           | YES  |     | NULL    |                |
| server_id           | int(10) unsigned     | YES  |     | NULL    |                |
| position_sha        | char(40)             | YES  | UNI | NULL    |                |
| charset             | varchar(255)         | YES  |     | NULL    |                |
| version             | smallint(5) unsigned | NO   |     | 0       |                |
| deleted             | tinyint(1)           | NO   |     | 0       |                |
+---------------------+----------------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)

mysql> select * from `schemas` limit 10;
+----+------------------+-----------------+---------------------+----------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------------------------------------------+---------+---------+---------+
| id | binlog_file      | binlog_position | last_heartbeat_read | gtid_set | base_schema_id | deltas                                                                                                                                                                                                                                            | server_id | position_sha                             | charset | version | deleted |
+----+------------------+-----------------+---------------------+----------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------------------------------------------+---------+---------+---------+
|  1 | mysql-bin.000004 |       116921508 |                   0 | NULL     |           NULL | NULL                                                                                                                                                                                                                                              |         8 | 208d7f8ea97aebd992e57b1dcb671ae2dfc83a31 | utf8    |       4 |       0 |
|  2 | mysql-bin.000004 |       122049865 |       1597309899708 | NULL     |              1 | [{"type":"database-create","database":"lpc","charset":"utf8"}]                                                                                                                                                                                    |         8 | 19387c3e3ad37c3017b21c3f18d00506712736a6 | utf8    |       4 |       0 |
|  3 | mysql-bin.000004 |       122099465 |       1597309980279 | NULL     |              2 | [{"type":"table-create","database":"lpc","table":"student","def":{"database":"lpc","charset":"utf8","table":"student","columns":[{"type":"int","name":"id","signed":true},{"type":"varchar","name":"name","charset":"utf8"}],"primary-key":[]}}]  |         8 | 01264f5fa4f869911ae493aea10daf30e0c70606 | utf8    |       4 |       0 |
|  4 | mysql-bin.000004 |       122630438 |       1597310799236 | NULL     |              3 | [{"type":"table-create","database":"lpc","table":"address","def":{"database":"lpc","charset":"utf8","table":"address","columns":[{"type":"int","name":"id","signed":true},{"type":"varchar","name":"value","charset":"utf8"}],"primary-key":[]}}] |         8 | 5a390505a36b8c65dac68ea301542965c1d5b94b | utf8    |       4 |       0 |
|  5 | mysql-bin.000004 |       167304909 |                   0 | NULL     |           NULL | NULL                                                                                                                                                                                                                                              |         7 | c27b47d78566d7bda8d82987133776448607ca15 | utf8    |       4 |       0 |
+----+------------------+-----------------+---------------------+----------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------------------------------------------+---------+---------+---------+

7、tables

mysql> desc tables;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| schema_id   | int(10) unsigned | YES  | MUL | NULL    |                |
| database_id | int(10) unsigned | YES  | MUL | NULL    |                |
| name        | varchar(255)     | YES  |     | NULL    |                |
| charset     | varchar(255)     | YES  |     | NULL    |                |
| pk          | varchar(1024)    | YES  |     | NULL    |                |
+-------------+------------------+s------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> select * from tables limit 10;
+----+-----------+-------------+--------------------+---------+--------------------+
| id | schema_id | database_id | name               | charset | pk                 |
+----+-----------+-------------+--------------------+---------+--------------------+
|  1 |         1 |           2 | adminpermission    | utf8    | permission_id      |
|  2 |         1 |           2 | adminprincipal     | utf8    | principal_id       |
|  3 |         1 |           2 | adminprincipaltype | utf8    | principal_type_id  |
|  4 |         1 |           2 | adminprivilege     | utf8    | privilege_id       |
|  5 |         1 |           2 | adminresource      | utf8    | resource_id        |
|  6 |         1 |           2 | adminresourcetype  | utf8    | resource_type_id   |
|  7 |         1 |           2 | alert_current      | utf8    | alert_id           |
|  8 |         1 |           2 | alert_definition   | utf8    | definition_id      |
|  9 |         1 |           2 | alert_group        | utf8    | group_id           |
| 10 |         1 |           2 | alert_group_target | utf8    | group_id,target_id |
+----+-----------+-------------+--------------------+---------+--------------------+
10 rows in set (0.01 sec)

mysql> select count(*) from tables;
+----------+
| count(*) |
+----------+
|      846 |
+----------+
1 row in set (0.00 sec)
本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2021-07-21,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 大数据最后一公里 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档