Quick Trick: Drop All Tables in MySQL Database in One Step

raindog308

Neat little trick I learned today:

mysqldump -u${user} -p${pass} -h ${host} --add-drop-table --no-data ${database} | grep ^DROP | mysql -u${user} -p${pass} -h ${host} ${database}

akb

Wouldn't

DROP DATABASE my_db;
CREATE DATABASE my_db;

do?

yomero

@akb said: Wouldn't

DROP DATABASE my_db;

CREATE DATABASE my_db;

do?

I agree lol!
But I wonder if the permissions persist doing this.

Also, the first method doesn't have issues when you have foreign keys? =(

raindog308

Yes, but

• User may not have root on the MySQL database (drop/create permissions).

• I believe you'd have to recreate user grants. So if you granted user joebob ALL on my_db.*, you'd have to regrant ALL to joebob (and any other users).

raindog308

Good point @yomero - this was in MyISAM.

yomero

@raindog308 said: I believe you'd have to recreate user grants. So if you granted user joebob ALL on my_db.*, you'd have to regrant ALL to joebob (and any other users).

I guess so

@raindog308 said: User may not have root on the MySQL database (drop/create permissions).

I can't remember if there is a permission for DROP/CREATE. If so, maybe I am not able to even drop the database =P

George_Fusioned

@raindog308 said: I believe you'd have to recreate user grants. So if you granted user joebob ALL on my_db.*, you'd have to regrant ALL to joebob (and any other users).

User grants stay intact when you drop a database. If you create a database with the same name, you will be able to connect with the username/password you had before dropping it.

raindog308

Interesting. MySQL is so ugly under the covers :-) I think you're right - grants are just entries in a permissions table.

nickvanw

You could also do
SELECT concat('DROP TABLE IF EXISTS ', table_name, ';') FROM information_schema.tables WHERE table_schema = 'MyDatabaseName';

Preventing foreign key issues can be done with SET FOREIGN_KEY_CHECKS = 0

DomainBop

My quick trick involves pushing 2 buttons in Webmin: 1. click the "drop database" button 2. click the "just drop all tables" button.

SP

murky

or
or
or checking all tables in phpMyAdmin, and selecting "Drop"

raindog308

@murky said: or checking all tables in phpMyAdmin, and selecting "Drop"

Lots of clicking if you 100 tables...

mojeda

@raindog308 said: Lots of clicking if you 100 tables...

Unless you go all the way down and select "Check All"

mpkossen

@raindog308 said: Interesting. MySQL is so ugly under the covers :-) I think you're right - grants are just entries in a permissions table.

It could also be considered a neat feature Not that I disagree that there's definite room for improvement in MySQL. But it isn't all that bad.

raindog308

@mpkossen said: It could also be considered a neat feature Not that I disagree that there's definite room for improvement in MySQL. But it isn't all that bad.

Actually, all major RDBMS systems I can think of keep this kind of config info in an internal table of some sort - after all, the RDBMS system has a perfectly good data storage system (itself) so why not use it.

But what is crude about MySQL is that as @George_Fusioned points out, permissions are maintained even if the DB is dropped and recreated.

Not all RDBMS systems use the "database" metaphor. For example, in Oracle you have schemas and tablespaces. SQL Server and Postgres are like MySQL - you create multiple databases.

Anyway, I believe in SQL Server (and maybe Pg) the DB name is just a human-friendly name. The real "db name" is some internal number, to prevent this kind of silliness.

This is one of the reasons that cPanel creates DBs with the username prepended (e.g., raindog_mydb) so that users can't accidentally inherit or be given access to recreated databases, collide with other users' DBs, etc.

I was curious and so confirmed that @George_Fusioned is right...

MySQL 5.1.66

As mysql root:

mysql> create database letrules;
mysql> create user cocacola@localhost identified by 'pepsi';
mysql> grant all on letrules.* to cocacola@localhost;
mysql> flush privileges;

As cocacola@localhost:

mysql> create table sometable ( id integer );

As myql root:

mysql> drop database letrules;
mysql> flush privileges;
mysql> create database letrules;

Again as cocacola@localhost:

mysql> show tables;
mysql> create table sometable ( id integer );

njt1982

I've covered this topic on my blog in 2006 - it covers several options in the post and in the comments on dropping all the tables (or truncating the database) in MySQL.