Mysql restores recovery structure and data from a given backup (schema.sql)

Question

Hi, I am using the mysql admin and restored the backup files (backup.sql). I would like to use structureless recovery without data, and this does not give me the opportunity to do this. I understand that phpadmin provides this. However, I cannot use this. Can anyone tell me an easy way?

Answer 1

Dump database only:

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

This will do everything in the backup.sql file, except for the INSERT lines that would populate the tables. After that, you should have a complete table structure along with any stored procedures / views / etc. That were in the original data table, but your tables will be empty.

Answer 2

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 simply swallow the INSERT statements and the tables will remain empty. Of course you must change the ENGINE again using:

 ALTER TABLE `mytable` ENGINE=MYISAM; 

Answer 3

IIRC backup.sql files (if mysqldump created them) are simply SQL commands in a text file. Just copy all the "create ..." instructions from the beginning of the file, but do not paste it into another file, and with mysql <newfile you should have an empty database without any data in it.

Answer 4

There is no way to tell the mysql client to skip the INSERT command. the smallest way to do this is to run the script as-is and let it load the data, and then just TRUNCATE all the tables.

Answer 5

you can write a script to do the following:

1: import the dump into a new database.

2: truncate all tables using a loop.

3: export db again.

4: now u just has a structure

Answer 6

You can back up the MYSQL database structure with

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

(You should not specify a password on the command line, as this leads to security risks. MYSQL will ask for a default password.)