I am using MySQL Administrator for making my database backup. I can perfectly back up the whole database with all its tables. There are some tables whose size is very big so I wonder if I could only back up the tables' structure (only their elements) but not their data.

3 Answers 3


Use the --no-data switch with mysqldump to tell it not to dump the data, only the table structure.

This will output the CREATE TABLE statement for the tables.

Something like this

mysqldump --no-data -h localhost -u root -ppassword mydatabase > mydatabase_backup.sql

To target specific tables, enter them after the database name.

mysqldump --no-data -h localhost -u root -ppassword mydatabase table1 table2 > mydatabase_backup.sql



  • Thanks a lot LukeR. I could managed with that. At the moment of writing i didn't even know anything about mysqldump but i could solve the problem with the --no-data switch. Then i also found in MySQL Administrator the "CREATE SCRIPT" of that table so i could have resolve this problem with this alternative also.
    – user16629
    Aug 18, 2009 at 14:00

as LukeR said, the --no-data option to mysqldump will do what you want.

to add to that, here's a backup script i wrote that dumps all mysql databases to plain text files, and creates separate dump files for each database's table schema and data (it's quite common to want to restore or create the tables on another mysql server WITHOUT the data, and that's a lot easier to do when you already have a small file with just the CREATE TABLE/CREATE INDEX etc commands)

#! /bin/bash

# backup-mysql.sh
# Craig Sanders <[email protected]>
# this script is in the public domain.  do whatever you want with it.


ARGS="--single-transaction --flush-logs --complete-insert"

DATABASES=$( mysql -D mysql --skip-column-names -B -e 'show databases;' | egrep -v 'information_schema' );


YEAR=$(date +"%Y")
MONTH=$(date +"%m")
DAY=$(date +"%d")



for i in $DATABASES ; do
  echo -n "backing up $i: schema..."
  mysqldump $ARGS --no-data -u$MYUSER -p$MYPWD $i > $i.schema.sql

  echo -n "data..."
  mysqldump $ARGS --skip-opt --no-create-db --no-create-info -u$MYUSER -p$MYPWD $i > $i.data.sql

  echo -n "compressing..."
  gzip -9fq $i.schema.sql $i.data.sql
  echo "done."

# delete backup files older than 30 days
OLD=$(find $BACKUPDIR -type d -mtime +30)
if [ -n "$OLD" ] ; then
        echo deleting old backup files: $OLD
        echo $OLD | xargs rm -rfv
  • Thanks a lot Craig for your answer! I just pick up LukeR answer as it was first. Thanks for the script, it's a good reference.
    – user16629
    Aug 18, 2009 at 13:49
  • May I suggest adding --routines to the schema script?
    – Jonathan
    Oct 24, 2019 at 11:17

You can also manually do this through the mysql commandline interface by doing a DESCRIBE <tablename> and copy/pasting the results.

  • DESCRIBE won't give you something you can copy and paste. SHOW CREATE TABLE will. Aug 15, 2009 at 3:31
  • it's not a dump of what the CREATE statement would need to be, but it does show column types and relations
    – warren
    Aug 15, 2009 at 9:59

You must log in to answer this question.