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?