SEARCH

How-To Geek

Dump just the table structure to a file in MySQL

For this exercise, we will use the mysqldump utility the same as if we were backing up the entire database.

Syntax:

mysqldump -d -h localhost -u root -pmypassword databasename > dumpfile.sql

The only option that is different than creating an entire backup is the -d switch, which tells mysqldump not to output the data.

Example:

mysqldump -d -h localhost -u root -p2Uad7as9 database01 > dumpfile.sql

Lowell Heddings, better known online as the How-To Geek, spends all his free time bringing you fresh geekery on a daily basis. You can follow him on if you'd like.

  • Published 09/11/06

Comments (10)

  1. Girish


    –Use this to take only the Table structure of the database table in Linux –

    On the terminal
    [root@localhost ~]# mysqldump -uroot -proot123 -h200.200.200.12 mydatabasename tbl_mytable_name –no-data > /tmp/tbl_mytable_name_dump.sql

    Remember , if u dont redirect it to a file, it will stores the dump at pwd by the name “oot”

    Regards,
    Girish :-)

  2. Girish

    u can restore it in the database using ,,,,
    mysql > source /path/to/a/dump/file

  3. shravan

    Thanks. It helps!

  4. scrumpyjack

    ok, say you have two dbs very similar in structure. one you use as a master template which goes through various revisions, let’s say we’re now on revision 3. but the second db is a copy of the template as it was at revision 1.

    because db 2 is in use, you don’t want to drop any tables, but you do need to update the structure of the entire db to the latest version of db 1.

    is there any method of doing this without having to manually go through and make all the same changes again and again (over many dbs at various versions) and without losing any of the data?

  5. Geert

    *Bump* for scrumpys question. I’m interested in this question also. I have multiple databases running all using same structure but different data. How to update all structures based on a structure dump without affecting data?

  6. Apex

    Bump… I too would like to know if this is possible.

  7. Shane

    Instead of making the changes manually write your changes out in a file as SQL statements and then run the batch of SQL statements on each server. We keep a file called sql_changelog.txt in our SVN repository that gets updated with all structural changes between pushes. Then we go out to a BETA system, run the batch, and verify that nothing was broken, then repeat the process up into production.

  8. Jay

    Helpful indeed Girish! Thanks!

  9. Sai Ram Gone

    How can i pull the Views with out tables and tables data?

  10. Samrat Gavale

    Quite helpful. Thanks a lot!!!!!!!!!

Get Free Articles in Your Inbox!

Join 134,000 newsletter readers

Email:

Go check your email!