6、Canal实现MySQL到ES实时同步1

1、准备一台虚拟机

1.1 配置静态ip

可以参考 https://www.cnblogs.com/braveym/p/9096402.html 

1.2给hadoop用户赋予root权限

切换到root用户

并输入命令 

visudo

这时会进入/etc/sudoers文件的编辑页面，增加如下配置即可

hadoop ALL=(ALL) NOPASSWD: ALL
#以下这行一定要注释掉以免被覆盖(hadoop用户属于wheel组)
#%wheel ALL=(ALL) ALL

1.3 配置hostname

永久修改hostname执行如下命令

sudo hostnamectl set-hostname canal01

1.4 配置hosts文件

sudo vi /etc/hosts

添加以下内容

172.16.25.31 canal01

2、安装Mysql

配置Mysql 8.0安装源

sudo rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

安装mysql

 sudo yum -y install mysql-community-server

出现以下错误

warning: /var/cache/yum/x86_64/7/mysql80-community/packages/mysql-community-client-8.0.28-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

The GPG keys listed for the "MySQL 8.0 Community Server" repository are already installed but they are not correct for this package.
Check that the correct key URLs are configured for this repository.

 Failing package is: mysql-community-client-8.0.28-1.el7.x86_64
 GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

出现上述提示原因是Mysql的GPG升级了，需要重新获取

执行以下命令解决：

rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

再次进行服务安装：sudo yum -y install mysql-server就可以了

设置开机启动mysql:

sudo systemctl enable mysqld

启动mysql:

sudo systemctl start mysqld

查看Mysql状态：

sudo systemctl status mysqld

查看root的临时密码：

sudo grep 'temporary password' /var/log/mysqld.log

 我们看到随机生成的root临时密码是(注意每次随机生成密码不一样，不要死记)

修改root密码，先进入mysql命令行:

mysql -u root -p

 这时会要求输入密码，我们输入上述临时密码然后回车即可，然后按照如下命令把root用户的密码改为root%123

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Root_12root';
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

mysql> set global validate_password.policy=0;
Query OK, 0 rows affected (0.01 sec)

mysql> set global validate_password.length=1;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root%123';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

 在生产上建议把root密码设置的更加复杂，我这里设置的root只能本机连接

 3、MySQL特殊处理

在生产上Canal-Admin自己用的MySQL和你要增量采集binlog的MySQL大概率不是同一个，甚至在不

同的机器上。为了简单，我们这里采用同一个MySQL，生产上自己要注意。

 

 

为Canal-Server采集数据创建用户

mysql -u root -p

set global validate_password.policy=0; 
set global validate_password.length=1; 
CREATE USER canal IDENTIFIED BY 'canal'; 
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%'; 
GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%' ; 
FLUSH PRIVILEGES; 
exit

为Canal-Admin创建用户

mysql -u root -p

set global validate_password.policy=0; 
set global validate_password.length=1; 
CREATE USER canaladmin IDENTIFIED BY 'canaladmin'; 
GRANT ALL ON canal_manager.* TO 'canaladmin'@'%'; 

  revoke all privileges,grant option from 'canaladmin'@'%';

FLUSH PRIVILEGES; 
exit

配置源MySQL的Binlog格式

sudo vi /etc/my.cnf

增加以下配置：

server-id=1 
log-bin=mysql-bin
binlog-format=ROW 
binlog-ignore-db=information_schema 
binlog-ignore-db=mysql 
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

log-bin用于指定binlog日志文件名前缀，默认存储在/var/lib/mysql 目录下。

server-id用于标识唯一的数据库，不能和别的服务器重复，建议使用ip的最后一段，默认值也不可以。

binlog-ignore-db：表示同步的时候忽略的数据库。

binlog-do-db：指定需要同步的数据库（如果没有此项，表示同步所有的库）。

配置完保存退出，然后重启MySQL。

登录mysql

mysql -uroot -p

如果能看到上面的图，则binlog 就成功开启了。

禁用explicit_defaults_for_timestamp

mysql -uroot -p

SHOW VARIABLES LIKE '%explicit_defaults_for_timestamp%';
set persist explicit_defaults_for_timestamp=0;
SHOW VARIABLES LIKE '%explicit_defaults_for_timestamp%';

重启MySQL使上面修改生效：

sudo systemctl status mysqld

 

安装MySQL的Java驱动

sudo mkdir -p /usr/share/java

将MySQL的java驱动mysql-connector-java-8.0.18.jar上传到该目录下

如果本地没有改驱动板也可以通过wget下载一个

wget https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.18/mysql-connector-java-8.0.18.jar

4、安装Canal

 

下载安装包

 

 wget https://github.com/alibaba/canal/releases/download/canal-1.1.4/canal.admin-1.1.4.tar.gz

 wget https://github.com/alibaba/canal/releases/download/canal-1.1.4/canal.deployer-1.1.4.tar.gz

安装Canal-Admin

 解压缩

mkdir -p app/canal-admin
tar -zxvf canal.admin-1.1.4.tar.gz -C app/canal-admin
cd app/canal-admin

配置环境变量

sudo vim /etc/profile

末尾添加：

#canal-admin
export CANAL_ADMIN_HOME=/home/hadoop/app/canal-admin
export PATH=${CANAL_ADMIN_HOME}/bin:$PATH

使环境变量生效：

source /etc/profile

修改配置

server:
  port: 8089
spring:
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss
    time-zone: GMT+8

spring.datasource:
  address: canal01:3306
  database: canal_manager
  username: canal
  password: canal
  driver-class-name: com.mysql.jdbc.Driver
  url: jdbc:mysql://${spring.datasource.address}/${spring.datasource.database}?useUnicode=true&characterEncoding=UTF-8&useSSL=false
  hikari:
    maximum-pool-size: 30
    minimum-idle: 1

canal:
  adminUser: admin
  adminPasswd: admin

替换MySQL驱动包

我们安装的MySQL8，但是Canal-Admin默认的MySQL驱动不支持MySQL8，因此需要替换驱动包：

ln -s /usr/share/java/mysql-connector-java-8.0.18.jar $CANAL_ADMIN_HOME/lib/mysql-connector-java-8.0.18.jar
rm $CANAL_ADMIN_HOME/lib/mysql-connector-java-5.1.40.jar

初始化元数据库

登录mysql执行以下命令：

source /home/hadoop/app/canal-admin/conf/canal_manager.sql

[hadoop@canal01 lib]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> source /home/hadoop/app/canal-admin/conf/canal_manager.sql
Query OK, 1 row affected, 2 warnings (0.00 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected, 2 warnings (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 2 warnings (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 4 warnings (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 4 warnings (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 6 warnings (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 2 warnings (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

启动canal-admin

sh $CANAL_ADMIN_HOME/bin/startup.sh

查看日志：

tail -n 100 admin.log

报错了！！！

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2022-03-28 15:21:00.496 [main] ERROR org.springframework.boot.SpringApplication - Application run failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'ebeanServer' defined in class path resource [com/alibaba/otter/canal/admin/config/EbeanConfig.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [io.ebean.EbeanServer]: Factory method 'ebeanServer' threw exception; nested exception is javax.persistence.PersistenceException: java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed
        at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:587)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1250)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1099)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:541)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:501)
        at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:317)
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228)
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:315)
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199)
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:760)
        at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:869)
        at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:550)
        at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:140)
        at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:759)
        at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:395)
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:327)
        at com.alibaba.otter.canal.admin.CanalAdminApplication.main(CanalAdminApplication.java:19)
Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [io.ebean.EbeanServer]: Factory method 'ebeanServer' threw exception; nested exception is javax.persistence.PersistenceException: java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed
        at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:185)
        at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:579)
        ... 16 common frames omitted
Caused by: javax.persistence.PersistenceException: java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed
        at io.ebeaninternal.server.core.DefaultContainer.checkDataSource(DefaultContainer.java:323)
        at io.ebeaninternal.server.core.DefaultContainer.createServer(DefaultContainer.java:106)
        at io.ebeaninternal.server.core.DefaultContainer.createServer(DefaultContainer.java:35)
        at io.ebean.EbeanServerFactory.createInternal(EbeanServerFactory.java:109)
        at io.ebean.EbeanServerFactory.create(EbeanServerFactory.java:70)
        at com.alibaba.otter.canal.admin.config.EbeanConfig.ebeanServer(EbeanConfig.java:38)
        at com.alibaba.otter.canal.admin.config.EbeanConfig$$EnhancerBySpringCGLIB$$c85da08f.CGLIB$ebeanServer$0(<generated>)
        at com.alibaba.otter.canal.admin.config.EbeanConfig$$EnhancerBySpringCGLIB$$c85da08f$$FastClassBySpringCGLIB$$d3e49a6c.invoke(<generated>)
        at org.springframework.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:228)
        at org.springframework.context.annotation.ConfigurationClassEnhancer$BeanMethodInterceptor.intercept(ConfigurationClassEnhancer.java:361)
        at com.alibaba.otter.canal.admin.config.EbeanConfig$$EnhancerBySpringCGLIB$$c85da08f.ebeanServer(<generated>)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:154)
        ... 17 common frames omitted
Caused by: java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:836)
        at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456)
        at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246)
        at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:199)
        at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:117)
        at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:123)
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:365)
        at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:194)
        at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:460)
        at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:534)
        at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)
        at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112)
        at io.ebeaninternal.server.core.DefaultContainer.checkDataSource(DefaultContainer.java:316)
        ... 32 common frames omitted

 在mysql的jdbc连接参数添加allowPublicKeyRetrieval=true

修改配置文件的jdbc连接参数

  重新启动，并查看日志

 通过浏览器打开页面 http://canal01:8089/#/login?redirect=%2Fdashboard

 

默认账号密码：admin/123456

 其他操作：

sh $CANAL_ADMIN_HOME/bin/stop.sh  //停止
sh  $CANAL_ADMIN_HOME/bin/restart.sh  //重启

安装Canal-Server

引入了canal-admin之后，canal-server之前面向命令行的运维方式需要有一些变化，主要的变化在于

配置体系上，每个server节点上不应该再去维护复杂而且冗长的

canal.properties/instance.properties，应该选择以最小化、无状态的方式去启动，因此在canal 1.1.4

上，对于配置做了一些重构来支持canal-admin，同时也兼容了原先的命令行运维模式。

解压缩安装包：

cd ~; 
mkdir -p app/canal-server; 
tar -zxvf canal.deployer-1.1.4.tar.gz -C app/canal-server;

 

配置环境变量：

sudo vim /etc/profile

末尾新增：

#canal-server 
export CANAL_SERVER_HOME=/home/hadoop/app/canal-server 
export PATH=${CANAL_SERVER_HOME}/bin:$PATH

使环境变量生效：

source /etc/profile

配置canal-server

采用canal-admin来管理canal-server之后，不再需要按照以前的方式修改大量配置了。

cd $CANAL_SERVER_HOME/conf/; 
mv canal.properties canal.properties.bak; 
mv canal_local.properties canal.properties; 
vim  $CANAL_SERVER_HOME/conf/canal.properties;

配置内容如下：

# register ip
canal.register.ip =

# canal admin config
canal.admin.manager = canal01:8089
canal.admin.port = 11110
canal.admin.user = admin
canal.admin.passwd = 4ACFE3202A5FF5CF467898FC58AAB1D615029441
# admin auto register
canal.admin.register.auto = true
canal.admin.register.cluster = 

替换MySQL驱动包

我们安装的MySQL8，但是Canal-Admin默认的MySQL驱动不支持MySQL8，因此需要替换驱动包

ln -s /usr/share/java/mysql-connector-java-8.0.18.jar $CANAL_SERVER_HOME/lib/mysql-connector-java-8.0.18.jar 
rm $CANAL_SERVER_HOME/lib/mysql-connector-java-5.1.47.jar

 

启停canal-server

$CANAL_SERVER_HOME/bin/startup.sh 
$CANAL_SERVER_HOME/bin/stop.sh 
$CANAL_SERVER_HOME/bin/restart.sh