...
This
...
document
...
will
...
detail
...
how
...
to
...
configure
...
a
...
generic
...
MySQL
...
instance
...
using
...
replication.
...
This
...
document
...
does
...
not
...
detail
...
a
...
specific
...
installation
...
case,
...
but
...
is
...
rather
...
intended
...
to
...
be
...
used
...
as
...
a
...
guide.
...
You
...
should
...
make
...
sure
...
you
...
start
...
with
...
a
...
reasonably
...
good
...
copy
...
of
...
the
...
master
...
instance
...
installed
...
on
...
the
...
slave
...
machine.
...
You
...
can
...
do
...
this
...
by
...
shutting
...
both
...
the
...
master
...
and
...
slave
...
instances
...
down,
...
and
...
taking
...
a
...
cold
...
copy
...
of
...
the
...
MySQL
...
database
...
files
...
from
...
the
...
master
...
server
...
and
...
placing
...
them
...
onto
...
the
...
slave
...
server.
...
The
...
exact
...
location
...
of
...
these
...
files
...
will
...
vary
...
depending
...
on
...
where
...
MySQL
...
was
...
installed.
...
Setting
...
up
...
Replication:
...
The
...
process
...
of
...
setting
...
up
...
MySQL
...
replication
...
consists
...
of
...
three
...
primary
...
parts:
...
- Setting
...
- up
...
- replication
...
- accounts
...
- on
...
- each
...
- machine
...
- Configuring
...
- the
...
- master
...
- and
...
- slave
...
- machines
...
- Starting
...
- the replication
To access a MySQL instance as the root user, use the following command:
| Code Block |
|---|
replication \\ \\ To access a MySQL instance as the root user, use the following command: \\ {code} mysql -u root -p {code} \\ On each |
On each server,
...
as
...
root:
| Code Block |
|---|
} mysql> grant replication slave, replication client on *.* to 'replicate'@'<IP Address of slave server>' identified by '<Replication password>'; {code} |
In
...
this
...
case,
...
we
...
want
...
the
...
"IP
...
Address
...
of
...
slave
...
server",
...
for
...
each
...
machine,
...
to
...
be
...
the
...
IP
...
address
...
of
...
the
...
slave
...
server
...
that
...
will
...
connect
...
to
...
it.
...
For
...
instance,
...
if
...
your
...
master
...
server
...
is
...
10.0.0.1
...
and
...
your
...
slave
...
server
...
is
...
10.0.0.2,
...
you
...
would
...
do
...
the
...
following:
...
On
...
10.0.0.1:
| Code Block |
|---|
} mysql> grant replication slave, replication client on *.* to 'replicate'@'10.0.0.2' identified by 'replicationpassword'; {code} |
On
...
10.0.0.2:
| Code Block |
|---|
} mysql> grant replication slave, replication client on *.* to 'replicate'@'10.0.0.1' identified by 'replicationpassword'; {code} |
On
...
the
...
master
...
server,
...
ensure
...
the
...
following
...
is
...
in
...
the
...
/etc/my.cnf
...
file:
| Code Block |
|---|
} log_bin = mysql-bin server_id = 1 {code} |
On
...
the
...
slave
...
server,
...
ensure
...
that
...
the
...
following
...
is
...
in
...
the
...
/etc/my.cnf
...
file:
| Code Block |
|---|
} log_bin = mysql-bin server_id = 2 relay_log = mysql-relay-bin log_slave_updates = 1 read_only = 1 {code} |
Please
...
note
...
that
...
the
...
server_id
...
should
...
only
...
be
...
2
...
for
...
the
...
first
...
slave
...
server.
...
Each
...
server
...
(including
...
the
...
master)
...
should
...
have
...
a
...
different
...
server_id.
...
You
...
don't
...
have
...
to
...
use
...
1
...
and
...
2;
...
the
...
point
...
is
...
to
...
choose
...
numbers
...
that
...
are
...
unique.
...
Once
...
the
...
my.cnf
...
files
...
are
...
updated
...
on
...
both
...
machines,
...
you
...
should
...
restart
...
the
...
MySQL
...
instances
...
on
...
both
...
machines
...
in
...
order
...
for
...
them
...
to
...
pick
...
up
...
the
...
changes.
...
Please
...
do
...
not
...
place
...
replication
...
configuration
...
options
...
such
...
as
...
"master_host"
...
and
...
"master_port"
...
into
...
the
...
slave
...
my.cnf
...
file,
...
as
...
this
...
can
...
cause
...
problems.
...
Also
...
note
...
that
...
the
...
slave
...
must
...
be
...
able
...
to
...
connect
...
to
...
the
...
master
...
server,
...
so
...
adjust
...
any
...
firewall
...
rules
...
for
...
port
...
3306
...
accordingly.
...
On
...
the
...
master
...
server:
| Code Block |
|---|
} mysql> show master status; {code} |
You
...
should
...
see
...
a
...
binary
...
log
...
file
...
name
...
returned
...
in
...
the
...
output
...
of
...
the
...
command.
...
That
...
file
...
name
...
is
...
needed
...
for
...
the
...
next
...
step,
...
which
...
is
...
performed
...
on
...
the
...
slave
...
server.
...
On
...
the
...
slave
...
server:
| Code Block |
|---|
} mysql> change master to master_host='<master host name>', master_user='replicate', master_password='<replication password>', master_log_file='<log file name from "show master status" command on master server>', master_log_pos=0; {code} |
So,
...
for
...
example,
...
if
...
"show
...
master
...
status"
...
on
...
your
...
master
...
server
...
returns
...
the
...
filename
...
"mysql-bin.000001",
...
your
...
master
...
server
...
is
...
named
...
"masterserver",
...
and
...
your
...
replication
...
password
...
is
...
"password",
...
you
...
would
...
use
...
the
...
following
...
command
...
on
...
your
...
slave
...
server:
| Code Block |
|---|
} mysql> change master to master_host='masterserver', master_user='replicate', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=0; {code} |
At
...
this
...
point,
...
your
...
slave
...
server
...
is
...
configured
...
to
...
connect
...
to
...
the
...
master
...
server.
...
The
...
next
...
step
...
is
...
telling
...
the
...
slave
...
server
...
to
...
actually
...
start
...
the
...
replication
...
process.
...
You
...
can
...
do
...
this
...
by
...
running
...
the
...
following
...
command
...
on
...
the
...
slave
...
server:
| Code Block |
|---|
} mysql> start slave; {code} |
Replication
...
should
...
now
...
be
...
fully
...
operational
...
on
...
your
...
MySQL
...
installation.
...
To
...
verify
...
that
...
the
...
replication
...
is
...
actually,
...
occurring,
...
you
...
could
...
issue
...
the
...
following
...
command
...
on
...
the
...
slave
...
server:
| Code Block |
|---|
} mysql> show slave status\G {code} |
Your
...
output
...
from
...
the
...
"show
...
slave
...
status\G"
...
command
...
should
...
be
...
something
...
similar
...
to
...
the
...
following,
...
although
...
it
...
probably
...
will
...
not
...
be
...
identical:
| Code Block |
|---|
} Slave_IO_State: Waiting for master to send event Master_Host: masterserver.mit.edu Master_User: replicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 298070 Relay_Log_File: mysql-relay-bin.000123 Relay_Log_Pos: 5250 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 298070 Relay_Log_Space: 5250 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) {code} |
You
...
should
...
now
...
have
...
a
...
fully
...
functional
...
MySQL
...
replication
...
setup.