Solved

Copy Structure and data from one mySql database to a new one on a different hosting account.

Posted on 2006-11-27
9
379 Views
Last Modified: 2008-02-01
I currently have a hosting account with GoDaddy. I installed one of their Value Applications call Coppermine photo album. They automatically created a mySql database (for this program) on a server where I can't access the actual database layout. I need to change and view some of the tables.

What I would like to do is extract all of the data (and structure) to a file, then I will setup a new database (that I CAN access) and I want to import this information.

I have the path, username and password for the database.

I've been reading here about mySqlDump. Is this the correct program to do this? And also I don't understand how to run it (I'm a begginer in web design and mySql).

Thanks.
0
Comment
Question by:esmyhs
  • 5
  • 4
9 Comments
 
LVL 19

Accepted Solution

by:
VoteyDisciple earned 200 total points
ID: 18027000
Yes, mysqldump is the right way to go.  Open a command line, and browse to the "bin" directory where mysql is installed on your local machien.  If it's Windows it's probably somewhere like C:\Program Files\MySQL\bin.  If it's *NIX, it's actually probably already in the path (in which case it doesn't matter where you are -- you can run the command anyway), or it may be, say, /usr/local/mysql/bin/

In any case, the command to run is just:

mysqldump -h hostname -u username -p databasename > some_output_file.sql

You'll be prompted for a password separately after hitting enter (so you don't have to type it on the command line).  This will generate the file "some_output_file.sql" containing all the SQL commands needed to reconstruct the database.  It's that simple.  You could, if you had to, copy and paste the whole thing into some SQL interface, and you'd have it.  That, of course, would be somewhat silly; what'd make more sense is to just feed it directly back to the mysql command-line interface:

mysql -h new_hostname -u new_username -p new_databasename < some_output_file.sql

Again you'll be prompted for your password after you hit enter.


That's it.  Those two commands download the entire database from one host and recreate it in full on another.
0
 

Author Comment

by:esmyhs
ID: 18030983
I moved mysqldump.exe to c:\

I ran it like you said. It asks for the password. I entered it. Then it stays that way for about 30 seconds. Then it closes. I search my hard drive for the file and I can't seem to find it.

This is what I entered in run:

c:\mysqldump -h 10.0.11.146 -u 506145_Ca -p user_50 > coppermine.sql

(I changed the User and Database name a little for security purposes)
0
 
LVL 19

Expert Comment

by:VoteyDisciple
ID: 18031130
As you wrote it there the file will be placed in whatever directory you were in when you ran the command.

If you were in C:\Documents and Settings\bjones\Desktop\ then the file will be on your (i.e. bjones') desktop.

You should be able to do...
dir coppermine.sql

... and see the file after you've created it.

The fact that it ran for 30 seconds suggests pretty strongly that it worked.  If it weren't able to connect or there were some other problem you should have seen that immediately.
0
 

Author Comment

by:esmyhs
ID: 18031163
I ran it from C:\

There is no file coppermine.sql in c:\

I also did a search with Windows search and it can't find it either.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 19

Expert Comment

by:VoteyDisciple
ID: 18031231
Very strange.  You could always give an explicit path where you want it to end up, though that really shouldn't matter.

mysqldump ..... > C:\path\to\destination\of\coppermine.sql


In the UNIX world redirecting to a file will create the file regardless of what the commmand does (or even if it worked right).  I'm pretty sure the same applies in Windows, so not finding the file at all suggests something else is up.  
0
 

Author Comment

by:esmyhs
ID: 18031269
I gave it a direct path of > c:\coppermine.sql

I'm not sure how this works. Am I allowed to send you the exact information some other way that you can try and see if it works?
0
 
LVL 19

Expert Comment

by:VoteyDisciple
ID: 18031456
If it doesn't work for you it's not gonna work any better for me.  (-:


For fun, you could try running mysqldump without the > C:\coppermine.sql at the end at all, so it should just spit out all the SQL directly to the console.

You can limit that (since it'd get surpemely annoying very fast) by adding a table name (perhaps a small table) to the end of the command:

mysqldump -h blah -u me -p database_name table_name


That would at least reveal whether the command is working or not (though any errors should have been reported to the console anyway).


My best guess at the moment is that you just can't create that file in C:\ and some other path would work better (like "My Documents" or some other user-specific space).  Any other problem that I can think up should still have created the file, even if were left incomplete or empty.
0
 

Author Comment

by:esmyhs
ID: 18032218
I tried running it a different way. I opened a command prompt in windows and then ran it. Now at least I can see the error message (before it just closed after running).

The message is: mysqldump: Got error: 2003: Can't connect to MySql server on '10.0.11.146' <10060> when trying to connect.

I got all of the database connection info (User name, Password, etc.) out of the pHp file that coppermine uses to connect to the database.

If Coppermine can connect to the database, shouldn't I be able to connect as well (I know that I don't have direct access because GoDaddy set up the database on a different server and not under my hosting account, since it's a value application installed by them)?

0
 
LVL 19

Expert Comment

by:VoteyDisciple
ID: 18033141
MySQL has pretty decent user control; its entirely possible the credentials you were using are valid only from the server that's running PHP.  If that's the case you may have to install something like phpMyAdmin and export the database from there.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now