Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

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.

share|improve this answer
    
Yeah, I saw something similar. But once I export the schema in the schema.sql, should I erase the whole db and then restore it by importing the schema or what? And how? –  elect Dec 19 '12 at 22:03
    
Either you can import the schema to a new database and use new database for your site or drop existing database and create DB with the same name and import the schema. In my opinion, 2nd option is good as you don't have to create mysql users again. To be on safe side, keep the backup of your old data. –  mwasif Dec 20 '12 at 8:40
    
@mwasif: Isn't that exactly what I said in the first part of my answer ? –  Iain Dec 20 '12 at 8:43

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;
share|improve this answer
    
No problem with cross-references? –  elect Dec 19 '12 at 22:17
    
@elect Providing you're referring to foreign key constraints in "cross references", you can try disabling that check as in my updated answer. –  gertvdijk Dec 19 '12 at 22:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.