深入使用DBus

中间的MySQL(hadoop002)即是主又是从，一定要及得加上log_slave_updates配置，否则在 MySQL主节点(hadoop001)上插入数据，MySQL从虽然可以同步数据，但是它不会级联生成binlog日志， canal就采集不到数据了。

在hadoop001节点上执行如下命令

[hadoop@hadoop001 ~]$ sudo vim /etc/my.cnf

server-id=1
log-bin=mysql-bin
binlog-format=Row
binlog-ignore-db=information_schema
binlog-ignore-db=ambari
binlog-ignore-db=dbusmgr
binlog-ignore-db=hive
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

[hadoop@hadoop001 ~]$ sudo systemctl restart mysqld

mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_mixed_case_count=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_number_count=3;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_special_char_count=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=3;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+---------------------------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                                    | Executed_Gtid_Set |
+------------------+----------+--------------+---------------------------------------------------------------------+-------------------+
| mysql-bin.000001 |      834 |              | information_schema,ambari,dbusmgr,hive,mysql,performance_schema,sys |                   |
+------------------+----------+--------------+---------------------------------------------------------------------+-------------------+
1 row in set (0.00 sec)

登录hadoop002，编辑MySQL配置文件:

[hadoop@hadoop002 ~]$ sudo vim /etc/my.cnf

#跟主库的server-id一定要不一样
server-id=2
log-bin=mysql-bin

#下面这个必须加上，因为node02上的MySQL既是slave又是master，加上该选项才会生成级联binlog

log_slave_updates
binlog-format=Row
binlog-ignore-db=information_schema
binlog-ignore-db=ambari
binlog-ignore-db=dbusmgr
binlog-ignore-db=hive
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

[hadoop@hadoop002 ~]$ sudo systemctl restart mysqld

[hadoop@hadoop002 ~]$ mysql -uroot -p

mysql> CHANGE MASTER TO
    -> MASTER_HOST='hadoop001',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=834;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: hadoop001
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 834
               Relay_Log_File: hadoop002-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 834
              Relay_Log_Space: 531
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: f6423798-3c25-11ea-a925-00163e0426ac
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+---------------------------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                                    | Executed_Gtid_Set |
+------------------+----------+--------------+---------------------------------------------------------------------+-------------------+
| mysql-bin.000002 |      154 |              | information_schema,ambari,dbusmgr,hive,mysql,performance_schema,sys |                   |
+------------------+----------+--------------+---------------------------------------------------------------------+-------------------+
1 row in set (0.00 sec)

mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
+------------------------------------+
| query                              |
+------------------------------------+
| User: 'repl'@'%';                  |
| User: 'mysql.session'@'localhost'; |
| User: 'mysql.sys'@'localhost';     |
| User: 'root'@'localhost';          |
+------------------------------------+
4 rows in set (0.00 sec)

登录node01上的MySQL:

mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql>   set global validate_password_mixed_case_count=0;
Query OK, 0 rows affected (0.00 sec)

mysql>   set global validate_password_number_count=3;
Query OK, 0 rows affected (0.00 sec)

mysql>   set global validate_password_special_char_count=0;
Query OK, 0 rows affected (0.00 sec)

mysql>   set global validate_password_length=3;
Query OK, 0 rows affected (0.00 sec)

mysql> create database dbus;
Query OK, 1 row affected (0.01 sec)

mysql> CREATE USER 'dbus'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON dbus.* TO dbus@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> use dbus;
Database changed

DROP TABLE IF EXISTS `db_full_pull_requests`;
CREATE TABLE `db_full_pull_requests` (
  `seqno` bigint(19) NOT NULL AUTO_INCREMENT,
  `schema_name` varchar(64) DEFAULT NULL,
  `table_name` varchar(64) NOT NULL,
  `physical_tables` varchar(10240) DEFAULT NULL,
  `scn_no` int(11) DEFAULT NULL,
  `split_col` varchar(50) DEFAULT NULL,
  `split_bounding_query` varchar(512) DEFAULT NULL,
  `pull_target_cols` varchar(512) DEFAULT NULL,
  `pull_req_create_time` timestamp(6) NOT NULL,
  `pull_start_time` timestamp(6) NULL DEFAULT NULL,
  `pull_end_time` timestamp(6) NULL DEFAULT NULL,
  `pull_status` varchar(16) DEFAULT NULL,
  `pull_remark` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`seqno`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `db_heartbeat_monitor`;
CREATE TABLE `db_heartbeat_monitor` (
    `ID` bigint(19) NOT NULL AUTO_INCREMENT COMMENT '心跳表主键',
    `DS_NAME` varchar(64) NOT NULL COMMENT '数据源名称',
    `SCHEMA_NAME` varchar(64) NOT NULL COMMENT '用户名',
    `TABLE_NAME` varchar(64) NOT NULL COMMENT '表名',
    `PACKET` varchar(256) NOT NULL COMMENT '数据包',
    `CREATE_TIME` datetime NOT NULL COMMENT '创建时间',
    `UPDATE_TIME` datetime NOT NULL COMMENT '修改时间',
    PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET FOREIGN_KEY_CHECKS=0;

注意:从库hadoop02不用执行上述操作，之前作了主动复制，他会自动复制的，你可以自行登录从库查看。

登录hadoop002的MySQL

从上图能看到，在hadoop001刚创建的表已经同步到hadoop002上

我们依然登录node01上的MySQL(主库)

mysql> create database hello;
Query OK, 1 row affected (0.00 sec)

mysql> use hello
Database changed
mysql> create table t1(a int, b varchar(50));
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE USER 'test_user'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON hello.* TO test_user@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)


mysql> GRANT select on test_schema1.* TO dbus;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

登录备库hadoop002上的MySQL(从库):

mysql> CREATE USER 'canal'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

在DBus-keeper页面添加数据线时可以选择自动部署Canal，强烈不推荐这种方式。 要想使用一键部署，我们只需要把Canal的安装包放到要安装的目标机机器的指定目录即可， 我们将要把 Canal部署在hadoop002、hadoop003的/home/hadoop/app/dbus下。 请自行把dbus-canal-auto-0.5.0.tar.gz上传至hadoop002、hadoop003的/home/hadoop/app/dbus下并解压缩:

cd /home/hadoop/app/dbus
tar -zxvf dbus-canal-auto-0.5.0.tar.gz

推荐手工部署，在hadoop002、hadoop003都要部署。

[root@hadoop001 software]# scp dbus-canal-auto-0.5.0.tar.gz root@hadoop002:/home/hadoop/app/dbus

[root@hadoop001 software]# scp dbus-canal-auto-0.5.0.tar.gz root@hadoop003:/home/hadoop/app/dbus

[hadoop@hadoop002 dbus]$ tar -zxvf dbus-canal-auto-0.5.0.tar.gz

[hadoop@hadoop003 dbus]$ tar -zxvf dbus-canal-auto-0.5.0.tar.gz

[hadoop@hadoop002 conf]$ vim canal-auto.properties

#数据源名称，需要与dbus keeper中添加的一致
dsname=hello_db
#zk地址,替换成自己的信息
zk.path=hadoop001:2181
#canal 用户连接地址。即：要canal去同步的源端库的备库的地址
canal.address=hadoop002:3306
#canal用户名
canal.user=canal
#canal密码，替换成自己配置的
canal.pwd=123456

执行deploy.sh脚本

 

执行完之后多了几个目录:

canal-hello-db:这个就是他自动部署的canal，实际上是从它上面的canal目录拷贝了一份，并自动生成了配置

canal.log、hello_db.log就是两个软连接，方便我们查看日志，省得进到很深的目录里

reports这里存放自动部署和deploy.sh check时生成的报告，如果发现canal异常可以去这里面检查部署时的报错信息

我们的启停脚本就在canal-hello-db这个目录下，大家进入目录就能看到。

自动部署之后就不要再执行deploy.sh。

jdbc:MySQL://hadoop001:3306/dbus?useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false&noAccessToProcedureBodies=true&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false

[hadoop@hadoop003 dbuskeeper_web]$ sudo cp -r dbus_jars /usr/hdp/current/storm-client/

[hadoop@hadoop003 dbuskeeper_web]$ sudo scp -r dbus_jars root@hadoop002:/usr/hdp/current/storm-client/