[ILUG] Mysqldump problem

[P at draigBrady.com]

Eugene van den Hurk wrote:
> Hello,
> 
> Had a look on linux.ie to subscribe to a mailing list but could not find 
> link, so I thought i'd send to this address.

community -> mailing lists -> join gives:
http://www.linux.ie/mailman/listinfo/ilug/

> Apologies if this is not the correct procedure.
 >
> I did a mysql dump of a database between two servers we have using the 
> following command:
> 
> mysqldump --opt <db_name> -u <username> --password=<password> | mysql 
> --host=<hostname> -C <db_name> -u <username>
> --password=<password>
> 
> This command seemed to run ok as it transferred all the structure and 
> data from the original server to the new server
> 
> database.
> However, on closer inspection there is a problem. Many characters in the 
> new database are showing up as a a question mark ?
> 
> (e.g. characters such as ", trademark symbol, and fractions such as 1/3).
> If I edit this data using phpmyadmin and replace it with the relevant 
> symbol manually then it is ok.
> There are hundreds of instances of these characters in the database and 
> to change them manually would be very time consuming.
> Is there and option regarding character sets I can set in the mysqldump 
> that might solve this problem?
> Any help would be appreciated.

I've never used mysql before but I do know that the default
charset changed from latin-1 to utf-8 in version 4.1.2
I'm guessing you're dumping from an old to a new database
and hence getting this error when the new utf8 database has
the invalid characters in it. Try adding the
--default-character-set=utf8 parameter to mysqldump

-- 
Pádraig Brady - http://www.pixelbeat.org
--- Following generated by rotagator ---

Console hotkeys

   Add entries like the following to ~/.inputrc
   "\e[[A":"lynx\C-M" # F2

Gnome 2 hotkeys

   Using gconf-editor edit the following keys as appropriate:
   apps->metacity->keybinding_commands->command_{1,12}
   apps->metacity->global_keybindings->run_command_{1,12}
--