1.开启binlog https://www.cnblogs.com/yoyo1216/p/10209107.html mysql安装教程
# 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
3.9 查看clickhouse日志 https://www.cnblogs.com/yoyo1216/protected/p/12703256.html clickhouse安装
cat /var/log/clickhouse-server/clickhouse-server.log | tail -n 200 cat /var/log/clickhouse-server/clickhouse-server.err.log | tail -n 200