《告别迁移焦虑：OceanBase MySQL 模式能否兼容 DBA 的“祖传”运维 SQL？》

AustinDatabases

发布于 2026-06-09 11:40:59

❝开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, OceanBase, Sql Server等有问题，有需求都可以加群加群请联系 liuaustin3 ，（共3500人左右 1 + 2 + 3 + 4 +5 + 6 + 7 + 8 ）(1 2 3 4 5 6 7 8群已经爆满 9群为纯聊天群，默认不加入不得发广告，自己公众号文章链接等，发一次直接踢，默认加入8群，开10群PolarDB专业学习群115+)

OceanBase 单机也能玩，安装中会遇到些什么？

上期我们已经安装好了OceanBase数据库单机服务器，本期我们将开始针对兼容系进行一些列的探讨，首先我们要探讨的就是一些MySQL中的兼容系统表中的兼容语句。

在OceanBase的单机版本的操作中有几个概念和之前操作MySQL是不同的，之前没有租户的概念，同时登陆的时候是没有用户名@租户名的用户名方式。

从MySQL切换到Oceanbase单机版本中，第一个问题就是之前的我们撰写的那些监控语句，SQL系统新系获取的语句，以及各种秘籍SQL还能在OB上使用吗！

这是一个好问题，如果运维 debuf都要修改很多的内容，则这个问题不会被运维人员所接受。所以今天我们来好好的研究一下 OceanBase兼容MySQL在系统表上到底有没有做了功课。在即要兼容MySQL5.7同时也要兼容MySQL8.0的情况下，MySQL的两个大的系统库 mysql 和 information_schema都是要支持的。下面我们找几个语句来验证一下OceanBase对于MySQL运维中的一些基本语句的支持。

1 查询当前系统的正在运行的语句

obclient(root@test)[information_schema]> SELECT
    ->     user,
    ->     host,
    ->     db,
    ->     command,
    ->     time,
    ->     state,
    ->     info
    -> FROM information_schema.processlist
    -> WHERE command != 'Sleep'
    -> ORDER BY time DESC;
+------+-----------------------+--------------------+---------+------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user | host                  | db                 | command | time | state  | info                                                                                                                                                          |
+------+-----------------------+--------------------+---------+------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| root | 192.168.198.108:57674 | information_schema | Query   |    3 | ACTIVE | SELECT
    user,
    host,
    db,
    command,
    time,
    state,
    info
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC |
+------+-----------------------+--------------------+---------+------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row inset (2.837 sec)

2 查询mysql正在运行的事务运行的时间，来发现长时间运行的事务


obclient(root@test)[information_schema]> SELECT
    ->     trx_id,
    ->     trx_state,
    ->     trx_started,
    ->     TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) AS trx_seconds,
    ->     trx_query
    -> FROM information_schema.innodb_trx
    -> ORDER BY trx_started;
+--------+-----------+---------------------+-------------+-----------+
| trx_id | trx_state | trx_started         | trx_seconds | trx_query |
+--------+-----------+---------------------+-------------+-----------+
| 0      | 0         | 2026-05-20 01:09:12 |           0 | 0         |
+--------+-----------+---------------------+-------------+-----------+
1 row inset (0.238 sec)

3 查看数据库中正在进行的锁等待情况

obclient(root@test)[information_schema]> SELECT
    ->     r.trx_id waiting_trx_id,
    ->     r.trx_mysql_thread_id waiting_thread,
    ->     b.trx_id blocking_trx_id,
    ->     b.trx_mysql_thread_id blocking_thread,
    ->     b.trx_query blocking_sql
    -> FROM information_schema.innodb_lock_waits w
    -> JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
    -> JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
+----------------+----------------+-----------------+-----------------+--------------+
| waiting_trx_id | waiting_thread | blocking_trx_id | blocking_thread | blocking_sql |
+----------------+----------------+-----------------+-----------------+--------------+
| 0              |              0 | 0               |               0 | 0            |
+----------------+----------------+-----------------+-----------------+--------------+
1 row inset (0.189 sec)

obclient(root@test)[information_schema]>

4 查询数据库中表的大小

obclient(root@test)[information_schema]> SELECT
    ->     table_schema,
    ->     table_name,
    ->     ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb,
    ->     table_rows
    -> FROM information_schema.tables
    -> ORDER BY (data_length + index_length) DESC
    -> LIMIT 10;
+--------------+------------------------------+---------+------------+
| table_schema | table_name                   | size_mb | table_rows |
+--------------+------------------------------+---------+------------+
| oceanbase    | DBA_SCHEDULER_RUNNING_JOBS   |    0.00 |          0 |
| oceanbase    | USER_JAVA_POLICY             |    0.00 |          0 |
| oceanbase    | DBA_JAVA_POLICY              |    0.00 |          0 |
| oceanbase    | DBA_OB_SYNC_STANDBY_DEST     |    0.00 |          0 |
| oceanbase    | DBA_TABLES                   |    0.00 |          0 |
| oceanbase    | V$OB_SS_OBJECT_TYPE_IO_STAT  |    0.00 |          0 |
| oceanbase    | GV$OB_SS_OBJECT_TYPE_IO_STAT |    0.00 |          0 |
| oceanbase    | DBA_OB_AI_MODEL_ENDPOINTS    |    0.00 |          0 |
| oceanbase    | DBA_OB_AI_MODELS             |    0.00 |          0 |
| oceanbase    | V$OB_HMS_CLIENT_POOL_STAT    |    0.00 |          0 |
+--------------+------------------------------+---------+------------+

5 查看MySQL核心变量值

obclient(root@test)[information_schema]> SHOW VARIABLES
    -> WHERE variable_name IN (
    ->     'innodb_buffer_pool_size',
    ->     'innodb_log_buffer_size',
    ->     'key_buffer_size',
    ->     'query_cache_size',
    ->     'tmp_table_size',
    ->     'max_heap_table_size',
    ->     'sort_buffer_size',
    ->     'join_buffer_size',
    ->     'read_buffer_size',
    ->     'read_rnd_buffer_size',
    ->     'thread_stack'
    -> );
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
| innodb_log_buffer_size  | 16777216  |
| join_buffer_size        | 262144    |
| key_buffer_size         | 8388608   |
| max_heap_table_size     | 16777216  |
| query_cache_size        | 0         |
| read_buffer_size        | 131072    |
| read_rnd_buffer_size    | 262144    |
| sort_buffer_size        | 262144    |
| thread_stack            | 262144    |
| tmp_table_size          | 16777216  |
+-------------------------+-----------+
11 rows inset (3.028 sec)

6 rows inset (4.340 sec)

6 修改查询线程隔离级别

obclient(root@test)[information_schema]> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row inset (0.004 sec)

obclient(root@test)[information_schema]> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
^[[AQuery OK, 0 rows affected (1.363 sec)

obclient(root@test)[information_schema]> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row inset (0.032 sec)

obclient(root@test)[information_schema]> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.020 sec)

obclient(root@test)[information_schema]> SELECT @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row inset (0.283 sec)

obclient(root@test)[information_schema]> SET SESSION TRANSACTION ISOLATION LEVEL serializable;
Query OK, 0 rows affected (0.047 sec)

obclient(root@test)[information_schema]> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row inset (0.041 sec)

780 rows in set (0.590 sec)

obclient(root@test)[information_schema]> show variables like 
    -> 
    -> 'thread%';
+--------------------------------------+---------------------------+
| Variable_name                        | Value                     |
+--------------------------------------+---------------------------+
| thread_cache_size                    | 9                         |
| thread_handling                      | one-thread-per-connection |
| thread_pool_algorithm                | 0                         |
| thread_pool_high_priority_connection | 0                         |
| thread_pool_max_unused_threads       | 0                         |
| thread_pool_prio_kickup_timer        | 1000                      |
| thread_pool_size                     | 16                        |
| thread_pool_stall_limit              | 6                         |
| thread_stack                         | 262144                    |
+--------------------------------------+---------------------------+
9 rows inset (0.474 sec)

OceanBase Mysql兼容中的用户部分

obclient(root@test)[information_schema]> show grants;
+------------------------------------------------------------------------+

| Grants for root@%                                                      |
+------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root' WITH GRANT OPTION                |
| GRANT ENCRYPT, DECRYPT, PLAINACCESS ON *.* TO 'root' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `oceanbase`.* TO 'root'                        |
| GRANT ALL PRIVILEGES ON `__recyclebin`.* TO 'root'                     |
| GRANT ALL PRIVILEGES ON `mysql`.* TO 'root'                            |
| GRANT ALL PRIVILEGES ON `test`.* TO 'root'                             |
| GRANT ALL PRIVILEGES ON `information_schema`.* TO 'root'               |
| GRANT ALL PRIVILEGES ON `__public`.* TO 'root'                         |
+------------------------------------------------------------------------+
8 rows inset (0.993 sec)

obclient(root@test)[information_schema]> CREATE USER 'mysqlsystem'@'%'
    -> IDENTIFIED BY '*&93dk3K';
    
Query OK, 0 rows affected (8.725 sec)

obclient(root@test)[information_schema]> GRANT ALL PRIVILEGES ON *.* TO 'mysqlsystem'@'%';

Query OK, 0 rows affected (3.370 sec)

obclient(root@test)[information_schema]> flush privileges;
Query OK, 0 rows affected, 1 warning (0.011 sec)

obclient(root@test)[information_schema]> show create user mysqlsystem@'%';
+----------------------------------------------------------------------------------------------------------------+
| CREATE USER for mysqlsystem@%                                                                                  |
+----------------------------------------------------------------------------------------------------------------+

| create user if not exists `mysqlsystem`@`%` IDENTIFIED BY PASSWORD '*69dda70446e3520268bf1a678b8e1897a3c00d64' |

+----------------------------------------------------------------------------------------------------------------+

1 row inset (0.733 sec)


obclient(root@test)[information_schema]> show grants for'mysqlsystem'@'%';

+----------------------------------------------+
| Grants for mysqlsystem@%                     |
+----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'mysqlsystem' |
+----------------------------------------------+
1 row inset (0.446 sec)

以上操作中，OceanBase单机版，语句在MySQL8.025中运行后鉴定无报错后，再在OceanBase单机版中运行。以上命令兼容性100%没有问题。

后续还是持续对OB 和MYSQL的语句兼容性进行各种测试。