1.安装mysql

Ubuntu上安装MySQL

2.安装jdk

Linux笔记

3.canal提前设置

0) 查询是否开启binlog

  1. show variables like '%log_bin%';
  2. +---------------------------------+-------+
  3. | Variable_name                   | Value |
  4. +---------------------------------+-------+
  5. | log_bin                         | OFF   |
  6. | log_bin_basename                |       |
  7. | log_bin_index                   |       |
  8. | log_bin_trust_function_creators | OFF   |
  9. | log_bin_use_v1_row_events       | OFF   |
  10. | sql_log_bin                     | ON    |
  11. +---------------------------------+-------+

1) 首先需要知道mysql数据库安装在什么位置
查询mysql安装路径：

  1. adrian@adrian-Virtual-Machine:/etc$ which mysql
  2. /usr/bin/mysql

2)查找配置文件位置
然后在根据这个目录，查看配置文件在哪里了（路径后面加上 —verbose —help|grep -A 1 ‘Default options’），就会出现以下信息:

  1. adrian@adrian-Virtual-Machine:/etc$ /usr/bin/mysql --verbose --help|grep -A 1 'Default option'
  2. Default options are read from the following files in the given order:
  3. /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

3)修改文件
服务器首先读取的是/etc/my.cnf文件，如果前一个文件不存在则继续读/etc/mysql/my.cnf文件，如果还不存在依次向后查找，找到配置文件以后就可以根据需要修改配置文件，这里我修改的/etc/mysql/my.cnf

  • 对于自建 MySQL , 需要先开启 Binlog 写入功能，配置 binlog-format 为 ROW 模式，my.cnf 中配置如下: （一定要放在[mysqld]下，没有则加上）

    1. [mysqld]
    2. log-bin=mysql-bin # 开启 binlog
    3. binlog-format=ROW # 选择 ROW 模式
    4. server_id=1 # 配置 MySQL replaction 需要定义，不要和 canal 的 slaveId 重复
    • 注意：针对阿里云 RDS for MySQL , 默认打开了 binlog , 并且账号默认具有 binlog dump 权限 , 不需要任何权限或者 binlog 设置,可以直接跳过这一步
  • 重启mysql

service mysql stop
service mysql start

  1.  show variables like '%log_bin%';
  2. +---------------------------------+--------------------------------+
  3. | Variable_name                   | Value                          |
  4. +---------------------------------+--------------------------------+
  5. | log_bin                         | ON                             |
  6. | log_bin_basename                | /var/lib/mysql/mysql-bin       |
  7. | log_bin_index                   | /var/lib/mysql/mysql-bin.index |
  8. | log_bin_trust_function_creators | OFF                            |
  9. | log_bin_use_v1_row_events       | OFF                            |
  10. | sql_log_bin                     | ON                             |
  11. +---------------------------------+--------------------------------+
  • 授权 canal 链接 MySQL 账号具有作为 MySQL slave 的权限, 如果已有账户可直接 grant，默认账号canal，canal
    1. CREATE USER canal IDENTIFIED BY 'canal';  
    2. GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
    3. -- 下面直接赋予所有权限，可以直接连接创建数据库
    4. -- GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%' ;
    5. FLUSH PRIVILEGES;

    4.安装cannal

    1. 下载canal通过ftp工具上传到/home/adrian目录
    2. sudo mv canal.deployer-1.1.4.tar.gz /usr/local
    3. cd /usr/local
    4. ## 自己创建的文件夹只有ll查看权限，chmod更改权限
    5. sudo mkdir /usr/local/canal
    6. sudo tar zxvf canal.deployer-1.1.4.tar.gz -C /usr/local/canal
    7. sudo rm -rf canal.deployer-1.1.4.tar.gz
    配置修改 ```powershell sudo vim conf/example/instance.properties

修改内容

mysql serverId

canal.instance.mysql.slaveId = 0

position info，需要改成自己的数据库信息

canal.instance.master.address = 192.168.101.184:3306 canal.instance.master.journal.name = canal.instance.master.position = canal.instance.master.timestamp =

canal.instance.standby.address =

canal.instance.standby.journal.name =

canal.instance.standby.position =

canal.instance.standby.timestamp =

username/password，需要改成自己的数据库信息

canal.instance.dbUsername = canal
canal.instance.dbPassword = canal canal.instance.defaultDatabaseName = canal.instance.connectionCharset = UTF-8

table regex

canal.instance.filter.regex = .*\\..*

  2. - canal.instance.connectionCharset 代表数据库的编码方式对应到 java 中的编码类型，比如 UTF-8，GBK , ISO-8859-1
  3. - 如果系统是1个 cpu，需要将 canal.instance.parser.parallel 设置为 false
  5. 启动canal
  6. ```shell
  7. cd /usr/local/canal
  8. ./bin/startup.sh
  9. ## 报没有创建文件夹的权限，临时解决直接给所有用户权限
  10. sudo chmod -R 777 /usr/local/canal
  11. ## 没有启动查看日志
  12. cd /usr/local/canal
  13. cat logs/canal/canal.log
  14. ## 报地址正在使用,kill进程或者重启

5.安装cananl-adapter

  1. cd ~
  2. sudo mv canal.adapter-1.1.4.tar.gz  /usr/local
  3. sudo mkdir /usr/local/canal-adapter
  4. sudo tar zxvf canal.adapter-1.1.4.tar.gz -C /usr/local/canal-adapter
  5. ## 临时直接给所有权限
  6. sudo chmod -R 777 /usr/local/canal-adapter

先创建mysql同步的表以及ES创建对应的索引再启动canal-adapter，否则会报错

  1. -- 先创建数据库canal，再创建用户表，导入数据库数据，通过kibana创建索引
  2. create table user (
  3.   `id` int(10) NOT NULL,
  4.   `name` varchar(100) DEFAULT NULL,
  5.   `role_id` int(10) NOT NULL,
  6.   `c_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  7.   `c_utime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  8.   PRIMARY KEY (`id`)
  9. );
  1. PUT /mytest_user
  2. {
  3.   "mappings": {
  4.     "_doc": {
  5.       "properties": {
  6.         "name": {
  7.           "type": "text",
  8.           "fields": {
  9.             "keyword": {
  10.               "type": "keyword"
  11.             }
  12.           }
  13.         },
  14.         "role_id": {
  15.           "type": "long"
  16.         },
  17.         "c_time": {
  18.           "type": "date"
  19.         }
  20.       }
  21.     }
  22.   }
  23. }

修改配置文件

  1. cd /usr/local/canal-adapter
  2. sudo vim conf/application.yml
  1. server:
  2.   port: 8081
  3. spring:
  4.   jackson:
  5.     date-format: yyyy-MM-dd HH:mm:ss
  6.     time-zone: GMT+8
  7.     default-property-inclusion: non_null
  8. canal.conf:
  9.   mode: tcp # kafka rocketMQ
  10.   canalServerHost: 192.168.101.184:11111
  11. #  zookeeperHosts: slave1:2181
  12. #  mqServers: 127.0.0.1:9092 #or rocketmq
  13. #  flatMessage: true
  14.   batchSize: 500
  15.   syncBatchSize: 1000
  16.   retries: 0
  17.   timeout:
  18.   accessKey:
  19.   secretKey:
  20.   srcDataSources:
  21.     defaultDS:
  22.       url: jdbc:mysql://192.168.101.184:3306/canal?useUnicode=true
  23.       username: canal
  24.       password: canal
  25.   canalAdapters:
  26.   - instance: example # canal instance Name or mq topic name
  27.     groups:
  28.     - groupId: g1
  29.       outerAdapters:
  30.       - name: logger
  31. #      - name: rdb
  32. #        key: mysql1
  33. #        properties:
  34. #          jdbc.driverClassName: com.mysql.jdbc.Driver
  35. #          jdbc.url: jdbc:mysql://127.0.0.1:3306/mytest2?useUnicode=true
  36. #          jdbc.username: root
  37. #          jdbc.password: 121212
  38. #      - name: rdb
  39. #        key: oracle1
  40. #        properties:
  41. #          jdbc.driverClassName: oracle.jdbc.OracleDriver
  42. #          jdbc.url: jdbc:oracle:thin:@localhost:49161:XE
  43. #          jdbc.username: mytest
  44. #          jdbc.password: m121212
  45. #      - name: rdb
  46. #        key: postgres1
  47. #        properties:
  48. #          jdbc.driverClassName: org.postgresql.Driver
  49. #          jdbc.url: jdbc:postgresql://localhost:5432/postgres
  50. #          jdbc.username: postgres
  51. #          jdbc.password: 121212
  52. #          threads: 1
  53. #          commitSize: 3000
  54. #      - name: hbase
  55. #        properties:
  56. #          hbase.zookeeper.quorum: 127.0.0.1
  57. #          hbase.zookeeper.property.clientPort: 2181
  58. #          zookeeper.znode.parent: /hbase
  59.       - name: es
  60.         hosts: 192.168.101.184:9300
  61.         properties:
  62.           cluster.name: my-application
  1. sudo vim conf/es/mytest_user.yml
  1. dataSourceKey: defaultDS
  2. destination: example
  3. groupId: g1
  4. esMapping:
  5.   _index: mytest_user
  6.   _type: _doc
  7.   _id: _id
  8.   upsert: true
  9. #  pk: id
  10.   sql: "select a.id as _id, a.name, a.role_id, a.c_time from user a"
  11. #  objFields:
  12. #    _labels: array:;
  13. #  etlCondition: "where a.c_time>='{0}'"
  14.   commitBatch: 3000

启动canal-adapter

  1. cd /usr/local/canal-adapter
  2. ./bin/startup.sh

查看日志

  1. cat logs/adapter/adapter.log