What is canal?

Canal [kə næl] is used for incremental log parsing based on MySQL database, providing incremental data subscription and consumption

Logging based incremental subscription and consumption services include

  • Database mirroring
  • Real-time Database backup
  • Index building and real-time maintenance (split heterogeneous index, inverted index, etc.)
  • Service Cache Refresh
  • Incremental data processing with business logic

Current canal supports source MySQL versions including 5.1.x, 5.5.x, 5.6.x, 5.7.x, 8.0.x

The working principle of

Based on the above, before implementing Canal, let’s do a simple master-slave copy. A master from

  • First download the mysql image and start it
docker pull mysql:latest
docker run -itd --name mysql-1 -p 23306:3306 -e MYSQL_ROOT_PASSWORD=root  mysql
docker run -itd --name mysql-2 -p 23307:3306 -e MYSQL_ROOT_PASSWORD=root  mysql

Copy the code
  • Related commands to explain again:

Name XXX: XXX indicates the container name p 111:222 where 111 indicates the host port and 222 indicates the container port. MYSQL_ROOT_PASSWORD=root Set the password of user root to root

  • Enter the container and test it. Everything is fine

  • Set mysql-1 to primary and mysql-2 to secondary
  • Install the vim editor for mysql
apt-get update
apt-get install vim
Copy the code
  • Create a mysql account in the primary database for the secondary database
CREATE USER 'slave'@'%' IDENTIFIED BY '123456'; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON . TO 'slave'@'%'; FLUSH PRIVILEGES;Copy the code

  • Modify the slave server
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
server_id=100
log-bin=mysql-slave-bin
relay_log=edu-mysql-relay-bin
Copy the code
  • Exit and restart the docker from the server
  • Enter slave server execution
Mysql > change master_host='172.17.0.4', master_user='slave', master_password='123456', master_port=3306, mysql> change master to master_host='172.17.0.4', master_user='slave', master_password='123456', master_port=3306, master_log_file='edu-mysql-bin.000001', master_log_pos= 877, master_connect_retry=30;Copy the code

Master_port: indicates the port number of the Master, which refers to the port number of the container. Master_user: indicates the user used for data synchronization. Master_password: indicates the password used for data synchronization. Master_log_pos: Specifies the Position from which the Slave starts to replicate data. Master_connect_retry: If the connection fails, retry interval, in seconds (default is 60 seconds) run show Slave status \G on the mysql terminal in Slave; This command is used to view the master/slave synchronization status.

  • The configuration is successful if the following information is displayed

  • Then write data to the master library, and synchronize data to the slave library successfully

  • Simple master-slave synchronization is done, but we have to figure out how to do that, right;

In fact, by synchronizing binary log files, an IO process from the slave server will read the binary file and synchronize it to the slave server

  • Take a quick look at the contents of the binary;

Canal disguised itself as a slave server, so that it could read logs and get data.

Deploy Canal using Docker

Refer to the link

Docker pull canal/canal - server: the latest download script wget https://raw.githubusercontent.com/alibaba/canal/master/docker/run.sh # # Create a queue whose destination name is Test, address corresponds to database IP + port, dbUsername corresponds to database user name, dbPassword corresponds to database password, Change to your own sh run.sh -e canal.auto. Scan =false \ -e canal.destinations=test \ -e Canal. The instance. The master. The address = 172.17.0.4: \ 3306 - e canal. The instance. The dbUsername = canal \ e canal instance. DbPassword = canal \ -e canal.instance.connectionCharset=UTF-8 \ -e canal.instance.tsdb.enable=true \ -e canal.instance.gtidon=false \Copy the code
  • After startup, you can enter the container and take a look at the log inside. If there is a message marked red, it indicates success. Otherwise, you can view the error message inside! It’s not hard to

  • Use PHP to view data changes (not limited here)
  • Multilingual connection github.com/alibaba/can…

PHP listens for data changes

  • canal-php

Canal is a PHP client for the incremental subscription & consumption component of Alibaba mysql database Binlog. Provides PHP developers with a more friendly way to use Canal. Canal is the incremental subscription & consumption component of the mysql database binlog.

Log-based incremental subscription & consumption supported services:

  1. Database mirroring
  2. Real-time Database backup
  3. Multilevel index (seller and buyer’s index)
  4. search build
  5. Service Cache Refresh
  6. Price changes and other important business news

More information about Canal can be found at github.com/alibaba/can…

  • Application scenarios

Canal-php is the client of Canal, and its application scenario is the application scenario of Canal. The application scenarios are outlined in the Canal Introduction section. Here are some practical examples:

1. Instead of using polling database to monitor database changes, effectively improve polling cost of database resources.

2. Update the search engine in real time according to the changes in the database, for example, when the commodity information changes in the e-mall scene, synchronize it to the commodity search engine Elasticsearch and Solr, etc

3. Update the cache in real time according to the changes of the database, for example, the changes of commodity prices and inventory in the e-shopping scene are synchronized to Redis in real time

4. Remote database backup and data synchronization

5. Trigger some business according to the database change. For example, in the e-market scene, the creation of an order is automatically cancelled if the payment is not made within xx time.

6. Send database changes in their own data format to message queues such as Kafka for consumption by message queue consumers.

  • The working principle of

Canal-php is CANAL’s PHP client. It communicates with Canal using Socket, transmission Protocol is TCP, and interaction Protocol is Google Protocol Buffer 3.0.

  • The working process

  • Use component installation, here I have a Laravel framework, installed directly inside Laravel using the relevant code posted
# canal-php composer require xingwenge/canal_php php namespace App\Console\Commands; use xingwenge\canal_php\CanalClient; use xingwenge\canal_php\CanalConnectorFactory; use xingwenge\canal_php\Fmt; use Illuminate\Console\Command; ini_set('display_errors', 'On'); error_reporting(E_ALL); class CanalDemo extends Command { /** * The name and signature of the console command. * * @var string */ protected $signature = 'CanalDemo'; /** * The console command description. ** @var string */ protected $description = 'testCanal '; /** * Create a new command instance. * * @return void */ public function __construct() { parent::__construct(); } /** * Execute the console command. * * @return mixed */ public function handle() { try { $client = CanalConnectorFactory::createClient(CanalClient::TYPE_SOCKET_CLUE); # $client = CanalConnectorFactory::createClient(CanalClient::TYPE_SWOOLE); $client - > connect (" 172.17.0.5 ", 11111); $client->checkValid(); $client->subscribe("1001", "test", ".*\\.. * "); While (true) {$message = $client->get(100); if ($entries = $message->getEntries()) { foreach ($entries as $entry) { Fmt::println($entry); } } sleep(1); } $client->disConnect(); } catch (\Exception $e) { echo $e->getMessage(), PHP_EOL; }}}Copy the code
  • Change the data

  • Surveillance results