CODEWITHSUNDEEP
mysqldatabaserestore
Nore
Nore

Reputation:

Mysql restore to restore structure and no data from a given backup (schema.sql)

Hi I use mysql administrator and have restored backup files (backup.sql). I would like to use restore the structure without data and it is not giving me an option to do so. I understand phpadmin provides this. I can not use this however. Any one can tell me an easy way?

Upvotes: 4

Views: 8568

Answers (6)

Vinod Kumar
Vinod Kumar

Reputation: 489

You can backup you MYSQL database structure with

mysqldump -u username –p  -d database_name > backup.sql

(You should not supply password at command line as it leads to security risks.MYSQL will ask for password by default.)

Upvotes: -1

georgepsarakis
georgepsarakis

Reputation: 1957

You can change the ENGINE to BLACKHOLE in the dump using sed

cat backup.sql | sed 's/ENGINE=(MYISAM|INNODB)/ENGINE=BLACKHOLE/g' > backup2.sql

This engine will just "swallow" the INSERT statements and the tables will remain empty. Of course you must change the ENGINE again using:

ALTER TABLE `mytable` ENGINE=MYISAM;

Upvotes: 1

Maelstrom
Maelstrom

Reputation: 3048

Dump database structure only:

cat backup.sql | grep -v ^INSERT | mysql -u $USER -p

This will execute everything in the backup.sql file except the INSERT lines that would have populated the tables. After running this you should have your full table structure along with any stored procedures / views / etc. that were in the original databse, but your tables will all be empty.

Upvotes: 6

Sabeen Malik
Sabeen Malik

Reputation: 10880

you can write a script to do the following:

1 : import the dump into a new database.

2 : truncate all the tables with a loop.

3 : export the db again.

4 : now u just have the structure

Upvotes: 0

longneck
longneck

Reputation: 12226

there is no way to tell the mysql client to skip the INSERT commands. the least-hassle way to do this is run the script as-is and let it load the data, then just TRUNCATE all of the tables.

Upvotes: 0

Kimvais
Kimvais

Reputation: 39638

IIRC the backup.sql files (if created by mysqldump) are just SQL commands in a text file. Just copy-paste all the "create ..." statements from the beginning of the file, but not the "insert" statements in to another file and "mysql < newfile" you should have the empty database without any data in it.

Upvotes: 0

Related Questions