SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with duplicating a databse

    I need to make a duplicate of a mysql database for testing purposes. It sounds easy enough... but there were problems.

    Here's what I did. In phpMyAdmin, I selected the database I wanted to copy, I did a dump of both structure and data (with send and complete inserts selected) and saved the sql file to my desktop. I then created a new database called FAKE and used phpMyAdmin to run the just saved sql file. The result is an error and only the first of twenty tables is added. On that table I get only the first 94 records out of 186 records in the original table. I have looked at the sql file and can not see any reason why this is happening.

    The error I get just say, "Error" and nothing else.

    Any and all help is welcomed.

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Bah... silly phpMyAdmin, seems to error out on any little thing, I would do it this way. Login with telnet or ssh


    $>mysqldump -u username -p dbname > dbname.sql


    Create the new databse
    $>mysqladmin -u username -p create newdbname

    Import data
    $>mysqldump -u username -p newdbname < dbname.sql
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    ********* Addict
    Join Date
    Apr 2000
    Location
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree, Freddy. phpMyAdmin has already caused me soooo much trouble. I now prefer doing it the old traditional way: having 2 or 3 largeMS-DOS windows running mysql. I realised how much better it is - unfortunately only very recently.

    I had to use that way a while ago.

  4. #4
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried Freddy's suggestion and I got the following error:

    ERROR 1064: You have an error in your SQL syntax near 'mysqldump -u username -p rma
    > rma.sql' at line 1

    I have changed my username in the example above. I did this thru ssh on a Sun Solaris server.

    Any ideas???

  5. #5
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are doing this from the bash prompt not from the mysql prompt, right? You need to do it outside of MySQL not in the actual mysql console. Let me know if you know what I mean.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  6. #6
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


    If I do it outside of the mysql console I get:

    mysqldump: Command not found

    I get the feeling I'm missing something. I know just enough Unix to be dangerous, so more info will be very helpful.

  7. #7
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm... Is this your server? Seems like the path to mysql/bin is not in your users path, what is weird is that you can type in mysql and get in but you can't type in mysqldump, try doing a locate on mysqldump

    $>locate mysqldump

    You may need to specify the whole path like


    $>/usr/local/mysql/bin/mysqldump -u username -p dbname > dbname.sql

    Now don't take that literal as I am sure your location of mysql is probably somewhere else.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  8. #8
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I got it to work

    The problem was that I needed to execute the script from the bin directory like this:

    ./mysqldump -u username -p dbname > dbname.sql


    To insert the sql file:

    ./mysql -u username -p newdbname < dbname.sql

    Note the "./" and the use of mysql not mysqldump for the insert.

    Silly Unix.

  9. #9
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My mistake on the mysql vs mysqldump on the insert, I knew that and am terribly sorry for steering you down the wrong path. Also like I sadi the reason you must run it from the bin folder is because it isn't in your path. You can add it very easily, which would allow you to run those commands from any folder on your system. Although I am not sure on the exact syntax on Solaris to do this.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.


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
  •