案例：实时同步MySQL中的notify库到TiDB

一、下载工具包

#su -tidb

$cd  /home/tidb/

$wget http://download.pingcap.org/tidb-enterprise-tools-latest-linux-amd64.tar.gz

$wget http://download.pingcap.org/tidb-enterprise-tools-latest-linux-amd64.sha256

$sha256sum -c tidb-enterprise-tools-latest-linux-amd64.sha256

$tar -xzf tidb-enterprise-tools-latest-linux-amd64.tar.gz

二、使用mydumper从MySQL导出数据

$/home/tidb/tidb-enterprise-tools-latest-linux-amd64/bin/mydumper -h 192.168.1.98 -P 3306 -u root -p 'xxxxxx' -t 16 -F 64 -B notify --skip-tz-utc -o /app/backup/tidb/notify

PS：各参数解释见help

三、使用loader将导出数据灌入TiDB

$/home/tidb/tidb-enterprise-tools-latest-linux-amd64/bin/loader -h 10.8.8.240 -P 4000 -u root -p 'xxxxxx' -m 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI' -t 24 -d /app/backup/tidb/notify/

PS： 各参数解释见help

四、使用syncer将MySQL增量数据实时同步到TiDB

1、查看mydumper从MySQL导出数据时的位置信息

$cat /app/backup/tidb/notify/metadata

Started dump at: 2020-04-15 10:41:03

SHOW MASTER STATUS:

        Log: mysql_bin.025493

        Pos: 910571470

        GTID:

SHOW SLAVE STATUS:

        Host: 192.168.1.97

        Log: mysql_bin.024855

        Pos: 251919780

        GTID:

2、将上一步查到位置信息，写入syncer做增量同步的起始配置信息文件中：

$vim /home/tidb/tidb-enterprise-tools-latest-linux-amd64/conf/syncer.meta

binlog-name = "mysql_bin.025494"

binlog-pos = 780858549

binlog-gtid = ""

3、根据具体场景信息编译syncer配置文件

$vim /home/tidb/tidb-enterprise-tools-latest-linux-amd64/conf/config.toml

log-level = "info"

log-file = "/home/tidb/tidb-enterprise-tools-latest-linux-amd64/log/syncer.log"

log-rotate = "day"

server-id = 3

## meta 文件地址

meta = "/home/tidb/tidb-enterprise-tools-latest-linux-amd64/conf/syncer.meta"

worker-count = 16

batch = 1000

flavor = "mysql"

## pprof 调试地址，Prometheus 也可以通过该地址拉取 Syncer metrics

status-addr = ":8271"

## 如果设置为 true，Syncer 遇到 DDL 语句时就会停止退出

stop-on-ddl = false

## 跳过 DDL 语句，格式为 **前缀完全匹配**，如：`DROP TABLE ABC` 至少需要填入 `DROP TABLE`

# skip-ddls = ["ALTER USER", "CREATE USER"]

## 在使用 route-rules 功能后，

## replicate-do-db & replicate-ignore-db 匹配合表之后 (target-schema & target-table) 数值

## 优先级关系: replicate-do-db --> replicate-do-table --> replicate-ignore-db --> replicate-ignore-table

## 指定要同步数据库名；支持正则匹配，表达式语句必须以 `~` 开始

#replicate-do-db = ["~^b.*","s1"]

replicate-do-db = ["notify"]

## 指定 **忽略** 同步数据库；支持正则匹配，表达式语句必须以 `~` 开始

#replicate-ignore-db = ["~^b.*","s1"]

# skip-dmls 支持跳过 DML binlog events，type 字段的值可为：'insert'，'update' 和 'delete'

# 跳过 foo.bar 表的所有 delete 语句

# [[skip-dmls]]

# db-name = "foo"

# tbl-name = "bar"

# type = "delete"

#

# 跳过所有表的 delete 语句

# [[skip-dmls]]

# type = "delete"

#

# 跳过 foo.* 表的 delete 语句

# [[skip-dmls]]

# db-name = "foo"

# type = "delete"

## 指定要同步的 db.table 表

## db-name 与 tbl-name 不支持 `db-name ="dbname，dbname2"` 格式

#[[replicate-do-table]]

#db-name ="site_message"

#tbl-name = "business_user_link"

#[[replicate-do-table]]

#db-name ="site_message"

#tbl-name = "service_user_link"

#[[replicate-do-table]]

#db-name ="site_message"

#tbl-name = "message_business"

#[[replicate-do-table]]

#db-name ="site_message"

#tbl-name = "*"

#[[replicate-do-table]]

#db-name ="dbname1"

#tbl-name = "table-name1"

## 指定要同步的 db.table 表；支持正则匹配，表达式语句必须以 `~` 开始

#[[replicate-do-table]]

#db-name ="test"

#tbl-name = "~^a.*"

## 指定 **忽略** 同步数据库

## db-name & tbl-name 不支持 `db-name ="dbname，dbname2"` 语句格式

#[[replicate-ignore-table]]

#db-name = "your_db"

#tbl-name = "your_table"

## 指定要 **忽略** 同步数据库名；支持正则匹配，表达式语句必须以 `~` 开始

#[[replicate-ignore-table]]

#db-name ="test"

#tbl-name = "~^a.*"

# sharding 同步规则，采用 wildcharacter

# 1. 星号字符 (*) 可以匹配零个或者多个字符,

#    例子, doc* 匹配 doc 和 document, 但是和 dodo 不匹配;

#    星号只能放在 pattern 结尾，并且一个 pattern 中只能有一个

# 2. 问号字符 (?) 匹配任一一个字符

#[[route-rules]]

#pattern-schema = "route_*"

#pattern-table = "abc_*"

#target-schema = "route"

#target-table = "abc"

#[[route-rules]]

#pattern-schema = "route_*"

#pattern-table = "xyz_*"

#target-schema = "route"

#target-table = "xyz"

[from]

host = "192.168.1.98"

user = "root"

password = "xxxxxx"

port = 3306

[to]

host = "10.8.8.240"

user = "tidb"

password = "xxxxxx"

port = 4000

4、启动增量数据实时同步进程

$/home/tidb/tidb-enterprise-tools-latest-linux-amd64/bin/syncer -config /home/tidb/tidb-enterprise-tools-latest-linux-amd64/conf/config.toml