Duplicate a MySQL Database

Tags:
Posted 15. November 2006.

This shell command will duplicate an existing database:

# mysqladmin create DB_name -u DB_user --password=DB_pass && \
mysqldump -u DB_user --password=DB_pass DB_name | mysql -u DB_user --password=DB_pass -h DB_host DB_name

Replace DB_* with your MySQL settings. Where could this be useful besides making a mirror backup of a database?

I am thinking of a Rails app running on a production server. You would like to test major code upgrades or database migrations on the production hardware before you mess with your business data, but don't have money to clone your production hardware.

This approach would simply duplicate the production database and deploy the app on the same server, running on a high port number. Needs a Capistrano recipe to be done!

SubscribeSubscribe to this Feed
del.icio.usSocial bookmark

Comment [6]

  1. Nathan B said 512 days ago:

    Thanks for this quick little script. It saved me a bunch of time! :)

  2. Giorgio said 466 days ago:

    Thx for your idea, I created a rake task
    see this gist

    bye

  3. tlc said 380 days ago:

    Thanks for the script. It was helpful.

    Just one note, for the less experienced users, you may want to clarify the DB_name on which is the new one, which is the existing one.

  4. Photos On Canvas said 171 days ago:

    Thanks for this, what I am really looking to do is duplicate my website into a sub folder without php reading the root files, does anyone have any ideas.

    Thanks

  5. Ashley said 166 days ago:

    Thanks for the nice information. This script will be really helpful for me.

  6. Leo Ju said 116 days ago:

    Quite simple and useful! Thanks for sharing this nice tip.
    I’ve extended this nice one-liner to a script that can handle multiple databases. hope this will be helpful for someone.

    #!/bin/sh
    CREATE_DB=1 # 1 for true, 0 for false
    OLD_DB=( “db1” “db2” )
    NEW_DB=( “db1_dup” “db2_dup” )
    NUM_OF_DB=${#OLD_DB[@]}
    DB_USER=admin
    DB_PASSWORD=invisible
    for (( i=0; i<$NUM_OF_DB; i++))
    do
    if [ $CREATE_DB -eq 1 ];then
    mysqladmin create ${NEW_DB[$i]} -u $DB_USER —password=$DB_PASSWORD
    fi
    mysqldump -u $DB_USER —password=$DB_PASSWORD ${OLD_DB[$i]} | mysql -u $DB_USER —password=$DB_PASSWORD ${NEW_DB[$i]}”
    done

Commenting is closed for this article.

Related articles: