MySQL8.0.30一主两从复制与配置(一)_蜗牛杨哥的博客-CSDN博客
一: 使用CanalAdmin
来搭建一个canal集群
1.1 环境规划
服务名称|主机节点 | 机器IP | 节点 | 备注 |
canal admin | docker0 | 192.168.1.100 | 192.168.1.100:8888 | canal admin 机器 |
canal server 1 | docker1 | 192.168.1.101 | 192.168.1.101:8888 | canal server机器 |
canal server 2 | docker2 | 192.168.1.102 | 192.168.1.102:8888 | canal server机器 |
zookeeper | 192.168.1.100 | 192.168.1.100:2181 | 主从节点随机选举 |
192.168.1.101 | 192.168.1.101:2181 | 主从节点随机选举 | |
192.168.1.102 | 192.168.1.102:2181 | 主从节点随机选举 | |
mysql
| 192.168.1.100 | 192.168.1.100:3306 | master |
192.168.1.101 | 192.168.1.101:3306 | slave | |
192.168.1.102 | 192.168.1.102:3306 | slave |
1.2: 上传相应的包至docker0主机
主要包有:
canal.adapter-1.1.6.tar.gz
canal.admin-1.1.6.tar.gz
canal.deployer-1.1.6.tar.gz
1.3:分发这些包至|docker1|docker2|主机
使用Xshell客户端控制同时控制|docker0|docker1|docker2|主机
在docker0主机上执行创建响应目录
[root@www canal]# mkdir -p /usr/local/canal/adapter
[root@www canal]# mkdir -p /usr/local/canal/admin
[root@www canal]# mkdir -p /usr/local/canal/deployerdocker0主机执行命令完成,则其他机器会同步创建这些目录
关闭docker1|docker2主机上的Xshell同步开发置为off,暂停同步操作
在docker0主机上分发对应安装包到docker1|docker2主机对应目录:/usr/local/tools/
[root@www tools]# scp canal.adapter-1.1.6.tar.gz canal.admin-1.1.6.tar.gz canal.deployer-1.1.6.tar.gz docker1:/usr/local/tools/
[root@www tools]# scp canal.adapter-1.1.6.tar.gz canal.admin-1.1.6.tar.gz canal.deployer-1.1.6.tar.gz docker2:/usr/local/tools/
1.3: Xshell同步执行解压包到响应目录
[root@www canal]# cd /usr/local/tools
[root@www tools]# ll
总用量 1816124
-rw-r--r-- 1 root root 19935216 7月 19 17:17 apache-zookeeper-3.9.0-bin.tar.gz
-rw-r--r-- 1 root root 247732147 8月 30 09:07 canal.adapter-1.1.6.tar.gz
-rw-r--r-- 1 root root 39171682 8月 30 09:07 canal.admin-1.1.6.tar.gz
-rw-r--r-- 1 root root 107152758 8月 30 09:07 canal.deployer-1.1.6.tar.gz
-rw-r--r-- 1 root root 195094741 8月 21 14:21 jdk-8u221-linux-x64.tar.gz
-rwxr-xr-x 1 root root 139219380 8月 21 15:50 jdk-8u371-linux-x64.tar.gz
-rw-r--r--. 1 root root 1111524352 7月 7 2022 mysql-8.0.30-linux-glibc2.12-x86_64.tar
-rwxr-xr-x 1 root root 7 8月 21 21:04 send.txt
[root@www tools]# tar -zxvf canal.adapter-1.1.6.tar.gz -C /usr/local/canal/adapter/
[root@www tools]# tar -zxvf canal.admin-1.1.6.tar.gz -C /usr/local/canal/admin/
[root@www tools]# tar -zxvf canal.deployer-1.1.6.tar.gz -C /usr/local/canal/deployer/
1.4: 配置Canal Admin的数据库连接信息
[root@www conf]# cat application.yml
server:
port: 8888
spring:
jackson:
date-format: yyyy-MM-dd HH:mm:ss
time-zone: GMT+8spring.datasource:
address: 192.168.1.100:3306
database: canal_manager
username: canal
password: canal
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://${spring.datasource.address}/${spring.datasource.database}?sslMode=DISABLED&serverTimezone=GMT%2B8&useSSL=false
hikari:
maximum-pool-size: 30
minimum-idle: 1canal:
adminUser: admin
adminPasswd: admin123456
1.5: 导入数据库脚本
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| tope-pay-user |
+--------------------+
5 rows in set (0.03 sec)mysql> source /usr/local/canal/admin/conf/canal_manager.sql
Query OK, 1 row affected, 2 warnings (0.00 sec)mysql> show databases;
+--------------------+
| Database |
+--------------------+
| canal_manager |
| information_schema |
| mysql |
| performance_schema |
| sys |
| tope-pay-user |
+--------------------+
6 rows in set (0.00 sec)mysql> use canal_manager;
Database changed
mysql> show tables;
+-------------------------+
| Tables_in_canal_manager |
+-------------------------+
| canal_adapter_config |
| canal_cluster |
| canal_config |
| canal_instance_config |
| canal_node_server |
| canal_user |
+-------------------------+
6 rows in set (0.00 sec)
1.6: 启动canal admin
bin/startup.sh
确认是否已经启动[root@www bin]# netstat -lntp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 192.168.1.100:33806 0.0.0.0:* LISTEN 1185/minio
tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN 661/rpcbind
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1173/sshd
tcp 0 0 192.168.1.100:9666 0.0.0.0:* LISTEN 1185/minio
tcp 0 0 127.0.0.1:9666 0.0.0.0:* LISTEN 1185/minio
tcp6 0 0 :::33060 :::* LISTEN 1171/mysqld
tcp6 0 0 :::2181 :::* LISTEN 1221/java
tcp6 0 0 :::3306 :::* LISTEN 1171/mysqld
tcp6 0 0 :::111 :::* LISTEN 661/rpcbind
tcp6 0 0 192.168.1.100:3888 :::* LISTEN 1221/java
tcp6 0 0 :::8080 :::* LISTEN 1221/java
tcp6 0 0 :::22 :::* LISTEN 1173/sshd
tcp6 0 0 :::45183 :::* LISTEN 1221/java
tcp6 0 0 ::1:9666 :::* LISTEN 1185/minio
[root@www bin]# netstat -lntp | grep 8888
没有发现8888这个端口进程,启动失败,看日志
该异常:由于装的数据库时8.0.30,该jar版本低了,上传高版本的mysql驱动包:mysql-connector-java-8.0.30.jar
[root@www lib]# find ./ -name mysq*
./mysql-connector-java-8.0.30.jar再次重启:
[root@www logs]# tail -f admin.log
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:85)
at com.mysql.cj.protocol.a.authentication.CachingSha2PasswordPlugin.nextAuthenticationStep(CachingSha2PasswordPlugin.java:130)
at com.mysql.cj.protocol.a.authentication.CachingSha2PasswordPlugin.nextAuthenticationStep(CachingSha2PasswordPlugin.java:49)
at com.mysql.cj.protocol.a.NativeAuthenticationProvider.proceedHandshakeWithPluggableAuthentication(NativeAuthenticationProvider.java:447)
at com.mysql.cj.protocol.a.NativeAuthenticationProvider.connect(NativeAuthenticationProvider.java:212)
at com.mysql.cj.protocol.a.NativeProtocol.connect(NativeProtocol.java:1433)
at com.mysql.cj.NativeSession.connect(NativeSession.java:133)
at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:948)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:818)
... 44 common frames omitted
这个是数据库加密升级原因;
创建一个canal账号及密码: canal
CREATE USER 'canal'@'%' IDENTIFIED WITH mysql_native_password BY 'canal';
再次启动:
[root@www logs]# tail -f admin.log
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)
[root@www lib]# find ./ -name Hikari*
./HikariCP-2.7.8.jar
Access denied for user 'canal'@'%' to database 'canal_manager'这个异常一般没权限: canal 用户没权限访问数据库canal_manager
UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='canal';
GRANT INSERT ON canal_cluster TO 'canal'@'%'
GRANT INSERT ON canal_config TO 'canal'@'%'
flush privileges;
解决方案:
canal报错,解决方法,将数据库切到canal_manager,使用以下命令即可
GRANT INSERT ON canal_node_server TO 'canal'@'%'
同样新增集群也会报这个错,类似命令:
GRANT INSERT ON canal_cluster TO 'canal'@'%'
模板保存也是
GRANT INSERT ON canal_config TO 'canal'@'%'
使用local启动的时候要创建相应的模板。
查看日志启动成功:
[root@www logs]# ps aux | grep 'canal'
[root@www logs]# netstat -lntp | grep 8888
tcp 0 0 0.0.0.0:8888 0.0.0.0:* LISTEN 6188/java
默认账号/密码: admin/123456
后续补充完整.........................