错误：在 MySQL 中执行主从复制时“无法初始化主信息结构”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5029485/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error: "could not initailize master info structure" while doing Master Slave Replication in MySQL
提问by user619684
I am trying to do Master Slave Replication for MySQL. When i am typing the following command:
我正在尝试为 MySQL 做主从复制。当我输入以下命令时：
CHANGE MASTER TO MASTER_HOST='10.1.100.1', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=451228;
mysql> START SLAVE;
it throws the following error:
它引发以下错误：
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log
ERROR 1201 (HY000)：无法初始化主信息结构；更多错误信息可以在 MySQL 错误日志中找到
Any help would be greatly appreciated.
任何帮助将不胜感激。
回答by Neo
TRY TO RESET IT, IT DOES MAGIC! ON SLAVE THE SLAVE MYSQL COMMAND TYPE:
尝试重置它，它很神奇！在从属 MYSQL 命令类型：
RESET SLAVE;
THEN TRY AGAIN:
然后再试一次：
CHANGE MASTER TO MASTER_HOST='10.1.100.1', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=451228;
mysql> START SLAVE;
回答by RolandoMySQLDBA
Please check several things:
请检查几件事：
1) Make sure the Master's /etc/my.cnf has server_id actually set
1) 确保 Master 的 /etc/my.cnf 实际设置了 server_id
Here is why: Replication relies on the server_id. Whenever a query is executed and is recorded in the master's binary log, the server_id of the master is recorded with it. By default, if a server_id is not defined in /etc/my.cnf, the server_id is defaulted to 1. However, the rules MySQL Replication demand that a server_id be explicitly defined in the master's /etc/my.cnf. In addition, for any given slave, mysqld checks the server_id of the SQL statement as it reads it from the relay log and makes sure it is different from the slave's server_id. That is how MySQL Replication knows it is safe to execute that SQL statement. This rule is necessary in the event Circular (Master-Master,MultiMaster) Replication is implemented.
原因如下：复制依赖于 server_id。每当执行查询并记录在 master 的二进制日志中时，master 的 server_id 都会随之记录。默认情况下，如果 /etc/my.cnf 中未定义 server_id，则 server_id 默认为 1。但是，MySQL Replication 规则要求在 master 的 /etc/my.cnf 中显式定义 server_id。此外，对于任何给定的从站，mysqld 在从中继日志中读取 SQL 语句的 server_id 时会检查它，并确保它与从站的 server_id 不同。这就是 MySQL 复制如何知道执行该 SQL 语句是安全的。在实施循环（主-主，多主）复制时，此规则是必需的。
use select @@server_id;in sql command line to check config really on server.
使用 select @@server_id;在SQL命令行来检查服务器的配置真的。
2) Make sure the Slave's /etc/my.cnf has server_id actually set
2) 确保 Slave 的 /etc/my.cnf 实际设置了 server_id
Here is why: Same reason as in #1
原因如下：与#1 相同的原因
3) Make sure the server_id in the Master's /etc/my.cnf is different from the server_id in the Slave's /etc/my.cnf
3）确保Master的/etc/my.cnf中的server_id与Slave的/etc/my.cnf中的server_id不同
Here is why: Same reason as in #1
原因如下：与#1 相同的原因
As a side note : If you setup multiple slaves, please make sure each slave has a different server_id from its master and its sibling slaves.
附带说明：如果您设置了多个从站，请确保每个从站的 server_id 与其主站和兄弟从站不同。
Here is why : Example
原因如下：示例
A master with 2 slaves
MASTER has server_id 1
SLAVE1 has server_id 2
SLAVE2 has server_id 2
一个有 2 个从站的主站
MASTER 有 server_id 1
SLAVE1 有 server_id 2
SLAVE2 有 server_id 2
Replication will become agressively sluggish on SLAVE2 because a sibling slave has the same server_id. In fact, it will steadily fall behind, catch a break, process a few SQL statements. This is the master's fault for having one or more slaves with identical server_ids. This is a gotcha that is not really documented anywhere. I've seen this dozens of times in my life time.
SLAVE2 上的复制将变得非常缓慢，因为同级从属具有相同的 server_id。实际上，它会稳步落后，休息一下，处理一些SQL语句。这是 master 的错误，因为它有一个或多个具有相同 server_ids 的 slave。这是一个没有在任何地方真正记录的问题。我这辈子已经见过几十次了。
回答by Walter
I had something very close to that and got same error messages. Replication run fine, mariadb restart -> "cannot open relay log"
我有一些非常接近的东西，并收到了相同的错误消息。复制运行正常，mariadb 重启 ->“无法打开中继日志”
Solution from Neo helped in the first place.
Neo 的解决方案首先提供了帮助。
But the root cause it seems were to small open file limits.
但根本原因似乎是小的打开文件限制。
Try a lsof | wc and increase DefaultLimitNOFILE to 65535 in /etc/systemd/system.conf and /etc/systemd/user.conf
试试 lsof | wc 并将 /etc/systemd/system.conf 和 /etc/systemd/user.conf 中的 DefaultLimitNOFILE 增加到 65535
