OB V3 中查询分区主从副本分布的几种方式

原创范计杰 2024-08-21

255

[[toc]]

概述

OB为原生分区式数据库，高可用可以做到分区级，很多问题的分析需要判断分区主副本位置。这里记录几种查询主副本位置的方法。

3个视图可以查主副本位置（用户表）

__all_virtual_meta_table

__all_virtual_clog_stat

查rootservice主副本位置(系统租户位置)

__all_virtual_core_meta_table

示例

查tabha主副本位置

tabha的table_id

MySQL [oceanbase]> select table_id from __all_virtual_table where table_name='TABHA';
+------------------+
| table_id         |
+------------------+
| 1100611139454075 |
+------------------+
1 row in set (0.011 sec)

通过__all_virtual_meta_table查主副本位置

MySQL [oceanbase]> select tenant_id,table_id,partition_id,svr_ip,zone,role,member_list from __all_virtual_meta_table where table_id=1100611139454075 and role=1\G
*************************** 1. row ***************************
   tenant_id: 1001
    table_id: 1100611139454075
partition_id: 0
      svr_ip: 192.168.56.36
        zone: zone1
        role: 1
 member_list: 192.168.56.36:2882:1717555556783731,192.168.56.37:2882:1717555556783731,192.168.56.38:2882:1717555556783731
1 row in set (0.016 sec)

通过__all_virtual_clog_stat查主副本位置

MySQL [oceanbase]>  select svr_ip,table_id,partition_idx,role,leader,is_offline,is_in_sync,quorum from __all_virtual_clog_stat where table_id=1100611139454075;
+---------------+------------------+---------------+----------+----------------------+------------+------------+--------+
| svr_ip        | table_id         | partition_idx | role     | leader               | is_offline | is_in_sync | quorum |
+---------------+------------------+---------------+----------+----------------------+------------+------------+--------+
| 192.168.56.37 | 1100611139454075 |             0 | FOLLOWER | "192.168.56.36:2882" |          0 |          1 |      3 |
| 192.168.56.36 | 1100611139454075 |             0 | LEADER   | "192.168.56.36:2882" |          0 |          1 |      3 |
| 192.168.56.38 | 1100611139454075 |             0 | FOLLOWER | "192.168.56.36:2882" |          0 |          1 |      3 |
+---------------+------------------+---------------+----------+----------------------+------------+------------+--------+

通过__all_virtual_election_info查主副本位置

MySQL [oceanbase]> select table_id,partition_idx,svr_ip,current_leader,previous_leader,proposal_leader,member_list,role from __all_virtual_election_info where table_id=1100611139454075 and role=1\G
*************************** 1. row ***************************
       table_id: 1100611139454075
  partition_idx: 0
         svr_ip: 192.168.56.36
 current_leader: "192.168.56.36:2882"
previous_leader: 192.168.56.37
proposal_leader: 192.168.56.37
    member_list: 3{server:"192.168.56.36:2882", timestamp:1717555556783731, flag:0, need_encrypt:false}{server:"192.168.56.37:2882", timestamp:1717555556783731, flag:0, need_encrypt:false}{server:"192.168.56.38:2882", timestamp:1717555556783731, flag:0, need_encrypt:false}
           role: 1
1 row in set (0.044 sec)

通过__all_virtual_core_meta_table查rootservice所在机器

MySQL [oceanbase]> SELECT table_id,svr_ip,role FROM oceanbase.__all_virtual_core_meta_table;
+---------------+----------------+------+
| table_id      | svr_ip         | role |
+---------------+----------------+------+
| 1099511627777 | 192.168.56.225 |    1 |
| 1099511627777 | 192.168.56.202 |    2 |
| 1099511627777 | 192.168.56.210 |    2 |
+---------------+----------------+------+
3 rows in set (0.005 sec)

通过__all_virtual_election_event_history查询切主日志

MySQL [oceanbase]> ALTER SYSTEM SWITCH REPLICA LEADER PARTITION_ID  '0%0@1100611139454075' SERVER  '192.168.56.36:2882' ;
Query OK, 0 rows affected (0.004 sec)



MySQL [oceanbase]> select * from __all_virtual_election_event_history where table_id=1100611139454075 order by gmt_create;
+----------------------------+---------------+----------+------------------+---------------+-----------------+----------------------+--------------------+
| gmt_create                 | svr_ip        | svr_port | table_id         | partition_idx | event           | leader               | info               |
+----------------------------+---------------+----------+------------------+---------------+-----------------+----------------------+--------------------+
| 2024-06-05 10:45:56.794007 | 192.168.56.37 |     2882 | 1100611139454075 |             0 | leader takeover | "192.168.56.36:2882" | assign leader      |
| 2024-06-05 10:45:56.794204 | 192.168.56.38 |     2882 | 1100611139454075 |             0 | leader takeover | "192.168.56.36:2882" | assign leader      |
| 2024-06-05 10:45:56.794268 | 192.168.56.36 |     2882 | 1100611139454075 |             0 | leader takeover | "192.168.56.36:2882" | assign leader      |
| 2024-06-05 11:03:20.203166 | 192.168.56.36 |     2882 | 1100611139454075 |             0 | leader takeover | "192.168.56.37:2882" | renew lease succ   |
| 2024-06-05 11:03:20.203166 | 192.168.56.36 |     2882 | 1100611139454075 |             0 | leader revoke   | "192.168.56.36:2882" | old leader revoke  |
| 2024-06-05 11:03:20.203351 | 192.168.56.37 |     2882 | 1100611139454075 |             0 | leader takeover | "192.168.56.37:2882" | change leader succ |
| 2024-06-05 11:03:20.203867 | 192.168.56.38 |     2882 | 1100611139454075 |             0 | leader takeover | "192.168.56.37:2882" | change leader succ |
| 2024-06-05 11:06:23.603744 | 192.168.56.36 |     2882 | 1100611139454075 |             0 | leader takeover | "192.168.56.36:2882" | change leader succ |
| 2024-06-05 11:06:23.603872 | 192.168.56.37 |     2882 | 1100611139454075 |             0 | leader takeover | "192.168.56.36:2882" | renew lease succ   |
| 2024-06-05 11:06:23.603872 | 192.168.56.37 |     2882 | 1100611139454075 |             0 | leader revoke   | "192.168.56.37:2882" | old leader revoke  |
| 2024-06-05 11:06:23.604033 | 192.168.56.38 |     2882 | 1100611139454075 |             0 | leader takeover | "192.168.56.36:2882" | change leader succ |
| 2024-06-05 11:06:55.805495 | 192.168.56.36 |     2882 | 1100611139454075 |             0 | leader takeover | "192.168.56.37:2882" | renew lease succ   |
| 2024-06-05 11:06:55.805495 | 192.168.56.36 |     2882 | 1100611139454075 |             0 | leader revoke   | "192.168.56.36:2882" | old leader revoke  |
| 2024-06-05 11:06:55.805701 | 192.168.56.37 |     2882 | 1100611139454075 |             0 | leader takeover | "192.168.56.37:2882" | change leader succ |
| 2024-06-05 11:06:55.805803 | 192.168.56.38 |     2882 | 1100611139454075 |             0 | leader takeover | "192.168.56.37:2882" | change leader succ |
| 2024-06-05 11:14:11.200855 | 192.168.56.37 |     2882 | 1100611139454075 |             0 | leader takeover | "192.168.56.36:2882" | renew lease succ   |
| 2024-06-05 11:14:11.200855 | 192.168.56.37 |     2882 | 1100611139454075 |             0 | leader revoke   | "192.168.56.37:2882" | old leader revoke  |
| 2024-06-05 11:14:11.203003 | 192.168.56.36 |     2882 | 1100611139454075 |             0 | leader takeover | "192.168.56.36:2882" | change leader succ |
| 2024-06-05 11:14:11.203458 | 192.168.56.38 |     2882 | 1100611139454075 |             0 | leader takeover | "192.168.56.36:2882" | change leader succ |
| 2024-06-05 11:14:36.402438 | 192.168.56.36 |     2882 | 1100611139454075 |             0 | leader takeover | "192.168.56.37:2882" | renew lease succ   |
| 2024-06-05 11:14:36.402438 | 192.168.56.36 |     2882 | 1100611139454075 |             0 | leader revoke   | "192.168.56.36:2882" | old leader revoke  |
| 2024-06-05 11:14:36.403650 | 192.168.56.37 |     2882 | 1100611139454075 |             0 | leader takeover | "192.168.56.37:2882" | change leader succ |
| 2024-06-05 11:14:36.404553 | 192.168.56.38 |     2882 | 1100611139454075 |             0 | leader takeover | "192.168.56.37:2882" | change leader succ |
| 2024-06-05 11:21:53.202458 | 192.168.56.37 |     2882 | 1100611139454075 |             0 | leader takeover | "192.168.56.36:2882" | renew lease succ   |
| 2024-06-05 11:21:53.202458 | 192.168.56.37 |     2882 | 1100611139454075 |             0 | leader revoke   | "192.168.56.37:2882" | old leader revoke  |
| 2024-06-05 11:21:53.203201 | 192.168.56.38 |     2882 | 1100611139454075 |             0 | leader takeover | "192.168.56.36:2882" | change leader succ |
| 2024-06-05 11:21:53.203465 | 192.168.56.36 |     2882 | 1100611139454075 |             0 | leader takeover | "192.168.56.36:2882" | change leader succ |<<<<切至36

| 2024-06-05 11:22:15.602699 | 192.168.56.36 |     2882 | 1100611139454075 |             0 | leader takeover | "192.168.56.37:2882" | renew lease succ   |<<<<因为primary_zone作用，1分钟后会切回
| 2024-06-05 11:22:15.602699 | 192.168.56.36 |     2882 | 1100611139454075 |             0 | leader revoke   | "192.168.56.36:2882" | old leader revoke  |
| 2024-06-05 11:22:15.603569 | 192.168.56.38 |     2882 | 1100611139454075 |             0 | leader takeover | "192.168.56.37:2882" | change leader succ |
| 2024-06-05 11:22:15.604192 | 192.168.56.37 |     2882 | 1100611139454075 |             0 | leader takeover | "192.168.56.37:2882" | change leader succ |
+----------------------------+---------------+----------+------------------+---------------+-----------------+----------------------+--------------------+
31 rows in set (0.039 sec)

参考

__all_virtual_meta_table

MySQL [oceanbase]> desc __all_virtual_meta_table;
+--------------------+---------------+------+-----+-----------------------+-------+
| Field              | Type          | Null | Key | Default               | Extra |
+--------------------+---------------+------+-----+-----------------------+-------+
| tenant_id          | bigint(20)    | NO   | PRI | NULL                  |       |
| table_id           | bigint(20)    | NO   | PRI | NULL                  |       |
| partition_id       | bigint(20)    | NO   | PRI | NULL                  |       |
| svr_ip             | varchar(46)   | NO   | PRI | NULL                  |       |
| svr_port           | bigint(20)    | NO   | PRI | NULL                  |       |
| gmt_create         | timestamp(6)  | NO   |     | NULL                  |       |
| gmt_modified       | timestamp(6)  | NO   |     | NULL                  |       |
| sql_port           | bigint(20)    | NO   |     | NULL                  |       |
| unit_id            | bigint(20)    | NO   |     | NULL                  |       |
| partition_cnt      | bigint(20)    | NO   |     | NULL                  |       |
| zone               | varchar(128)  | NO   |     | NULL                  |       |
| role               | bigint(20)    | NO   |     | NULL                  |       |<<<
| member_list        | varchar(4480) | NO   |     | NULL                  |       |
| row_count          | bigint(20)    | NO   |     | NULL                  |       |
| data_size          | bigint(20)    | NO   |     | NULL                  |       |
| data_version       | bigint(20)    | NO   |     | NULL                  |       |
| data_checksum      | bigint(20)    | NO   |     | NULL                  |       |
| row_checksum       | bigint(20)    | NO   |     | NULL                  |       |
| column_checksum    | varchar(8192) | NO   |     | NULL                  |       |
| is_original_leader | bigint(20)    | NO   |     | 0                     |       |
| is_previous_leader | bigint(20)    | NO   |     | 0                     |       |
| create_time        | bigint(20)    | NO   |     | NULL                  |       |
| rebuild            | bigint(20)    | NO   |     | 0                     |       |
| replica_type       | bigint(20)    | NO   |     | 0                     |       |
| required_size      | bigint(20)    | NO   |     | 0                     |       |
| status             | varchar(64)   | NO   |     | REPLICA_STATUS_NORMAL |       |
| is_restore         | bigint(20)    | NO   |     | 0                     |       |
| partition_checksum | bigint(20)    | NO   |     | 0                     |       |
| quorum             | bigint(20)    | NO   |     | -1                    |       |
| fail_list          | varchar(1024) | NO   |     |                       |       |
| recovery_timestamp | bigint(20)    | NO   |     | 0                     |       |
| memstore_percent   | bigint(20)    | NO   |     | 100                   |       |
| data_file_id       | bigint(20)    | NO   |     | 0                     |       |
+--------------------+---------------+------+-----+-----------------------+-------+
33 rows in set (0.003 sec)

__all_virtual_clog_stat

+--------------------------+---------------------+------+-----+---------+-------+
| Field                    | Type                | Null | Key | Default | Extra |
+--------------------------+---------------------+------+-----+---------+-------+
| svr_ip                   | varchar(46)         | NO   |     | NULL    |       |
| svr_port                 | bigint(20)          | NO   |     | NULL    |       |
| table_id                 | bigint(20)          | NO   |     | NULL    |       |
| partition_idx            | bigint(20)          | NO   |     | NULL    |       |
| partition_cnt            | bigint(20)          | NO   |     | NULL    |       |
| role                     | varchar(32)         | NO   |     | NULL    |       |<<<
| status                   | varchar(32)         | NO   |     | NULL    |       |
| leader                   | varchar(32)         | NO   |     | NULL    |       |
| last_index_log_id        | bigint(20) unsigned | NO   |     | NULL    |       |
| last_index_log_timestamp | timestamp(6)        | NO   |     | NULL    |       |
| last_log_id              | bigint(20) unsigned | NO   |     | NULL    |       |
| active_freeze_version    | varchar(32)         | NO   |     | NULL    |       |
| curr_member_list         | varchar(1024)       | NO   |     | NULL    |       |
| member_ship_log_id       | bigint(20) unsigned | NO   |     | NULL    |       |
| is_offline               | tinyint(4)          | NO   |     | NULL    |       |
| is_in_sync               | tinyint(4)          | NO   |     | NULL    |       |
| start_id                 | bigint(20) unsigned | NO   |     | NULL    |       |
| parent                   | varchar(32)         | NO   |     | NULL    |       |
| children_list            | varchar(1024)       | NO   |     | NULL    |       |
| accu_log_count           | bigint(20) unsigned | NO   |     | NULL    |       |
| accu_log_delay           | bigint(20) unsigned | NO   |     | NULL    |       |
| replica_type             | bigint(20)          | NO   |     | 0       |       |
| allow_gc                 | tinyint(4)          | NO   |     | NULL    |       |
| quorum                   | bigint(20)          | NO   |     | NULL    |       |
| is_need_rebuild          | tinyint(4)          | NO   |     | NULL    |       |
| next_replay_ts_delta     | bigint(20) unsigned | NO   |     | NULL    |       |
+--------------------------+---------------------+------+-----+---------+-------+
26 rows in set (0.009 sec

__all_virtual_election_info

MySQL [oceanbase]> desc __all_virtual_election_info;
+-----------------------------+---------------------+------+-----+---------+-------+
| Field                       | Type                | Null | Key | Default | Extra |
+-----------------------------+---------------------+------+-----+---------+-------+
| svr_ip                      | varchar(46)         | NO   |     | NULL    |       |
| svr_port                    | bigint(20)          | NO   |     | NULL    |       |
| table_id                    | bigint(20)          | NO   |     | NULL    |       |
| partition_idx               | bigint(20)          | NO   |     | NULL    |       |
| is_running                  | bigint(20)          | NO   |     | NULL    |       |
| is_changing_leader          | bigint(20)          | NO   |     | NULL    |       |
| current_leader              | varchar(64)         | NO   |     | NULL    |       |
| previous_leader             | varchar(64)         | NO   |     | NULL    |       |
| proposal_leader             | varchar(64)         | NO   |     | NULL    |       |
| member_list                 | varchar(4480)       | NO   |     | NULL    |       |
| replica_num                 | bigint(20)          | NO   |     | NULL    |       |
| lease_start                 | bigint(20)          | NO   |     | NULL    |       |
| lease_end                   | bigint(20)          | NO   |     | NULL    |       |
| time_offset                 | bigint(20)          | NO   |     | NULL    |       |
| active_timestamp            | bigint(20)          | NO   |     | NULL    |       |
| T1_timestamp                | bigint(20)          | NO   |     | NULL    |       |
| leader_epoch                | bigint(20)          | NO   |     | NULL    |       |
| state                       | bigint(20)          | NO   |     | NULL    |       |
| role                        | bigint(20)          | NO   |     | NULL    |       |
| stage                       | bigint(20)          | NO   |     | NULL    |       |
| eg_id                       | bigint(20) unsigned | NO   |     | NULL    |       |
| remaining_time_in_blacklist | bigint(20)          | NO   |     | NULL    |       |
+-----------------------------+---------------------+------+-----+---------+-------+
22 rows in set (0.004 sec)

墨力计划 oceanbase

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

OB V3 中查询分区主从副本分布的几种方式

概述

示例

参考

评论