MySQL 二进制日志复制：可以设置为忽略错误吗？
我正在运行一个主从 MySQL 二进制日志复制系统（唷！），对于某些数据，该系统不同步（这意味着主服务器比从服务器保存更多的数据）。 但是从站经常因为最轻微的 MySQL 错误而停止，这可以禁用吗？ （可能是复制从站忽略复制错误或某种类型的 my.cnf 设置；））
这就是当从站尝试复制不存在的项目时时不时发生的情况，从站只是死了。 快速检查 SHOW SLAVE STATUS \G; 给出了
Slave-IO-Running: Yes
Slave-SQL-Running: No
Replicate-Do-DB:
Last-Errno: 1062
Last-Error: Error 'Duplicate entry '15218' for key 1' on query. Default database: 'db'. Query: 'INSERT INTO db.table ( FIELDS ) VALUES ( VALUES )'
我通过执行以下操作立即修复的内容（一旦我意识到从属设备已停止）：
STOP SLAVE;
RESET SLAVE;
START SLAVE;
...最近这变得有点令人厌烦，在我吐出某种 PHP 来为我做这件事之前，我想知道是否有一些 my.cnf 条目不会在第一个错误时杀死从属设备。
干杯，
/mp
I'm running a master-slave MySQL binary log replication system (phew!) that, for some data, is not in sync (meaning, the master holds more data than the slave). But the slave stops very frequently on the slightest MySQL error, can this be disabled? (perhaps a my.cnf setting for the replicating slave ignore-replicating-errors or some of the sort ;) )
This is what happens, every now and then, when the slave tries to replicate an item that does not exist, the slave just dies. a quick check at SHOW SLAVE STATUS \G; gives
Slave-IO-Running: Yes
Slave-SQL-Running: No
Replicate-Do-DB:
Last-Errno: 1062
Last-Error: Error 'Duplicate entry '15218' for key 1' on query. Default database: 'db'. Query: 'INSERT INTO db.table ( FIELDS ) VALUES ( VALUES )'
which I promptly fix (once I realize that the slave has been stopped) by doing the following:
STOP SLAVE;
RESET SLAVE;
START SLAVE;
... lately this has been getting kind of tiresome, and before I spit out some sort of PHP which does this for me, i was wondering if there's some my.cnf entry which will not kill the slave on the first error.
Cheers,
/mp
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
停止奴隶； 设置全局 sql_slave_skip_counter=1； 启动奴隶；
您可以仅忽略当前错误并继续复制过程。
stop slave; set global sql_slave_skip_counter=1; start slave;
You can ignore only the current error and continue the replication process.
是的，在 my.cnf 中使用 --slave-skip-errors=xxx，其中 xxx 是“全部”或逗号分隔的错误代码列表。
Yes, with --slave-skip-errors=xxx in my.cnf, where xxx is 'all' or a comma sep list of error codes.
首先，您真的想忽略错误吗？ 如果出现错误，则数据可能不再同步。 也许您想要的是在出现错误时删除从数据库并重新启动同步过程。
其次，我认为您遇到的错误不是当您复制不存在的项目时（这意味着什么？） - 看起来您正在复制从属数据库中已经存在的项目。
我怀疑问题主要是由于没有从干净的数据副本开始而引起的。 看起来master已经被复制到了slave； 那么复制已被关闭（或失败）； 然后它又开始了，但没有给奴隶机会追上它错过的事情。
如果您曾经有一段时间可以关闭主服务器以进行写入访问足够长的时间来克隆数据库并将其导入从服务器中，那么这可能会解决问题。
First, do you really want to ignore errors? If you get an error, it is likely that the data is not in sync any more. Perhaps what you want is to drop the slave database and restart the sync process when you get an error.
Second, I think the error you are getting is not when you replicate an item that does not exist (what would that mean anyway?) - it looks like you are replicating an item that already exists in the slave database.
I suspect the problem mainly arises from not starting at a clean data copy. It seems that the master has been copied to the slave; then replication has been turned off (or failed); and then it has started up again, but without giving the slave the chance to catch up with what it missed.
If you ever have a time when the master can be closed for write access long enough to clone the database and import it into the slave, this might get the problems to go away.
现代 mysqldump 命令有几个选项可以帮助设置一致的复制。 查看
--master-data，它将把二进制日志文件和位置放入转储中，并在加载到从属时自动设置。 此外，--single-transaction将在事务内执行转储，以便不需要写锁来执行一致的转储。Modern
mysqldumpcommands have a couple options to help with setting up consistent replication. Check out--master-datawhich will put the binary log file and position in the dump and automatically set when loaded into slave. Also--single-transactionwill do the dump inside a transaction so that no write lock is needed to do a consistent dump.如果从服务器除了复制之外不用于任何写入操作，High Performance MySQL 的作者建议在从服务器上添加
read_only以防止用户错误地更改从服务器上的数据，因为这也会影响从服务器的性能。创建与您经历过的相同的错误。If the slave isn't used for any writes other than the replication, the authors of High Performance MySQL recommend adding
read_onlyon the slave server to prevent users from mistakenly changing data on the slave as this is will also create the same errors you experienced.我认为您正在进行复制而不同步数据库，首先同步数据库并尝试复制，服务器正在生成相同的唯一ID并尝试设置自动增量偏移
i think you are doing replication with out sync the database first sync the database and try for replication and servers are generating same unique ids and try to set auto incerment offset