Overall overview and structure

An overview of the

Use Ali’s Canal database synchronization tool to synchronize Mysql data to RabbitMQ or Kafka, and then take messages from MQ and synchronize them to ElasticSearch.

Overall structure and process

Canal introduce

The main purpose of Canal is to provide subscription and consumption of incremental data based on the incremental log parsing of MySQL database. Simply put, it can synchronize the incremental data of MySQL in real time.

Working principle of Canal

Canal will simulate the interaction protocol between the master and slave database of MySQL, thus pretending to be the slave database of MySQL, and then send the dump protocol to the master database of MySQL. Upon receiving the dump request, the master database will push binlog to Canal. Canal parses the binlog to synchronize the data to other stores.

The overall process of synchronizing MySql data using the Canal and MQ components

  • Mysql –> Canal –> MQ (RabbitMq/Kafka) –> ElasticSearch

What you really need to write is this:

  • Canal synchronizes data to MQ (SyncCanal2Mq)
  • Consume MQ data and store it to ElasticSearch (SyncMq2Elastic)

Environment description and installation

Installation environment: Linux-Centos7.

Install Docker tutorial search for install yourself.

Since there are compatibility issues with different versions of MySQL, Elasticsearch, and Canal, let’s make a convention on which version to use.

application port version
MySQL 3306 5.7
Elasticsearch 9200 7.6.2
Kibanba 5601 7.6.2
canal-server 11111 1.1.15
canal-adapter 8081 1.1.15
canal-admin 8089 1.1.15

Install MySql using Docker

Docker install and deploy mysql

Pull the mirror

Docker pull mysql:5.7 Docker imagesCopy the code

Creating a Configuration file

[mysqld]
server_id=101
binlog-ignore-db=mysql
log-bin=mall-mysql-bin
binlog_cache_size=1M
binlog_format=row
expire_logs_days=7
slave_skip_errors=1062

character-set-server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysql]
default-character-set=utf8

[client]
default-character-set=utf8
Copy the code

Create and deploy the mysql container

# Create data and configuration file directory for host mysql container
mkdir /root/docker/mysql/{conf,data} -p
cd /root/docker/mysql/

# create a container named mysql5.7
docker run -p 3306:3306 -v $PWD/data:/var/lib/mysql -v $PWD/ conf/my. CNF: / etc/mysql/my CNF - e MYSQL_ROOT_PASSWORD = your_password - name mysql5.7 - d mysql: 5.7# Check which containers are running
docker ps
Copy the code

Run the following command to check whether binlog is enabled

show variables like '%log_bin%'

Let’s look at MySQL’s binlog mode

show variables like 'binlog_format%';

Next, you need to create an account with slave library permissions to subscribe to binLog

The account created here is canal:canal;

CREATE USER canal IDENTIFIED BY 'canal';  
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@The '%';
FLUSH PRIVILEGES;
Copy the code

Create the test databasestatementThen create a tabletrack

As follows:

CREATE TABLE `track`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `sub_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL.-- `price` decimal(10, 2) NULL DEFAULT NULL,
  `price` DOUBLE NOT NULL,
  `pic` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL.PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
Copy the code

Insert some data for later testing

INSERT INTO track ( title, sub_title, price, pic ) VALUES ( 'iPhone4'.'The greatest smartphone ever made is 6GB+64GB'.4444.88, "http://mycanal/phone/pic.jpeg" );
INSERT INTO track ( title, sub_title, price, pic ) VALUES ( 'iPhone5'.'The greatest smartphone ever made is 6GB+64GB'.5555.00, "http://mycanal/phone/pic.jpeg" );
INSERT INTO track ( title, sub_title, price, pic ) VALUES ( 'iPhone6'.'The greatest smartphone ever made is 6GB+64GB'.6666.00, "http://mycanal/phone/pic.jpeg" );
Copy the code

Install ElasticSearch using Docker

Docker install ES, remember to refer to

Pull the mirror

Docker pull Elasticsearch :7.6.2 Docker pull Mobz/Elasticsearch -head:5 Docker imagesCopy the code

Create elasticSearch and es-Head containers

docker run --name myes -d -e  ES_JAVA_OPTS="-Xms512m -Xmx512m" -e "discovery.type=single-node"-p 9200:9200 -p 9300:9300 -v /etc/localtime:/etc/localtime:ro -v /home/es/data:/usr/share/elasticsearch/data Docker run-d --name es-head -p 9100:9100 mobz/ Elasticsearch-head :5Copy the code

Access a browser to view the ES overview

http://192.168.7.130:9100/

Docker Install Kibana (optional)

Reference: Install Kibana using Docker

Pull the mirror and view the mirror

Docker Pull Kibana :7.6.2 Docker imagesCopy the code

Create the Kibana profile and its mount directory

mkdir -p /root/mydata/kibana/conf
cd /root/mydata/kibana/conf
Copy the code

vim kibana.yml

#
## ** THIS IS AN AUTO-GENERATED FILE **
#

# Default Kibana configuration for docker target
server.name: kibana
server.host: "0"
elasticsearch.hosts: [ "http://192.168.7.130:9200" ]
xpack.monitoring.ui.container.elasticsearch.enabled: true
Copy the code

Note: Change the address of ElasticSearch as required.

Create the Kibana container

docker run -d --name=kibana --restart=always -p 5601:5601 -v $PWD/ conf/kibana. Yml: / usr/share/kibana/config/kibana yml kibana: 7.6.2Copy the code

See the log

Docker logs -f Kibana, wait 30 seconds, if the following information is displayed, the startup is successful.

{"type":"log"."@timestamp":"2020-08-27T03:00:28Z"."tags": ["listening"."info"]."pid": 6,"message":"Server running at http://0:5601"}
{"type":"log"."@timestamp":"2020-08-27T03:00:28Z"."tags": ["info"."http"."server"."Kibana"]."pid": 6,"message":"http server running at http://0:5601"}
Copy the code

Visit the Kibana page

http://192.168.31.190:5601/
Copy the code

Here’s how it works. Here, click Explore on my own

Click on dev Tools on the left to manipulate ES through the restful API:

Canal – server installation

Reference address: Install canal-server

Component Download Address

  • Each of Canal’s components needs to be downloadedcanal-server,canal-adapter,canal-admin, download address:Github.com/alibaba/can…

In fact, only the Canal-server component is used this time.

This section describes the components

  • Canal-server (Canal-deploy) : You can directly listen to the MySQL binlog and pretend to be a MySQL slave, only receiving data, but not processing it.

  • Canal-adapter: a canal client that obtains data from canal-server and synchronizes the data to MySQL, Elasticsearch, and HBase.

  • Canal-admin: Provides o&M functions for Canal, such as overall configuration management and node operation and maintenance (O&M). It also provides a user-friendly web user interface (WebUI) to facilitate quick and safe operations for more users.

Canal – server installation

  • Upload the downloaded package canal.deployer-1.1.5-snapshot.tar. gz to the Linux server and decompress it to the specified directory /mydata/canal-server. Run the following command to decompress it:

    The tar - ZXVF canal. Deployer - 1.1.5 - the SNAPSHOT. Tar. GzCopy the code

Modifying a Configuration Fileconf/example/instance.properties

  • Perform the following operations to modify database configurations

    # MySQL address where data needs to be synchronizedCanal. The instance. The master. The address = 192.168.7.130:3306 canal. The instance. The master. The journal. The name = canal. The instance. The master. The position = canal.instance.master.timestamp= canal.instance.master.gtid=# Database account and password used to synchronize data and code
canal.instance.dbUsername=canal
canal.instance.dbPassword=canal
canal.instance.connectionCharset = UTF-8

Table regular expressions that need to subscribe to binlogcanal.instance.filter.regex=.*\\.. *Copy the code

The Canal-server service is started

  • Use the startup.sh script to start the Canal-server service

    # Start service
sh bin/startup.sh
# Stop service
sh bin/stop.sh
# Restart service
sh bin/restart.sh
Copy the code

  • You can run the following command to view service logs

    tailf logs/canal/canal.log
Copy the code

  • After the instance is successfully started, run the following command to view the instance logs

    tailf logs/example/example.log
Copy the code

    Log contents

    The 2021-07-25 01:16:21. 978. [the main] INFO C.A.O tter. Canal. The instance. The spring. CanalInstanceWithSpring - start CannalInstancefor1-2021-07-25 example 01:16:22. 044. [the main] WARN C.A.O.C anal. Parse. The inbound. Mysql. Dbsync. LogEventConvert - > init table filter : ^.*\.. * $2021-07-25 01:16:23. 537. [the main] INFO C.A.O tter. Canal. The instance. The core. AbstractCanalInstance - start successful... 2021-07-25 01:16:35.092 [destination = example, address = /192.168.7.130:3306, EventParser] WARN c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - ---> find start position successfully, EntryPosition[included=false,journalName=mall-mysql-bin.000002,position=91467,serverId=101,gtid=,timestamp=1627139329000] cost : 11666ms , the next step is binlog dump
Copy the code

Docker to install the rabbitmq

Refer to docker to install RabbitMQ

Pull the image, create the mount directory, and create the RabbitMQ container

# Pull and view images
docker pull rabbitmq
docker images

# Create mount directory
mkdir -p /root/docker/rabbitmq/data
cd /root/docker/rabbitmq/

Create a RabbitMQ container
docker run -d --name rabbitmq -p 5672:5672 -p 15672:15672 -v $PWD/data:/var/lib/rabbitmq --hostname myRabbit -e RABBITMQ_DEFAULT_USER=admin -e RABBITMQ_DEFAULT_PASS=admin rabbitmq
Copy the code

Description:

  • -d Running the container in the background;
  • –name specifies the container name;
  • -p Specifies the port on which the service is running (5672: application access port; 15672: Console Web port number);
  • -v Maps directories or files (host and container mappings).
  • –hostname specifies the hostname (an important note about RabbitMQ is that it stores data according to a so-called “node name”, which defaults to the hostname);
  • -e specifies an environment variable. RABBITMQ_DEFAULT_VHOST: default VM name; RABBITMQ_DEFAULT_USER: default user name. RABBITMQ_DEFAULT_PASS: password of the default user name.

Start rabbitmq_management (web UI)

Where rabbitmq is the container name above
docker exec -it rabbitmq rabbitmq-plugins enable rabbitmq_management
Copy the code
  • Go to http://ip:15672 on the web management side

The login account password is the password used to create the container (default: admin/admin).

Implement data synchronization

  • Mysql -> canal; Mysql -> canal;
  • To synchronize data from Canal to MQ (SyncCanal2Mq), it is necessary to implement the code logic of SyncCanal2Mq, and then continue to supplement and optimize;
  • Consuming MQ data to store to ElasticSearch (SyncMq2Elastic) requires the implementation of SyncMq2Elastic code logic, which will be further supplemented and optimized.

The code is available on GitHub: github.com/Scoefield/s…

Gitee address: gitee.com/scoefield/s…