2

I'm in the middle of writing a script to automate configuring MySQL replication between two servers (Master/Master replication), and was looking for some advice on a few things related to MySQL.

The goal is to basically allow the same script to be executed on both servers (probably just prompting for the IP of the other master server), and have it completely configure MySQL. Most of it is pretty easy, but a few of the configuration values are unique to the server. The servers are pretty much identical (including MySQL credentials)

The /etc/my.cnf file has two settings that are unique to the server, there's a server-id and an auto-increment-offset.

Does the server-id need to start at any order or be consecutive? Because if not, then I was just going to grab the numeric value from the servers hostname (it's something like appdb-stg-m01, so I could grab the 01, or whatever number, since that will be unique), or even the last octet in the servers IP address... Would that suffice?

Then for the auto-increment-offset, can this not be set the same on both servers? I have it set at 1 on the first master, then 2 on the other. I got those values from some online tutorials, but they didn't explain why they were different.

Then for the values used in the CHANGE MASTER TO command... It needs the MASTER_LOG_FILE and the MASTER_LOG_POS...

We can assume that these servers are relatively new, with no existing databases on them. So I was thinking that just resetting the MASTER_LOG_FILE to mysql-bin.000001 would suffice, but then id need to delete the other mysql-bin.? from /var/lib/mysql, as well as from /var/lib/mysql/mysql-bin.index. Would that suffice?

The only other setting that I'm somewhat hung up on would be the MASTER_LOG_POS... Is there a way to set that myself? I'm trying to make this as least complicated as possible, so connecting to the other mysql server and looking at the output of SHOW MASTER STATUS is something id like to stay away from. Is there a way instead to reset it to 313?

Thanks!

Justin
  • 485

1 Answers1

1

I was trying to comment, but the answer was starting to get long.

The general idea that we have to keep in mind with a MySQL master-master relationship, is that we with the replication working, we have now two servers potentially writing to the same records either at same time, or in different times.

So keeping that in mind, auto-increment-offset need to be different - they have to be 1 and 2 because the ideia is to generate different record numbers (i.e. no conflicts). So the records created by one of the masters will be even, and the other odd, and thus they cannot create both a conflicting record number. So, for instance the first master generates records 1,3,5,7, and so on, and the second one, 2,4,6,8...

In what touches the server-id, they just have to be different.Traditionally people use 1 and 2, but it is not a requirement. I think they cannot be 0, not sure.

Finally as for MASTER_LOG_FILE and the MASTER_LOG_POS, you can try to assume the default values; however it is never the best of ideas assuming things, and, more importantly, you might want to reuse the routing to establish again the master-master relationship in case of any event, and as such in the name of code reuse, it would be far more useful to connect to the remote MySQL and get the proper values.

For another angle on automating things, you have here the link to the Ansible module page mysql_replication

And here a former answer of mine that will give you a clue about Ansible Linux equivalent to PowerShell's "one-to-many" remoting

Community
  • 1
Rui F Ribeiro
  • 56,709
  • 26
  • 150
  • 232
  • You say the server-id & auto-increment-offset values don't necessarily need to be 1 and 2, or even consecutive, they can be anything, as long as they aren't the same value - Correct? If that's the case, then I can just take the node number of the server. So if the server hostnames are mysql-prd-03.biz.com & mysql-prd-12.biz.com, I could take the short numeric value of the 3rd section for both values, right? IE- Master 01: server-id = 3, auto-increment-offset = 3; and Master 02: server-id = 12, auto-increment-offset = 12; And that would suffice? – Justin May 11 '16 at 17:49
  • Or even rather than the node number in the hostname, I could take the fourth octet of the servers IP address... correct? – Justin May 11 '16 at 17:52
  • auto-increment-offset has to be 1 in one, and 2 in other, otherwise you will have conflicts or waste a lot of record number. Has for server-id, you are more free to use them. updated the post. – Rui F Ribeiro May 11 '16 at 18:04
  • If it's a smaller number, would that suffice? I tried this out (lines 46 and 51, and it works fine, but it does waste the record number... but does that matter? does it impact performance or anything? If it does, how about a smaller number? But potentially not 1 and 2, but perhaps 3 and 4? (Just thinking of ways to get unique numbers on each server, without having to query each other, hostnames and IP are the only thing I can think of) – Justin May 11 '16 at 18:47
  • I would not use other numbers. for me the tendency is also to control the administration externally, so the process will now well who is 1 and 2 – Rui F Ribeiro May 12 '16 at 05:20