SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast Rob Roye's Avatar
    Join Date
    Jun 2003
    Location
    Indiana, in the Good 'ol U.S. of A.
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Moving a database to another computer

    I want to move a MySQL database from one machine to another. My main problem is they are text-only Linux boxes at different physical locations. The distro is the fantastic Mitel Networks E-Smith server 6.0. The database contains all of my message board information. I was (wrongly) assuming (yes, I know) that the backups were also backing up the database. Now that I know otherwise, I'll be taking pains to also back up the database manually or just get the tape drive.

    My main problem is getting the database moved to the new machine so my message boards will work without having to completely redo them. Any help would be appreciated. I'm fairly new to MySQL and haven't gotten any books yet. (Will be done today however).

    Thanks in advance!
    Seek ye not to enter the realm of the dragon,
    for thou art crunchy and taste good with ketchup...
    Rob Roye Web Design

  2. #2
    SitePoint Columnist
    Join Date
    Nov 2003
    Location
    Ohio
    Posts
    411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You would use mysqldump to generate entire sql structure of your database, including data to then import into a new database on the new server.

    mysqldump info is here - http://dev.mysql.com/doc/mysql/en/mysqldump.html

    To get the dump file into the new server in MySQL - http://dev.mysql.com/doc/mysql/en/mysqlimport.html

    Both of these procedures support command line only environments.
    Freelance System Administrator, Researcher, Writer
    Practical Applications
    Open Sourcery "SitePoint's Open Source Blog"

  3. #3
    SitePoint Enthusiast Rob Roye's Avatar
    Join Date
    Jun 2003
    Location
    Indiana, in the Good 'ol U.S. of A.
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot. I appreciate the information. I'll try it tonight and see how things go.

    Pray for me...
    Seek ye not to enter the realm of the dragon,
    for thou art crunchy and taste good with ketchup...
    Rob Roye Web Design

  4. #4
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    198
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I had something similar too once.

    I located the mysql files on my source server, zipped it all into one file, sent it with fpt to target server, unzipped it all into the right directory, then changed owner and group details to suit with different account name there .. it all worked out nicely when I checked on the new site And i'm a total clueless newbie when it comes to linux stuff (first manual gzip ever, first ftp from commandline, first chown/chgrp, etc ) so that should give a clue as to how easy it could be

    I think you can safely do that as long you keep using the same message board version

  5. #5
    SitePoint Enthusiast Rob Roye's Avatar
    Join Date
    Jun 2003
    Location
    Indiana, in the Good 'ol U.S. of A.
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    Well, I did the mysqldump but have only one problem. What is the file called and where does it put it? I'm new to MySQL admininstration, but am trying to learn.

    I'm also willing to learn how to gzip it and ftp it over, but I don't know what the database files themselves are called.

    Not sure if it matters or not but my command line is via SSH on a remote machine.

    Help?
    Seek ye not to enter the realm of the dragon,
    for thou art crunchy and taste good with ketchup...
    Rob Roye Web Design

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your file would be whatever you called it and can be stored in whatever directory you want.

    Code:
    mysqldump -uroot -p12345 mydatabase > '/usr/local/mysqldumpfilename.txt'
    would give you a file mysqldumpfilename.txt in the /usr/local directory.

    DOn't worry if you goofed up once, just do the dump again and resave the file.

  7. #7
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Here are step by step instructions. They are meant for vBulletin owners but they will work for any database actually:

    http://www.vbulletin.com/docs/html/appendix_3

    Just click on "Moving Servers".
    Wayne Luke
    ------------



Bookmarks

Posting Permissions

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