CODEWITHSUNDEEP
mysqlcopydatabase-schema
Pramod
Pramod

Reputation: 2896

copy database structure without data in mysql (with empty tables)

Is there any way to copy database structure without data in MySQL, so the new database will be the same as it is copied from, but with empty tables.

After getting some suggestions I tried the command, but I am getting syntax error, my username = root and password = nothing. I guess the default one. I am trying following command, 

mysqldump -u root -p -d xyz_db | mysql -u root -p -Dnew_db

what I am missing or misplacing in command?

Upvotes: 59

Views: 70204

Answers (7)

Puneet
Puneet

Reputation: 29

As the manual says in Copying Databases you can pipe the dump directly into the mysql client:

mysqldump db_name | mysql new_db_name

If you're using MyISAM you could copy the files, but I wouldn't recommend it. It's a bit dodgy.

Integrated from various good other answers

Both mysqldump and mysql commands accept options for setting connection details (and much more), like:

mysqldump -u <user name> --password=<pwd> <original db> | mysql -u <user name> -p <new db>

Example : mysqldump -u root -pmypassword old_database | mysql -u root -pmypassowrd new_database

Upvotes: 0

bortunac
bortunac

Reputation: 4808

mysqldump -d is not enough because in TRIGGERS, PROCEDURES etc. you can have references like 'old_db'.tb_name that must be replaced with 'new_db'.tb_name

mysql -u user -ppass -e "CREATE DATABASE new_db" 

mysqldump -u user -ppass -d old_db |     # pull structure  -d flag  
sed "s/\`old_db\`\./\`new_db\`\./g" |    # solve foreign_keys, triggers etc 
sed 's/AUTO_INCREMENT=[0-9]\+/''/g' |    # reset indexes
mysql -u user -ppass new_db              # push structure

below is a bash script that also creates target database and perform some checks

# ./db_clone.sh  old_db  new_db

I use a file /root/.mysqlcnx with credentials (instead of using -u root -pPass)

[client]
user = root # or powerfull_user with GRANTS 
password = pass_string
host = localhost

and the following script to clone a database [ experiment it on a virtual machine for confidence ]

#!/bin/bash

if [ $# -lt 2 ]; then
   echo "syntax:\n.\db_clone.sh source_db_name target_db_name [--drop]\n"
exit 1
fi

    # checking target database $2 exits
tg_db=$(mysql --defaults-extra-file=~/.mysqlcnx -s -N -e "SELECT count(*)>0 FROM 
         information_schema.SCHEMATA where SCHEMA_NAME='${2}'")
if [ $tg_db = '1' ]; then
    # checking target database has tables
    tg_tb=$(mysql --defaults-extra-file=~/.mysqlcnx -s -N -e " SELECT count(*) > 0 
            FROM information_schema.TABLES WHERE TABLE_SCHEMA='${2}'")
  if [ $tg_tb = '1' ]; then
     if [ $# -eq 3 ] && [ "$3" = '--drop' ]; then
        echo "TYPE 'YES' (case sensitive) TO OVERRIDE"
        read opt # request confirmation 
        if [ "$opt" != "YES" ]; then
            echo "OPERATION CANCELED!"
            exit 1
        fi
     else
       printf "DATABASE \"${2}\" EXISTS AND HAS TABLES\nUSE --drop as 3rd arg to override!\n"
       exit 1
     fi
  fi
else 
  mysql --defaults-extra-file=~/.mysqlcnx -e "CREATE DATABASE \`${2}\`" 
fi

echo "CREATING ${2}"
mysqldump --defaults-extra-file=~/.mysqlcnx -d $1 | # dump source
sed "s/\`${1}\`\./\`${2}\`\./g" |  #  solving `source_db`.tb_name       
sed 's/AUTO_INCREMENT=[0-9]\+/''/g' | # solving  AUTO_INCREMENT=1
mysql --defaults-extra-file=~/.mysqlcnx $2  # populating target_db 
echo "DONE"

Upvotes: 1

lampwins
lampwins

Reputation: 920

From electrictboolbox.com/mysqldump-schema-only:

Dumping the database structure for all tables with no data Add the -d flag to signify that no data should be included in the output like so, where "mydatabase" is the name of the database to dump, and "someuser" is the login name used to connect to the database. The following command will dump the table structure for all tables in the specified MySQL database:

$ mysqldump -d -u someuser -p mydatabase

The -d flag says not to include data in the dump. Alternatively you can use --no-data instead if you find that easier to remember:

$ mysqldump --no-data -u someuser -p mydatabase

The -u flag indicates the username and the -p flag that a password will be supplied. After pressing you will be prompted for the password.

Alternatively, the password can be supplied on the command line, but there must be no space between the -p flag and the password. For example, if the password was "apples" do this:

$ mysqldump --no-data -u someuser -papples mydatabase
$ mysqldump -d -u someuser -p mydatabase > mydatabase.sql # This will output to a sql file

Upvotes: 3

Saharsh Shah
Saharsh Shah

Reputation: 29051

You can take backup using mysqldump and restore with mysql using commandline.

For backup database

$ mysqldump -u root-pPassword -P3309 --routines --no-data testdb > "d:\dbwithnodata.sql"

For restoration of database

$ mysql -u root-pPassword -P3309 newdb < "d:\dbwithnodata.sql"

Upvotes: 11

Phoenix
Phoenix

Reputation: 1530

Try this one:

$ mysqldump --no-data -h localhost -u root -p database_name > imported_db_name.sql

Upvotes: 1

Rab
Rab

Reputation: 35572

mysqldump -u user -ppass -d olddb | mysql -u user -ppass -D newdb

The new database must already exist. The -d flag in the mysqldump command prevents copying of data.

There's no space between the flag -p and the password.

Upvotes: 82

Vinod Kumar
Vinod Kumar

Reputation: 489

You can backup you MYSQL database structure with

mysqldump -u username –p  -d database_name > backup.sql

(You should not supply password at command line as it leads to security risks.MYSQL will ask for password by default.) And you can create create tables in database with 

mysql -u username -p new_database < backup.sql

Now you can use pipe to give the output of first command as output for second one and you will no longer need backup.sql

mysqldump -u username –p  -d database_name|mysql -u username -p new_database

All tables in will be created in new_database without data.

Upvotes: 3

Related Questions