MySQL for the Internet of Things

ChapTEr 8 ■ DEmonsTraTion of high availabiliTy TEChniquEs

Now that you understand and can use GTIDs, you can start using some advanced replication concepts
such as failover.

teStING repLICatION WIth MYSQL UtILItIeS

in the following sections, i present several concepts and examples that use replication. To run the

examples, particularly the scaling and failover examples, you will need to set up a replication topology

of one master and two or three slaves. The following are steps you can take to set up a test replication

topology on your own system. Documentation for each of these steps and examples is included in the

online mysql utilities documentation (http://dev.mysql.com/doc/mysql-utilities/1.6/en/).

1. Clone a running (installed) mysql server with mysqlreplicate. make at least three
   or four clones assigning the correct port, server iD, database director, and so on
   (change all occurrences of 13001 in the command).

mysqlserverclone --server=root:secret@localhost:3306

--new-data=/tmp/13001

--new-port=13001 --new-id=1 --root-password=root --del

--mysqld="--log_bin=mysql-bin

--gtid-mode=on --enforce-gtid-consistency

--master-info-repository=table

--report-host=localhost --report-port=13001"

2. set up the replication user on each server. use the mysql client to connect to each
   server in turn and run the following sql statements:

SET @@sql_log_bin=0;

CREATE USER 'rpl'@'localhost' IDENTIFIED BY 'rpl';

GRANT REPLICATION SLAVE ON *.* TO 'rpl'@'localhost';

SET @@sql_log_bin=1;

3. once all servers are cloned, set up replication with mysqlreplication. here i use
   the server on port 13001 as the master. run this command once for each slave
   (change the slave port accordingly).

mysqlreplicate --master=root:root@localhost:13001

--slave=root:root@localhost:13002

--rpl-user=rpl:rpl

4. To see the topology, use mysqlrplshow using the server and the discover slaves
   login option.

mysqlrplshow --master=root:root@localhost:13001 --disco=root:root

Wasn’t that a lot easier than doing it manually? i have included with the source code for the book a file

named setup_topology.txt that demonstrates commands you can use to create the topology with

mysql utilities. Just change the options to match your system.