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?

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 :)

You could use mysqlfront. Its windows based and you can import/export sql files. I think you can get a free trial from vendor.

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...

Is it (by chance) an invision board database?

I've seen it happen, special with comments and ## and // within the sql file dump

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:
CREATE TABLE ... (
...
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
When I removed "ENGINE=MyISAM DEFAULT CHARSET=latin1" mysql was able to execute the files.

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 ""

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?

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.

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.

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.

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.

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.