Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Advertise on LowEndTalk.com
Quick Trick: Drop All Tables in MySQL Database in One Step
New on LowEndTalk? Please read our 'Community Rules' by clicking on it in the right menu!

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

raindog308raindog308 Administrator, Moderator
edited February 2013 in Tutorials

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}

For LET support, please visit the support desk.

Comments

  • Wouldn't

    DROP DATABASE my_db;
    CREATE DATABASE my_db;

    do?

  • yomeroyomero Member
    edited February 2013

    @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? =(

  • raindog308raindog308 Administrator, Moderator

    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).

    For LET support, please visit the support desk.

  • raindog308raindog308 Administrator, Moderator

    Good point @yomero - this was in MyISAM.

    For LET support, please visit the support desk.

  • @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

  • @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.

    Fusioned | KVM SSD VPS | LSI RAID10 | Netherlands 1Gbps | R1Soft | IPv4 & IPv6 | SolusVM
  • raindog308raindog308 Administrator, Moderator

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

    For LET support, please visit the support desk.

  • 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

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

  • SperrymanSperryman Member
    edited February 2013

    image

  • murkymurky Disabled

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

  • raindog308raindog308 Administrator, Moderator

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

    Lots of clicking if you 100 tables...

    For LET support, please visit the support desk.

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

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

  • @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.

    I recommend Prometeus, the best provider ever!

  • raindog308raindog308 Administrator, Moderator

    @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 [email protected] identified by 'pepsi';
    mysql> grant all on letrules.* to [email protected];
    mysql> flush privileges;
    

    As [email protected]:

    mysql> create table sometable ( id integer );
    

    As myql root:

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

    Again as [email protected]:

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

    For LET support, please visit the support desk.

  • 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.

Sign In or Register to comment.