MySQL bulk delete tables based on regular expressions

Recently in the maintenance of a WordPress mall project, the mall is multi-station, will be independent of the station to create a few tables. Project on the test server, it seems to be a malicious crawler registration, resulting in thousands of users, resulting in tens of thousands of tables, resulting in the database import and export like a snail, slow to death, so it is only on the server to execute the SQL delete, first temporarily shut down the registration function, due to registration seemingly not verified conditions caused, Then I wrote a few SQL statements to delete the tens of thousands of extra tables.

First login to the server with SSH, log in to the MySQL database:

Mysql-uroot-proot

Note:-U is followed by your database username-p followed by your database password

It then generates an SQL statement that deletes the table according to the regular expression and saves:

Select CONCAT (' drop table ', table_name, '; ') From Information_schema.tables

WHERE table_name REGEXP ' ss_[0-9]{3,}.. ' into outfile ' del.sql ';;

Note: REGEXP is followed by regular expressions that need to delete the table name

Here are some common rules for MySQL regular expressions:

Logical OR: SELECT * from S_user where name REGEXP ' A|b|c '

Specific character characters: SELECT * from S_user where name REGEXP ' [abc]123 '

Matching range: [0-9] [A-z]

Repeating meta characters: * 0 or more

+ one or more

? 0 or one

{n} specified number of times

{N,M} specified number of times range

{N,} The specified number of times range above

Anchor metacharacters: ^ Beginning of line

$ End of line

Any number of characters:..

.... More specific matching rules can be Google a bit

The SQL file here is imported by default to/var/lib/mysql/del.sql here.

Then we execute del.sql inside the command can be, if not rest assured, you can use VI open del.sql look inside the content:

Execution: Source Del.sql

This article is from the "hope to the end of the Road" blog, please be sure to keep this source http://wujingchao.blog.51cto.com/6558896/1563513

MySQL bulk delete tables based on regular expressions