使用Canal实现MySQL到MySQL的全量、增量同步
使用Canal实现MySQL到MySQL的全量、增量同步
一、简要说明
canal的使用需要一个服务端canal-deploy 和客户端canal-adapter，简单来说，服务端来监听源数据库的bin-log变化并解析为 sql等待客户端消费；客户端连接服务端来进行sql消费。
二、MySQL配置
参考官方文档：https://github.com/alibaba/canal/wiki/QuickStart?utm_source=www.jeeinn.com
2.1 安装 MySQL
Mysql8.0安装部分-主备库都需要安装MySQL
一、环境准备：
0.服务器规划
服务器名称 服务器IP 安装组件
master-db1 192.168.100.202 master, canal-deploy,canal-adapter,canal-admin
slave-db2 192.168.100.203 slave
1.目录创建：
mkdir -p /data/{software,mysql}
mkdir -p /data/mysql/{data,log,tmp}
2.用户创建：
useradd mysql
3.安装依赖包：
yum install perl perl-devel perl-Data-Dumper libaio-devel -y
二、安装：
1.下载包文件：
cd /data/software/
wget https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.26-el7-x86_64.tar.gz
tar -xvf /data/software/mysql-8.0.26-el7-x86_64.tar.gz
ln -s /data/software/mysql-8.0.26-el7-x86_64 /usr/local/mysql
2.权限赋值：
chown -R mysql:mysql /usr/local/mysql /data/mysql/*
三、配置文件：
参数可以根据实际需求进行调配
vi /etc/my.cnf
[client]
port = 3307
socket = /data/mysql/tmp/mysql.sock
[mysql]
prompt="\u@db \R:\m:\s [\d]> "
no-auto-rehash
[mysqld]
user = mysql
port = 3307
basedir = /usr/local/mysql
datadir = /data/mysql/data
tmpdir = /data/mysql/tmp
socket = /data/mysql/tmp/mysql.sock
pid-file = /data/mysql/tmp/mysql.pid
character-set-server=utf8mb4
collation-server = utf8mb4_general_ci
sql_mode=‘NO_UNSIGNED_SUBTRACTION,NO_ENGINE_SUBSTITUTION’
open_files_limit = 65535
innodb_open_files = 65535
back_log=1024
max_connections = 512
max_connect_errors=1000000
interactive_timeout=300
wait_timeout=300
max_allowed_packet = 1024M
secure_file_priv=’’
log-error=/data/mysql/log/error.log
slow_query_log=ON
slow_query_log_file=/data/mysql/log/slow_mysql.log
long_query_time=2
innodb_flush_log_at_trx_commit=1
innodb_log_file_size =1G
innodb_log_files_in_group=3
innodb_log_group_home_dir=./
log-bin-trust-function-creators=1
sync_binlog = 1
binlog_cache_size = 16M
max_binlog_cache_size = 1G
max_binlog_size=1G
expire_logs_days = 30
log-bin= /data/mysql/log/binlog-mysql
binlog_format=row
binlog_row_image=full
server-id = 1
default_authentication_plugin =mysql_native_password
# 大小根据实际系统内存情况而定
innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=2
四、初始化
1初始化操作
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/data --user=mysql --initialize
#查看初始化后数据库文件：
[root@localhost log]# ls /data/mysql/data
auto.cnf ca.pem client-key.pem ibdata1 ib_logfile1 #innodb_temp mysql.ibd private_key.pem server-cert.pem sys undo_002
ca-key.pem client-cert.pem ib_buffer_pool ib_logfile0 ib_logfile2 mysql performance_schema public_key.pem server-key.pem undo_001
2查看随机密码：可以看到我们的初始密码为 qsD#h/qwP4p?
[root@localhost redis]# cat /data/mysql/log/error.log | grep password
[Note] [MY-010454] [Server] A temporary password is generated for root@localhost: wAdJXLFSV7>P
五、最后我们启动mysql：
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
#建议软链接
ln -s /data/mysql/tmp/mysql.sock /tmp/mysql.sock
六、修改密码，设置环境变量以及自启动
1. 配置环境变量
[root@localhost log]# echo ‘export PATH=/usr/local/mysql/bin:$PATH’ >> /etc/profile
[root@localhost ~]# source /etc/profile
# 修改密码
[root@localhost ~]# /usr/local/mysql/bin/mysqladmin -uroot -p password
Enter password: 8y/,JHui*y;a
New password: Password123
Confirm new password: Password123
# 设置自启动
[root@localhost ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# chkconfig --add mysqld
[root@localhost ~]# chkconfig mysqld on
# 登入Mysql
[root@localhost bin]# mysql -uroot -pPassword123
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.26 MySQL Community Server - GPL
Copyright © 2000, 2021, 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.
#赋予远程访问权限
select host, user, authentication_string, plugin from mysql.user;
update mysql.user set host=’%’ where user=‘root’;
grant all privileges on *.* to root@’%’ with grant option; //执行两次
#刷新权限
flush privileges;
#登陆
mysql -uroot -pPassword123 -h192.168.100.202 -P3307
2.2 修改 my.ini 配置文件
[mysqld]
log-bin=mysql-bin # 开启binlog
binlog-format=ROW # 选择row模式
server_id=1 # serverid唯一
2.3 授权
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘canal’@’%’ IDENTIFIED BY ‘canal’;
FLUSH PRIVILEGES;
2.4 重启数据库
service mysqld restart
三、部署 canal
3.1 安装 java
yum -y install java
验证安装
java -version
openjdk version “1.8.0_312”
OpenJDK Runtime Environment (build 1.8.0_312-b07)
OpenJDK 64-Bit Server VM (build 25.312-b07, mixed mode)
3.2 下载 canal
wget https://github.com/alibaba/canal/releases/download/canal-1.1.5/canal.deployer-1.1.5.tar.gz
mkdir -p /opt/canal/deploy
tar xf canal.deployer-1.1.5.tar.gz -C /opt/canal/deploy
3.3 配置 canal
cd /opt/canal/deploy
vim conf/example/instance.properties
需要修改的参数：canal.instance.master.address，canal.instance.dbUsername，canal.instance.dbPassword，canal.instance.filter.regex
#################################################
## mysql serverId , v1.0.26+ will autoGen server-id 自动生成
1. canal.instance.mysql.slaveId=0
# enable gtid use true/false 是否启用gtid
canal.instance.gtidon=false
# position info 位置信息
canal.instance.master.address=192.168.100.202:3307 #deploy服务器的IP和端口
canal.instance.master.journal.name=
canal.instance.master.position=
canal.instance.master.timestamp=
canal.instance.master.gtid=
# rds oss binlog rds oss信息
canal.instance.rds.accesskey=
canal.instance.rds.secretkey=
canal.instance.rds.instanceId=
# table meta tsdb info 时序数据库相关信息
canal.instance.tsdb.enable=true # 启用时序数据库，默认使用h2数据库保存
#canal.instance.tsdb.url=jdbc:mysql://127.0.0.1:3306/canal_tsdb
#canal.instance.tsdb.dbUsername=canal
#canal.instance.tsdb.dbPassword=canal
# canal 备机信息
#canal.instance.standby.address =
#canal.instance.standby.journal.name =
#canal.instance.standby.position =
#canal.instance.standby.timestamp =
#canal.instance.standby.gtid=
# username/password 账号密码
canal.instance.dbUsername=canal # 用户名
canal.instance.dbPassword=canal # 密码
canal.instance.connectionCharset = UTF-8
1. enable druid Decrypt database password
canal.instance.enableDruid=false
#canal.instance.pwdPublicKey=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALK4BUxdDltRRE5/zXpVEVPUgunvscYFtEip3pmLlhrWpacX7y7GCMo2/JM6LeHmiiNdH1FWgGCpUfircSwlWKUCAwEAAQ==
# table regex
canal.instance.filter.regex=mytest.t1 #需要同步的表
1. table black regex
canal.instance.filter.black.regex=mysql\\.slave_.*
1. table field filter(format: schema1.tableName1:field1/field2,schema2.tableName2:field1/field2)
#canal.instance.filter.field=test1.t_product:id/subject/keywords,test2.t_company:id/name/contact/ch
1. table field black filter(format: schema1.tableName1:field1/field2,schema2.tableName2:field1/field2)
#canal.instance.filter.black.field=test1.t_product:subject/product_image,test2.t_company:id/name/contact/ch
# mq config
canal.mq.topic=example
1. dynamic topic route by schema or table regex
#canal.mq.dynamicTopic=mytest1.user,mytest2\\…*,.*\\…*
canal.mq.partition=0
1. hash partition config
#canal.mq.partitionsNum=3
#canal.mq.partitionHash=test.table:id^name,.*\\…*
#canal.mq.dynamicTopicPartitionNum=test.*:4,mycanal:6
#################################################
3.4 运行
./bin/startup.sh
运行之后会监听11110、11111、11112端口，检查
[root@master-db1 rdb]# ss -tanpl |grep java
LISTEN 0 100 *:8089 *😗 users:((“java”,pid=6453,fd=106))
LISTEN 0 50 *:11110 *😗 users:((“java”,pid=9528,fd=93))
LISTEN 0 50 *:11111 *😗 users:((“java”,pid=9528,fd=91))
LISTEN 0 3 *:11112 *😗 users:((“java”,pid=9528,fd=74))
LISTEN 0 100 *:8081 *😗 users:((“java”,pid=13288,fd=107))
