Maxwell 系列（二）—— 入门（7000字的干货）
相关介绍 请看Maxwell 系列（一），下面开始进入实战环节。
一、mysql操作
0、安装Mysql
省略
1、修改mysql配置文件
vi /etc/my.cnf
#[mysqld]
binlog_format=row2、创建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=stdoutKafka
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 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
腾讯云开发者
