ChapTEr 8 ■ DEmonsTraTion of high availabiliTy TEChniquEsYou could also take a backup of the master, but you would have to suspend writing to the binary log to
do so.^3 A better method is to temporarily stop the existing slave and wait until it has read and applied the
events in its relay log (use the SHOW SLAVE STATUS statement to see the current state). The columns you need
to view are highlighted here using an excerpt from an actual replication slave:
Slave_IO_State: Waiting for master to send event
...
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Here you can see the slave has read all events from the master (slave_io_state) and the slave has
processed all the events in its relay log (slave_sql_running_state).
If you use binary log file and position-based replication, you will need to record the current master
binary log file and position as shown in the SHOW SLAVE STATUS output. If you use GTIDs, you do not need
this information.
Once the slave is stopped and all relay log events have been executed, you can make the backup, restart
the original slave, then restore the data on the new slave, and finally start replication on the new slave.
Example: Scaling Applications
Scaling is about performance. Read scaling is where you use additional servers to direct reads (SELECT
statements) so that no one server is burdened with processing a lot of read requests. There is also shard
scaling where you divide (partition) the data among several servers ideally so that they all have the same
amount of data. To access the shard, you must use an algorithm (commonly a simple hash function) or
a range of values dedicated to each shard. As you can imagine, it can be a challenge to route the query
to the correct shard. Fortunately, most IOT solutions will not require the use of sharding for improving
performance. Rather, it is more likely some IOT solutions can benefit from read scaling.
Read scaling with MySQL uses a replication topology with all the writes (CREATE, INSERT, UPDATE,
DELETE, and so on) sent to the master and all reads sent to one of the slaves. The number of slaves you will
need will be determined largely by how often (or much) your application reads data.
While setting up read scaling is no more difficult than setting up replication (you don’t need to do
anything special), writing your application to scale the reads is the challenge. More specifically, knowing
which server to send a client (application) can be a simple round-robin or queue mechanism, or you could
implement sophisticated load balancing to direct the reads to the least used server.
For solutions that need a modest boost in read performance, a simple server selection mechanism is
all you may need. Larger, more sophisticated solutions may need to build their own load balancer or use a
third-party load balancer.^4 Even so, the load balancing mechanism may be entirely application dependent
and even in some cases access method dependent (think type of queries).
For hobbyist and enthusiasts IOT solutions and even small to medium-sized commercial IOT solutions,
the simple server selection mechanism is more than sufficient. If performance starts to slow, you simply add
another server and spread the reads.
You may be wondering where or how you would set up read scaling. One possible implementation is
the use of a topology of low-cost computer boards. Another is a cloud service that provides MySQL instances
you can create and destroy on the fly. Fortunately, MySQL works in the cloud the same way it does on any
other device—the cloud server you get is just a virtualized server instead of real hardware.
(^3) There are other tricks, but this is the safest way.
(^4) Oracle doesn’t have a load balancer for MySQL read scaling yet. But it does have a connection router capability in the
MySQL Router product.