<think>我们计划使用Binlog监听技术实现双主数据库的实时双向同步。参考引用[1]中提到的OMS服务原理，我们可以通过订阅源库的Binlog来实现增量数据的同步。同时，我们还需要处理全量数据同步和冲突解决。
### 一、Binlog监听同步原理
```mermaid
graph LR
A[本地数据库] -->|Binlog| B[Binlog解析服务]
C[云数据库] -->|Binlog| D[Binlog解析服务]
B --> E[消息队列]
D --> E
E --> F[同步执行器]
F -->|写入| A
F -->|写入| C
```
### 二、具体实现步骤
#### 1. 开启MySQL Binlog
**在本地和云数据库的my.cnf中配置：**
```ini
[mysqld]
server_id=1 # 本地设置为1，云设置为2（唯一ID）
log-bin=mysql-bin # 开启Binlog
binlog_format=ROW # 必须为ROW模式
expire_logs_days=7
```
#### 2. 安装Binlog解析服务（使用Canal或Maxwell）
这里以Canal为例：
```bash
# 下载Canal
wget https://github.com/alibaba/canal/releases/download/canal-1.1.7/canal.deployer-1.1.7.tar.gz
# 配置canal.properties
canal.destinations = local,cloud
canal.instance.master.address=127.0.0.1:3306 # 本地数据库地址
```
#### 3. 创建同步服务（ThinkPHP命令行）
`application/command/BinlogSync.php`：
```php
class BinlogSync extends Command
{
public function configure()
{
$this->setName('binlog:sync');
}
public function handle()
{
// 连接Canal服务
$client = \Canal\Client::instance();
$client->connect("127.0.0.1", 11111);
$client->subscribe(".*\\..*");
while (true) {
$message = $client->get(100);
if ($entries = $message->getEntries()) {
foreach ($entries as $entry) {
$this->processEntry($entry);
}
}
usleep(100000); // 100ms
}
}
private function processEntry($entry)
{
$rowChange = \Canal\Message\EntryParser::parseEntry($entry);
$tableName = $entry->getHeader()->getTableName();
foreach ($rowChange->getRowDatas() as $rowData) {
$eventType = $rowChange->getEventType();
$data = $this->extractData($rowData, $eventType);
// 排除同步队列表自身（防止循环同步）
if ($tableName === 'data_sync_queue') continue;
// 分发到目标数据库
$this->dispatchToTarget($tableName, $eventType, $data);
}
}
private function dispatchToTarget($table, $eventType, $data)
{
$targetDb = ($data['source'] === 'local') ? 'cloud' : 'local';
switch ($eventType) {
case EventType::INSERT:
Db::connect($targetDb)->table($table)->insert($data['after']);
break;
case EventType::UPDATE:
Db::connect($targetDb)->table($table)
->where('id', $data['before']['id'])
->update($data['after']);
break;
case EventType::DELETE:
Db::connect($targetDb)->table($table)
->delete($data['before']['id']);
break;
}
}
}
```
#### 4. 冲突解决策略
在数据表中增加版本字段：
```sql
ALTER TABLE user ADD version INT DEFAULT 0;
```
同步更新时使用乐观锁：
```php
// 更新操作示例
Db::connect($targetDb)->table($table)
->where('id', $data['id'])
->where('version', $data['version'])
->update([
'field' => $newValue,
'version' => $data['version'] + 1
]);
// 如果更新失败（影响行数为0），则说明有冲突
// 记录冲突日志或启动冲突解决流程
```
#### 5. 全量数据初始化
```php
// 全量同步脚本
public function fullSync()
{
$tables = ['user', 'order']; // 需要同步的表
foreach ($tables as $table) {
// 从本地数据库读取
$localData = Db::connect('local')->table($table)->select();
// 写入云数据库
Db::connect('cloud')->table($table)->insertAll($localData);
// 反向同步（如果云数据库已有数据）
$cloudData = Db::connect('cloud')->table($table)->select();
Db::connect('local')->table($table)->insertAll($cloudData);
}
}
```
### 三、实时性保障
1. **Binlog解析延迟监控**
```php
// 记录最后处理的Binlog位置
$logFile = $entry->getHeader()->getLogfileName();
$logPos = $entry->getHeader()->getLogfileOffset();
file_put_contents('binlog_pos.log', "{$logFile}:{$logPos}");
```
2. **异常重试机制**
```php
try {
$this->dispatchToTarget(...);
} catch (\Exception $e) {
// 写入失败队列
Redis::rpush('sync_retry_queue', json_encode([
'table' => $table,
'data' => $data,
'retry_count' => 0
]));
}
```
### 四、部署架构优化
```mermaid
graph TB
subgraph 本地数据中心
A[本地MySQL] --> B[Canal服务]
end
subgraph 云数据中心
C[云MySQL] --> D[Canal服务]
end
B --> E[Kafka集群]
D --> E
E --> F[ThinkPHP同步服务]
F --> A
F --> C
```
### 五、注意事项
1. **数据循环同步问题**
- 在Binlog解析服务中标记来源（添加`__source`字段）
- 同步时忽略来自对端的数据变更
2. **性能瓶颈**
- 使用Kafka作为消息缓冲
- 批量写入数据库（每100条提交一次）
3. **数据一致性校验**
```bash
# 使用pt-table-checksum
pt-table-checksum --databases=test --tables=user \
--host=localhost --user=root --password=123456
```
> 此方案可实现毫秒级同步（通常延迟在100-500ms），适合对实时性要求高的场景[^1]。生产环境建议使用云服务商提供的DTS工具（如阿里云DTS）简化运维复杂度[^2]。
---
§§相关问题§§
1. 如何监控Binlog同步的延迟情况？
2. 在双主架构下如何避免数据循环同步？
3. 使用Binlog同步时如何保证数据顺序？</think>### 基于 Binlog 监听的双向实时同步方案
通过 Binlog 监听实现双主数据库实时双向同步的核心思路是：**利用 Binlog 解析工具捕获数据变更，通过消息队列分发事件，由同步服务执行反向数据库操作**。参考引用[1]中 OMS 服务的实现原理，以下是具体实现方案：
---
### 一、整体架构设计
```mermaid
graph LR
A[本地MySQL] -->|Binlog| B[Binlog解析服务]
C[云MySQL] -->|Binlog| D[Binlog解析服务]
B --> E[Kafka/RabbitMQ]
D --> E
E --> F[同步执行器]
F -->|写入| A
F -->|写入| C
```
---
### 二、具体实现步骤
#### 1. 开启 MySQL Binlog 配置
**在双数据库的 `my.cnf` 中启用 Binlog：**
```ini
[mysqld]
server_id=1 # 本地设为1，云设为2（唯一ID）
log-bin=mysql-bin # 开启Binlog
binlog_format=ROW # 必须为ROW模式
expire_logs_days=7
```
#### 2. 部署 Binlog 解析服务（以 Canal 为例）
```bash
# 安装 Canal 服务
wget https://github.com/alibaba/canal/releases/download/canal-1.1.7/canal.deployer-1.1.7.tar.gz
tar -zxvf canal.deployer-1.1.7.tar.gz
# 配置实例（本地示例）
vi conf/example/instance.properties
```
```properties
canal.instance.master.address=127.0.0.1:3306 # 本地数据库
canal.instance.dbUsername=canal_user
canal.instance.dbPassword=canal_pass
canal.instance.filter.regex=.*\\.user,.*\\.order # 仅同步指定表[^1]
```
#### 3. 实现同步服务（ThinkPHP 命令行）
`application/command/BinlogSync.php`：
```php
class BinlogSync extends Command
{
public function handle()
{
// 连接Canal服务
$client = \Canal\Client::instance();
$client->connect("127.0.0.1", 11111);
$client->subscribe(".*\\..*");
while (true) {
$message = $client->get(100);
if ($entries = $message->getEntries()) {
foreach ($entries as $entry) {
$this->processEntry($entry);
}
}
usleep(100000); // 100ms轮询
}
}
private function processEntry($entry)
{
$rowChange = \Canal\Message\EntryParser::parseEntry($entry);
$tableName = $entry->getHeader()->getTableName();
foreach ($rowChange->getRowDatas() as $rowData) {
$eventType = $rowChange->getEventType();
$data = $this->extractData($rowData, $eventType);
// 排除同步队列表自身（防止循环同步）
if ($tableName === 'binlog_sync_queue') continue;
// 分发到目标数据库
$this->dispatchToTarget($tableName, $eventType, $data);
}
}
private function dispatchToTarget($table, $eventType, $data)
{
$sourceDb = $data['source_db']; // 从Binlog元数据获取来源
// 判断目标数据库（反向同步）
$targetDb = ($sourceDb === 'local') ? 'cloud' : 'local';
switch ($eventType) {
case EventType::INSERT:
Db::connect($targetDb)->table($table)->insert($data['after']);
break;
case EventType::UPDATE:
Db::connect($targetDb)->table($table)
->where('id', $data['before']['id'])
->update($data['after']);
break;
case EventType::DELETE:
Db::connect($targetDb)->table($table)
->delete($data['before']['id']);
break;
}
}
}
```
#### 4. 冲突解决策略
在数据表中增加版本字段：
```sql
ALTER TABLE user ADD version INT DEFAULT 0;
```
同步更新时使用乐观锁：
```php
// 更新操作示例
Db::connect($targetDb)->table($table)
->where('id', $data['id'])
->where('version', $data['version'])
->update([
'field' => $newValue,
'version' => $data['version'] + 1
]);
// 更新失败时记录冲突
if (0 === Db::getAffectedRows()) {
Log::error("数据冲突: table={$table} id={$data['id']}");
}
```
#### 5. 全量数据初始化
```php
// 全量同步脚本
public function fullSync()
{
$tables = ['user', 'order']; // 需同步的表
foreach ($tables as $table) {
// 从本地数据库读取
$localData = Db::connect('local')->table($table)->select();
// 写入云数据库
Db::connect('cloud')->table($table)->insertAll($localData);
// 反向同步（如果云数据库已有数据）
$cloudData = Db::connect('cloud')->table($table)->select();
Db::connect('local')->table($table)->insertAll($cloudData);
}
}
```
---
### 三、关键优化措施
1. **防循环同步机制**
- 在 Binlog 元数据中添加来源标记：
```sql
ALTER TABLE user ADD _source_db ENUM('local','cloud') DEFAULT NULL;
```
- 同步服务忽略带来源标记的数据变更
2. **消息队列缓冲（Kafka 示例）**
```php
// 将Binlog事件写入Kafka
$producer = new \RdKafka\Producer();
$producer->addBrokers("kafka:9092");
$topic = $producer->newTopic("binlog_events");
$topic->produce(RD_KAFKA_PARTITION_UA, 0, json_encode($event));
```
3. **断点续传保障**
```php
// 记录最后同步的Binlog位置
$logPos = $entry->getHeader()->getLogfileOffset();
Redis::set('last_sync_pos', $logPos);
```
4. **同步状态监控**
```bash
# 监控同步延迟
mysqlbinlog --base64-output=decode-rows -v \
--start-position=$(redis-cli get last_sync_pos) \
mysql-bin.000001 | grep "###" | wc -l
```
---
### 四、部署架构建议
```mermaid
graph TB
subgraph 本地数据中心
A[本地MySQL] --> B[Canal服务]
end
subgraph 云数据中心
C[云MySQL] --> D[Canal服务]
end
B --> E[Kafka集群]
D --> E
E --> F[ThinkPHP同步服务]
F --> A
F --> C
```
---
### 五、与传统方案对比
| **能力** | Binlog监听方案 | 触发器+队列方案 |
|------------------|-------------------------------|-------------------------|
| **实时性** | 毫秒级（<100ms） | 秒级（1-5s） |
| **数据库压力** | 低（异步解析） | 中（触发器写队列） |
| **数据一致性** | 强（原生日志级） | 中（依赖队列可靠性） |
| **开发复杂度** | 高（需处理Binlog解析） | 中（需维护触发器） |
| **适用场景** | 高频写入/大型系统 | 中小型系统 |
> 此方案可实现毫秒级同步（通常延迟在100-500ms），适合对实时性要求高的场景[^1]。生产环境建议使用云服务商提供的DTS工具（如阿里云DTS）简化运维复杂度[^2]。
---
超级会员免费看
这篇博客介绍了如何查看MySQL的二进制日志文件，并将其转换为可读的SQL文件。在转换过程中，可能会遇到'unknownvariable'default-character-set=utf8mb4'的错误，解决方法包括修改配置文件或在命令行中使用--no-defaults参数。此外，还详细阐述了转换过程和相关命令。
订阅专栏 解锁全文
扫一扫
3915
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
