Howdy, Stranger!

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


Quick Trick: Drop All Tables in MySQL Database in One Step
New on LowEndTalk? Please Register and read our Community Rules.

All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.

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

raindog308raindog308 Administrator, Veteran
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}

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, Veteran

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

  • raindog308raindog308 Administrator, Veteran

    Good point @yomero - this was in MyISAM.

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

  • raindog308raindog308 Administrator, Veteran

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

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

  • SPSP Member
    edited February 2013

    image

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

  • raindog308raindog308 Administrator, Veteran

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

    Lots of clicking if you 100 tables...

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

  • raindog308raindog308 Administrator, Veteran

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