Monitoring databases using Canal

What is canal#

canal, translated as waterway/pipe/ditch, is mainly used for parsing incremental logs based on MySQL database, providing incremental data subscription and consumption.

In general, canal is used to synchronize or monitor data in MySQL databases, such as synchronizing data to ES after data is stored in the database.
canal disguises itself as a slave of MySQL, parsing and processing binary log logs sent by the master.

Configure canal#

Before using canal, you need to configure the local MySQL service to enable binlog logs.
For Windows, find the my.ini file under the installed MySQL directory and add the following configuration under [mysqld].

#binlog file name
log-bin=mysql-bin
#choose row mode
binlog_format=ROW
#mysql instance id, cannot be the same as canal's slaveId
server_id=1

For Linux, you can directly modify the /etc/my.cnf file using the vim command.

[mysqld]
log-bin=mysql-bin # enable binlog
binlog-format=ROW # choose ROW mode
server_id=1 # configure MySQL replication, make sure it does not conflict with Canal's slaveId

After completing the configuration, restart the MySQL service.
Use the following command to check if it is enabled.

show variables like 'log_bin';

Then, download the latest version from the official repository and extract it.
Modify the canal.properties file in the conf folder (you can also use the default settings without modification).
The important configurations are as follows:

# specify the interface
canal.port = 11111
# specify the instance
canal.destinations = example
# specify the mode, default is tcp, other options include kafka, rocketMQ, rabbitMQ, pulsarMQ
canal.serverMode = tcp
# specify rabbitmq, if not configured, default tcp can be used
rabbitmq.host =
rabbitmq.virtual.host =
rabbitmq.exchange =
rabbitmq.username =
rabbitmq.password =
rabbitmq.deliveryMode =

Then, modify the instance.properties file under example (corresponding to the instance configuration above).

# host address
canal.instance.master.address=127.0.0.1:3306
# account name and password for connecting to the host
canal.instance.dbUsername=canal
canal.instance.dbPassword=canal
# specify the encoding for the connection
canal.instance.connectionCharset = UTF-8
# specify the database and table to listen to, this configuration means listening to all tables under the test database
canal.instance.filter.regex=test..*

After completing the configuration, use the startup.bat file in the bin directory to start canal.

At this point, it means that the startup is successful.

Configure spring boot#

There are several ways to use canal in spring boot:

Use the official dependency.
Use third-party dependencies.

Repository	Version	Pros and Cons
https://github.com/alibaba/canal	Official	Simple configuration, difficult to implement
https://github.com/NormanGyllenhaal/canal-client	Third-party	Easy to use, no longer maintained, has bugs
https://github.com/behappy-project/behappy-canal	Third-party	Modified version of the above, continuously updated, only supports JDK 17+
https://github.com/xizixuejie/canal-spring	Third-party	Easy to use, continuously updated

It is recommended to use the last one.

Add the dependency in Maven.

<dependency>
    <groupId>io.github.xizixuejie</groupId>
    <artifactId>canal-spring-boot-starter</artifactId>
    <version>0.0.5</version>
</dependency>

# canal server address
canal.server=127.0.0.1:11111
# canal cluster name, should be consistent with the name set during canal installation
canal.destination=example
# If not configured, the default tcp mode will be used, but if not configured, it may not be able to listen, so it is recommended to configure it
canal.server-mode=tcp

After completing the above steps, add the following code to the startup class.

@EnableCanalListener(basePackages = "com.jkkj.config.canal")

This enables the listener and specifies the configuration path for the listener.
Then, configure the listener.

@CanalListener(schemaName = "test", tableName = "testTable")
public class TestListener implements EntryListener<TestEntity> {

    private static final Logger log = LoggerFactory.getLogger(TestListener.class);

    @Override
    public void insert(TestEntity testEntity) {
        log.info("insert={}", testEntity);
    }

    @Override
    public void update(TestEntity before, TestEntity after) {
        log.info("update before={}", before);
        log.info("update after={}", after);
    }

    @Override
    public void delete(TestEntity testEntity) {
        log.info("delete={}", testEntity);
    }
}

You can use @CanalListener to specify the database name and table name.

@CanalListener(schemaName = "test", tableName = "testTable")

Alternatively, you can omit the specification and let it listen based on the Entity object. In this case, you need to specify it in the entity class.

@TableName(value = "inspection_result_home")

The table name will be automatically converted based on the entity class name.

Extension#

During usage, if the field in the entity class is in the following format, an error will occur.

This is because the parser is not specified during the conversion.
You can refer to the issue I raised or wait for the author to make changes (the author has made changes).

Docker installation#

Installing canal using Docker on Linux is relatively simple.

Download the image.

docker pull canal/canal-server:v1.1.5

Start it for the first time.

docker run -p 11111:11111 --name canal -d canal/canal-server:v1.1.5

Copy the configuration file.

# Copy the configuration file
docker cp canal:/home/admin/canal-server/conf/example/instance.properties /home/docker/canal/conf
docker cp canal:/home/admin/canal-server/conf/canal.properties /home/docker/canal/conf

The first path is the container address, and the second path /home/jk/docker/canal/conf is the host address.
4. Modify the configuration file.
The main modification is in instance.properties.

# host address
canal.instance.master.address=127.0.0.1:3306

127.0.0.1 represents the container address. If your MySQL is installed on the host machine, you need to specify the host's database address. You can use the ifconfig command to check the docker0 interface, which represents the host machine address.

Restart the image.

docker stop canal
docker rm canal
docker run -p 11111:11111 --name canal \
-v /home/jk/docker/canal/conf/instance.properties:/home/admin/canal-server/conf/example/instance.properties \
-v /home/jk/docker/canal/conf/canal.properties:/home/admin/canal-server/conf/canal.properties \
-d canal/canal-server:v1.1.5

The reason for not using the latest version of the canal image can be found in this link.