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 |
|---|
mysql -u root -p
|
On each server, as root:
| Code Block |
|---|
| Wiki Markup |
<\!-\- @page { size: 8.5in 11in; margin: 0.79in } { margin-bottom: 0.08in } \-->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: 1. Setting up replication accounts on each machine 2. Configuring the master and slave machines 3. Starting the replication To access MySQL as the root user, use "mysql \-u root \-p" and enter the password. On each server, as root: mysql> grant replication slave, replication client on *.* to 'replicate'@'<IP Address of slave server>' identified by '<Replication password>'; |
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';
|
On
...
10.0.0.2:
| Code Block |
|---|
mysql> grant replication slave, replication client on *.* to 'replicate'@'10.0.0.1' identified by 'replicationpassword';
|
On
...
the
...
master
...
server,
...
ensure
...
the
...
following
...
is
...
in
...
the
...
/etc/my.cnf
...
file:
| Code Block |
|---|
log_bin = mysql-bin server_id = 101 |
On
...
the
...
slave
...
server,
...
ensure
...
that
...
the
...
following
...
is
...
in
...
the
...
/etc/my.cnf
...
file:
| Code Block |
|---|
log_bin = mysql-bin server_id = 202 relay_log = mysql-relay-bin log_slave_updates = 1 read_only = 1 |
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 |
|---|
problems. On the master server: mysql> show master status; |
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;
|
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; |
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; |
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 |
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) |
You
...
should
...
now
...
have
...
a
...
fully
...
functional
...
MySQL
...
replication
...
setup.