Solved

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

Posted on 2006-11-27
9
387 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 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
Webinar: MongoDB® Index Types

Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

695 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