Here is an interesting diagram to look at first.
This is pretty simple stuff. A web app, in this case, requests a write to the MySQL master, which replicates the information to each of the slaves. 10/10!
You need to run your slaves with the --log-bin option and without --log-slave-updates. What does this mean exactly? Well these are optional values you can use when start the mysql.
In linux flavours, you can edit the file /etc/my.sql and set these values in this file that get loaded when mysql starts. You can also specify some values using a mysql client, connecting to the database and specifying the values required. I would recommend using /etc/my.sql as a one stop shop.
In Windows, MySQL programs read startup options from the following files, in the following order:
First of all, what do we need to set?
For --log-bin, this needs a filename value. See here:
So Type = file name and the default is off. If we need to set up replication, the mysql /etc/my.cnf file will look like this:
[mysqld]
log-bin=mysql-bin
For --log-slave-updates, well this is slightly easier. Why? Take a look at this:
The default is set to false anyhow so unless the config has explicitly set this to true, we don't need to do anything. For good measure:
[ mysqld]
log-slave-updates=false
However, what happens in a failure here? Each MySQL Slave (Slave 1, Slave 2, and Slave 3) is a slave running with --log-bin and without --log-slave-updates. Because updates received by a slave from the master are not logged in the binary log unless --log-slave-updates is specified, the binary log on each slave is empty initially. If for some reason MySQL Master becomes unavailable, you can pick one of the slaves to become the new master. For example, if you pick Slave 1, all Web Clients should be redirected to Slave 1, which will log updates to its binary log. Slave 2 and Slave 3 should then replicate from Slave 1.
The reason for running the slave without --log-slave-updates is to prevent slaves from receiving updates twice in case you cause one of the slaves to become the new master. Suppose that Slave 1 has --log-slave-updates enabled. Then it will write updates that it receives from Master to its own binary log. When Slave 2 changes from Master to Slave 1 as its master, it may receive updates from Slave 1 that it has already received from Master.
However there is some intervention required to do all of this. It's not automated. Here is what you would need to do in a case of a MYSQL failure:
Make sure that all slaves have processed any statements in their relay log. On each slave, issue STOP SLAVE IO_THREAD, then check the output of SHOW PROCESSLIST until you see Has read all relay log. When this is true for all slaves, they can be reconfigured to the new setup. On the slave Slave 1 being promoted to become the master, issue STOP SLAVE and RESET MASTER.
On the other slaves Slave 2 and Slave 3, use STOP SLAVE and CHANGE MASTER TO MASTER_HOST='Slave1' (where 'Slave1' represents the real host name of Slave 1). To use CHANGE MASTER TO, add all information about how to connect to Slave 1 from Slave 2 or Slave 3 (user, password, port). In CHANGE MASTER TO, there is no need to specify the name of the Slave 1 binary log file or log position to read from: We know it is the first binary log file and position 4, which are the defaults for CHANGE MASTER TO. Finally, use START SLAVE on Slave 2 and Slave 3.
There is an interesting scaling solution you could consider which I like. Take a look at this diagram:
In this scenario, the web client are directed to slaves for read purposes only, and any writes are directed to the MySQL master server. This takes the read load off from the MySQL master server. Reads generate more requests than writes generally. However, you need to have the application aware of the slave setup. This is generally done by creating MYSQL connectors that are aware of read only functions. These connectors would then utilise a slave server rather than a master server. For more info, see this link https://dev.mysql.com/doc/refman/5.7/en/replication-solutions-scaleout.html.
Happy playing!
