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