mysql-maxwell binlog订阅

原文地址： http://maxwells-daemon.io/quickstart/

测试经过和结果：

1、mysql安装略过

2、配置mysql,并开启bin_log

# vi /etc/my.cnf

[mysqld]

server-id=1

log-bin=master

binlog_format=row

**需要重启mysql

声明：通过 mysql --help | grep my.cnf查看，可知 mysql配置文件的加载顺序：

/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

3、mysql 权限配置，用户名： maxwell，密码：123456

mysql> set global binlog_row_image=FULL;

mysql> GRANT ALL on maxwell.* to 'maxwell'@'%' identified by '123456';mysql> GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE on *.* to 'maxwell'@'%';

mysql> GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE on *.* to 'maxwell'@'localhost' identified by '123456';

mysql> GRANT ALL on maxwell.* to 'maxwell'@'localhost';

注意：开启bin_log 监控权限

4、测试 maxwell;

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

错误：

提示显示为 bin_log没打开，重启mysql即可

创建表：

mysql> use test; // 切换数据库

mysql> create table t1(id varchar(10),name varchar(20));

maxwell控制台：

15:19:14,042 INFO AbstractSchemaStore - storing schema @BinlogPosition[master.000001:21162] after applying "create table t1(id varchar(10),name varchar(20))"

mysql> insert into t1(id,name) values('1','haha');

maxwell控制台：

{"database":"test","table":"t1","type":"insert","ts":1469776891,"xid":697,"commit":true,"data":{"id":"1","name":"haha"}}

格式化：

{

"database": "test",

"table": "t1",

"type": "insert",

"ts": 1469776891,

"xid": 697,

"commit": true,

"data": {

"id": "1",

"name": "haha"

}

}

批量修改mysql的数据：

update t1 set name = 'fdafdsa'; //总有有三条数据

得到的结果：

{

"database": "test",

"table": "t1",

"type": "update",

"ts": 1469779091,

"xid": 4861,

"data": {

"id": "1",

"name": "fdafdsa"

},

"old": {

"name": "haha"

}

}

删除数据：

mysql> delete from t1; //共有三条数据

maxwell 收到的数据：

{"database":"test","table":"t1","type":"delete","ts":1469782623,"xid":11929,"data":{"id":"1","name":"fdafdsafdsafdsa"}}

{"database":"test","table":"t1","type":"delete","ts":1469782623,"xid":11929,"data":{"id":"2","name":"fdafdsafdsafdsa"}}

{"database":"test","table":"t1","type":"delete","ts":1469782623,"xid":11929,"commit":true,"data":{"id":"3","name":"fdafdsafdsafdsa"}}

5、maxwell to kafka

a) 创建 topic ：

bin/kafka-topics.sh --create --zookeeper 10.32.30.225:2181,10.32.30.226:2181,10.32.30.227:2181 --replication-factor 2 --partitions 1 --topic maxwell

b） 控制台消费者：

bin/kafka-console-consumer.sh --zookeeper 10.32.30.225:2181,10.32.30.226:2181,10.32.30.227:2181 --topic maxwell

c) bin/maxwell --user='maxwell' --password='123456' --host='127.0.0.1' --producer=kafka --kafka.bootstrap.servers= 10.32.30.225:9092,10.32.30.226:9092,10.32.30.227:9092,10.32.30.228:9092

默认的topic 为 maxwell

对表进行CRUD....

6、mysql主从设置

主库： 10.32.30.226

从库： 10.32.30.227

6.1、从库同步主库（略）

6.2、从库bin_log 配置

# cat /etc/my.cnf

[mysqld]

log-bin=mysql-bin

server-id=227

binlog_format=row

测试：启动maxwell，监控slave的bin_log

主库：执行insert ，从库同步到数据，但是maxwell未收到数据

原因分析：bin_log的产生默认情况下，不是链式的

解决方案：

处理方法：修改/etc/my.cnf，增加一行log_slave_updates=1，重启数据库；

主库执行instert ，maxwell 收到数据！