❝开头还是介绍一下群，如果感兴趣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的语句兼容性进行各种测试。
