MySQL 复制错误 (1062)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4668126/
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
MySQL Replication Error(1062)
提问by Mubashar
I am new to MySQL and after a long search I am able to configure master-slave ROW based replication. I thought it would be safe and I would not have to recheck it again and again.
我是 MySQL 的新手，经过长时间的搜索，我能够配置基于主从 ROW 的复制。我认为它是安全的，我不必一次又一次地重新检查。
But today when I did SHOW SLAVE STATUS;on slave then I found following
但是今天当我SHOW SLAVE STATUS;在奴隶上做的时候，我发现了以下
could not execute Write_rows event on table mydatabasename.atable; Duplicate entry '174465' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000004, end_log_pos 60121977
无法在表 mydatabasename.atable 上执行 Write_rows 事件；密钥“PRIMARY”的重复条目“174465”，错误代码：1062；处理程序错误 HA_ERR_FOUND_DUPP_KEY; 事件的主日志 mysql-bin.000004, end_log_pos 60121977
Can someone tell me how this can even come when master has no such error and schema on both server is the same then how could this happen. And how to fix it to make this work again and how to prevent such thing in future.
有人能告诉我，当 master 没有这样的错误并且两台服务器上的架构相同时，这怎么会发生，那么这怎么会发生。以及如何修复它以使其再次工作以及如何防止将来发生此类事情。
Please also let me know what else unexpected I should expect other than this.
还请让我知道除此之外我还应该期待什么。
回答by ajreal
It would never happen on master, why?
它永远不会发生在主人身上，为什么？
The series of SQL are replicated from master,
if the record already exist in master, mysql reject on master
该系列SQL是从master复制过来的，
如果master中已经存在该记录，mysql在master上拒绝
but on slave, if fails and the replication position does not advanced to next SQL (it just halted)
但是在从站上，如果失败并且复制位置没有前进到下一个 SQL（它只是停止了）
Reason?
原因？
The insert query of that record is write directly into slave without using replication from the master
该记录的插入查询直接写入从属，而无需使用主服务器的复制
How to fix?
怎么修？
Skip the error on slave, like
跳过奴隶上的错误，比如
SET GLOBAL sql_slave_skip_counter = N;
details - http://dev.mysql.com/doc/refman/5.0/en/set-global-sql-slave-skip-counter.html
详细信息 - http://dev.mysql.com/doc/refman/5.0/en/set-global-sql-slave-skip-counter.html
Or delete the duplicate record on slave, resume the slave again (let the replication do the insertion)
或者删除slave上的重复记录，重新恢复slave（让replication做插入）
The worse scenario, required you to re-do the setup again to ensure data integrity on slave.
更糟糕的情况是，需要您再次重新设置以确保从站上的数据完整性。
How to prevent?
如何预防？
Check application level, make sure no write directly into slave
This including how you connect to mysql in command prompt
检查应用程序级别，确保没有直接写入从站
这包括您如何在命令提示符下连接到 mysql
Split mysql user that can do write and read,
So, your application should use read user (master and slave) when does not require write.
Use write user (master only) for action require write to database.
拆分可以写入和读取的mysql用户，
因此，您的应用程序应该在不需要写入时使用读取用户（主从）。
使用写入用户（仅限主）执行需要写入数据库的操作。
回答by Suyash Jain
skip counter is not a viable solution always, you are skipping the records but it might affect the further records.
跳过计数器始终不是一个可行的解决方案，您正在跳过记录，但它可能会影响更多记录。
Here is the complete details on why sql slave skip counter is bad.
这是有关为什么 sql slave skip counter 不好的完整详细信息。
回答by Ferhat KO?ER
You can delete bigger than duplicate rows in slave db;
您可以删除从属数据库中大于重复的行；
DELETE FROM mydatabasename.atable WHERE ID>=174465;
then
然后
START SLAVE;
