部署tidb同步到mysql(drainer)

环境:
TIDB:V6.0.0
Mysql:5.7
OS:Centos 7

192.168.1.118 pd,tidb,tikv,tiflash,monitoring,grafana,alertmanager,pump,drainer
192.168.1.85 pd,tidb,tikv,tiflash,pump
192.168.1.134 pd,tidb,tikv,pump

 

1.在mysq数据库中,添加同步用户
grant all on *.* to 'tidb_sync'@'%' identified by 'mysql';

 

2.编写scale-out-binlog.yaml文件
我这里已经部署了pump_servers,下面的pump_servers需要去掉,没有kafka的也也需要注释掉
vi /tmp/scale-drainer.yaml

#pump_servers:
#  - host: 192.168.40.160
#    config:
#      gc: 7
#      storage.stop-write-at-available-space: 200MB
drainer_servers:
  - host: 192.168.1.118
        ssh_port: 22
    port: 8249
    deploy_dir: "/tidb-deploy/drainer-8249"
    data_dir: "/tidb-data/drainer-8249"
    config:
      syncer.db-type: "mysql"
      syncer.to.host: "192.168.1.134"
      syncer.to.user: "tidb_sync"
      syncer.to.password: "mysql"
      syncer.to.port: 13306
    #kafka配置
      #syncer.db-type: "kafka"
      #syncer.to.kafka-addrs: "127.0.0.1:9092"
      #syncer.to.kafka-version: "0.8.2.0"

 

3.开始扩容
[root@localhost tmp]#tiup cluster scale-out mytidb_cluster /tmp/scale-drainer.yaml

 

4.查看集群状态
[root@localhost tmp]#tiup cluster display mytidb_cluster

 

5.开启binlog
[root@localhost tmp]#tiup cluster edit-config mytidb_cluster

server_configs:
  tidb:
    binlog.enable: true
    binlog.ignore-error: true

 

6.重新加载配置(集群会重启动)
[root@localhost tmp]#tiup cluster reload mytidb_cluster

 

7.查看集群状态

[root@localhost tmp]#tiup cluster display mytidb_cluster

 

8.验证
登陆一个tidb查看
[root@localhost ~]# /opt/mysql5727/bin/mysql -h 192.168.1.134 -P4000 -uroot -p
mysql> show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.04 sec)

mysql> show pump status;
+--------------------+--------------------+--------+--------------------+---------------------+
| NodeID | Address | State | Max_Commit_Ts | Update_Time |
+--------------------+--------------------+--------+--------------------+---------------------+
| 192.168.1.118:8250 | 192.168.1.118:8250 | online | 433469605565702466 | 2022-05-26 16:57:47 |
| 192.168.1.134:8250 | 192.168.1.134:8250 | online | 433469605565702483 | 2022-05-26 16:57:46 |
| 192.168.1.85:8250 | 192.168.1.85:8250 | online | 433469605565702510 | 2022-05-26 16:57:47 |
+--------------------+--------------------+--------+--------------------+---------------------+
3 rows in set (0.44 sec)

mysql> show drainer status;
+--------------------+--------------------+--------+--------------------+---------------------+
| NodeID | Address | State | Max_Commit_Ts | Update_Time |
+--------------------+--------------------+--------+--------------------+---------------------+
| 192.168.1.118:8249 | 192.168.1.118:8249 | online | 433469605224644609 | 2022-05-26 16:57:49 |
+--------------------+--------------------+--------+--------------------+---------------------+
1 row in set (0.00 sec)

 

9.数据同步验证
登陆tidb建库,建表写入数据
[root@localhost ~]# /opt/mysql5727/bin/mysql -h 192.168.1.134 -P4000 -uroot -p
mysql> create database db_tidb_sync;
mysql> create table tb_sync_test(id int,name varchar(20));
Query OK, 0 rows affected (0.63 sec)

mysql> insert into tb_sync_test values(1,'name1');
Query OK, 1 row affected (0.36 sec)

mysql> insert into tb_sync_test values(2,'name2');
Query OK, 1 row affected (0.05 sec)

mysql> insert into tb_sync_test values(3,'name3');
Query OK, 1 row affected (0.02 sec)

mysql> select * from tb_sync_test;
+------+-------+
| id | name |
+------+-------+
| 1 | name1 |
| 2 | name2 |
| 3 | name3 |
+------+-------+
3 rows in set (0.01 sec)

 

10.登陆mysql查看
[root@localhost data]# /opt/mysql5727/bin/mysql -h localhost -uroot -pmysql -S /opt/mysql5727/mysql.sock
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db_tidb_sync |
| tidb_binlog |
+--------------------+
10 rows in set (0.00 sec)

mysql> show tables;
+------------------------+
| Tables_in_db_tidb_sync |
+------------------------+
| tb_sync_test |
+------------------------+
1 row in set (0.00 sec)

mysql> select * from tb_sync_test;
+------+-------+
| id | name |
+------+-------+
| 1 | name1 |
| 2 | name2 |
| 3 | name3 |
+------+-------+
3 rows in set (0.00 sec)

看到数据同步过来了.

 

11.binlogctl工具使用
查看 pump 节点的状态
/root/.tiup/components/ctl/v6.0.0/binlogctl -pd-urls=http://192.168.1.118:2379 -cmd pumps

查看drainer节点的状态
/root/.tiup/components/ctl/v6.0.0/binlogctl -pd-urls=http://192.168.1.118:2379 -cmd drainers

 

12.停掉drainers

/root/.tiup/components/ctl/v6.0.0/binlogctl -pd-urls=http://192.168.1.118:2379 -cmd pause-drainer -node-id 192.168.1.118:8249

使用如下命令可以查看到node-id

tiup ctl:v6.0.0 binlog -pd-urls=http://192.168.1.118:2379 -cmd drainers

停掉以后可以使用如下命令启动(tiup cluster start xxxx -N drainer_id)

tiup cluster start mytidb_cluster -N 192.168.1.118:8249