# mysql配置文件/etc/my.cnf添加下面配置
vim /etc/my.cnf
#插入下面内容
[mysqld]
log-bin = mysql-bin # 开启日志
server-id = 1
log_bin = /var/lib/mysql/bin.log
binlog-format = row
# optional
expire_logs_days = 30
max_binlog_size = 100M
# setup listen address
bind-address = 0.0.0.0
2.重启mysql
service mysqld restart # 重启mysql
3.查看binlog是否开启
mysql -uroot -proot
show variables like 'log_%'; # 查看日志是否开启成功
show master status; # 查看当前日志
show variables like 'binlog_%'; # 查看binlog信息
show binary logs; # 获取logbin文件列表
show master status\G; # 查看当前正在写入的日志
show binlog events in 'mysql-bin.000002'; # 查看制定binlog文件的内容
show binary logs; # 查看binlog文件
purge binary logs to 'bin.000005'; # 删除bin.000005以前的binlog日志 不包含这个日志
4.新增同步账号
#登陆mysql,执行下面命令，创建账号canal，密码为root
mysql -h192.168.107.216 -P3306 -uroot -proot
set global validate_password_policy=0;
set global validate_password_length=1;
CREATE USER 'canal'@'%' IDENTIFIED BY 'root';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO "canal"@"%";
flush privileges;
grant all privileges on *.* to 'root1'@'%' identified by 'root' with grant option;
all privileges：表示将所有权限授予给用户。也可指定具体的权限，如：SELECT、CREATE、DROP等。
on：表示这些权限对哪些数据库和表生效，格式：数据库名.表名，这里写“*”表示所有数据库，所有表。如果我要指定将权限应用到test库的user表中，可以这么写：test.user
to：将权限授予哪个用户。格式：”用户名”@”登录IP或域名”。%表示没有限制，在任何主机都可以登录。比如：”yangxin”@”192.168.0.%”，表示yangxin这个用户只能在192.168.0IP段登录
identified by：指定用户的登录密码
with grant option：表示允许用户将自己的权限授权给其它用户
show grants for 'yangxin'@'localhost'; # 查看用户权限
2.canal-server安装配置
2.1 创建目录
mkdir /usr/local/canal && cd /usr/local/canal
2.2 下載地址 https://github.com/alibaba/canal/releases
wget https://github.com/alibaba/canal/releases/download/canal-1.1.4/canal.deployer-1.1.4.tar.gz
2.3 解压
tar -zxvf canal.deployer-1.1.4.tar.gz
2.4 添加环境变量
vim /etc/profile source /etc/profile
export PATH=$PATH:/usr/local/canal/bin
2.5 修改example文件夹下面的instance.properties文件下面几项为你自己的数据库配置即可
vi /usr/local/canal/conf/example/instance.properties
canal.instance.master.address=192.168.107.216:3306 # position info
canal.instance.dbUsername=canal # username
canal.instance.dbPassword=root # password
2.6 启动，安装目录下执行以下命令,server,instance出现下面日记说明启动成功
bash /usr/local/canal/bin/startup.sh # 启动
bash /usr/local/canal/bin/stop.sh # 关闭
bash /usr/local/canal/bin/restart.sh # 重启
2.7 查看server日记
cat /usr/local/canal/logs/canal/canal.log | tail -n 200
2.8 查看instance日记
cat /usr/local/canal/logs/example/example.log | tail -n 200
3.canal-client安装配置
3.1 创建目录
mkdir /usr/local/canal-adapter && cd /usr/local/canal-adapter
3.2 下载 canal-client安装配置 # https://github.com/wzq1990413/canal/releases https://github.com/alibaba/canal/releases
wget https://github.com/alibaba/canal/releases/download/canal-1.1.4/canal.adapter-1.1.4.tar.gz
wget https://github.com/wzq1990413/canal/releases/download/adapter-clickhouse/canal.adapter-1.1.4.tar.gz # 带clickhouse 驱动 推荐下载这个
3.3 解压
tar zxvf canal.adapter-1.1.4.tar.gz
3.4 在lib目录下面添加clickhouse连接驱动 下载clickhouse 版本 自带这些jar包 无需在下载这些jar包
clickhouse-jdbc-0.2.jar
httpclient-4.2.5.jar
httpcore-4.4.5.jar
3.5 修改conf/application.yml
vim /usr/local/canal-adapter/conf/application.yml
#canal-server地址
canalServerHost: 127.0.0.1:11111
#同步数据源配置
srcDataSources
defaultDS:
#mysql连接信息
url: jdbc:mysql://192.168.107.216:3306/canal?useUnicode=true&characterEncoding=utf8&useSSL=false
username: canal
password: root
canalAdapters:
- instance: example # canal instance Name or mq topic name
groups:
- groupId: g1
outerAdapters:
- name: logger
- name: rdb #rdb类型
key: mysql1
properties:
#clickhouse数据看配置
jdbc.driverClassName: ru.yandex.clickhouse.ClickHouseDriver
jdbc.url: jdbc:clickhouse://192.168.107.216:8123/canal
jdbc.username: default
jdbc.password:
3.6 修改rdb文件夹中配置
#1.同步增个库配置，前提条件是mysql数据库名，跟clickhouse数据库名一致
vim /usr/local/canal-adapter/conf/rdb/mytest_user.yml
#添加以下内容
## Mirror schema synchronize config
dataSourceKey: defaultDS #application.yml中一致
destination: example #跟application.yml中instance值一样
groupId: g1 #跟application.yml中groupId值一样
outerAdapterKey: mysql1 #跟application.yml中outerAdapters中key值一样
concurrent: true
dbMapping:
mirrorDb: true
database: canal #要同步的mysql库
#2.如果mysql,clickhouse库名不一致，则要同步的表分别在rdb中新增一个配置文件，以t_download表为列
vim /usr/local/canal-adapter/conf/rdb/mytest_user.yml
#添加以下内容
dataSourceKey: defaultDS # application.yml中一致
destination: example # 跟application.yml中instance值一样
groupId: g1 # 跟application.yml中groupId值一样
outerAdapterKey: mysql1 # 跟application.yml中outerAdapters中key值一样
concurrent: true
dbMapping:
database: clickhouse # mysql数据库名
table: t_download # mysql要同步的表
targetTable: maxwell.t_download # clickhouse中对应的表
id: id # 如果是复合主键可以换行映射多个
mapAll: true # 是否整表映射, 要求源表和目标表字段名一模一样 (如果targetColumns也配置了映射,则以targetColumns配置为准)
#targetColumns: # 字段映射, 格式: 目标表字段: 源表字段, 如果字段名一样源表字段名可不填
# id:
# name:
# role_id:
#c_time:
#test1:
3.7 启动
bash /usr/local/canal-adapter/bin/startup.sh # 启动
bash /usr/local/canal-adapter/bin/stop.sh # 关闭
bash /usr/local/canal-adapter/bin/restart.sh # 重启
3.8 查看日志
cat /usr/local/canal-adapter/logs/adapter/adapter.log | tail -n 200
cat /var/log/clickhouse-server/clickhouse-server.log | tail -n 200
cat /var/log/clickhouse-server/clickhouse-server.err.log | tail -n 200
