环境介绍：
OB 3.2.4
RHEL 7.6
1、MySQL租户支持索引、表、数据库、租户
2、Oracle租户支持，索引、表
租户管理员操作
mysql -h192.168.15.66 -P2883 -uroot@sysbench_poc#Gaoyc:1667379307 -p'an~7&jtGcd|F43uoo*AOK' -A
mysql -h192.168.15.66 -P2883 -usysbench_test@sysbench_poc#Gaoyc:1667379307 -p'an~7&jtGcd|F43uoo*AOK' -A
obclient -h192.168.15.66 -P2883 -uSYS@ORADB#Gaoyc:1667379307 -p'an~7&jtGcd|F43uoo*AOK' -A
obclient -h192.168.15.66 -P2883 -uUSERGYC@ORADB#Gaoyc -pusergyc -A
==闪回表
MySQL [(none)]> show variables like '%recycle%';
+---------------+-------+
| VARIABLE_NAME | VALUE |
+---------------+-------+
| recyclebin | OFF |
+---------------+-------+
1 row in set (0.003 sec)
MySQL [(none)]> set @@recyclebin=on;
Query OK, 0 rows affected (0.001 sec)
MySQL [(none)]> show variables like '%recycle%';
+---------------+-------+
| VARIABLE_NAME | VALUE |
+---------------+-------+
| recyclebin | ON |
+---------------+-------+
1 row in set (0.001 sec)
MySQL [(none)]> drop table gyc;
Query OK, 0 rows affected (0.312 sec)
MySQL [(none)]> show recyclebin;
+---------------------------------------+---------------+-------+--------------+
| OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME |
+---------------------------------------+---------------+-------+--------------+
¸¼7 |$_1667379307_1683536773933712 | GYC | TABLE |
+---------------------------------------+---------------+-------+--------------+
1 row in set (0.004 sec)
MySQL [(none)]>
MySQL [(none)]>
MySQL [(none)]> flashback table gyc to before drop rename to gyc_flash;
Query OK, 0 rows affected (0.031 sec)
MySQL [(none)]> show recyclebin;
Empty set (0.004 sec)
MySQL [(none)]> select * from gyc_flash;
Empty set (0.014 sec)
MySQL [(none)]> desc gyc_flash;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| ID | NUMBER(38) | YES | NULL | NULL | NULL |
| NAME | VARCHAR2(20) | YES | NULL | NULL | NULL |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.002 sec)
MySQL [(none)]> insert into gyc_flash values (1,'aaa'),(2,'bbb');
Query OK, 2 rows affected (0.019 sec)
Records: 2 Duplicates: 0 Warnings: 0
MySQL [(none)]> create index gyc_idx on gyc_flash(id);
Query OK, 0 rows affected (0.855 sec)
MySQL [(none)]> commit;
Query OK, 0 rows affected (0.000 sec)
MySQL [(none)]> select * from gyc_flash;
+------+------+
| ID | NAME |
+------+------+
| 1 | aaa |
| 2 | bbb |
+------+------+
2 rows in set (0.002 sec)
MySQL [testa]> flashback table __recycle_$_1667379307_1683621594553480 to before drop rename to test8;
Query OK, 0 rows affected (0.02 sec)
MySQL [testa]> select count(*) from test8;
+----------+
| count(*) |
+----------+
| 168684 |
+----------+
1 row in set (0.23 sec)
==闪回数据库：
MySQL [(none)]> drop database test;
Query OK, 0 rows affected (0.42 sec)
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| oceanbase |
| information_schema |
| mysql |
+--------------------+
3 rows in set (0.00 sec)
MySQL [(none)]> show recyclebin;
+-----------------------------------------+---------------+----------+----------------------------+
| OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME |
+-----------------------------------------+---------------+----------+----------------------------+
| __recycle_$_1667379307_1683540330640336 | test | DATABASE | 2023-05-08 18:05:30.640733 |
+-----------------------------------------+---------------+----------+----------------------------+
1 row in set (0.00 sec)
MySQL [(none)]> use oceanbase;
Database changed
MySQL [oceanbase]> select * from __all_recyclebin;
+----------------------------+-----------+-----------------------------------------+------+-------------+----------+---------------+---------------+
| gmt_create | tenant_id | object_name | type | database_id | table_id | tablegroup_id | original_name |
+----------------------------+-----------+-----------------------------------------+------+-------------+----------+---------------+---------------+
| 2023-05-08 18:05:30.640733 | 0 | __recycle_$_1667379307_1683540330640336 | 4 | 1054 | -1 | -1 | test |
+----------------------------+-----------+-----------------------------------------+------+-------------+----------+---------------+---------------+
1 row in set (0.00 sec)
MySQL [oceanbase]> show variables like '%recyclebin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| recyclebin | OFF |
+---------------+-------+
1 row in set (0.01 sec)
MySQL [oceanbase]> set @@recyclebin=on;
Query OK, 0 rows affected (0.00 sec)
MySQL [oceanbase]> create database gyc;
Query OK, 1 row affected (0.02 sec)
MySQL [oceanbase]> use gyc;
Database changed
MySQL [gyc]> create table t (id int ,name varchar(200));
Query OK, 0 rows affected (0.04 sec)
MySQL [gyc]> insert into t values (1,'aaa'),(2,'bbb');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
MySQL [gyc]> commit;
Query OK, 0 rows affected (0.00 sec)
MySQL [gyc]> use mysql
Database changed
MySQL [mysql]> drop database gyc;
Query OK, 0 rows affected (0.01 sec)
MySQL [mysql]> select * from oceanbase.__all_recyclebin;
+----------------------------+-----------+-----------------------------------------+------+-------------+----------+---------------+---------------+
| gmt_create | tenant_id | object_name | type | database_id | table_id | tablegroup_id | original_name |
+----------------------------+-----------+-----------------------------------------+------+-------------+----------+---------------+---------------+
| 2023-05-08 18:05:30.640733 | 0 | __recycle_$_1667379307_1683540330640336 | 4 | 1054 | -1 | -1 | test |
| 2023-05-08 18:24:05.808296 | 0 | __recycle_$_1667379307_1683541445808408 | 4 | 1055 | -1 | -1 | gyc |
+----------------------------+-----------+-----------------------------------------+------+-------------+----------+---------------+---------------+
2 rows in set (0.05 sec)
MySQL [mysql]> flashback database __recycle_$_1667379307_1683541445808408 to before drop;
Query OK, 0 rows affected (0.47 sec)
MySQL [mysql]> show database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'database' at line 1
MySQL [mysql]> show databases;
+--------------------+
| Database |
+--------------------+
| oceanbase |
| information_schema |
| mysql |
| gyc |
+--------------------+
4 rows in set (0.00 sec)
MySQL [mysql]> use gyc
Database changed
MySQL [gyc]> show tables;
+---------------+
| Tables_in_gyc |
+---------------+
| t |
+---------------+
1 row in set (0.01 sec)
MySQL [gyc]> use oceanbase;
Database changed
MySQL [oceanbase]> select * from oceanbase.__all_recyclebin;
+----------------------------+-----------+-----------------------------------------+------+-------------+----------+---------------+---------------+
| gmt_create | tenant_id | object_name | type | database_id | table_id | tablegroup_id | original_name |
+----------------------------+-----------+-----------------------------------------+------+-------------+----------+---------------+---------------+
| 2023-05-08 18:05:30.640733 | 0 | __recycle_$_1667379307_1683540330640336 | 4 | 1054 | -1 | -1 | test |
+----------------------------+-----------+-----------------------------------------+------+-------------+----------+---------------+---------------+
1 row in set (0.00 sec)
MySQL [oceanbase]> flashback database __recycle_$_1667379307_1683540330640336 to before drop rename to testa;
Query OK, 0 rows affected (0.15 sec)
MySQL [oceanbase]> use testa;
Database changed
MySQL [testa]> show tables;
==闪回租户：
MySQL [oceanbase]> select tenant_id,tenant_name,replica_num,zone_list from __all_tenant;
+-----------+--------------------------+-------------+-------------------+
| tenant_id | tenant_name | replica_num | zone_list |
+-----------+--------------------------+-------------+-------------------+
| 1 | sys | -1 | zone1;zone2;zone3 |
| 1001 | ORADB | -1 | zone1;zone2;zone3 |
| 1002 | fsp_fsp001 | -1 | zone1;zone2;zone3 |
| 1003 | fsp_fsp002 | -1 | zone1;zone2;zone3 |
| 1004 | zncbtenant | -1 | zone1;zone2;zone3 |
| 1005 | MYSDB | -1 | zone1;zone2;zone3 |
| 1006 | sysbench_poc | -1 | zone1;zone2;zone3 |
| 1010 | restore_sysbentch_tables | -1 | zone1;zone2;zone3 |
| 1012 | restore_sysbentch | -1 | zone1;zone2;zone3 |
| 1013 | oratest | -1 | zone1;zone2;zone3 |
+-----------+--------------------------+-------------+-------------------+
10 rows in set (0.004 sec)
MySQL [oceanbase]> show variables like '%recyclebin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| recyclebin | OFF |
+---------------+-------+
1 row in set (0.002 sec)
MySQL [oceanbase]> set @@recyclebin=on;
Query OK, 0 rows affected (0.001 sec)
MySQL [oceanbase]> show variables like '%recyclebin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| recyclebin | ON |
+---------------+-------+
1 row in set (0.001 sec)
MySQL [oceanbase]> select * from __all_recyclebin;
Empty set (0.059 sec)
MySQL [oceanbase]> drop tenant restore_sysbentch_tables;
Query OK, 0 rows affected (0.077 sec)
MySQL [oceanbase]> select * from __all_recyclebin;
+----------------------------+-----------+-----------------------------------------+------+-------------+----------+---------------+--------------------------+
| gmt_create | tenant_id | object_name | type | database_id | table_id | tablegroup_id | original_name |
+----------------------------+-----------+-----------------------------------------+------+-------------+----------+---------------+--------------------------+
| 2023-05-09 16:56:46.221630 | 1010 | __recycle_$_1667379307_1683515595123200 | 7 | -1 | -1 | -1 | restore_sysbentch_tables |
+----------------------------+-----------+-----------------------------------------+------+-------------+----------+---------------+--------------------------+
1 row in set (0.002 sec)
MySQL [oceanbase]> show recyclebin;
+-----------------------------------------+--------------------------+--------+----------------------------+
| OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME |
+-----------------------------------------+--------------------------+--------+----------------------------+
| __recycle_$_1667379307_1683515595123200 | restore_sysbentch_tables | TENANT | 2023-05-09 16:56:46.221630 |
+-----------------------------------------+--------------------------+--------+----------------------------+
1 row in set (0.005 sec)
=ocp可以看到删除的租户情况
mysql -h192.168.15.66 -P2883 -uroot@__recycle_$_1667379307_1683515595123200#Gaoyc:1667379307 -p'an~7&jtGcd|F43uoo*AOK' -A
MySQL [oceanbase]> show recyclebin;
+-----------------------------------------+--------------------------+--------+----------------------------+
| OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME |
+-----------------------------------------+--------------------------+--------+----------------------------+
| __recycle_$_1667379307_1683515595123200 | restore_sysbentch_tables | TENANT | 2023-05-09 16:56:46.221630 |
+-----------------------------------------+--------------------------+--------+----------------------------+
1 row in set (0.005 sec)
MySQL [oceanbase]> flashback tenant __recycle_$_1667379307_1683515595123200 to before drop rename to restore_sysbentch_tab;
Query OK, 0 rows affected (0.027 sec)
MySQL [oceanbase]> show recyclebin;
Empty set (0.004 sec)
[root@dbdriver ~]# mysql -h192.168.15.66 -P2883 -uroot@restore_sysbentch_tab#Gaoyc:1667379307 -p'an~7&jtGcd|F43uoo*AOK' -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 121563
Server version: 5.6.25 OceanBase 3.2.3.0 (r20220530152606-797fcd72e170e45935ab7b6141b034bfa01472af) (Built May 30 2022 15:36:02)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| oceanbase |
| information_schema |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
注：生产上的操作请经过严格测试。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24585765/viewspace-2952615/ ，如需转载，请注明出处，否则将追究法律责任。
