FreeBSD - Tutorials, Security
Home   Archives   Sitemap   About   Contact

How to copy a mysql database using mysqldump

Home NEW! Unix Forum News 100 Tips and Tricks Website Development Server Operating Systems Databases
 Ivorde.ROarrow Databases arrowSecurity and BackuparrowHow to copy a mysql database using mysqldump 

Article Sections

    Hello, Guest !
User name:
Password:
 
Google

 SSD VPS Hosting - Vpsie.com
 Mo.nitor.me
 Ivorde forum
 FreeBSD Tutorials
 Linux LVM Commands
 Free Shell Accounts
 FreeBSD Project
 FreeBSD Handbook
 Advanced Bash-Scripting Guide
 The OpenBSD Project
 Distrowatch
 FreeBSD Handbook


Apache Webserver Home Page

Posted on: 06 Nov 2007
Author: mandrei
Section: Databases | Security and Backup
Views: 10481
Comments: 5 (Add)

How to copy a mysql database using mysqldump
How to create a copy of a mysql database using mysqldump, create database and source commands and make your tests on that copy of your original(production)database (of course, in case you have one server to test it on)..

 



How to create a copy of a mysql database using mysqldump, create database and source commands and make your tests on that copy of your original(production)database (of course, in case you have one server to test it on).

1. Back-up the original database that you want to "clone"

In case you you have a mysql password for user root you need to use mysql(dump) -u root -p dbname and you will be asked for root password by mysql program. If you don't wish to be asked you can youse mysql(dump) -u root -ppassword (yes, one word) dbname to access or backup the database (mysqldump) and you can redirect the output (in case of backup) to a file (preferably .sql file).

$ mysqldump -u root -p dbname >~/db_name.sql

I used home directory absolute path because we will use it in step 3 - Populating the copy of the mysql database.

2. Creating the copy of the mysql database

Now that we have the file that is the database backup (~/db_name.sql) we can go ahead and connect to mysql database using mysql command:

$ mysql -u root -pmypassword
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 812
Server version: 5.1.20-beta-log FreeBSD port: mysql-server-5.1.20

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> create database dbname_copy
mysql> use dbname_copy

note that mysql> is mysql prompt. From here you can give all commands thatyou will give to this mysql db.
1st command creates a new database and 2nd command tells the client to use that database.

3. Populating the copy of the mysql database

(previous step use dbname_copy)

mysql> source ~/db_name.sql


This command populates the copy of your database with the contents of your original database backed up in ~/db_name.sql file.

Now you can check both mysql databases dbname and dbname_copy and see that they are identical and you can perform any tests on dbname_copy database.

This whole operation was similar to a mysql database backup or mysql database move procedure which I will cover in a future tutorial.

Note: dbname and dbname_copy must be changed to your needs.

Helpfull Links:
One command to backup all mysql databases
support.adobe.com - How To: How do I copy or move a MySQL database?

Bookmarks: Echo "How to copy a mysql database using mysqldump" around:
del.icio.usdiggFurlYahooMyWebGoogleBookmarksFaceBookTechnocratti
-------------------advertising-----------------

Other articles in Databases / Security and Backup
» How to backup all mysql databases with one command
» How to set up a password for root user in MySql
» MySQL table - change or rename MySql table name
» Recover a lost MySQL root password
» How to calculate difference in days between two dates in MySQL




Contact webmaster regarding this article
Register or Login to post your article
Hello, Guest ! You can Login or Register to www.ivorde.ro!

 Post comment:

Name:
Title:
Comment:
Please type the word you see in the image (anti-spam verification). Refresh the page if you don't understand the word.
Allowed HTML Tags for comments:<p><strong><em><u><h1><h2><h3><h4><h5><h6><img><li>
<ol><ul><span><div><br><ins><del>

5 comment(s) to How to copy a mysql database using mysqldump:

1. Re: How to copy a mysql database using mysqldump
Thank You by Nick at July 15th, 2011 - 07:41
Concise and easy to understand. Thanks

2. Re: How to copy a mysql database using mysqldump
-h Command does waht? by Jennifer at April 28th, 2011 - 19:21
What does the -h command do? I see some places use it and some do not. For example check this tutorial: http://www.fortasse.com/tutorials/mysqldump-mysql-database-backup/

They use -h. What does it mean?

3. Re: How to copy a mysql database using mysqldump
great job by zarzu at January 28th, 2011 - 08:33
THANKS...3 years later and its still helpful :D

4. Re: How to copy a mysql database using mysqldump
Worked great by Leumas at November 05th, 2008 - 23:30
thanks!

5. Re: How to copy a mysql database using mysqldump
Thank you by Cosmin at April 30th, 2008 - 05:35
Thank you! Excellent tutorial!

   Latest topics on the forum:
 
   Most viewed articles:
How to copy a mysql database using mysqldump - 10481 views
How to change a user's password in AIX with the output from ECHO command - 9575 views
FreeBSD: Add/remove an additional IP alias - 6753 views
Qmail relay to smarthost: How to route all mail to a smarthost - 4465 views
Change user shell on FreeBSD Linux and AIX - 3192 views

   Latest 10 articles:
Qmail relay to smarthost: How to route all mail to a smarthost - 03 Feb 2009
EXIM 4 relay to smarthost: How to route all mail except local domain - 03 Feb 2009
Windows XP: print LISTEN ports and network connections using netstat - 30 Jan 2009
How to cut out first last n characters from each file name, from a filelist - 04 Nov 2008
Mozilla Firefox3 is now released - 18 Jun 2008
How to switch lower case to upper case and upper case to lower case in a string - 17 Jun 2008
How to rename files/directories to uppercase/lowercase character names - 17 Jun 2008
How to convert lower case to upper case letters in a shell script/command - 17 Jun 2008
Unix,Linux,FreeBSD - How to rename a list of files, replacing spaces inside their names - 12 Jun 2008
How to change a user's password in AIX with the output from ECHO command - 21 May 2008


Archives
» 2007  |  June  |  October  |  November  |  December
» 2008  |  January  |  February  |  March  |  April  |  May  |  June  |  November
» 2009  |  January  |  February



Home | Archives | Sitemap | About | Contact

Designed and developed by Andrei Manescu. Optimized for Mozilla Firefox.  
Copyright 2007 Andrei Manescu
All trademarks and copyrights on this page are owned by their respective owners. Comments are owned by those who posted them.
Valid W3 Document Valid XHTML 1.0 Transitional Valid CSS! The FreeBSD Project Viewable With Any Browser