Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to achieve real-time data synchronization of MySQL database in Canal. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

1.1 introduction to canal

Canal is a high performance data synchronization system based on MySQL binary logs. Canal is widely used in Alibaba Group (including https://www.taobao.com) to provide a reliable low-latency incremental data pipeline, github address: https://github.com/alibaba/canal

Canal Server can parse MySQL binlog and subscribe to data changes, while Canal Client can broadcast changes anywhere, such as databases and Apache Kafka.

It has the following functions:

All platforms are supported.

Support fine-grained system monitoring supported by Prometheus.

Support parsing and subscribing to MySQL binlog in different ways, such as through GTID.

Support high-performance, real-time data synchronization. (see Performance for details)

Both Canal Server and Canal Client support HA / Scalability, supported by Apache ZooKeeper

Docker support.

Disadvantages:

Full updates are not supported, only incremental updates are supported.

Full wiki address: https://github.com/alibaba/canal/wiki

1.2 principle of operation

The principle is simple:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Canal simulates the interaction protocol of MySQL's slave, disguises it as mysql slave, and sends the forwarding protocol to the MySQL Master server.

MySQL Master receives the dump request and starts pushing the binary log to slave (that is, canal).

Canal parses the binary log object to its own data type (raw byte stream)

As shown in the figure:

Preparatory work

2.1 download and extract canal-server

Download the canal-server release version via github (v1.1.4 is used in this installation document)

Root@locahost:/# wget https://github.com/alibaba/canal/releases/download/canal-1.1.4/canal.deployer-1.1.4.tar.gz

Decompression

Tar-zxvf canal.deployer-1.1.4.tar.gz

2.2 download and extract canal-adapter

Download the canal-adapter release version via github (v1.1.4 is used in this installation document)

Root@locahost:/# wget https://github.com/alibaba/canal/releases/download/canal-1.1.4/canal.adapter-1.1.4.tar.gz

Decompression

Tar-zxvf canal.adapter-1.1.4.tar.gz

Configure canal-server

3.1 canal-server configuration

After decompressing, go to the conf folder and modify the canal.properties as needed (if you don't use kafka or MQ default tcp)

Canal.destinations = prod # specifies that multiple names of instance are separated by commas

Create a prod folder in the conf directory after saving and transfer the nstance.properties copy from the example folder to and_prod

Mkdir ant_prod # create folder cp example/nstance.properties prod/ # copy file

Modify the nstance.properties configuration as follows:

Canal.instance.master.address=127.0.0.1:3306 # Source Mysql address canalcanal.instance.dbUsername=canal # Source Mysql account canalcanal.instance.dbPassword=canal # Source Mysql password canal.instance.connectionCharset=UTF-8 # consistent with source database encoding format canal.instance.defaultDatabaseName=test_database # default listening source database

3.2 canal-server start

Start by entering the canal-server bin directory

Cd canal-server/bin # enter the directory. / startup.sh & # start in the background

Check the log to see if the startup is successful

Cd canal-server/logs/ant_prod # enter the log directory

Started successfully:

2020-06-09 17 Invalid JavaBean property 1314 04.956 [main] WARN o.s.beans.GenericTypeAwarePropertyDescriptor-Invalid JavaBean property 'connectionCharset' being accessed! Ambiguous write methods found next to actually used [public void com.alibaba.otter.canal.parse.inbound.mysql.AbstractMysqlEventParser.setConnectionCharset (java.nio.charset.Charset)]: [public void com.alibaba.otter.canal.parse.inbound.mysql.AbstractMysqlEventParser.setConnectionCharset (java.lang.String)] 2020-06-09 17 java.nio.charset.Charset 1315 04.990 [main] INFO c.a.o.c.i.spring.support.PropertyPlaceholderConfigurer-Loading properties file from class path resource [canal.properties] 2020-06-09 17:13 04.990 [main] INFO c.a.o.c.i.spring.support.PropertyPlaceholderConfigurer-Loading properties file from class path resource [ant_prod/instance.properties] 2020-06-09 17 main 1315 main [main] INFO c.a.otter.canal.instance.spring.CanalInstanceWithSpring-start CannalInstance for 1-ant_prod 2020-06-09 1715 1715 05.311 [main] WARN c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert-- > init table filter: ^. *\. * $2020-06-09 17 init table black filter 1315 [main] WARN c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert-- > init table black filter: 2020-06-09 17 init table black filter 1315 [main] INFO c.a.otter.canal.instance.core.AbstractCanalInstance-start successful.... 2020-06-09 17 address 13 rm-wz99s5v03gso12521.mysql.rds.aliyuncs.com/192.xxxxxx:3306 05.422 [destination = ant_prod, address = rm-wz99s5v03gso12521.mysql.rds.aliyuncs.com/192.xxxxxx:3306, EventParser] WARN c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy-> begin to find start position, it will be long time for reset or first position 2020-06-09 17 14 address 13 Switzerland 05.423 [destination = ant_prod, address = rm-wz99s5v03gso12521.mysql.rds.aliyuncs.com/192.xxxxxx:3306 EventParser] WARN c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy-prepare to find start position just show master status 2020-06-09 17 prepare to find start position just show master status 13buret 06.483 [destination = ant_prod, address = rm-wz99s5v03gso12521.mysql.rds.aliyuncs.com/192.xxxxxx:3306, EventParser] WARN c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy-> find start position successfully, EntryPosition [included=false,journalName=mysql-bin.000234,position=6676924,serverId=184376678,gtid=,timestamp=1591693973000] cost: 1051ms, the next step is binlog dump

Configure canal-adapter

4.1 canal-adapter configuration

Since Mysql is 8.0, you need to download mysql-connector-java-8.0.20.jar and put it into lib.

Cp mysql-connector-java-8.0.20.jar / canal-adapter/lib/

After decompressing, go to the conf folder and modify application.yml.

Server: port: 8089 spring: jackson: date-format: yyyy-MM-dd HH:mm:ss time-zone: GMT+8 default-property-inclusion: non_null canal.conf: mode: tcp # kafka rocketMQ canalServerHost: 127.0.0.1 kafka rocketMQ canalServerHost 11111 batchSize: 500 syncBatchSize: 1000 retries: 0 timeout: accessKey: secretKey: # Source Mysql address, account password and other srcDataSources: defaultDS: Url: jdbc:mysql://localhost:3306/test_database?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai username: canal password: canal # requires real-time database synchronization If multiple instances are distinguished, you can canalAdapters:-instance: prod # canal instance Specify the name of instance in canal-server groups:-groupId: G1 outerAdapters:-name: rdb key: mysql1 # unique label properties: jdbc.driverClassName: com.mysql.jdbc.Driver jdbc.url: jdbc:mysql://localhost:3306/test_database_01?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai jdbc.username: canal jdbc.password: canal

Edit the mapping file for the tables under the rdb directory, database / table (multiple tables create multiple mapping files, file names correspond to table names), and so on

DataSourceKey: defaultDS destination: prod outerAdapterKey: mysql1 concurrent: true dbMapping: database: test_database_01 table: test targetTable: test_database_01.test targetPk: id: id mapAll: true

4.1 canal-adapter start

Start by entering the canal-adapter/bin directory

Cd canal-adapter/bin # enter the directory. / startup.sh & # start in the background

Check the log to see if the startup is successful

Cd canal-adapter/adapter/logs/ # enter the log directory tail-f adapter.log # to see if the log starts successfully

Test database synchronization

Update / delete / batch insert / batch update / batch delete the above is how to achieve real-time data synchronization of MySQL database in Canal shared by Xiaobian. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.