ChapTEr 8 ■ DEmonsTraTion of high availabiliTy TEChniquEsthe REPLICATION SLAVE privilege for all databases. It is also good practice to assign the user a password.
The following statements show how to set up the replication user account and grant the appropriate
privileges:
CREATE USER 'rpl'@'localhost' IDENTIFIED BY 'rpl';
GRANT REPLICATION SLAVE ON . TO 'rpl'@'localhost';
Now we are ready to set up the servers to use GTIDs. To do so, you must specify the following options
for the master. You can place these in the my.cnf file or specify them as startup commands.
[mysqld]
gtid-mode=ON
enforce-gtid-consistency
Here, we turn on GTIDs and then add an additional option to enforce consistency across servers. You
will want to use this option if you plan to switch the master role or plan to set up failover.
On the slaves, we also need to specify the following options in the my.cnf file or specified as startup
commands. These options are required for all servers in the topology. In addition, the slaves can be
configured to use a table to store the master information. You can see these options in the following sample
configuration file for each slave:
[mysqld]
gtid-mode=ON
enforce-gtid-consistency
master-info-repository=TABLE
report-host=slave1
report-port=13002
■Note if you ever plan to use your master as a slave, you should use the options specified for the slave.
Once all the servers have been restarted and you have confirmed there are no errors, you can begin
configuring replication. Recall from Chapter 7 , we issue the CHANGE MASTER TO and START SLAVE statements
on each slave. The following presents an example of the statements:
CHANGE MASTER TO MASTER_HOST='master', MASTER_PORT=13001, MASTERUSER='rpl', MASTER
PASSWORD='secret', MASTER_AUTO_POSITION = 1;
START SLAVE;
Notice here we no longer need to specify the master binary log file and position. Instead, we simply
instruct the slave to use the automatic position feature provided by GTIDs. You can check the status of
replication on the slave using SHOW SLAVE STATUS. You should not see any errors. For more information
about GTIDs, see the online MySQL reference manual at http://dev.mysql.com/doc/refman/5.7/en/
replication-gtids.html.
■Tip use the \G option to see a vertical list of values instead of a tabular output. it makes wide rows easier
to read and, in this case, the output readable by mere mortals.