ChapTEr 8 ■ DEmonsTraTion of high availabiliTy TEChniquEs
Note in the previous screenshots that the utility is running as a console. If you need to run it as a
background process, there is a companion utility that permits you to run it as a process or daemon (for
non-Windows platforms only). To learn more about mysqlfailover and its companion daemon process
including all the available commands and more examples of use, see the online manual for MySQL Utilities
at http://dev.mysql.com/doc/mysql-utilities/1.6/en/utils-task-autofailover.html.
Replication and Database Maintenance Tips
As mentioned, MySQL replication is relatively easy to set up and maintain for smaller topologies. For IOT
solutions, you are not likely to encounter replication topologies with many servers (but you may). Recall
MySQL replication can require a bit of work should things go wrong. In this section, I focus on some
common things that can go wrong and present methods to prevent and recover from errors.
Avoiding Errant Transactions
Perhaps the most common mistake new administrators make with MySQL replication is introducing errant
transactions. Errant transactions are those statements (or transactions) that apply to only one server and
either are invalid when executed on another server or cause replication errors when executed. Errant
transactions are most often due to running one or more SQL statements on the master that should not be
replicated. With GTIDs, errant transactions can occur when the same is performed on a slave and that slave
later becomes the master or leaves and rejoins the topology.
For example, if you execute any SQL command on the master to create, update, or delete anything that
does not exist on the slaves, you could encounter errors severe enough to stop replicaiton on the slave. Recall
that once replication is started, every SQL statement is written to the binary log and then transmitted to the
slaves. Thus, we must be careful when executing statements on the master when binary logging is enabled.
Fortunately, there is a way to omit statements from being recorded to the binary log. There is a special
variable named sql_log_bin that you can turn off for the current session (the logged-in user) that does
not affect any other connection. Simply turn the binary log off, execute your statements, and then turn it
on again. The following code shows how you can use this technique to add a replication user account to a
server that already has the binary log enabled:
SET @@sql_log_bin=0;
CREATE USER 'rpl'@'localhost' IDENTIFIED BY 'rpl';
GRANT REPLICATION SLAVE ON . TO 'rpl'@'localhost';
SET @@sql_log_bin=1;
If you use this technique when performing maintenance on your replication servers, you can avoid
errant transactions and thus avoid a lot unwarranted trouble resolving slave errors.
Resolving Slave Errors
When you encounter errors on the slave, you will see the error in the SHOW SLAVE STATUS output. If the error
is caused by an errant transaction, you must instruct the master to skip those transactions, which is easy for
binary log file and position-based replication but can be a little tricky for GTID-enabled servers. Recall an
errant transaction is normally some event in the binary log of the master that does not apply or produces an
error when run on the slave.