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

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