MySQL 5.0.22 export dump file not importing - syntax errors

Question

Hello,

I backed up my db with mysqldump from phpMyAdmin. Using MySQL 5.0.22. Made no changes to database file. Import fails. Found many instances of extra spaces using notepad, but now cannot find any other such extraneous spaces. Error is 1064.

Any suggestions on how to import file properly?

Thanks.

Answer 1

You have done several things wrong here

1. Using PHPMyAdmin for anything critical, especially backups. It is not production-ready, in my experience. Feel free to use it for unimportant read-only work on noncritical servers however.
2. Editing mysqldump files with notepad (or any other editor). Despite appearances, mysql dump files are not text files and should not be edited with any editor. They contain binary data which is not valid in most character encodings, and therefore can probably not be loaded/saved without introducing errors.

Make a fresh dump using mysqldump, which is the only reliable way of making them, and import that. Do not edit mysql dump files using notepad or any other text tool (this includes the likes of grep, sed etc).

If you need to edit a mysql dump file, then restore it into another (i.e. non-production) database instance, make the necessary changes using SQL commands and re-dump the database. This may be slow but it's reliable.