How can I back up a table structure but NOT its data in MySQL

Question

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.

Solution 1

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

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_no-data

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

Solution 2

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.

MYUSER="USERNAME"
MYPWD="PASSWD"

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

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


BACKUPDIR=/var/backups/mysql

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

DATE="$YEAR-$MONTH/$YEAR-$MONTH-$DAY"

mkdir -p $BACKUPDIR/$DATE
cd $BACKUPDIR/$DATE

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."
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
fi

Comments

[David Pashley]

+1 looks about right.

[David Pashley]

DESCRIBE won't give you something you can copy and paste. SHOW CREATE TABLE will.

[warren]

it's not a dump of what the CREATE statement would need to be, but it does show column types and relations

[Admin]

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.

[Carko]

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.

[Jonathan]

May I suggest adding --routines to the schema script?