Databases Reference
In-Depth Information
This replication architecture decouples the processes of fetching and replaying events
on the replica, which allows them to be asynchronous. That is, the I/O thread can work
independently of the SQL thread. It also places constraints on the replication process,
the most important of which is that
replication is serialized on the replica
. This means
updates that might have run in parallel (in different threads) on the master cannot be
parallelized on the replica, because they're executed in a single thread. As we'll see later,
this is a performance bottleneck for many workloads. There are some solutions to this,
but most users are still subject to the single-threaded constraint.
Setting Up Replication
Setting up replication is a fairly simple process in MySQL, but there are many variations
on the basic steps, depending on the scenario. The most basic scenario is a freshly
installed master and replica. At a high level, the process is as follows:
1. Set up replication accounts on each
3
server.
2. Configure the master and replica.
3. Instruct the replica to connect to and replicate from the master.
This assumes that many default settings will suffice, which is true if you've just installed
the master and replica and they have the same data (the default
mysql
database). We
show you here how to do each step in turn, assuming your servers are called
server1
(IP address 192.168.0.1) and
server2
(IP address 192.168.0.2). We then explain how
to initialize a replica from a server that's already up and running and explore the rec-
ommended replication configuration.
Creating Replication Accounts
MySQL has a few special privileges that let the replication processes run. The slave
I/O thread, which runs on the replica, makes a TCP/IP connection to the master. This
means you must create a user account on the master and give it the proper privileges,
so the I/O thread can connect as that user and read the master's binary log. Here's how
to create that user account, which we'll call
repl
:
mysql>
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
->
TO repl@'192.168.0.%' IDENTIFIED BY 'p4ssword',;
We create this user account on both the master and the replica. Note that we restricted
the user to the local network, because the replication account has the ability to read all
changes to the server, which makes it a privileged account. (Even though it has no
ability to
SELECT
or change data, it can still see some of the data in the binary logs.)
3. This isn't strictly necessary, but it's something we recommend; we'll explain later.
