29

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.

Andrew Schulman
  • 9,142

3 Answers3

52

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

quanta
  • 52,423
LukeR
  • 3,176
3

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 <cas@taz.net.au>


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
Pablo A
  • 210
cas
  • 6,841
0

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

warren
  • 19,297