SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    ********* Celica Lover Coomer's Avatar
    Join Date
    Apr 2002
    Location
    Not worth the drive
    Posts
    474
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Copying databases in mysql?

    Hi,
    Is there a simple way to copy a database to a different database name? I've tried exporting and importing the data and database structure in phpmyadmin, but that always errors out. I've also tried doing an mysqldump and then importing it from the mysql command line, but that errors out as well. Does anyone have any ideas on what I could do?
    + Celica =
    6G Celicas :: My '94-99 Toyota Celica resource

  2. #2
    morphine for a wooden leg randem's Avatar
    Join Date
    Jun 2002
    Location
    .chicago.il.us
    Posts
    957
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I can't say whether or not this is an acceptable method, but it's always worked for me to simply navigate to the database folder and copy it.

    I was actually surprised that something so simple worked, but it's kept working every time I've done it. YMMV.
    ----Adopt-a-Sig----
    Your message here!

  3. #3
    ********* Celica Lover Coomer's Avatar
    Join Date
    Apr 2002
    Location
    Not worth the drive
    Posts
    474
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's a good idea. I'll try that, since my mysqldumps and imports aren't working correctly. Thanks!
    + Celica =
    6G Celicas :: My '94-99 Toyota Celica resource

  4. #4
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if you don't have access to the MySQL data directory to copy the database, you can create `db2' and run this command:

    Code:
    mysqldump --opt -uuser -ppass db1 | mysql -uuser -ppass db2
    then you can drop `db1'.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  5. #5
    ********* Celica Lover Coomer's Avatar
    Join Date
    Apr 2002
    Location
    Not worth the drive
    Posts
    474
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your code didn't seem to work either. Maybe I have an error in the structure of one of my tables or something, because not even phpmyadmin or the code in the mysql documentation did the trick. Is there a way to go through the database and check for errors or something like that?
    + Celica =
    6G Celicas :: My '94-99 Toyota Celica resource

  6. #6
    ********* Celica Lover Coomer's Avatar
    Join Date
    Apr 2002
    Location
    Not worth the drive
    Posts
    474
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Interesting...I tried the mysqlcheck function and this is what it came up with:

    C:\mysql\bin>mysqlcheck -uroot -pMyPass arr-tech
    arr-tech.info_requests OK
    arr-tech.news OK
    arr-tech.newsletters OK
    arr-tech.product_categories OK
    arr-tech.product_photos OK
    arr-tech.products OK
    arr-tech.trade_shows OK
    arr-tech.usedequipment OK
    arr-tech.usedequipment_photos OK

    I wonder why none of the mysqldump stuff works. Is it possible that mysql itself got corrupted?
    + Celica =
    6G Celicas :: My '94-99 Toyota Celica resource

  7. #7
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what is the error you get when trying to dump and/or import?

  8. #8
    ********* Celica Lover Coomer's Avatar
    Join Date
    Apr 2002
    Location
    Not worth the drive
    Posts
    474
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is the error that I tried to dump the data directly into another table:

    C:\mysql\bin>mysqldump --opt -uroot -pMyPass arr-tech | mysql -uroot -pMyPass test23
    ERROR 1064 at line 139: You have an error in your SQL syntax near 'desc text NOT NULL,
    image text NOT NULL,
    showlink int(11) NOT NULL default '' at line 5

    It created the first three of the nine tables and filled those three with data, but doesn't get past that.
    + Celica =
    6G Celicas :: My '94-99 Toyota Celica resource

  9. #9
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it looks like you have a column named `desc'! that's a reserved word in MySQL. i suggest changing the name of the column.

    BTW, if you created the table with phpMyAdmin, you didn't get an error because it puts backticks around column names (e.g. `desc` TEXT NOT NULL...).

  10. #10
    ********* Celica Lover Coomer's Avatar
    Join Date
    Apr 2002
    Location
    Not worth the drive
    Posts
    474
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah...so that's the problem. I changed the 'desc' fields to 'description' and now my mysqldump works fine. Thanks for the help.
    + Celica =
    6G Celicas :: My '94-99 Toyota Celica resource


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
  •