+ Post New Thread
Results 1 to 7 of 7
*nix Thread, move mysql to new server in Technical; Ive bought a new server to replace one of the old ones. Im slowly installing all the software, bringing over ...
  1. #1

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,512
    Thank Post
    1,320
    Thanked 469 Times in 306 Posts
    Blog Entries
    6
    Rep Power
    199

    move mysql to new server

    Ive bought a new server to replace one of the old ones. Im slowly installing all the software, bringing over all the configs etc etc.

    Not quite sure how to get the entire mysql accross.

    mysqldump seems to do it database by database - is there any way to export the whole system, users, databases, tables etc in one go, that I can then import directly to the new isntallation on the new server?

  2. #2

    Ric_'s Avatar
    Join Date
    Jun 2005
    Location
    London
    Posts
    7,590
    Thank Post
    109
    Thanked 762 Times in 593 Posts
    Rep Power
    180
    The following will dump all the databases at once to a file called backup.sql:

    Code:
    mysqldump -A > backup.sql
    Simply restroe that into your new instance and job's done!

  3. Thanks to Ric_ from:

    RabbieBurns (20th June 2008)

  4. #3
    contink's Avatar
    Join Date
    Jul 2006
    Location
    South Yorkshire
    Posts
    3,791
    Thank Post
    303
    Thanked 327 Times in 233 Posts
    Rep Power
    118
    Slight amendment to that to save a lot of standing around or large files if it's huge or over the net...
    Code:
    mysqldump -A > backup.sql | gzip -c > backup.tar.gz
    That should pipe the output into a tar.gzip file which is a good deal smaller and you just have to extract at the other end and then restore.

    Makes life a lot easier too if you want a backup on your local machine before you commit.

  5. Thanks to contink from:

    RabbieBurns (20th June 2008)

  6. #4

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,512
    Thank Post
    1,320
    Thanked 469 Times in 306 Posts
    Blog Entries
    6
    Rep Power
    199
    cheers

    How do i re-import it to the new mysql installation?

    Is it as simple as
    Code:
    mysql < backup.sql
    ?

  7. #5

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,512
    Thank Post
    1,320
    Thanked 469 Times in 306 Posts
    Blog Entries
    6
    Rep Power
    199
    Quote Originally Posted by contink View Post
    Code:
    mysqldump -A > backup.sql | gzip -c > backup.tar.gz
    Is that correct ?

    Code:
    -rw-r--r--  1 root root  1740665 Jun 20 15:20 backup.sql
    -rw-r--r--  1 root root       20 Jun 20 15:20 backup.tar.gz
    seems a bit small

  8. #6


    Join Date
    Jan 2006
    Posts
    8,202
    Thank Post
    442
    Thanked 1,032 Times in 812 Posts
    Rep Power
    339
    Quote Originally Posted by RabbieBurns View Post
    Is that correct ?

    Code:
    -rw-r--r--  1 root root  1740665 Jun 20 15:20 backup.sql
    -rw-r--r--  1 root root       20 Jun 20 15:20 backup.tar.gz
    seems a bit small
    it is. try:
    Code:
    mysqldump [options]  | gzip -c > backup.gz
    After you import the database to the new server you will need to reset the permissions.

    in mysql
    GRANT ALL ON *.* TO 'someuser'@'somehost';
    GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';
    MySQL :: MySQL 5.0 Reference Manual :: 12.5.1.3 GRANT Syntax

  9. Thanks to CyberNerd from:

    RabbieBurns (20th June 2008)

  10. #7

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,512
    Thank Post
    1,320
    Thanked 469 Times in 306 Posts
    Blog Entries
    6
    Rep Power
    199
    excellent thanks, I was wondering why the Joomlas werent able to connect to the database.

    That was painless

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 16
    Last Post: 15th October 2008, 03:15 PM
  2. OS X Server MySQL default installation
    By HodgeHi in forum *nix
    Replies: 2
    Last Post: 30th May 2008, 02:37 PM
  3. Replies: 2
    Last Post: 8th March 2008, 02:18 PM
  4. sims server move
    By Oops_my_bad in forum MIS Systems
    Replies: 8
    Last Post: 4th June 2007, 12:49 PM
  5. using mysql & php on a W2K SP2 server
    By beeswax in forum Web Development
    Replies: 44
    Last Post: 29th June 2006, 08:54 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •