MySQL data synchronization scheme practice

Posted by nitram on Tue, 08 Mar 2022 03:44:32 +0100

MySQL data synchronization scheme practice

 

prepare

  • MySQL 5.6
  • Canal 1.0.24
  • Kafka 1.0.0
  • Zookeeper 3.4.11
  • Spring Boot
  • JDK 8
  • IntelliJ Idea 14.1.7

background

In practice, it is common for multiple systems to use the same user and other business data. With the iterative development of requirements, the number of systems increases. The business data change notification of the system has gradually become a pain point. It is barely acceptable to notify data changes to other systems one by one when there are few systems. However, with more systems and more business data to be notified, it is undoubtedly not worth the loss to use this kind of method again, which is very difficult to maintain and has strong coupling, which will affect the whole body. At this time, the introduction of middleware is undoubtedly a wise choice. Free the system from complicated notifications and leave the notification to the middleware. This is based on the fact that business operations will eventually be implemented in the database, which is also the premise of the feasibility of this scheme.

Architecture diagram

realization

Construction of Canal Server

MySQL configuration

  • Enter the C:\Program Files\MySQL\MySQL Server 5.6 directory
  • Edit my INI file (modify as needed), open mysql bin log function, as follows
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
basedir = C:\Program Files\MySQL\MySQL Server 5.6
datadir = C:\Program Files\MySQL\MySQL Server 5.6\data
# port = .....
# server_id = .....

log-bin=mysql-bin  #Just add this line
#Select row mode
binlog-format=ROW
#The MySQL replacement configuration needs to be defined and cannot be the same as the slaveId of canal
server_id=1 
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
  • The principle of canal is to simulate itself as a mysql slave, so you must have the relevant permissions as a mysql slave here (this user is required for canal server configuration)
CREATE USER canal IDENTIFIED BY 'canal';  
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
-- GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%' ;
FLUSH PRIVILEGES;
  • Restart mysql server
  • View bin log status
show binlog events;

image

#canal.properties
##Configure zookeeper address
canal.zkServers=127.0.0.1:2181

#################################################
#instance.properties
## mysql serverId
canal.instance.mysql.slaveId = 1234

# position info, which needs to be changed to its 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, which needs to be changed to its own database information
canal.instance.dbUsername = canal
canal.instance.dbPassword = canal
canal.instance.defaultDatabaseName =
canal.instance.connectionCharset = UTF-8

# table regex
canal.instance.filter.regex = .*\\..*
# table black regex
canal.instance.filter.black.regex =  

#################################################

  • Start canal server

    • Enter D:\MySQL_Sync_Component\canal.deployer-1.0.24\bin directory
    • Run startup bat

  • View the canal server log

    • D:\MySQL_Sync_Component\canal.deployer-1.0.24\logs\canal\canal.log
    • D:\MySQL_Sync_Component\canal.deployer-1.0.24\logs\example\example.log
canal.log

2017-11-24 17:55:32.550 [main] INFO  com.alibaba.otter.canal.deployer.CanalLauncher - ## start the canal server.
2017-11-24 17:55:32.770 [main] INFO  com.alibaba.otter.canal.deployer.CanalController - ## start the canal server[192.168.191.1:11111]
2017-11-24 17:55:34.503 [main] INFO  com.alibaba.otter.canal.deployer.CanalLauncher - ## the canal server is running now ......

example.log

2017-11-24 17:55:33.202 [main] INFO  c.a.o.c.i.spring.support.PropertyPlaceholderConfigurer - Loading properties file from class path resource [canal.properties]
2017-11-24 17:55:33.209 [main] INFO  c.a.o.c.i.spring.support.PropertyPlaceholderConfigurer - Loading properties file from class path resource [example/instance.properties]
2017-11-24 17:55:33.343 [main] WARN  org.springframework.beans.TypeConverterDelegate - PropertyEditor [com.sun.beans.editors.EnumEditor] found through deprecated global PropertyEditorManager fallback - consider using a more isolated form of registration, e.g. on the BeanWrapper/BeanFactory!
2017-11-24 17:55:33.471 [main] INFO  c.a.otter.canal.instance.spring.CanalInstanceWithSpring - start CannalInstance for 1-example 
2017-11-24 17:55:33.647 [main] INFO  c.a.otter.canal.instance.core.AbstractCanalInstance - start successful....
2017-11-24 17:55:33.896 [destination = example , address = /127.0.0.1:3306 , EventParser] WARN  c.a.otter.canal.parse.inbound.mysql.MysqlEventParser - prepare to find start position just show master status

Zookeeper Server setup

  • Get and configure zookeeper( http://www.apache.org/dyn/closer.cgi/zookeeper/)
  • Copy D:\MySQL_Sync_Component\zookeeper\zookeeper-3.4.11\conf\zoo_sample.cfg and rename it zoo CFG, zookeeper reads zoo CFG this configuration file
  • Start zookeeper and enter D:\MySQL_Sync_Component\zookeeper\zookeeper-3.4.11\bin, execute
zkServer.cmd

Kafka Server setup

#server.properteis#
#Configure zookeeper connection address
zookeeper.connect=localhost:2181
  • Start Kafka service and enter D:\MySQL_Sync_Component\kafka\kafka_2.12-1.0.0\bin\windows, execute
kafka-server-start.bat D:\MySQL_Sync_Component\kafka\kafka_2.12-1.0.0\bin\conf\server.properties
  • Create topic and enter D:\MySQL_Sync_Component\kafka\kafka_2.12-1.0.0\bin\windows, execute
kafka-topics.bat --create --zookeeper localhost:2181 --replication-factor 1 --partitions 1 --topic testbinlog
  • Start a kafka producer to test whether kafka is started successfully. implement
kafka-console-producer.bat --broker-list localhost:9092 --topic testbinlog
  • Start a kafka consumer and receive messages sent by the producer. If the test passes, the consumer can receive the message sent by the producer. implement
kafka-console-consumer.bat --bootstrap-server localhost:9092 --topic testbinlog --from-beginning

Create Spring Boot application

<dependency>
    <groupId>org.springframework.kafka</groupId>
    <artifactId>spring-kafka</artifactId>
</dependency>

summary

As mentioned above, the configuration and startup method of the components required for the whole solution have been introduced. The solution in this paper is used for business data notification or cache refresh. In addition to providing a solution for business scenarios, this paper hopes that readers can understand the design ideas, advantages and disadvantages of each component. In addition to being able to skillfully configure and use various components, the coding ideas and design patterns of these components are a model for our generation to learn. For the use of various components, it is best to read the official documents first, then refer to the relevant blogs, then code practice, and finally read the source code to achieve a comprehensive level.
PS:
Example code github address:
https://github.com/hxysea/mynote/tree/master/%E9%A1%B9%E7%9B%AE%E7%BB%8F%E9%AA%8C/Mysql%E6%95%B0%E6%8D%AE%E5%90%8C%E6%AD%A5%E8%A7%A3%E5%86%B3%E6%96%B9%E6%A1%88

Reference documents

https://github.com/alibaba/canal/wiki

http://kafka.apache.org/

https://docs.spring.io/spring-boot/docs/1.5.8.RELEASE/reference/htmlsingle/

Topics: Database

Hot Topics

©2024 Programmer Think