3.3.1 How do you setup and use replication?

MySQL has built-in support for several types of replication.
Replication is usually employed with the purpose of increasing
the performance and/or the fault-tolerance of the server and by
extension the application. However, setting up replication can
be a somewhat complicated and error prone process. But fear not,
MySQL Utilities has tools that can help simplify and even automate
several replication related tasks.

Consider a scenario where replication is used to obtain
scalability, i.e. to increase the performance. Let us imagine an
online shopping service. The shop started out small so a single
server was enough to handle all the requests, however now it has
become quite popular and as a result that single server is no
longer able to handle all the requests. Being an online store,
most of the operations are read operations (checking existing
products, reviews, stock availability, etc).

Objectives

Our goal is to use replication in order to improve the
throughput of the service by adding more servers which becomes
replicas of the already existing server. These replicas allows
scaling out of the service by taking up all the read requests,
leaving the old server (now called the master) in charge of
the writes. Rather than doing everything "by hand" with the
mysql command line, we are going to setup this replication
scenario using a single script,
mysqlreplicate which does most of the hard
work for us. We then check the result using the
mysqlrpladmin utility.

Let us assume the existing server, Server1, is running on port
13001 on the local machine with IP 192.168.1.1 and that we
want to add 2 new servers, Server2 running on
192.168.1.2:13001 and Server3 running on 192.168.1.3:3306.

Discussion

In the above example we made use of the
mysqlreplicate utility to setup a single
tier replication topology, where the existing server is now
the master for the two new servers which act as slaves. Notice
how we used the address of the old existing server in the
--master option and in
the --slave option we
used the addresses of the new servers. Also notice the use of
the -b flag, this makes replication start from the first event
recorded in the master's binary log.

Also notice how we used the mysqlrplcheck
utility to check the health of the replication. In this case,
the failing test "Check slave delay" is expected, since the
slaves are catching up with the master. When the slaves have
read and applied all the transactions from the master's binary
log the "Check slave delay" test passes. Also, in case the
slave wasn't properly configured and pointing to the master
specified the "Is slave connect to master" test would notify
us of that with a FAIL or WARN status.

Permissions Required

The m_account user needs the following privileges for the
mysqlreplicate: SELECT and INSERT
privileges on mysql database, REPLICATION SLAVE, REPLICATION
CLIENT and GRANT OPTION. As for the slave_acc users, they need
the SUPER privilege. The repl user, used as the argument for
the
--rpl-user
option, is either created automatically or if it exists, it
needs the REPLICATION SLAVE privilege.

Also, when using GTIDs, the slave_acc users must also have
SELECT privilege over the mysql database in order to run the
mysqlrplcheck utility successfully.

Tips and Tricks

In the mysqlreplicate utility we could have
also used the --test-db option which creates a dummy database
to test the replication setup. However, the
mysqlrplcheck provides more detailed
information in that regard.

As previously stated, the -b option tells the utility to start
replication from the first event recorded in the master's
binary log. Omitting this flag, in turn, makes the slaves
replicate only what is stored in the master's binary log from
the present moment onward.

Furthermore, using the
--master-log-file and
--master-log-pos
options it is possible to specify respectively the master log
file and the master log position from which the slave starts
its replication process.

The -p flag can be used to ensure that the replication setup
is only executed in case the storage engines match in both the
master and the slave.

Regarding the mysqlrplcheck utility, we can
use the -s option to check the output of the show slave status
command. This can be useful for instance to check what might
be causing the "Is slave connected" test to fail. We can also
use the
--master-log-file
option to specify the name of the master information file to
read.

Lastly, we can use the
--verbose option in
order to get more information about what is happening "under
the hood".