... without destroying the database. 

You can do it all in the shell:

  • Create a SQL script from the database to drop all the tables
  • Execute the SQL script against the database

mysql -u[pwd] -p[user] [dbname] -s -e 'show tables' | sed -e 's/^/drop table /' -e 's/$/;/' > dropalltables.sql
mysql -u[pwd] -p[user] [dbname] < dropalltables.sql

... don't work very well using the MyISAM table type. Corruptions and system lock-ups are a-plenty  when you have MyISAM tables with more than 1,000,000 records. The only  reason you would want to use MyISAM tables is to use the full-text searching functionality, and this feature is only marginally better than using the SQL 'like' statement.

Oh hell, my data is no more,
It all lies on the floor!
I used a cheap database,
And now I've lost some face.
MySQL is just such a chore! 

If you are hosting millions of records and need any text-searching capabillity, then your data is probably worth more than the system itself. So go for a proper, but paid-for, RDBMS like Oracle, which also has excellent full-text searching capability.  "Don't spoil the boat for a ha'penny's worth of tar" as they say.

A quick and dirty way of stripping duplicate records out of a MySQL table!, if your table has no indexes or constraints:

Assuming the name of the offending table is customers:

CREATE TABLE customer_dedupe AS SELECT DISTINCT * FROM customers;
RENAME TABLE customers TO customers_dupe;
RENAME TABLE customers_dedupe TO customers;


But what if your original table had indexes?