[Maxwellベース]--maxwell+kafkaをハンドで構築する環境

16245 ワード

Mysql bin log学習
1、mysqlバージョン
$ mysql -v
Server version: 5.6.41 MySQL Community Server (GPL)
 
2、mysql bin log紹介
https://dev.mysql.com/doc/internals/en/binary-log.html
https://blog.csdn.net/wudongxu/article/details/6598562
 
2.1 binlogログを有効にするかどうか
show variables like 'log_bin';
 
2.2 binlogの表示
show binlog events;
 
2.3最新のbinlogログファイル名の表示
show master status;
 
2.4 binlogログの検索
find/-name mysql-bin -type f
 
2.5詳細なログ構成情報の表示
SHOW  GLOBAL VARIABLES LIKE '%log%';
 
2.6 mysqlデータストアディレクトリ
show variables like '%dir%';
 
2.7 binlogのディレクトリの表示
       mysql> show global variables like "%log_bin%";
       +---------------------------------+--------------------------------+
       | Variable_name                   | Value                          |
       +---------------------------------+--------------------------------+
       | log_bin                         | ON                             |
       | log_bin_basename              |/var/lib/mysql/mysql-bin       |
       | log_bin_index                   |/var/lib/mysql/mysql-bin.index |
       | log_bin_trust_function_creators  | OFF                            |
       | log_bin_use_v1_row_events     | OFF                            |
       | sql_log_bin                     | ON                             |
       +---------------------------------+--------------------------------+
 
注意:パラメータの説明
https://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_log_bin_basename
1)log_bin_basename:バイナリ・ログ・ファイルの基本名とパスを保存
2)log_bin_index;バイナリ・ログ・インデックス・ファイルの基本名とパスを保存します.log_bin_indexシステム変数をMySQL 5.6.4に追加
 
 
2.8 bin-logバイナリファイルの表示
関連ddlとdml操作
  /////////////////////////////////////////////////////////////////////////////////////////////
実行されるsql文:
       mysql> create database test_binlog;
       Query OK, 1 row affected (0.00 sec)
 
       mysql> use test_binlog;
       Database changed
 
       mysql> create table test_bin_log (id int ,name varchar(30));
       Query OK, 0 rows affected (0.02 sec)
 
       mysql> insert into test_bin_log values(1,"mysql001");
       Query OK, 1 row affected (0.00 sec)
 
       mysql> insert into test_bin_log values(2,"mysql002");
       Query OK, 1 row affected (0.01 sec)    
 
 //////////////////////////////////////////////////////////////////////////////////////////////
 
2.8.1方法1(推奨)
     show binlog events in 'mysql-bin.000003';
     mysql> show binlog events in 'mysql-bin.000003';
+------------------+

| Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                                                                                                                                               |

----------------+

| mysql-bin.000003 |    4 | Format_desc |         1 |         120 | Server ver: 5.6.41-log, Binlog ver: 4                                                                                                                                              |

| mysql-bin.000003 |  120 | Query       |         1 |         235 | create database test_binlog                                                                                                                                                        |

| mysql-bin.000003 |  235 | Query       |         1 |         375 | use `test_binlog`; create table test_bin_log (id int ,name varchar(30))                                                                                                            |

| mysql-bin.000003 |  375 | Query       |         1 |         468 | BEGIN                                                                                                                                                                              |

| mysql-bin.000003 |  468 | Query       |         1 |         601 | use `test_binlog`; insert into test_bin_log values(1,"mysql001")                                                                                                                   |

| mysql-bin.000003 |  601 | Xid         |         1 |         632 | COMMIT /* xid=69452 */                                                                                                                                                             |

| mysql-bin.000003 |  632 | Query       |         1 |         725 | BEGIN                                                                                                                                                                              |

| mysql-bin.000003 |  725 | Query       |         1 |         858 | use `test_binlog`; insert into test_bin_log values(2,"mysql002")                                                                                                                   |

| mysql-bin.000003 |  858 | Xid         |         1 |         889 | COMMIT /* xid=69589 */                                                                                                                                                             |

| mysql-bin.000003 |  889 | Query       |         1 |         965 | BEGIN                                                                                                                                                                              |

| mysql-bin.000003 |  965 | Query       |         1 |        1188 | use `ambari`; UPDATE QRTZ_TRIGGERS SET TRIGGER_STATE = 'WAITING' WHERE SCHED_NAME = 'ExecutionScheduler' AND (TRIGGER_STATE = 'ACQUIRED' OR TRIGGER_STATE = 'BLOCKED')             |

| mysql-bin.000003 | 1188 | Query       |         1 |        1423 | use `ambari`; UPDATE QRTZ_TRIGGERS SET TRIGGER_STATE = 'PAUSED' WHERE SCHED_NAME = 'ExecutionScheduler' AND (TRIGGER_STATE = 'PAUSED_BLOCKED' OR TRIGGER_STATE = 'PAUSED_BLOCKED') |

| mysql-bin.000003 | 1423 | Query       |         1 |        1572 | use `ambari`; DELETE FROM QRTZ_FIRED_TRIGGERS WHERE SCHED_NAME = 'ExecutionScheduler'                                                                                              |

| mysql-bin.000003 | 1572 | Query       |         1 |        1647 | COMMIT                                                                                                                                                                             |

+------------------+------+-------------+-----------+-------------

2.8.2方法2
 [root@bdp03nn01 mysql]# mysqlbinlog -v --base64-output=decode-rows --start-datetime="2018-08-22 08:30:00"--stop-datetime="2018-08-26 00:30:00"/var/lib/mysql/mysql-bin.000003  
 \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#180823 16:28:03 server id 1  end_log_pos 120 CRC32 0x7f57797a     Start: binlog v 4, server v 5.6.41-log created 180823 16:28:03

# Warning: this binlog is either in use or was not closed properly.

BINLOG '

              E3B+Ww8BAAAAdAAAAHgAAAABAAQANS42LjQxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

              AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAXp5

              V38=

              '/*!*/;

              # at 120

              #180823 16:53:40 server id 1  end_log_pos 235 CRC32 0xe6eb36b4    Query     thread_id=1088    exec_time=0  error_code=0

              SET TIMESTAMP=1535014420/*!*/;

              SET @@session.pseudo_thread_id=1088/*!*/;

              SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

              SET @@session.sql_mode=1075838976/*!*/;

              SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

              /*!\C utf8 *//*!*/;

              SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;

              SET @@session.lc_time_names=0/*!*/;

              SET @@session.collation_database=DEFAULT/*!*/;

              create database test_binlog

              /*!*/;

              # at 235

              #180823 16:54:38 server id 1  end_log_pos 375 CRC32 0x8267becb    Query     thread_id=1088    exec_time=0  error_code=0

              use `test_binlog`/*!*/;

              SET TIMESTAMP=1535014478/*!*/;

              create table test_bin_log (id int ,name varchar(30))

              /*!*/;

              # at 375

              #180823 16:55:12 server id 1  end_log_pos 468 CRC32 0x10fcd3fe      Query     thread_id=1088    exec_time=0  error_code=0

              SET TIMESTAMP=1535014512/*!*/;

              BEGIN

              /*!*/;

              # at 468

              #180823 16:55:12 server id 1  end_log_pos 601 CRC32 0x161b0df5     Query     thread_id=1088    exec_time=0  error_code=0

              SET TIMESTAMP=1535014512/*!*/;

              insert into test_bin_log values(1,"mysql001")

              /*!*/;

              # at 601

              #180823 16:55:12 server id 1  end_log_pos 632 CRC32 0x776d3e6c    Xid = 69452

              COMMIT/*!*/;

              # at 632

              #180823 16:55:18 server id 1  end_log_pos 725 CRC32 0xc2d44757    Query     thread_id=1088    exec_time=0  error_code=0

              SET TIMESTAMP=1535014518/*!*/;

              BEGIN

              /*!*/;

              # at 725

              #180823 16:55:18 server id 1  end_log_pos 858 CRC32 0xde83d0d3    Query     thread_id=1088    exec_time=0  error_code=0

              SET TIMESTAMP=1535014518/*!*/;

              insert into test_bin_log values(2,"mysql002")

              /*!*/;

              # at 858

              #180823 16:55:18 server id 1  end_log_pos 889 CRC32 0xbb62f482     Xid = 69589

              COMMIT/*!*/;

              DELIMITER ;

              # End of log file

              ROLLBACK /* added by mysqlbinlog */;

              /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

              /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

  \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
 
パラメータの説明:
--start-datetime:タイムスタンプに等しい時間またはローカルコンピュータより遅い時間をバイナリ・ログから読み込む
--stop-datetime:指定したタイムスタンプ以下またはローカルコンピュータと同等のタイムスタンプをバイナリ・ログから読み出す
--start-position:指定したpositionイベントの場所をバイナリログから読み込みます.
--stop-position:イベント終了時に指定したpositionイベントの場所をバイナリ・ログから読み込みます
 
3、解析と採集
3.1ツール
canal:   
無料-アリババ
(
https://github.com/alibaba/canal/wiki/Introduction,
https://slidesplayer.com/slide/11557608/,
https://docs.google.com/presentation/d/1MkszUPYRDkfVPz9IqOT1LLT5d9tuwde_WC8GZvjaDRg/edit#)
 
maxwells:無料-アマゾン(http://maxwells-daemon.io/)
 
3.2例
maxwells:  https://github.com/bobshaw1912/cdc-kinesis-demo,https://blog.csdn.net/liguohuaBigdata/article/details/79472777
canal:     https://github.com/alibaba/canal/wiki/QuickStart
 
3.3バージョンの説明
3.3.1 canal
現在の最高サポートバージョンは
機能が多くて重い
https://github.com/alibaba/canal/wiki/AdminGuide
 
3.3.2 maxwells
機能が少なく、軽量で、私たちのアプリケーションシーンに適しています.
4、シーンの適用
4.1データ異性化、バッファリング、およびタスク配布
http://www.seekwd.com/article/details/150
 
4.2フロー型リアルタイム分析アーキテクチャの実現
https://aws.amazon.com/cn/blogs/china/mysql-binlog-architecture/
 
4.3データ復旧
https://www.cnblogs.com/kevingrace/p/5907254.html
 
4.4データのリアルタイム処理方法及びシステム
https://patents.google.com/patent/CN106446239A/zh
 
5、構築
5.1手でmaxwell+kafkaを構築する環境
http://maxwells-daemon.io/
5.2環境説明
5.2.1必要なバージョン
apache kafka:0.8.2以上、バージョンに対応する必要があります.そうしないと、互換性がない可能性があります.
maxwells :     1.2.X以上
mysql    :     5.1,5.5,5.6,5.7
jdk:1.8以上
OS:centos 7
5.2.2クラスタ上の環境
テストクラスタ:hdp-2.6.4.0-91、jdk-1.8.0_141、kafka-0.10.1.2.6.4.0-91
生産環境:hdp-2.6.0.3-8、jdk-1.8.0_141、kafka-0.10.1.2.6.0.3-8
自作環境:hdp-2.6.5.0-292、jdk-1.8.0_172-b11、mysql-5.6.41、kafka-1.0.0.2.6.5.0-292
5.2.3 maxwellsバージョン
maxwells-1.17.0 ----> https://github.com/zendesk/maxwell/tree/v1.17.0
 
5.3インストール
5.3.1 kafkaが正常にインストールされているかどうかをテストする
5.3.1.1 kafkaのtopicを作成する
/usr/hdp/2.6.5.0-292/kafka/bin/kafka-topics.sh --create  --topic  maxwells --zookeeper localhost:2181 --partitions 6 --replication-factor 2
 
5.3.1.2生産データ
/usr/hdp/2.6.5.0-292/kafka/bin/kafka-console-producer.sh  --broker-list bdp03nn01:6667,bdp03dn01:6667,bdp03nn02:6667 --topic maxwells
 
5.3.1.3消費データ
/usr/hdp/2.6.5.0-292/kafka/bin/kafka-console-consumer.sh --zookeeper bdp03nn01:2181,bdp03dn01:2181,bdp03nn02:2181 --topic maxwells --from-beginning
 
5.3.1.4 topicの説明
/usr/hdp/2.6.5.0-292/kafka/bin/kafka-topics.sh --describe  --zookeeper bdp03nn01:2181 --topic maxwells
 
5.3.2 maxwellのインストール
5.3.2.1 maxwellsのダウンロードと解凍
$ cd/usr/local
$ wget https://github.com/zendesk/maxwell/releases/download/v1.17.0/maxwell-1.17.0.tar.gz
 
5.3.2.2 mysqlの配置
5.3.2.2.1編集my.cnf
$ vi/etc/my.cnf
[mysqld]
server_id=1
log-bin=master
binlog_format=row
 
5.3.2.2.2権限の追加
 
mysql> GRANT ALL  on *.* to 'maxwell'@'172.16.5.117' identified by 'mysqlmaxwellpwd' ;
mysql> GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE on *.* to 'maxwell'@'172.16.5.117';
mysql> flush privaleges;
 
5.3.2.2.3 bin-logログの更新、mysqlの再起動
mysql> flush logs;
#systemctl  restart mysqld.service  
 
5.3.3 maxwellsを実行する
5.3.3.1未ろ過庫
/usr/local/maxwell/bin/maxwell --user='maxwell' --password='mysqlmaxwellpwd' --host='172.16.5.117'\
--producer=kafka --kafka.bootstrap.servers=bdp03nn01:6667,bdp03dn01:6667,bdp03nn02:6667\
--kafka_topic=maxwells  
 
5.3.3.2指定ルールのライブラリをフィルタする
/usr/local/maxwell/bin/maxwell --user='maxwell' --password='mysqlmaxwellpwd' --host='172.16.5.117'\
--producer=kafka --kafka.bootstrap.servers=bdp03nn01:6667,bdp03dn01:6667,bdp03nn02:6667\
--kafka_topic=maxwells  --filter 'exclude: ambari.*, include: test_binlog.*'
 
フィルタ構成を参照:http://maxwells-daemon.io/filtering/
 
5.4消費データ
#/usr/hdp/2.6.5.0-292/kafka/bin/kafka-console-consumer.sh --zookeeper bdp03nn01:2181,bdp03dn01:2181,bdp03nn02:2181 --topic maxwells --from-beginning
 
コマンドライン監視データ:
  {"database":"ambari","table":"alert_current","type":"update","ts":1535363391,"xid":98489,"commit":true,"data":{"alert_id":122,"definition_id":82,"history_id":451,"maintenance_state":"ON","original_timestamp":1534996534493,"latest_timestamp":1535363390926,"latest_text":"Connection failed: [Errno 111] Connection refused to bdp03nn01:16000","occurrences":4991,"firmness":"HARD"},"old":{"latest_timestamp":1535363330967,"occurrences":4990}}
 
5.5 maxwellのjsonデータ説明
http://maxwells-daemon.io/dataformat/
 
5.6参考
https://github.com/zendesk/maxwell
http://maxwells-daemon.io/compat/
https://github.com/zendesk/maxwell/releases
https://github.com/zendesk/maxwell/blob/master/CHANGELOG.md
http://maxwells-daemon.io/config/
https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.6.3/bk_security/content/secure-kafka-produce-events.html
https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.6.5/bk_kafka-component-guide/content/ch_kafka-development.html