How to clean up tables that don't belong into mysql/mysql database?

Question

It seems that a *.sql file I recently imported had use mysql instead of use dbname. Now the mysql database contains a large number of tables which don't belong there.

Is there an easy/efficient way to clean this up?

---

Note: I am using MariaDB, but I think a generic answer would be more useful for others.

mysql Ver 15.1 Distrib 10.1.10-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Answer 1

No clean way...

USE MyDatabase;
RENAME TABLE mysql.mytable1 TO mytable1;
RENAME TABLE mysql.mytable2 TO mytable2;

etc.

You said "large number"... You could get the list thus:

SELECT CONCAT('RENAME TABLE mysql.', TABLE_NAME, ' TO ', TABLENAME, ';')
    FROM information_schema.TABLES
    WHERE schema_name = 'mysql';

But be very careful not to rename the system tables!