在SpringBoot中通過Canal實現MySQL與Redis的數據同步
作者：Springboot實戰案例錦集
mysql-bin.000003 是首次配置的，如果master節點重啟了，這個文件會遞增變為mysql-bin.000004，這時我們的從節點會自動連上這mysql-bin.000004。
環境：Springboot2.7.12 + MySQL8
1 環境準備
master: 192.168.2.129
slave: 192.168.2.130
使用Docker安裝MySQL，這里Docker安裝省略，網上一堆教程。
Docker安裝完成后，安裝MySQL。
安裝MySQL
步驟1：
[root@node150 kafka]# mkdir -p /root/software/mysql/conf /root/software/mysql/data
[root@node150 kafka]# chmod -R 777 /root/software/mysql/步驟2：
進入/root/software/mysql/conf 創建my.cnf 內容如下：
[client]
#socket = /usr/mysql/mysqld.sock
default-character-set = utf8mb4
[mysqld]
#pid-file = /var/run/mysqld/mysqld.pid
#socket = /var/run/mysqld/mysqld.sock
#datadir = /var/lib/mysql
#socket = /usr/mysql/mysqld.sock
#pid-file = /usr/mysql/mysqld.pid
datadir = /var/lib/mysql
character_set_server = utf8mb4
collation_server = utf8mb4_bin
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Custom config should go here
!includedir /etc/mysql/conf.d/步驟3：
docker run --name mysql8 --restart=always --privileged=true -v /root/software/mysql/conf/my.cnf:/etc/mysql/my.cnf -v /root/software/mysql/data:/var/lib/mysql -v /etc/localtime:/etc/localtime:ro -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123123 -d mysql --lower_case_table_names=1完成分別在129，130上完成上面操作。
2 主從配置
129.168.2.129作為master節點，修改my.cnf配置，追加下面配置：
binlog_format=MIXED
log-bin=mysql-bin
server-id=1129.168.2.130作為slave節點，修改my.cnf配置，追加下面配置：
log-bin=mysql-bin
server-id=2修改完配置后分別重啟mysql
3 配置從節點
3.1 查看master狀態
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 156 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)這里的File，Position列再配置從節點時需要用到。
3.2 配置從節點
CHANGE MASTER TO
MASTER_HOST='192.168.2.129',
MASTER_PORT=3306,
MASTER_USER='root',
MASTER_PASSWORD='123123',
master_log_file='mysql-bin.000003',
master_log_pos=156,
master_connect_retry=60,
GET_MASTER_PUBLIC_KEY=1;執行上面的命令如果報錯如下：
This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.這時需要執行stop slave
注意：上面的mysql-bin.000003 是首次配置的，如果master節點重啟了，這個文件會遞增變為mysql-bin.000004，這時我們的從節點會自動連上這mysql-bin.000004
查看從節點狀態：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.2.129
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 156
Relay_Log_File: 85acaa370429-relay-bin.000014
Relay_Log_Pos: 371
# 該文件會自動與master節點同步
Relay_Master_Log_File: mysql-bin.000005
# 主要看這里的兩個Running是否為Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 156
Relay_Log_Space: 755
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 71bb9106-a9a9-11ed-9031-0242ac110002
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 1
Network_Namespace:
1 row in set, 1 warning (0.00 sec)3.3 測試
在主節點上執行創建數據庫，建表等操作都會自動同步到slave節點上。
4 創建Canal
docker run --name canal -p 11111:11111 -v /opt/canal/conf:/home/admin/canal-server/conf -v /opt/canal/logs:/home/admin/canal-server/logs -d canal/canal-server5 Springboot整合Canal
添加依賴
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>top.javatool</groupId>
<artifactId>canal-spring-boot-starter</artifactId>
<version>1.2.1-RELEASE</version>
</dependency>
</dependencies>配置文件
spring:
redis:
host: localhost
port: 6379
password: 123123
database: 8
lettuce:
pool:
maxActive: 8
maxIdle: 100
minIdle: 10
maxWait: -1
---
canal:
server: 192.168.2.130:11111
destination: redis #可設置*個，逗號隔開，對應需要創建*文件夾，且文件夾下有instance.properties文件數據模型
public class Users {
private Integer id ;
private String name ;
private Integer age ;
@Override
public String toString() {
return "Users [id=" + id + ", name=" + name + ", age=" + age + "]";
}
}具體服務組件
@Component
@CanalTable(value = "users")
public class UserServiceImpl implements EntryHandler<Users> {
private static final Logger logger = LoggerFactory.getLogger(UserServiceImpl.class);
private final StringRedisTemplate stringRedisTemplate ;
public UserServiceImpl(StringRedisTemplate stringRedisTemplate) {
this.stringRedisTemplate = stringRedisTemplate ;
}
@Override
public void insert(Users user) {
logger.info("新增數據為{}", user);
try {
this.stringRedisTemplate.opsForValue().set("users:" + user.getId(), new ObjectMapper().writeValueAsString(user)) ;
} catch (JsonProcessingException e) {
e.printStackTrace();
}
}
@Override
public void update(Users before, Users after) {
logger.info("原來數據為{}", before);
logger.info("更新數據為{}", after);
try {
this.stringRedisTemplate.opsForValue().set("users:" + after.getId(), new ObjectMapper().writeValueAsString(after)) ;
} catch (JsonProcessingException e) {
e.printStackTrace();
}
}
@Override
public void delete(Users user) {
logger.info("刪除的數據為{}", user);
this.stringRedisTemplate.delete("users:" + user.getId()) ;
}
}以上通過增，刪，改數據就能同步到Redis中了。
責任編輯：武曉燕
來源：
Spring全家桶實戰案例源碼
