How to search in all databases having `in use` table-CodePudding

My database server has been damaged recently and I have more than 300 databases in the server.

The way I'm currently doing is to run phpmyadmin and check all databases one by one to see if any database has at least one database with in use in collation to restore.

How to search for collation in all databases and tables having at least one in use in collation?

Server is CentOS 7 and I have root access. I can run PHP script if needed if you can help me with PHP.

Update 1

Until now I have found I should run these query to use database and show table status:

Use DatabaseName;
show TABLE STATUS;

It shows all tables status, including the corrupted ones:

| 2022-04-05 20:31:19 | utf8_persian_ci   |     NULL |                |                                                                  | 288230376151710720 | N         |
| users            | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |              NULL |         NULL |      NULL |           NULL | NULL                | NULL| NULL                | NULL              |     NULL | NULL           | Table 'db_name.users' doesn't exist in engine            |               NULL | NULL      |

And I need to either grep or pipe it to another file to check and list all of them.

CodePudding user response：

you can modify this shell script for your use:

mysql -N suppress header grep -v E "..|.." exclude system databases

root@localhost:~# mysql -N -e "SHOW DATABASES" | grep -v -E "mysql|information_schema|performance_schema" |
> while read dbs
> do
>   echo "CHECK DATABASE " $dbs
>. # your command
> done

output:

CHECK DATABASE  bernd
CHECK DATABASE  sys

with 2 commands:

mysql -N -e "SHOW DATABASES" | grep -v -E "mysql|information_schema|performance_schema" |
while read dbs
do
  echo "CHECK DATABASE " $dbs;
  mysql -N -e "USE $dbs; SHOW TABLE STATUS;"
done