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
Solved

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

Posted on 2006-11-27
9
385 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

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…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

840 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