How to Remove Empty Databases in a TiDB Cluster

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 如何清除tidb集群上那些空的数据库

| username: 随缘天空

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version] V4.0.0
[Reproduction Path] There are about 20,000 databases on the TiDB 4.0 cluster, of which 90% are empty databases (i.e., without any tables). The following SQL query has been executed to find the databases that contain tables. I want to delete all the empty databases that do not have any tables. What is a good method?

SELECT TABLE_SCHEMA, COUNT(*) AS table_count
   FROM information_schema.TABLES
   GROUP BY TABLE_SCHEMA
   ORDER BY TABLE_SCHEMA;
| username: h5n1 | Original post link

I want to know how these 20,000 databases were created? And why?

| username: zhanggame1 | Original post link

The query result is a script to drop databases. Just copy it and execute it.

| username: 像风一样的男子 | Original post link

I am also curious about how these empty databases were created.

| username: oceanzhang | Original post link

The query result is a script to drop databases. Just copy it and execute it.

| username: Fly-bird | Original post link

Just write a shell script to delete it.

| username: Soysauce520 | Original post link

Wouldn’t “show databases” cause a freeze?

| username: TiDBer_小阿飞 | Original post link

This… may I ask which company you are from, having 20,000 databases? :joy:

| username: TiDBer_小阿飞 | Original post link

What level are the big shots using TiDB? First, there was the 12T single table guy, and now there’s someone with 20,000 databases. That’s impressive. :+1:

| username: Kongdom | Original post link

:+1: Kindred spirits~

| username: andone | Original post link

Two-week database, impressive.

| username: 随缘天空 | Original post link

It was probably created when testing the TiDB database capacity before.

| username: 随缘天空 | Original post link

Your SQL statement might not work. The database names appearing in information_schema.TABLES are only those that contain tables. The information_schema.SCHEMATA table contains metadata information for all databases in the cluster.

| username: 随缘天空 | Original post link

In the past, we created empty databases for testing purposes and didn’t delete them in time, which has left some issues.

| username: 随缘天空 | Original post link

I have already identified all the empty databases. Actually, you can just execute drop database <database_name>. Put all the empty databases into the same SQL script and execute drop database <database_name> for each of them.

| username: 随缘天空 | Original post link

I haven’t tried it, but with 20,000 rows of data, it shouldn’t be a problem.

| username: 随缘天空 | Original post link

99% are empty databases.

| username: 随缘天空 | Original post link

In fact, there are quite a few empty databases.

| username: 随缘天空 | Original post link

Can’t handle it :joy:

| username: 随缘天空 | Original post link

Legacy issue