Synchronize MySQL data to Redis using Alibaba Canal Incremental Subscription & Consumption Component
background
Alibaba Incremental Subscription & Consumer Components https://github.com/alibaba/canal
In the early days, Alibaba B2B company had the business requirement of cross-room synchronization because of the dual-room deployment in Hangzhou and the United States. However, the early database synchronization business was mainly based on trigger to obtain incremental changes, but since 2010, Alibaba Company began to gradually try to synchronize incremental changes based on database log parsing, which resulted in incremental subscription-consumption business, and thus opened a new era.
Project introduction
Name: Canal [k'nl]
Channel/Pipeline/Channel
Language: Pure java development
Location: Provides incremental data subscription and consumption based on database incremental log parsing. At present, mysql is mainly supported.
Key words: mysql binlog parser / real-time / queue and topic
Log-based incremental subscription-consumer-supported business:
1. Database Mirror
2. Real-time database backup
3. Multilevel Index (Seller and Buyer Index)
4.search build
5. Business cache refresh
6. Important business news such as price changes
Working principle
Implementation of mysql master and standby replication
From the upper level, replication can be divided into three steps:
master will record changes to binary logs (these records are called binary log events, which can be viewed through show binary log events).
slave copies master's binary log events to its relay log.
Slve redo the events in the relay log, changing the data that reflects itself.
Working principle of canal
The principle is relatively simple:
1. The Canal simulates the interaction protocol of mysql slave, disguises itself as mysql slave, and sends dump protocol to mysql master.
2.mysql master receives a dump request and starts pushing binary logs to slave (that is, canal)
3. Canal parses binary objects (originally byte streams)
The principle of canal is based on mysql binlog technology, so it is necessary to turn on the binlog writing function of mysql. It is suggested to configure the binlog mode as row.
For Aliyun RDS account, binlog dump is already available by default. No permissions or binlog settings are required. You can skip this step directly.
Modify etc/my.cnf
$ cat /etc/my.cnf [mysqld] log-bin=mysql-bin #Add this line to ok binlog-format=ROW #Select row mode server_id=1 #Configuring MySQL replacement needs to be defined and cannot be duplicated with canal's slaveId
I. Configuration steps
MySQL installation
Installation of MySQL 5.7.19 Binary Version in CentOs 7.3
1. Download canal
Direct download access: https://github.com/alibaba/canal/releases For example, 1.0.24 will be used as an example:
$ ca /opt $ wget https://github.com/alibaba/canal/releases/download/canal-1.0.24/canal.deployer-1.0.24.tar.gz
or compiles by itself
$ git clone git@github.com:alibaba/canal.git $ cd canal; $ mvn clean install -Dmaven.test.skip -Denv=release
When compiled, target/canal.deployer-$version.tar.gz is generated in the root directory
2. decompress
$ mkdir /opt/canal $ tar zxvf canal.deployer-$version.tar.gz -C /opt/canal
3. Configuration modification
Application parameters:
$ vi conf/example/instance.properties
################################################# ## mysql serverId canal.instance.mysql.slaveId = 1234 canal.instance.master.address Need to change to your own database information canal.instance.master.address = 127.0.0.1:3306 canal.instance.master.journal.name = canal.instance.master.position = canal.instance.master.timestamp = #canal.instance.standby.address = #canal.instance.standby.journal.name = #canal.instance.standby.position = #canal.instance.standby.timestamp = username/password，Need to change to your own database information canal.instance.dbUsername = canal canal.instance.dbPassword = canal canal.instance.defaultDatabaseName = canal.instance.connectionCharset = UTF-8
4. boot
$ sh bin/startup.sh
5. View logs
$ less logs/canal/canal.log
$ less logs/example/example.log
6. stop
$ sh bin/stop.sh
II. Installation of Redis
In this test project, Redis stand-alone service is selected. Clusters also support
Redis standalone
CentOs 7.3 Build Redis-4.0.1 Stand-alone Service
Redis cluster
CentOs 7.3 Builds Redis-4.0.1 Cluster Cluster Service
Synchronized Redis
ClientExample provided by Canal
1. Create library tables
CREATE DATABASE `test`; use `test`; DROP TABLE IF EXISTS `test`; CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(1000) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of test -- ---------------------------- INSERT INTO `test` VALUES ('1', 'synchronization MySQL Data to Redis');
2. Importing Source Code
Cloning, Synchronizing MySQL Data to Redis Project https://github.com/souyunku/YmqExample
$ git clone https://github.com/souyunku/YmqExample.git
3. Running test classes
Open the ymq-alibaba-otter-canal project and run the SimpleCanalTest test class
Establish a canal client, retrieve data from canal, and update the data to Redis
import com.alibaba.otter.canal.client.CanalConnector; import com.alibaba.otter.canal.client.CanalConnectors; import io.ymq.example.util.AbstractCanalClientTest; import org.apache.commons.lang.exception.ExceptionUtils; import java.net.InetSocketAddress; /** * Examples of test in stand-alone mode * * @author jianghang 2013-4-15 04:19:20 p.m. * @version 1.0.4 */ public class SimpleCanalClientTest extends AbstractCanalClientTest { public SimpleCanalClientTest(String destination) { super(destination); } public static void main(String args[]) { // According to ip, direct link creation, no HA function String destination = "example"; // String ip = AddressUtils.getHostIp(); CanalConnector connector = CanalConnectors.newSingleConnector(new InetSocketAddress("192.168.252.125", 11111), destination, "", ""); final SimpleCanalClientTest clientTest = new SimpleCanalClientTest(destination); clientTest.setConnector(connector); clientTest.start(); Runtime.getRuntime().addShutdownHook(new Thread() { public void run() { try { logger.info("## stop the canal client"); clientTest.stop(); } catch (Throwable e) { logger.warn("##something goes wrong when stopping canal:\n{}", ExceptionUtils.getFullStackTrace(e)); } finally { logger.info("## canal client is down."); } } }); }
4. Update data
UPDATE `penglei`.`test` SET `id`='1', `name`='Use Alibaba Canal Incremental subscription&Consumption components,synchronization MySQL Data to Redis' WHERE (`id`='1');
5. View responses
**************************************************** * Batch Id: [27] ,count : [3] , memsize : [325] , Time : 2017-08-29 13:57:33 * Start : [mysql-bin.000005:13948:1503986259000(2017-08-29 13:57:39)] * End : [mysql-bin.000005:14295:1503986259000(2017-08-29 13:57:39)] **************************************************** ================> binlog[mysql-bin.000005:13948] , executeTime : 1503986259000 , delay : -5057ms BEGIN ----> Thread id: 27 ----------------> binlog[mysql-bin.000005:14076] , name[penglei,test] , eventType : UPDATE , executeTime : 1503986259000 , delay : -5057ms id : 1 type=int(11) name : Use Alibaba Canal Incremental subscription&consumption binlog synchronization MySQL Data to Redis colony type=varchar(1000) update=true -------> before id : 1 type=int(11) name : Use Alibaba Canal Incremental subscription&consumption binlog synchronization MySQL Data to Redis type=varchar(1000) -------> after ---------------- END ----> transaction id: 307 ================> binlog[mysql-bin.000005:14295] , executeTime : 1503986259000 , delay : -5056ms
6. Check Redis
Check if Redis is synchronized
$ /opt/redis-4.0.1/src/redis-cli -h 192.168.252.101 -c -p 6379 192.168.252.104:6379> get ymq-group:1 { "name": "Use Alibaba Canal Incremental subscription&Consumption components,synchronization MySQL Data to Redis", "id": "1" }