Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2002
    Posts
    302

    How to copy a database

    I am trying to move a database from one server on which I have a shared hosting account to another. As cpanel's db backup/restore does not work (I read this is a known issue) I tried to dump the db using phpmyadmin and then restore it on the other server, again using phpmyadmin.

    The restoration kept failing, although I tried different options for creating the sql. No matter what I tried, I always got syntax error messages when I tried to restore the file.

    I am not an expert on mysql, but the syntax seems correct, and it seems reasonable to believe that an sql created that way should be correct.

    So what could be wrong, and is there a better way I can use to copy databases?

  2. #2
    Join Date
    Aug 2004
    Location
    Miami
    Posts
    65
    You have several options.

    1) Do a dump using phpmyadmin, don't use the gzip format but rather do it in plain format. Once i's done open the result in wordpad or notepad and then open your new host and dump it step by step (I don't know how big is your db) but if it's big dump it in 100's of lines and see where the error comes from and just skip that error and complete the dump by skipping that lin e.

    Another alternative (If you have ssh/telnet access) simple do a mysqldump and then a mysql

    On the old server do:

    mysqldump -uUSER-NAME -pPASSWORD DB-NAME --add-drop-table --complete-insert>backup.sql

    Then move the backup.sql file into the new host and do it the other way arround

    mysql -uUSER-NAME -pPASSWORD DB-NAMEZ<backup.sql

    Either option should work fine.

    Other method (I dought you can do this) is to copy the mysql/db-name folder itself and then restore it

    Using mysql do something like this

    cp /var/lib/mysql/data/YOUR-DB-NAME DESTINATION -R

    then copy the destination folder to the new host and it will work, this may not work because you need ssh and permittions to do it and because it's a shared environment it may not work with your account but you can try
    Shopping Cart Builder - Complete ecommerce suite for simple and advanced shopping cart needs

  3. #3
    Join Date
    Feb 2005
    Location
    Oxfordshire UK
    Posts
    19
    You could use mysqlfront. Its windows based and you can import/export sql files. I think you can get a free trial from vendor.

  4. #4
    Join Date
    Jun 2002
    Posts
    302
    Thanks for the replies.

    In the meantime I discovered how to modify the dump file in order to make it readble (the line specifying the table type etc. after the table structure definition is the one causing the trouble). It is very strange, though, that the file created by mysql is unreadable to the very same kind of database...

  5. #5
    Is it (by chance) an invision board database?

  6. #6
    Join Date
    Aug 2004
    Location
    Miami
    Posts
    65
    I've seen it happen, special with comments and ## and // within the sql file dump
    Shopping Cart Builder - Complete ecommerce suite for simple and advanced shopping cart needs

  7. #7
    Join Date
    Jun 2002
    Posts
    302
    No, this isn't an invision board, it happened with all sorts of db.

    The problem was the addition
    ENGINE=MyISAM DEFAULT CHARSET=latin1
    at the ends of create table statemens:
    Code:
    CREATE TABLE ... (
     ...
    )  ENGINE=MyISAM DEFAULT CHARSET=latin1;
    When I removed "ENGINE=MyISAM DEFAULT CHARSET=latin1" mysql was able to execute the files.

  8. #8
    Join Date
    Aug 2004
    Location
    Miami
    Posts
    65
    Then you have your answer

    Probably your new server mysql version is older than the old one and does not support ENGINE=MyISAM DEFAULT CHARSET=latin1

    I also have seen this happen. I guess if you upgrade mysql to the lastest version this won't happen or do it as you did, search and replace "ENGINE=MyISAM DEFAULT CHARSET=latin1" with ""
    Shopping Cart Builder - Complete ecommerce suite for simple and advanced shopping cart needs

  9. #9
    Join Date
    Jun 2002
    Posts
    302
    Well, the problems aren't over. Due to these modifications, some of the text being pulled out of the db appears currupted, due the the use oa a wrong chatacter set, I suppose.

    Any idea how to change that?

  10. #10
    Join Date
    Feb 2005
    Location
    Oxfordshire UK
    Posts
    19
    I have just moved a db from one cpanel host to another. I did it by exporting each table to my work station as an sql file. I then opened these in wordpad, removed the table sructure info, just leaving the 'insert' text. I then saved these as txt files and imported into new db as txt files using phpmyadmin.

    It takes longer, but in this case it worked. In some tables I had to delete the existing data before the txt file would load.


    Hope this helps.

  11. #11
    Join Date
    Aug 2004
    Location
    Miami
    Posts
    65
    If MySQL version and speciall charsets are different from the two hosting companies, there isn't much you can do besides do a search & replace to the strange keywords your new host mysql complaings and simple change them.
    Shopping Cart Builder - Complete ecommerce suite for simple and advanced shopping cart needs

  12. #12
    Join Date
    Jun 2002
    Posts
    302
    Thank you very much. I suppose you are right and manual editing is inevitable.

    It is disappointing to see there are such incompatibilities between versions.

  13. #13
    Join Date
    Aug 2004
    Location
    Miami
    Posts
    65
    It's not an actuall incompatibilities you know

    It's just that your new host choosed not to install those languages support for some reason. Try nagging your host and see what he says.
    Shopping Cart Builder - Complete ecommerce suite for simple and advanced shopping cart needs

  14. #14
    Join Date
    Jun 2002
    Posts
    302
    If the new host does not have support for certain languages installed, would that result in syntax errors when reading a dump of another database?

    Anyway, my new host promised to upgrade their mysql so as to eliminate the chance for version incompatibilites.

Posting Permissions

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