Replication in MySQL
One way replication can be used is to increase both
robustness and speed. For robustness you can have two
systems and can switch to the backup if you have
problems with the master. The extra speed is achieved by
sending a part of the non-updating queries to the
replica server. Of course this only works if
non-updating queries dominate, but that is the normal
Starting in Version 3.23.15, MySQL supports one-way
replication internally. One server acts as the master,
while the other acts as the slave. Note that one server
could play the roles of master in one pair and slave in
the other. The master server keeps a binary log of
updates and an index file to binary logs to keep track
of log rotation. The slave, upon connecting, informs the
master where it left off since the last successfully
propagated update, catches up on the updates, and then
blocks and waits for the master to notify it of the new
Note that if you are replicating a database, all updates
to this database should be done through the master!
On older servers one can use the update log to do simple
Another benefit of using replication is that one can get
live backups of the system by doing a backup on a slave
instead of doing it on the master.
MySQL - Replication Implementation Overview
MySQL replication is based on the server keeping track
of all changes to your database (updates, deletes, etc)
in the binary log. and the slave server(s) reading the
saved queries from the master server's binary log so
that the slave can execute the same queries on its copy
of the data.
It is very important to realize that the binary log is
simply a record starting from a fixed point in time (the
moment you enable binary logging). Any slaves which you
set up will need copies of all the data from your master
as it existed the moment that you enabled binary logging
on the master. If you start your slaves with data that
doesn't agree with what was on the master when the
binary log was started, your slaves may fail.
A future version (4.0) of MySQL will remove the need to
keep a (possibly large) snapshot of data for new slaves
that you might wish to set up through the live backup
functionality with no locking required. However, at this
time, it is necessary to block all writes either with a
global read lock or by shutting down the master while
taking a snapshot.
Once a slave is properly configured and running, it will
simply connect to the master and wait for updates to
process. If the master goes away or the slave loses
connectivity with your master, it will keep trying to
connect every master-connect-retry seconds until it is
able to reconnect and resume listening for updates.
Each slave keeps track of where it left off. The master
server has no knowledge of how many slaves there are or
which ones are up-to-date at any given time.
MySQL - HOWTO
Below is a quick description of how to set up complete
replication on your current MySQL server. It assumes you
want to replicate all your databases and have not
configured replication before. You will need to shutdown
your master server briefly to complete the steops
Make sure you have a recent version of MySQL installed
on the master and slave(s). Use Version 3.23.29 or
higher. Previous releases used a different binary log
format and had bugs which have been fixed in newer
releases. Please, do not report bugs until you have
verified that the problem is present in the latest
Set up special a replication user on the master with the
FILE privilege and permission to connect from all the
slaves. If the user is only doing replication (which is
recommended), you don't need to grant any additional
privileges. For example, to create a user named repl
which can access your master from any host, you might
use this command:
GRANT FILE ON *.* TO repl@"%" IDENTIFIED BY '';
Shut down MySQL on the master.
mysqladmin -u root -p<password> shutdown
Snapshot all the data on your master server. The easiest
way to do this (on Unix) is to simply use tar to produce
an archvie of your entrie data directory. The exact data
directory location depends on your installation.
tar -cvf /tmp/mysql-snapshot.tar /path/to/data-dir
Windows users can use WinZip or similar software to
create an archive of the data directory.
In my.cnf on the master add log-bin and server-id=unique
number to the [mysqld] section and restart it. It is
very important that the id of the slave is different
from the id of the master. Think of server-id as
something similar to the IP address - it uniquely
identifies the server instance in the comminity of
Restart MySQL on the master.
Add the following to my.cnf on the slave(s):
master-host=<hostname of the master>
master-user=<replication user name>
master-password=<replication user password>
master-port=<TCP/IP port for master>
server-id=<some unique number between 2 and 2^32-1>
replacing the values in <> with what is relevant to your
system. server-id must be different for each server
participating in replication. If you don't specify a
server-id, it will be set to 1 if you have not defined
master-host, else it will be set to 2. Note that in the
case of server-id omission the master will refuse
connections from all slaves, and the slave will refuse
to connect to a master. Thus, omitting server-id is only
good for backup with a binary log.
Copy the snapshot data into your data directory on your
slave(s). Make sure that the privileges on the files and
directories are correct. The user which MySQL runs as
needs to be able to read and write to them, just as on
Restart the slave(s).
After you have done the above, the slave(s) should
connect to the master and catch up on any updates which
happened since the snapshot was taken.
If you have forgotten to set server-id for the slave you
will get the following error in the error log file:
Warning: one should set server_id to a non-0 value if
master_host is set.
The server will not act as a slave.
If you have forgot to do this for the master, the slaves
will not be able to connect to the master.
If a slave is not able to replicate for any reason, you
will find error messages in the error log on the slave.
Once a slave is replicating, you will find a file called
master.info in the same directory as your error log. The
master.info file is used by the slave to keep track of
how much of the master's binary log is has processed. Do
not remove or edit the file, unless you really know what
you are doing. Even in that case, it is preferred that
you use CHANGE MASTER TO command.
Page Numbers : 1