1

I'm trying to migrate one database from server A to server B.

The database is mysql. This database has some records with character like ç, ã é, ...

The database encoding is UTF8

So on server A I export like this

mysqldump -u root -p sis > sis3.sql  

Then I open the file (with vi) and the characters are not OK. So I tried

 mysqldump -u root -p sis --default-character-set=utf8 > sis3.sql 

Not OK yet. Then

 mysqldump -u root -p sis --default-character-set=latin1 > sis3.sql 

And now the file seems OK.

The copy

The copy to server B is made from server B using

scp -i p root@0.0.0.0:/home2/sis3.sql ~/

But whenever charset I copy to server B, the file is never OK. the "special" characters are always wrong.

I tried to import on three ways (latin1, utf8, no default), all go wrong.

I import this way:

mysql -u root -p"pwdpwdpwd" --default-character-set=utf8 sis < sis3.sql 

Of course, changing the default character set.

But, as before I import to mysql the file is already "damaged", I think this probably caused over ssh transfer.

Is there a way to transfer the mysql database without this problem?

Servers Info

Server A

Linux version 2.6.32-5-amd64 (Debian 2.6.32-48squeeze6) (jmm@debian.org) (gcc version 4.3.5 (Debian 4.3.5-4) ) #1 SMP Tue May 13 16:34:35 UTC 2014
# locale
locale: Cannot set LC_ALL to default locale: No such file or directory
LANG=en_US.UTF-8
LANGUAGE=en_US:en
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC=pt_BR.UTF-8
LC_TIME=pt_BR.UTF-8
LC_COLLATE="en_US.UTF-8"
LC_MONETARY=pt_BR.UTF-8
LC_MESSAGES="en_US.UTF-8"
LC_PAPER=pt_BR.UTF-8
LC_NAME=pt_BR.UTF-8
LC_ADDRESS=pt_BR.UTF-8
LC_TELEPHONE=pt_BR.UTF-8
LC_MEASUREMENT=pt_BR.UTF-8
LC_IDENTIFICATION=pt_BR.UTF-8
LC_ALL=

Server B

Linux version 4.4.0-0.bpo.1-amd64 (debian-kernel@lists.debian.org) (gcc version 4.9.2 (Debian 4.9.2-10) ) #1 SMP Debian 4.4.6-1~bpo8+1 (2016-03-20)
# locale
LANG=pt_BR.UTF-8
LANGUAGE=
LC_CTYPE="pt_BR.UTF-8"
LC_NUMERIC=pt_BR.UTF-8
LC_TIME=pt_BR.UTF-8
LC_COLLATE="pt_BR.UTF-8"
LC_MONETARY=pt_BR.UTF-8
LC_MESSAGES="pt_BR.UTF-8"
LC_PAPER=pt_BR.UTF-8
LC_NAME=pt_BR.UTF-8
LC_ADDRESS=pt_BR.UTF-8
LC_TELEPHONE=pt_BR.UTF-8
LC_MEASUREMENT=pt_BR.UTF-8
LC_IDENTIFICATION=pt_BR.UTF-8
LC_ALL=
  • You could try piping the file via ssh: ssh user@remote "cat - > new_remote_file.sql" < local_file.sql – Fiximan Aug 31 '16 at 15:51
  • 2
    Try computing a checksum on the file on both machines, to rule out the scp transfer as the source of your issues: md5sum sis3.sql. – user4556274 Aug 31 '16 at 15:59
  • @user4556274 the checksum is the same... D=, so it's system-related – lcssanches Aug 31 '16 at 16:01
  • It's not clear to me that the data in the file created from your first mysqldump command is actually damaged. Just because vi and your particular terminal interpret it as ISO8859-n data rather than UTF8 doesn't automatically mean that mysql on the target server won't do the right thing anyway. Have you actually tried importing the original file and using it in the target application? – roaima Aug 31 '16 at 16:55
  • You can do character set conversion with a command like iconv -f UTF-8 -t ISO-8859-15 < input.txt > output.txt but I'm not yet convinced this is really necessary – roaima Aug 31 '16 at 16:58
2

I found something that can be usefull to others

http://alexking.org/blog/2008/03/06/mysql-latin1-utf8-conversion

The thing is export as latin1 and change the file to "set NAMES utf8", as all of my tables are utf8.

Worked.

1
  1. Check that your terminal or terminal emulator is using UTF-8 encoding.

    locale dumps the configuration of your shell, not of your terminal emulator.

    If you use --default-character-set=utf8, the output will be utf8 encoded. So if your terminal (on server A) does not show it correctly, your terminal configuration is broken.

    E.g. in gnome-terminal menu, select Terminal, Set Character Encoding

  2. If the md5sum checksum of the file, on server A and B, is identical, you can be "sure" that the file was correctly transferred.

  • I don't know If i understand correctly what you pointed. But I'm using Ubuntu 14 with a terminal instance with 2 tabs. If the problem were the terminal, and the checksum is the same, then it should be the same output for files, right? – lcssanches Aug 31 '16 at 17:21
  • Some terminal-emulator can use different encoding for different tabs but it is not very likely. – andcoz Sep 1 '16 at 9:16
0

Its got nothing to do with your terminal emulator.

mysqldump -u root -p sis --default-character-set=latin1 > sis3.sql

mysql -u root -p"pwdpwdpwd" --default-character-set=utf8 sis < sis3.sql

Import the file using the same charset you exported it with.

What you see in the exported file depends on the settings for the tool you use to view it. Don't confuse the issue - see how mysql interprets the data. If your backup is huge then do some experiments with a smaller dataset.

Presumably this is openssh? As long as you're not using some other software in the middle, the the file will not be modified in transit. You can verify this using md5sum or similar.

  • Even using the same charset the imported data is wrong. – lcssanches Aug 31 '16 at 23:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.