MySQL and character set. Migration (or degradation)

Tuesday, 08 February 2011 14:16
User Rating: / 0

Today I met with the transfer of data between two servers: MySQL 4.0 on FreeBSD (access via SSH) and MySQL 5.0 on Windows XP locally (base package XAMPP Web Servers).

In general, does not matter ... we all know that for any import and export data from one database to another, there are problems with Russian letters. You are probably faced with a bunch of articles on the subject, and they are all good, but today I went a little deeper.

The task was simple - to remove a couple of lines from one plate to the remote Web server. Put phpmyadmin and decide for 2-3 minutes ..And you can keep track of primary keys of the required records and a simple query on a remote server to remove them.

Too all that easy. I took a different route.

On FreeBSD:

 mysqldump -u -p --default-character-set = cp1251 dbname tablename> dump_table.sql 

next step I do on the server, FreeBSD, because buggy notebook(win) will be three hours and 10 yards to open.

Alternative - UNIX editor vi:

vi dump_table.sql 

add two lines to the top of the file:

SET NAMES cp1251;

Then I downloaded the file on your computer, but here is a mysql.exe imported it into its local database. I open a MySQL Query Browser, the Russian text! Hooray! But this is just the beginning ... Removed a couple of lines.Reverse exports in MySQL 4.0 has not turned out. More precisely turned out, only Chinese. MySQL Administrator this also failed, even though all settings are correct.Trying:

mysqldump -u -p dbname tablename> dump_table.sql 

First MySQL 4.0 immediately begins to swear at the command SET NAMES and SET CHARACTER SET and DEFAULT CHARSET. It was not like that in his time (do not ask me where I found this old hosting). These lines can be manually removed if the dump is not large.But what if you export the tables 100? It's simple - include backward compatibility in MySQL:

mysqldump -u -p --compatible = mysql40 dbname tablename > dump_table.sql 

Even better. But the Russian language did not appear. You can set compulsory encoding when exporting:

mysqldump -u -p --compatible=mysql40 --default-character-set=cp1251 dbname tablename > dump_table.sql 

And then away we go ... error: mysqldump: Character set 'cp1251' is not a compiled character set and is not specified in the 'C:\mysql\share\charsets\Index.xml' file. Here I started to pick my.cnf, add various lines to indicate the path to the encodings, all according to the instructions on the official website of MySQL 5. No, nothing has helped ... Program mysql.exe mysqldump.exe and not at all in my config file did not react.Parameter-defaults-file also did not help.

Well, here is the actual solution:

mysqldump --character-sets-dir = C:/xampp/mysql/share/charsets/ --default-character-set=cp1251 -u -p dbname
 tablename> dump_table.sql

funny ... a plethora of forums flippednowhere is written about it, but found the documentation for MySQL 5 in the advanced options to mysqldump. As I understand it, mysql.exe and mysqldump.exe (and others as well) in advance as something that compiled-li, and do not depend on any configuration files, at least in XAMPP. Angry fans of Denver, too, swore at them. In general, it does not matter windows work...;)