0

I am setting up MySQL Group Replication with the following configuration

  • 1 Virtual IP
  • 2 ProxySQL
  • 3 MySQL Servers with Single Master Group Replication

I understand why MySQL chose to use 7 as the default auto_increment_increment value (link), my question is

  • Considering only one node will be responsible for the updates at any given point of time, is it safe to set auto_increment_increment value to 1 in my setup?
user14934989
  • 93
  • 4
  • Why do you want the increment to be 1? Are you concerned about running out of integers? Or are you hoping to ensure primary key numbers are consecutive? – Bill Karwin Mar 31 '22 at 15:35
  • @BillKarwin We are worried that we will run out – user14934989 Mar 31 '22 at 15:48
  • Do you know how to calculate how long it would take to run out of integers? You need to know the [maximum value for your integer primary key](https://dev.mysql.com/doc/refman/8.0/en/integer-types.html), and the rate you insert new rows to allocate id values. – Bill Karwin Mar 31 '22 at 15:54
  • You may also like to read this answer of mine: https://stackoverflow.com/a/65657761/20860 – Bill Karwin Mar 31 '22 at 16:04
  • @BillKarwin Thanks a lot for replying. I'm still curious to know how MySQL auto increment behaves in the setup that is mentioned in the post. – user14934989 Mar 31 '22 at 16:12
  • Yes, in theory, if you never run inserts on more than one node, you could set the increment to 1. But in practice, it's hard to guarantee no one _else_ will try to insert on other nodes, and you really do not want to risk having a database with a [split-brain error](https://www.percona.com/blog/2020/03/26/split-brain-101-what-you-should-know/). That's the kind of thing that gives database administrators post-traumatic stress disorder. – Bill Karwin Mar 31 '22 at 16:23

0 Answers0