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

Discussion in 'Mac Apps and Mac App Store' started by GeneR, Jun 6, 2003.

  1. GeneR macrumors 6502a

    GeneR

    Joined:
    Jan 2, 2003
    Location:
    The land of delusions, CA.
    #1
    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
     
  2. arn macrumors god

    arn

    Staff Member

    Joined:
    Apr 9, 2001
    #2
  3. AnotherMortal macrumors regular

    Joined:
    Jan 14, 2003
    Location:
    Baltimore
    #3
    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>
     
  4. ddtlm macrumors 65816

    Joined:
    Aug 20, 2001
    #4
    You can also transfer the data files themselves, on at least some platforms. I've done that between Windows and Linux.
     
  5. AnotherMortal macrumors regular

    Joined:
    Jan 14, 2003
    Location:
    Baltimore
    #5
    Really?

    What's the name of the file and where is it (usually) located?
     
  6. Rower_CPU Moderator emeritus

    Rower_CPU

    Joined:
    Oct 5, 2001
    Location:
    San Diego, CA
    #6
    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/
     
  7. GeneR thread starter macrumors 6502a

    GeneR

    Joined:
    Jan 2, 2003
    Location:
    The land of delusions, CA.
    #7
    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. :)
     
  8. backspinner macrumors 6502a

    Joined:
    Apr 29, 2002
    Location:
    Eindhoven
    #8
    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).
     
  9. mrjamin macrumors 65816

    mrjamin

    Joined:
    Feb 6, 2003
    Location:
    Strongbadia
    #9
    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.
     
  10. ddtlm macrumors 65816

    Joined:
    Aug 20, 2001
    #10
    GeneR:

    Note that all of this MySQL and Linux talk would apply to OSX as well.
     
  11. AnotherMortal macrumors regular

    Joined:
    Jan 14, 2003
    Location:
    Baltimore
    #11
    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.
     
  12. visor macrumors 6502

    Joined:
    May 13, 2003
    Location:
    in bed
    #12
    Re: Anyone know how to transfer a MySQL database to another host?

    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.
     

Share This Page