The server_id of the Master is recorded in its binary logs along with each query it executed to completion. To see those server_id values in the binary log, you run mysqlbinlog against any binary log.
Since Master -> Slave_A works fine, here is what you can do to clean things up between Slave_A and Slave_B:
On Slave_A, run the following in mysql:
mysql> STOP SLAVE;
mysql> RESET MASTER;
On Slave_A, run the following in the OS:
$ echo "STOP SLAVE;" > /root/MyData.sql
$ mysqldump -u... -p... --master-data=1 --routines --triggers --all-databases >> /root/MyData.sql
$ echo "START SLAVE;" >> /root/MyData.sql
$ mysql -hSlave_B -u... -p... -A < /root/MyData.sql
On Slave_A, run the following in mysql:
mysql> START SLAVE;
This will get Slave_B to stare at the correct values for server_id, and also reestablish MySQL Replication.
BTW You need to make sure binary logging is disabled on Slave_B since it is not necessary for a Slave unless Slave_B is going to a Master to some other Slave.
Since you have Master/Slave, you may want to implement the use of two things
- Circular Replication
- Database Virtual IP (aka DBVIP)
Circular Replication
Circular Replication is nothing more than first setting up Master/Slave then performing the same steps using the Slave as the Master's Master and the Master as the Slave's Slave. It just entails
- explicitly user a different server_id on each DB Server
- enabling binary logging on both DB servers
- making sure the replication user is defined on both DB servers
Database Virtual IP (aka DBVIP)
There are products you can download and install to setup a DBVIP. One such product I use is ucarp. Another product is Linux Heartbeat. I normally do not use such things with MySQL Circular Replication or Master/Slave. Why?
Since those products can perform automatic failover, you do not want to do that in the event a Slave is some number of seconds behind in replication lag.
You should perform manual failovers.
Here is a poor man's approach to implementing DBVIP management.
Suppose you have this setup
- DB Server1 has IP 10.1.2.30
- DB Server2 has IP 10.1.2.40
- You want to use DBVIP 10.1.2.50
Create the Script called /usr/local/sbin/MyAppDBVIP like this
echo echo 10.1.2.50 > /usr/local/sbin/MyAppDBVIP
Create the Script called /usr/local/sbin/dbvip-up
DBVIP=`/usr/local/sbin/MyAppDBVIP`
ip addr add ${DBVIP}/24 dev eth1
Create the Script called /usr/local/sbin/dbvip-down
DBVIP=`/usr/local/sbin/MyAppDBVIP`
ip addr del ${DBVIP}/24 dev eth1
Make sure all scripts are executable
chmod +x /usr/local/sbin/MyAppDBVIP
chmod +x /usr/local/sbin/dbvip-up
chmod +x /usr/local/sbin/dbvip-down
Make sure these script exist on both DB Servers
Simply run dbvip on whichever server you choose. .
So the failover process and protocol are the following:
- Run
dbvip-down on the DB Server that has the DBVIP. If you cannot
- Run
dbvip-up on the DB Server that you want to have the DBVIP
- Just remember you should not run
dbvip-up on both machines
- After running
dbvip-up, restart apache, JBoss, or any other app server contacting MySQL via the old Master
Best Answer
The server-id is stored in the /var/lib/mysql/auto.cnf in addition to the standard my.cnf config file.
When cloning the data directory from master to slave, you need to delete the auto.cnf file in the data directory (i.e. /var/lib/mysql) before starting the slave.