GNU Info

(mysql.info)Replication HOWTO

---

Next: Replication Features Prev: Replication Implementation Up: Replication

---

Enter node , (file) or (file)node

How To Set Up Replication
-------------------------

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 steps outlined
below.

  1. 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 release.

  2. 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 '<password>';

  3. Shut down MySQL on the master.

          mysqladmin -u root -p<password> shutdown

  4. Snapshot all the data on your master server.

     The easiest way to do this (on Unix) is to simply use *tar* to
     produce an archive of your entire 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.

  5. 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
     community of replication partners.

          [mysqld]
          log-bin
          server-id=1

  6. Restart MySQL on the master.

  7. 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.

  8. 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 the master.

  9. 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.

Replication Features

Replication Features and Known Problems

Replication Options

Replication Options in my.cnf

Replication SQL

SQL Commands Related to Replication

Replication FAQ

Replication FAQ

Replication Problems

Troubleshooting Replication