transfer database /w tables to another computer 1

[URLJones]

Hi, I was wondering if it was possible to move my database which I have created in MySQL to another computer? I tried just moving over the database folder (containing all the tables in that database)from the "data" folder onto the other computer, but this resulted in some errors (for example, the data stored in the tables could not be retrieved). Am I missing a file? Is there a better way? Any help would be much appreciated.

Thanks,

URL

 

[aardvark92]

When I tried moving a database to another computer by copying all the files, I found that I had to change the file permissions after copying.

Other than that, I would suggest checking to be sure the two computers have the same data path for MySQL.

 

[URLJones]

hmmm, I think the file permissions are ok, but I'll check that out tonight (after work).

I'll have to check with the owner of the 2nd computer to see what version of MySQL he has.

What happened was that I created a webpage for this other person to put on his server. So, I populated the databases with the existing data he had on his old site. Now, I'm in the process of migrating the site to his computer/server. So, I gave him all the necessary files, including the database I used to store everything in. But, when he ran the database, trying to access a table resulted in an error. Namely, it said it was missing an "ibd" file. The only thing I could think of was that the "ib_data" file located in MySQL's data folder was responsible for this error. He already has this file on his computer, but I don't want to overwrite his file with my file. If there was a way to merge the two, or create a whole new "ib_data" file, that would be great, but I just don't know.

I also tried, on the off-chance that it might work, the "mysqldump" function, but that didn't really do anything as far as I know. I thought maybe it would create a separate back-up copy of the database that I could use to bring over to this other guys' server. But, no luck.

By the way, thanks for the help aardvark29. Much appreciated.

If there are any other ideas out there, they are all welcome

Thanks again,

URL

 

[URLJones]

I checked with the client, and they are running the same version of MySQL, namely version 4.1. as for the file permissions, they seem to be enabled for read and write privelages.

If there are any other suggestions, I'll try anything.

thanks,

Ryan

 

[TonyGroves]

The most reliable way to transfer tables would be to use mysqldump to dump the database to a file:
[tt]
mysqldump -u username -ppassword -h hostname databasename >filepath
[/tt]
Then, at the other end, execute the SQL commands contained in the file:
[tt]
mysql -u username -ppassword -h hostname <filepath
[/tt]

 

[URLJones]

Tony, thanks a ton! That was a great help! The commands you listed worked like a charm. I had to perform a slight variation on what you gave, namely:

mysqldump --user=username --password=password databasename >filepath

where, in my case, username = root.

the mysql statement was done in a similar manner. Regardless, it was a great help.

Thanks,

URL