Centos7安装Mysql

https://juejin.im/post/6844903870053761037

关闭***/开启3306端口

Centos7***是failwall. systemctl stop failwalld
firewall-cmd --zone=public --add-port=80/tcp --permanent

创建Canal监控用户并设置权限

CREATE USER 'canal'@'%' IDENTIFIED BY 'canal';
GRANT SHOW VIEW, SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
FLUSH PRIVILEGES;

查看mysql日志是否启动

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF    |
+---------------+-------+
1 row in set (0.00 sec)

配置/etc/my.cnf

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# 密码校验关闭，可以设置简单密码
validate_password=OFF
# 设置服务编码
character_set_server=utf8
init-connect='SET NAMES utf8'
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 设置sql模式，防止每次sql出现 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated错误
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
# 5.7版本之上，设置该配置使用canal的getsql可以获取到执行sql语句。但该语句对应方式为query。要么自己判断sql类型，要么自己封装sql语句生成方法
binlog-rows-query-log-events=true

# log日志格式设置为row。并且开启log-bin(canal必须设置)
server-id=1
log-bin=mysql-bin
binlog_format=ROW

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# 设置mysql监听地址为所有地址均可以访问监听
bind-address=0.0.0.0

Canal相关配置简单讲解

config下instance实例配置

# 设置canal为mysql的slave节点的ID，有多Instance时必须唯一
canal.instance.mysql.slaveId=11306
# 主节点地址
canal.instance.master.address=192.168.220.128:3306

# 主节点账号
canal.instance.dbUsername=canal
# 主节点密码
canal.instance.dbPassword=canal
# 主节点连接编码格式
canal.instance.connectionCharset = UTF-8

# 实例监控数据库白名单正则表达式。
# 参考https://blog.csdn.net/my201110lc/article/details/80765356
canal.instance.filter.regex=canal\\..*
# 表过滤黑名单，过滤非需要的表
canal.instance.filter.black.regex=mysql\\.slave_.*

server服务器端canal.properties配置

# 监听端口，client需要连接该端口监听数据库日志信息
canal.port = 11111

# 客户端连接账户密码，密码在客户端为明文，配置处为select password('明文')
canal.user = canal
canal.passwd = 4A397EBD660A95CCE02BC2EA465D506AAE1527DB(明文为raiway)

 #ddl语句是否隔离发送,开启隔离可保证每次只返回发送一条ddl数据,不和其他dml语句混合返回.(otterddl同步使用)
canal.instance.filter.query.dcl = false           

 #是否忽略DML的query语句,比如insert/update/delete table.(mysql5.6的ROW模式可以包含statement模式的query记录)
canal.instance.filter.query.dml = false     

 #是否忽略DDL的query语句,比如create table/alater table/drop table/rename table/create index/drop index. (目前支持的ddl类型主要为table级别的操作,create databases/trigger/procedure暂时划分为dcl类型)
canal.instance.filter.query.ddl = false

客户端设置监控地址

# 该配置可以覆盖server端的表过滤配置;客户端配置覆盖订阅地址转义符需要去掉一个。服务端配置多一个转义符的意义在于读取数据流转换，客户端不需要
CanalConnector.subscribe(".*\..*")

是否客户端有覆盖订阅，如果有的话试着去掉客户端覆盖配置的一个\转义符试试。客户端不想覆盖订阅的话subscribe()参数空着就好
https://blog.csdn.net/he1154910941/article/details/108790548

SQL语句异常，表已存在

SQLSyntaxErrorException: Table 'online_status_backup_20201001' already exists

SQL语句异常,表不存在

SQLSyntaxErrorException: Table 'railway_biz.abcd' doesn't exist

Canal容器启动

version: '3'
services:
  canal-server:
    image: canal/canal-server:latest
    restart: always
    container_name: canal-server
    ports:
      - 11111:11111
    volumes:
      - /home/dockerimages/canal-server/conf/:/home/admin/canal-server/conf/
      - /home/dockerimages/canal-server/logs/:/home/admin/canal-server/logs/