Comments on: Replication configuration checklist https://shlomi-noach.github.io/blog/mysql/replication-configuration-checklist Blog by Shlomi Noach Wed, 25 May 2011 13:46:25 +0000 hourly 1 https://wordpress.org/?v=5.3.3 By: MySQL复制的概述、安装、故障、技巧、工具 | 火丁笔记 https://shlomi-noach.github.io/blog/mysql/replication-configuration-checklist/comment-page-1#comment-40975 Wed, 25 May 2011 13:46:25 +0000 https://shlomi-noach.github.io/blog/?p=2357#comment-40975 […] 注:常用的MySQL复制配置选项可以参考Replication configuration checklist。 […]

]]>
By: Shantanu Oak https://shlomi-noach.github.io/blog/mysql/replication-configuration-checklist/comment-page-1#comment-14640 Fri, 11 Jun 2010 02:00:23 +0000 https://shlomi-noach.github.io/blog/?p=2357#comment-14640 I have 2 slaves and one is used for testing purpose. I have set slave-skip-errors on that slave. It is also possible to skip the specific errors if you know the error number for e.g.
slave-skip-errors=1205,1422,1062
1205 Error on master: ‘Lock wait timeout exceeded; try restarting transaction’ (1205), Error on slave: ‘no error’ (0).
1422 Explicit or implicit commit is not allowed in stored function or trigger
1062 Duplicate entry for key 1
This is not recommended for obvious reasons. But comes handy at times.

]]>
By: shlomi https://shlomi-noach.github.io/blog/mysql/replication-configuration-checklist/comment-page-1#comment-14625 Thu, 10 Jun 2010 17:12:47 +0000 https://shlomi-noach.github.io/blog/?p=2357#comment-14625 @Shantanu:

I strongly disagree on slave-skip-errors=all.
I always prefer the slave to actually stop replicating upon error. There are good reasons for it to stop replicating. Simply ignoring the errors is, well, error-prone. This is one setting I never use.

Good points for all the rest!

]]>
By: Shantanu Oak https://shlomi-noach.github.io/blog/mysql/replication-configuration-checklist/comment-page-1#comment-14613 Thu, 10 Jun 2010 13:19:19 +0000 https://shlomi-noach.github.io/blog/?p=2357#comment-14613 skip-slave-start
# do not start slave thread when the server is restarted
slave-skip-errors=all
# use the error number or ‘all’ to skip all errors
relay-log=/var/log/mysql/
# good to have the path for relay log
max_allowed_packet=500M
# yes. you need it for replication too!

The “expert” will also need
replicate-wild-ignore-table=test.%
# to ignore all the tables from test database

]]>
By: shlomi https://shlomi-noach.github.io/blog/mysql/replication-configuration-checklist/comment-page-1#comment-14475 Sun, 06 Jun 2010 02:44:52 +0000 https://shlomi-noach.github.io/blog/?p=2357#comment-14475 @jay,
already on the list 😛

]]>
By: jay bharat https://shlomi-noach.github.io/blog/mysql/replication-configuration-checklist/comment-page-1#comment-14464 Sat, 05 Jun 2010 18:13:46 +0000 https://shlomi-noach.github.io/blog/?p=2357#comment-14464 Sync-binlog=1 to the list. It is important if you want replication to recover after a server crash on the master. Of course, there is overhead to it, so you need to decide if it is worth it or not for your application.

]]>
By: James Day https://shlomi-noach.github.io/blog/mysql/replication-configuration-checklist/comment-page-1#comment-13797 Wed, 19 May 2010 04:02:32 +0000 https://shlomi-noach.github.io/blog/?p=2357#comment-13797 Those options can definitely be interesting but do you always want to halve the replication throughput of all of your slaves?

In one case I was handling recently, turning off the binary log, the query cache and adding innodb_support_xa = 0 and innodb_locks_unsafe_for_binlog = 1 increased a lagged slave’s throughput from one to two binary logs per hour to two to four per hour.

Sometimes a mixture of high reliability and high speed slave configurations might be the best solution.

On the slave it’s best to stick to the table-based replication rules if you can. Also all of one type, say a mixture of do and wild do. It’s easier to understand and not get caught out that way. You also get the benefit of not relying solely on the USEd database to determine whether a table can be updated or not, potentially missing updates if the update was carried out from a different database than the table itself.

On the master, do remember that the primary purpose of the binary log is disaster recovery. If you don’t write things to the binary log you’re not going to be able to get them back from it when you’re replaying the binary log after hitting say a serious disk damage based corruption problem. The slaves might save you but it’s worth considering writing more to the binary log.

]]>
By: shlomi https://shlomi-noach.github.io/blog/mysql/replication-configuration-checklist/comment-page-1#comment-13763 Tue, 18 May 2010 15:59:57 +0000 https://shlomi-noach.github.io/blog/?p=2357#comment-13763 Hi Ryan,

Oh dear! Yes, indeed! Thank you.

]]>
By: Ryan https://shlomi-noach.github.io/blog/mysql/replication-configuration-checklist/comment-page-1#comment-13759 Tue, 18 May 2010 14:56:17 +0000 https://shlomi-noach.github.io/blog/?p=2357#comment-13759 Hey Shlomi,

What about read_only on the slave(s)? This should help to ensure that slaves don’t receive accidental writes (assuming application user grants don’t include SUPER).

]]>
By: shlomi https://shlomi-noach.github.io/blog/mysql/replication-configuration-checklist/comment-page-1#comment-13758 Tue, 18 May 2010 14:14:25 +0000 https://shlomi-noach.github.io/blog/?p=2357#comment-13758 @Harrison
Thank you

]]>