Anyone know how to transfer a MySQL database to another host?

[GeneR]

I'm currently migrating from Hypermart.net to Neureal.com (I was going to transfer to Lycos.co.uk, but that fell through when they added these really annoying frames) and I wanted to to know how I can transfer databases from one host to another.

I really have next to nothing in terms of understanding of MySQL. Most of what I do know is just a bunch of trial and error. However, if someone could tell me how I move data to the new host I'd appreciate it. Thanks!

:D

[arn]

do you have command line access at your current host and your new host?

The easiest way is to do a dump into a .txt file and then import it at your new host.

Tutorial:

http://www.theprojects.org/tutorials/mysqldump.php

[AnotherMortal]

I'd have to agree with Arn on this one. However, without command-line you could also use PHPMyAdmin (its almost always top ten on Sourceforge.net, look there for it) and export/import it. Its easy to use, and a nice front-end for web admin of MySQL databases.

The command to export from MySQL is below. Replace <xxxx> with appropriate info for your database. The -p option will prompt for your password.

mysqldump -u <username> -p <databasename> > <filename>

[ddtlm]

You can also transfer the data files themselves, on at least some platforms. I've done that between Windows and Linux.

[AnotherMortal]

What's the name of the file and where is it (usually) located?

[Rower_CPU]

It's more than one file, actually. It's a directory based on the name of the database, with several files inside.

Mine is located at /usr/local/mysql/data/

[GeneR]

This is kind of greek to me: "I think I understand, no, wait, I don't..." ???

I changed my name servers listed over at GoDaddy.com, and now I'm waiting for my domains at the new host (Neureal.com) to become available. Right now, the names are still pointed at my old host (hypermart.net).

At this point, I don't think I have command line access.

I do believe that I can use phpAdmin. However, to be fair to everyone here, it sounds like I just need to get my butt in gear and go down to the nearest Borders Books and read up on how exactly MySQL works, how Linux works, and how to backup the data.

I tried see if I could figure out what AnotherMortal was saying by going to my old Lycos.co.uk accounts that DO have the MySQL and phpAdmin installed. However, not much success there.

Anyway, thanks a lot for the help guys. I'll keep coming back to this thread to see what else I don't know. Thanks again. :)

[backspinner]

If you can access the database from outside your server, try CocoaMySQL. It's a nice and simple but powerfull OS X program to access a MySQL database somewhere else or on your own system (it asks for host address, username, password and port).

[mrjamin]

Once you manage to export the data, sign up for a free account with http://portlandsql.com and use that as pure DB hosting, that way next time you move host, you won't have to worry about switching DB's again.

edit: just checked and it looks like they've stopped the service, d'oh.

[ddtlm]

GeneR:

However, to be fair to everyone here, it sounds like I just need to get my butt in gear and go down to the nearest Borders Books and read up on how exactly MySQL works, how Linux works, and how to backup the data.
Note that all of this MySQL and Linux talk would apply to OSX as well.

[AnotherMortal]

MySQL would only be installed if one downloaded the package and installed it, unless you're running OS X server, where MySQL is included. Also, some linux nuiances are specific to OS X, and OS X uses a different default shell than Other Linux distros, like RedHat and Mandrake.

[visor]

Originally posted by GeneR

I really have next to nothing in terms of understanding of MySQL. Most of what I do know is just a bunch of trial and error. However, if someone could tell me how I move data to the new host I'd appreciate it. Thanks!

:D

As many have posted, the best way to do it is create a dumpfile.

The best way to create a dumpfile is run mysqldump on your mac
%mysqldump --opt -h hostname.provider.com database -uuser -ppassword >dumpfile.sql

however, you'l most likely not be able to access the mysqlport on your provider.
also you need to dl theclinet from mysql.com

using mysqlphpadmin is ok

creating dumpfiles with cocoamysql is painfully slow. I would advise against it if you have some real data tables in your database (>10000 entries)
For tiny tables it is still ok though.