Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to achieve mysql data synchronization in canal. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

What is canal?

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

Businesses based on log incremental subscriptions and consumption include

Database mirroring database real-time backup index construction and real-time maintenance (split heterogeneous index, inverted index, etc.) Business cache refresh incremental data processing with business logic

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

working principle

Based on the above explanation, let's simply do a master-slave replication before implementing canal. One master and one slave first download the mysql image, and launch docker pull mysql:latestdocker run-itd-- name mysql-1-p 23306 MYSQL_ROOT_PASSWORD=root mysqldocker run-itd-- name mysql-2-p 23307 itd 3306-e MYSQL_ROOT_PASSWORD=root mysql copy code related command to explain again:

Name xxx: xxx is the container name p 111MYSQL_ROOT_PASSWORD=root 222, where 111is the host port and 222is the container port. Set the root account password to root

Go into the container and test it. Everything is fine.

Set mysql-1 as master, mysql-2 to modify mysql configuration for slave library, install vim editor apt-get updateapt-get install vim copy code to create a mysql account in master library to slave library using CREATE USER 'slave'@'%' IDENTIFIED BY' 123456 slave REPLICATION SLAVE, REPLICATION CLIENT ON. TO 'slave'@'%';FLUSH PRIVILEGES; copy code

Modify the copy code from the server [mysqld] pid-file = / var/run/mysqld/mysqld.pidsocket = / var/run/mysqld/mysqld.sockdatadir = / var/lib/mysqlsecure-file-priv= NULLserver_id=100log-bin=mysql-slave-binrelay_log=edu-mysql-relay-bin exit restart from the server docker enter execute mysql from the server > 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

The relevant command interprets the port number of master_port:Master, referring to the port number of the container master_user: the user used for data synchronization master_password: the password of the user used for synchronization master_log_file: specify the log file from which Slave starts copying data, that is, the value of the File field mentioned above master_log_pos: from which Position to start reading, that is, the value of the Position field mentioned above master_connect_retry: if the connection fails The interval between retries (in seconds). The default is 60 seconds to execute show slave status in mysql terminals in Slave. Used to view the master-slave synchronization status.

The following message indicates that the configuration is successful.

Next, the master database writes the data, and the slave database is synchronized successfully.

The simple master-slave synchronization is complete, but we have to think about how to achieve master-slave synchronization, right?

In fact, by synchronizing the binary log files, there will be an io process from the server to read the binary files and synchronize them to the slave server.

Take a brief look at the contents of the binary file

Why should we say master-slave replication before canal? in fact, canal disguises itself as a slave server to read logs and get data; use docker to deploy canal

Reference link

Docker pull canal/canal-server:latest# download script wget https://raw.githubusercontent.com/alibaba/canal/master/docker/run.sh # builds a queue with destination name as test, database ip+ port corresponding to address, database user name corresponding to dbUsername, and database password corresponding to dbPassword Notice to modify it to your own sh run.sh-e canal.auto.scan=false\\-e canal.destinations=test\-e canal.instance.master.address=172.17.0.4:3306\-e canal.instance.dbUsername=canal\-e canal.instance.dbPassword=canal\\-e canal.instance.connectionCharset=UTF-8\-e canal.instance.tsdb.enable=true\\-e canal.instance.gtidon=false\ after the copy code starts, you can enter the container. Take a look inside the log, if there is a red message, indicating success, otherwise check the error message inside! It's not hard.

Check data changes with php (php,java,go,python and other APIs are not limited here) multilingual connection https://github.com/alibaba/canalphp listens for data changes canal-php

Canal-php is Alibaba open source project Canal is Alibaba mysql database binlog incremental subscription & consumption component php client. Provide a more friendly way for php developers to use Canal. Canal is an incremental subscription-consumption component of the mysql database binlog.

Log-based incremental subscriptions & businesses supported by consumption:

Database mirroring database real-time backup multi-level index (seller and buyer respectively sub-database index) search build business cache refresh price change and other important business messages

For more information about Canal, please visit github.com/alibaba/can …

Application scenario

As the client of Canal, the application scenario of canal-php is the application scenario of Canal. An overview of the application scenario is available in the Canal introduction section. Give some examples of practical use:

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

two。 Update search engines in real time according to changes in the database, such as changes in commodity information in e-commerce scenarios, and real-time synchronization to commodity search engines such as Elasticsearch, solr, etc.

3. Update the cache in real time according to changes in the database, such as real-time synchronization of changes in commodity prices and inventory to redis in e-commerce scenarios.

4. Database remote backup and data synchronization

5. According to the database change, a certain business is triggered. For example, in an e-commerce scenario, the creation of an order without payment after the xx is automatically cancelled. When we get the status change of this order data, we can push the message to the user.

6. The database changes are sorted out into their own data format and sent to message queues such as kafka for consumption by consumers of message queues.

working principle

Canal-php is the php client of Canal, it uses Socket to communicate with Canal, the transport protocol is TCP, and the interaction protocol uses Google Protocol Buffer 3.0.

Work flow

Using component installation, here I have a laravel framework, which is installed and used directly in laravel, and the related code is pasted out # installation component canal-php composer require xingwenge/canal_php# script listening

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.