Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

I would really apreciate your answer...

PD: Sorry for the English Level.

share|improve this question
add comment

3 Answers

up vote 14 down vote accepted

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

share|improve this answer
    
+1 looks about right. –  David Pashley Aug 14 '09 at 21:07
    
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 '09 at 14:00
add comment

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
share|improve this answer
    
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 '09 at 13:49
add comment

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

share|improve this answer
    
DESCRIBE won't give you something you can copy and paste. SHOW CREATE TABLE will. –  David Pashley Aug 15 '09 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 '09 at 9:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.