字符集在MySQL复制 [英] charsets in MySQL replication

问题描述

我可以如何确保复制会使用latin1而不是utf-8？

我在Linux系统上的MySQL 5.1.22服务器（主）和FreeBSD系统上的MySQL 5.1.42服务器（从属）之间迁移。我的复制效果很好，但当非ASCII字符在我的varchars，他们变成奇怪。 Linux / MySQL-5.1.22显示以下字符集变量：

  character_set_client = latin1 
 character_set_connection = latin1 
 character_set_database = latin1 
 character_set_filesystem = binary 
 character_set_results = latin1 
 character_set_server = latin1 
 character_set_system = utf8 
 character_sets_dir = / usr / share / mysql / charsets / 
 collat​​ion_connection = latin1_swedish_ci 
 collat​​ion_database = latin1_swedish_ci 
 collat​​ion_server = latin1_swedish_ci 
  

FreeBSD显示

  character_set_client = utf8 
 character_set_connection = utf8 
 character_set_database = utf8 
 character_set_filesystem = binary 
 character_set_results = utf8 
 character_set_server = utf8 
 character_set_system = utf8 
 character_sets_dir = / usr / local / share / mysql / charsets / 
 collat​​ion_connection = utf8_general_ci 
 collat​​ion_database = utf8_general_ci 
 collat​​ion_server = utf8_general_ci 
  

从MySQL CLI设置任何这些变量没有效果，并在my.cnf或在命令行中设置它们使服务器无法启动。

当然，两个服务器都有相同的表格创建方式，在这种情况下使用DEFAULT CHARSET = latin1。让我举个例子：

  CREATE TABLE`test`（
`test` varchar（5）DEFAULT NULL 
）ENGINE = MyISAM DEFAULT CHARSET = latin1 
  

Latin1终端，INSERT INTO test VALUES（'æøå'），当我从基于Latin1的终端中选择时，将变成从属。

  + -------- + 
 |测试| 
 + -------- + 
 | Ã|øå| 
 + -------- + 
  

在基于UTF-8的终端在复制slave上，测试包含：

  + -------- + 
 |测试| 
 + -------- + 
 | æøå| 
 + -------- + 
  

所以我的结论是转换为utf8，即使表定义是latin1。这是一个正确的结论吗？

当然，在master上，在latin1终端，它仍然说：

  + ------ + 
 |测试| 
 + ------ + 
 | æøå| 
 + ------ + 
  

由于两个系统字符集都是utf-8 ，如果我将两个终端都设置为utf-8，然后用utf-8终端在主机上再次执行INSERT INTO test VALUES（'æøå'），我得到：

  + ------------ + 
 |测试| 
 + ------------ + 
 | Ã|øÃ| 
 + ------------ + 
  

如果我的结论是正确的，所有我的复制数据转换为utf8（如果它是utf8，它被视为latin1并转换为utf8），而表中的所有旧数据，如CREATE TABLE建议，latin1。我想把它转换为utf-8，如果不是因为遗留应用程序依赖于它是latin1的事实，所以我需要保持它在latin1，而它们仍然存在。

我可以做什么来确保复制读取latin1，把它当做latin1，并把它作为latin1写在slave上？

p>

Nik

解决方案

通常，您必须使用完全相同的配置文件和版本的mysql在从属（除了在升级/迁移情况下，和一些需要在服务器上不同的东西像server_id）。

你会想要脚本您的数据库设置，以便您的数据库服务器是软件部署的一部分。所有数据库服务器（包括非生产环境中的数据库服务器）都必须使用完全相同的配置。

无法同步配置将导致意外的错误。

我不知道为什么你觉得需要在不同的服务器上运行不同的操作系统，但是你会让你的Ops工作人员的生活更困难，如果你这样做。

What can I do to ensure that replication will use latin1 instead of utf-8?

I'm migrating between an MySQL 5.1.22 server (master) on a Linux system and a MySQL 5.1.42 server (slave) on a FreeBSD system. My replication works well, but when non-ascii characters are in my varchars, they turn "weird". The Linux/MySQL-5.1.22 shows the following character set variables:

character_set_client=latin1
character_set_connection=latin1
character_set_database=latin1
character_set_filesystem=binary
character_set_results=latin1
character_set_server=latin1
character_set_system=utf8
character_sets_dir=/usr/share/mysql/charsets/
collation_connection=latin1_swedish_ci
collation_database=latin1_swedish_ci
collation_server=latin1_swedish_ci

While the FreeBSD shows

character_set_client=utf8
character_set_connection=utf8
character_set_database=utf8
character_set_filesystem=binary
character_set_results=utf8
character_set_server=utf8
character_set_system=utf8
character_sets_dir=/usr/local/share/mysql/charsets/
collation_connection=utf8_general_ci
collation_database=utf8_general_ci
collation_server=utf8_general_ci

Setting any of these variables from the MySQL CLI has no effect, and setting them in my.cnf or at the command line makes the server not start.

Of course, both servers have the tables in question created the same way, in this case with DEFAULT CHARSET=latin1. Let me give you an example:

CREATE TABLE `test` (
  `test` varchar(5) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

When I on the master do, in a Latin1 terminal, "INSERT INTO test VALUES ('æøå')", this becomes on the slave, when I select it from a Latin1 based terminal

+--------+
| test   |
+--------+
| æøå    |
+--------+

On a UTF-8 based terminal on the replication slave, test contains:

+--------+
| test   |
+--------+
| æøå    |
+--------+

So my conclusion is that it is converted to utf8, even though the table definition is latin1. Is this a correct conclusion?

Of course, on the master, in a latin1 terminal, it still says:

+------+
| test |
+------+
| æøå  | 
+------+

Since both system character sets are utf-8, if I set both terminals to utf-8 and do again "INSERT INTO test VALUES ('æøå')" on the master with a utf-8 terminal, on the slave with utf-8 I get:

+------------+
| test       |
+------------+
| æøà     |
+------------+

If my conclusion is correct, all my replicated data is converted to utf8 (if it is utf8, it is treated as latin1 and converted to utf8), while all the old data in the table is, as the CREATE TABLE suggests, latin1. I'd love to convert it all to utf-8 if it weren't for the fact that legacy applications rely on it being latin1, so I need to keep it in latin1 while they still exist.

What can I do to ensure that the replication reads latin1, treats it as latin1 and writes it on the slave as latin1?

Cheers

Nik

解决方案

In general, you must use the exact same configuration file and version of mysql on the slave (except during upgrades / migration scenarios, and a few things which need to be different on slaves like server_id).

You will want to script your database setup so that your DB servers are part of your software deployment. It is essential that all database servers, including those in non-production environments, use the exact same configuration.

Failure to sync the configs will result in unexpected bugs.

I don't know why you feel the need to run different OSs on your different servers, but you're going to make life more difficult for your Ops staff if you do so.

这篇关于字符集在MySQL复制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！