Duplicate a MySQL Database

Tags: mysql
Posted 2408 Tage zuvor.

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!

Subscribe to this Feed

Comment [23]

Nathan B (http://www.polardesign.com) said 1566 Tage zuvor:

Thanks for this quick little script. It saved me a bunch of time! :)
Giorgio (looks-interesting.blogspot.com) said 1521 Tage zuvor:

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

bye
tlc () said 1434 Tage zuvor:

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.
Photos On Canvas (http://www.canvasprintsonline.com) said 1225 Tage zuvor:

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
Ashley (www.dataladder.com) said 1220 Tage zuvor:

Thanks for the nice information. This script will be really helpful for me.
Leo Ju () said 1171 Tage zuvor:

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
photo enlargement (http://www.theimagebox.co.uk) said 1034 Tage zuvor:

Thanks for the script, Keep sharing, thanks guys
siju () said 1028 Tage zuvor:

thanks it works
Bradford (theblawblog.com) said 953 Tage zuvor:

Great, this works well for a script I’m using to automatically create a development copy of a wordpress site.
Brad Landis () said 918 Tage zuvor:

You might also want to grant ability to the user to the new database:

bc. GRANT ALL ON DB_new.* TO “DB_user”@“localhost” IDENTIFIED BY ‘DB_pass;

Assuming localhost is where you the host the user is on.
timani (http://timani.net) said 909 Tage zuvor:

Yes this is just what the doctored ordered. It is almost a daily operation now a breeze. I actually may try the script by #6 but this definitely worked for me
alex () said 837 Tage zuvor:

Hey guys,
Thanks for this post.
In my case i need to duplicate database from remote server to my netbook so i can work on web sites in subways etc.
I came up with the following batch file, all the folders are absolute, so i dont need to change paths, aslo all the confirmations are supressed. Maybe somebody will use it as well

you have to change patchs to the files as well as user,password,db name and hosts for both databases of course.

(in my case local database name will be “test”)

regards!

copy this into new file with extention : “.cmd”
for example into duplicate.cmd

——————

“c:\Program Files\MySQL\MySQL Server 5.1\bin\mysqladmin.exe” drop -u root —password=mypassword —force —silent test

“c:\Program Files\MySQL\MySQL Server 5.1\bin\mysqladmin.exe” create -u root —password=mypassword —force —silent test

“c:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump.exe” -u remote_db_user —password=remote_db_pass —host=database_host.com remote_db_name | “c:\Program Files\MySQL\MySQL Server 5.1\bin\mysql.exe” -u root —password=mypassword test

pause

———————
AJi Prabowo (http://ajiprabowo.wordpress.com) said 829 Tage zuvor:

great info,.. thank you.

regards,
AJi
DeDu (http://www.dedu.ch) said 730 Tage zuvor:

Thank you for this one liner! Saved me a lot of time ;P
Fire () said 598 Tage zuvor:

Hi,
You used DB_name to represent all the database names. How do one differentiate what database to replace which with? Please see my comments in the brackets below.

# mysqladmin create DB_name (I know this is new_DB_name) -u DB_user —password=DB_pass && \

mysqldump -u DB_user —password=DB_pass DB_name (I assume this is old_DB_name) |

mysql -u DB_user —password=DB_pass -h DB_host DB_name (Which DB goes here?)

Thanks!
Niko (http://www.rubyrobot.org) said 598 Tage zuvor:

Hi.

This should answer your question:
1st line: Create a new database
2nd line: Dump your existing database
3rd line: Import dump into NEW database
Craig (http://www.kararent.com) said 569 Tage zuvor:

Thanks for the script, this is really helpful!!!
Keep up the good work.
danilo di moia () said 532 Tage zuvor:

thanks for the script, very short & useful.
MithunC () said 531 Tage zuvor:

Thank you Niko! I thought I understood, but clarification was needed.
Lucian () said 475 Tage zuvor:

Thanks to all of you. Very useful post, indeed.
Brian (http://cimicdk.blogspot.com) said 453 Tage zuvor:

Hey. Needed this big time for setting up a database to use for integration testing.
I put together a guide for cloning and truncating a mysql database using this method. (have remembered to give you credit ;) )

If you are interested, it is here:
http://cimicdk.blogspot.com/2012/03/clone-mysqldatabase-for-integration.html
spedi (spdeals.com) said 421 Tage zuvor:

thanks. saved lots of time while trying to replicate my sandbox.
arun kumar () said 224 Tage zuvor:

mysqldump -u root —password=root SDP | mysql -u root —password=root -h 127.0.0.1 UGANDA

i have tried.. its working..
SDP is old DB
UGANDA is new DB.

Related articles:

Name		Merken
E-Mail
Web	Don't even bother spamming, comments are moderated.
Nachricht
	Textile-Hilfe