MySql: how to empty the database

Question

So, until today we were testing our website. Now we want to switch to the next level and we need to empty the db in order to do that. This means that we would like to be at the starting point, with all the tables but no data.

There are a lot of tables and cross-references, so maybe truncating table by table is not the best choice..

Googling and asking friends, I guess I got it that the right solution has something to do with schema (more precisely exporting a schema).. is this right for our case? If yes, which are the necessary steps to perform this cleaning?

Debian & mysql 5.1

Answer 1

You should be able to do this with mysqldump

mysqldump -u root -p --no-data dbname > schema.sql

then you can use mysql to clean the database

mysql -u root -p dbname < schema.sql

The defaults are such that the schema.sql file will contain the necessary commandsds to do what you want.

If you want to test this you can do something like

mysql -u root -p -e 'create database test;'
mysql -u root -p test < schema.sql

Then check that the test database has the structure etc that you expect.

Answer 2

About truncating tables with foreign key relationships, just try to disable the DDL to check for this as seen in this answer on StackOverflow.com (connection parameter):

SET FOREIGN_KEY_CHECKS=0;

Then combine it with this query to generate a list of queries to have it truncate all the tables.

SELECT
    CONCAT('TRUNCATE TABLE ', table_schema, '.', table_name, ' ;')
FROM
    information_schema.tables
WHERE
    table_schema = 'name_of_your_db' 
ORDER BY
    table_name DESC;