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

GeneR · Jun 6, 2003

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!

arn · Jun 6, 2003

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 · Jun 6, 2003

Agreed

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 · Jun 6, 2003

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

AnotherMortal · Jun 6, 2003

Really?

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

Rower_CPU · Jun 6, 2003

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 · Jun 6, 2003

Hmmm.

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 · Jun 6, 2003

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 · Jun 6, 2003

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 · Jun 6, 2003

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 · Jun 7, 2003

Additionaly Note

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 · Jun 8, 2003

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

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!

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.

Search

Search

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

GeneR

macrumors 6502a

arn

macrumors god

AnotherMortal

macrumors regular

ddtlm

macrumors 65816

AnotherMortal

macrumors regular

Rower_CPU

Moderator emeritus

GeneR

macrumors 6502a

backspinner

macrumors 6502a

mrjamin

macrumors 65816

ddtlm

macrumors 65816

AnotherMortal

macrumors regular

visor

macrumors 6502

Our Staff