Moving MySQL databases

Discussion in 'Site & Server Administration' started by Foxy, Apr 23, 2004.

Thread Status:
Not open for further replies.
0
  1. #1
    Does anybody have experience in moving a MySql DB from say a server in the UK to a server in USA?

    I wish to move such a beast and have no experience so I thought I would ask to avoid some [if any] pitfalls

    eg How do I do it? :)
     
    Foxy, Apr 23, 2004 IP
  2. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,334
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #2
    Couple ways to do it...

    If you have access to the server as an administrator, you can stop mysql altogether and grab the files (on RedHat the command is):

    /etc/init.d/mysql stop

    Another way to do it as an administrator is to SSH (or telnet) into the box and run the backup script that comes with mysql:

    /usr/bin/mysqlhotcopy [database name] [destination]

    Either of those methods will get you access to the raw MySQL files, which you could copy to the new machine and stat the MySQL daemon and it should work (assuming you put them into the right directory for mysqld on the new machine).

    If you don't have access to the server itself, you may need to install something like phpmyadmin, do a dump of your database (both structure and data) and then insert it on the new one (also would use phpmyadmin on the new one).

    - Shawn
     
    digitalpoint, Apr 23, 2004 IP
  3. Tomas

    Tomas Peon

    Messages:
    8
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    'luck' has it i had to do something similar this week whilst having no experience whatsoever with mysql.

    I struggled for quite a while, untill i installed mysql on my local machine and used the command line interface to connect to the website database

    what i did was export the DB to my pc, and then upload it again to the new site. this assumes that the db structure on the new site is already in place and it's the same as the old db.

    the following command exports the db to your pc:

    SELECT * INTO OUTFILE '<YourFileName>.txt' fields terminated by '*' optionally enclosed by '"' lines terminated by '\r\n' from <TableName>;

    to import use :
    load data infile '<YourFileName>.txt' into table <TableName> fields terminated by ";" optionally enclosed by '"' lines terminated by '\r\n';

    there is a command that dumps the whole db in one go as well, but it didn't work for me, so i used the above solution. Luckily i didn't need to transfer a lot of tables.

    Good luck
     
    Tomas, Apr 23, 2004 IP
  4. Foxy

    Foxy Chief Natural Foodie

    Messages:
    1,614
    Likes Received:
    48
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Thankyou guys

    Just what I wanted

    The last thing I needed was to fluff around [if I ever could!] trying to work out titchy problems

    Now this is what sets this forum on the plain above all the others - thankyou Shawn once again for the Forum [and the response] and thankyou Thomas for the "luck has it"

    Yes :D

    PS for Shawn

    1. Shawn you were here when I came on line at 7.30am this morning and did not [as far as I know] leave until about 8.45am [00.45am your time] - What do you run on - liquid oxygen?

    2. BTW Did you buy that G5?
     
    Foxy, Apr 23, 2004 IP
  5. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,334
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #5
    6 hours sleep. :)

    No new desktop Macs until I can get a dual 3Ghz G5 with 30" flat panel (this summer hopefully). Although I do think I'm going to pick up the new 15" Powerbook. I have a 15" Titanium already, but the backlit keyboard is really nice (and everything else too).

    - Shawn
     
    digitalpoint, Apr 23, 2004 IP
  6. Foxy

    Foxy Chief Natural Foodie

    Messages:
    1,614
    Likes Received:
    48
    Best Answers:
    0
    Trophy Points:
    0
    #6
    mmmm

    I think I will follow your lead with that G5 - this summer too

    I haven't looked at the powerbooks - will do - we run our laptops on 15" ibooks - they are cool
     
    Foxy, Apr 23, 2004 IP
  7. sarahk

    sarahk iTamer Staff

    Messages:
    28,832
    Likes Received:
    4,541
    Best Answers:
    123
    Trophy Points:
    665
    #7
    I just had to do that for a client, the mysql dump was 45MB!
    I had to do it the hard way with downloading the dump and then uploading, not telnet access.

    What I'd do differently next time:
    * get the create table script and then the inserts - this may require some manual editing. Some of the old table had columns like "desc" but the generated script failed because desc needed to be written `desc` - easier to clean up one script then run the inserts.
    * break the insert script up into parts
     
    sarahk, Apr 23, 2004 IP
  8. Foxy

    Foxy Chief Natural Foodie

    Messages:
    1,614
    Likes Received:
    48
    Best Answers:
    0
    Trophy Points:
    0
    #8
    Thanks SarahK

    I will let you all know when, or if, more likely, I succeed first time in moving successfully :)
     
    Foxy, Apr 24, 2004 IP
  9. mopacfan

    mopacfan Peon

    Messages:
    3,273
    Likes Received:
    164
    Best Answers:
    0
    Trophy Points:
    0
    #9
    Unless you're using innodb database tables, you just copy the entire database folder under /mysql/data/ and paste it into the corresponding folder on the new server. The next time you launch the control center, it will 'register' the database. You may need to create/edit users and permissions, but that's all there is to it.

    MN
     
    mopacfan, May 19, 2004 IP
  10. mushroom

    mushroom Peon

    Messages:
    369
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    0
    #10
    Rather pointless you require root access to the server in order to modify the config file and root access to MySql. :confused:
     
    mushroom, Jun 6, 2004 IP
  11. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,334
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #11
    No you don't... you don't have to have root access to use phpmyadmin.
     
    digitalpoint, Jun 6, 2004 IP
  12. mushroom

    mushroom Peon

    Messages:
    369
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    0
    #12
    I agree but one post said install the other said use .
     
    mushroom, Jun 6, 2004 IP
  13. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,334
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #13
    Well you don't need root to install it either.
     
    digitalpoint, Jun 6, 2004 IP
  14. mushroom

    mushroom Peon

    Messages:
    369
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    0
    #14
    Are you saying that you can install it and configure it so that it
    works without root access or knowing MySql's root password?
     
    mushroom, Jun 6, 2004 IP
  15. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,334
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #15
    Correct... You can install and use it with a secondary login/password.
     
    digitalpoint, Jun 6, 2004 IP
  16. onlineshopping

    onlineshopping Banned

    Messages:
    160
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #16
    I am just doing search on it, but really dont find any good results.
     
    onlineshopping, Jun 2, 2008 IP
  17. picos

    picos Active Member

    Messages:
    155
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    81
    #17
    only moving a mysql database, just use import/export tool of phpmyadmin. you can move ur site properly.
     
    picos, Aug 31, 2009 IP
Thread Status:
Not open for further replies.