SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist vhogarth's Avatar
    Join Date
    Nov 2003
    Location
    Taxachussets
    Posts
    415
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    help on copying a db file

    Maybe the answer is pretty simple, but i havent had the chance to play with it yet and was hoping one of you guys might be able to tell me how to go about it.

    I'm using mysql and I created a db with numerous tables that contain data. I just recently got a new computer and wanted to clone that db onto the new comp. Is there a sql commmand to copy an entire db? Or would it be better to find the db file using windows explorer and copy/paste the file onto the new computer?

    Going a little further with this scenario. If i want to keep these two db's in sync. How would i go about updating. For example, the new comp will contain all the data that the old one had and going forward all new info will be added to the new comp. How woudl i go about updating the newest records to the old db to keep them exactly the same?

    I was thinking of adding an date_added colum which stores the date info, then perform on a query on x date that i want to add. Store all the results in an array. Loop through the array with a sql update table command. But it seems like it would bogg down the computer to have to loop through that information and everytime it did a pass to execute the update command.

    Is my logic flawed (i'm guessing so) Is there another way to go about getting this end result but via an easier solution? Thanks for the help.

  2. #2
    SitePoint Addict streetlife's Avatar
    Join Date
    Mar 2004
    Location
    D-Town
    Posts
    369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    vhogarth, why would you post this question in the php forum and not the mysql one?

    Use mysql dump, from the command line as follows:

    Code:
    mysqldump -uusername -ppasswordd --opt dbname > dbname.sql
    This will create a textfile with the database contents within. Then on the new server from the command line do:

    Code:
    mysql -p < dbname.sql
    This will write the database to the new server.

    You could also use a web based tool like phpMyAdmin or the windows based mysqlcc to work with both simultaneously. This would make it very easy and quick to back the database up onto the old server.

    If you simply wanted to have the database copied to the old table for backup reasons, then I recommend using mysqldump to copy it exact. If you want it for other purposes, then yes maybe adding modified columns will be the answer.

  3. #3
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can also just copy the folders in your MySQL data directory

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  4. #4
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When using a method similar to streetlife's, I've needed to create the database in the new server and then specify it on the command line.

    Like so:

    mysql -u username -p
    create database newdb;
    quit;
    mysql -u username -p --database=newdb <dbname.sql
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  5. #5
    SitePoint Addict streetlife's Avatar
    Join Date
    Mar 2004
    Location
    D-Town
    Posts
    369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah sorry, missed that bit out, thanks samsm

  6. #6
    SitePoint Evangelist vhogarth's Avatar
    Join Date
    Nov 2003
    Location
    Taxachussets
    Posts
    415
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thanks

    Thanks for the responses, i'll try that out when i get home. Hopefully everything will work.

  7. #7
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by streetlife
    Yeah sorry, missed that bit out, thanks samsm
    I have missed that bit just enough times to remember it. :-)
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?


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
  •