?
Solved

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

Posted on 2006-11-27
9
Medium Priority
?
388 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 19

Accepted Solution

by:
VoteyDisciple earned 800 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

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
 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

741 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