mysqldump and restore with special characters. øæåØÆÅ

user_432863 0
Viewed 44879

Locally I do this to dump and move a database, upgrading silverstripe 2.3 to 2.4:

mysqldump --opt  --default-character-set=latin1 --skip-set-charset --user=$root -p$password $oldDatabase -r db.sql  

iconv -f LATIN1 -t UTF8 db.sql > db_utf.sql 


CREATE DATABASE $newDatabase CHARACTER SET utf8 COLLATE utf8_swedish_ci; FLUSH PRIVILEGES; GRANT ALL PRIVILEGES ON $newDatabase . * TO '$newUser'@'localhost';  FLUSH PRIVILEGES;
SET NAMES utf8; SOURCE db_utf.sql;

And it works, but on the server Ubuntu 8.04, with mysql Ver 14.12 Distrib 5.0.51a. I get crazy √∏ characters instead of øæåØÆå.

Anyone know where I've gone wrong?

silverstripe mysql
9 Answers

Try to run with the following command:

mysqldump -u root -p database -r output.sql

instead of redirecting the output with arrow '>'

user_6144670

This fixed the issue for me.

  1. Import the double encoded input.sql

  2. Export it again mysqldump -h "$DB_HOST -u "$DB_USER" -p"$DB_PASSWORD" --opt --quote-names --skip-set-charset --default-character-set=latin1 "$DB_NAME" > output.sql

  3. Import clean output.sql

How to restore the database double encoded by mysqldump

user_4328630

This worked for me:

  1. ssh the server and connect.
  2. create db dump running

mysqldump -h my_guid.cloud.database.com -u my_user -p my_database_name -r ~/my_db_backup.sql

The console will prompt and ask for the password and there you can type it, my pwd had special characters so i was able to run this command with ease

user_18192800

Only way that worked for me was to export the utf-8 tables as latin-1 (character set of file: iso-8859-1) in phpmyadmin.

Open the exported file in notepad++ convert to UTF8 (with BOM)

Then upload file and import with SOURCE dump.sql.

user_111608400
Related