( Replication HOWTO

Info Catalog ( Replication Implementation Details ( Replication ( Replication Compatibility
 6.4 How to Set Up Replication
 Here is a quick description of how to set up complete replication of
 your current MySQL server. It assumes that you want to replicate all
 your databases and have not configured replication before. You will need
 to shut down your master server briefly to complete the steps outlined
 The procedure is written in terms of setting up a single slave, but you
 can use it to set up multiple slaves.
 While this method is the most straightforward way to set up a slave, it
 is not the only one. For example, if you have a snapshot of the
 master's data, and the master has its server ID set and binary logging
 enabled, you can set up a slave without shutting down the master or
 even blocking updates to it.  For more details, please see 
 Replication FAQ.
 If you want to administer a MySQL replication setup, we suggest that
 you read this entire chapter through and try all statements mentioned in
  Replication Master SQL and  Replication Slave SQL.  You
 should also familiarize yourself with replication startup options
 described in  Replication Options.
 Note that this procedure and some of the replication SQL statements in
 later sections refer to the `SUPER' privilege. Prior to MySQL 4.0.2,
 use the `PROCESS' privilege instead.
   1. Make sure that you have a recent version of MySQL installed on the
      master and slaves, and that these versions are compatible
      according to the table shown in  Replication Compatibility.
      Please do not report bugs until you have verified that the problem
      is present in the latest release.
   2. Set up an account on the master server that the slave server can
      use to connect. This account must be given the `REPLICATION SLAVE'
      privilege.  If the account is used only for replication (which is
      recommended), you don't need to grant any additional privileges.
      Suppose that your domain is `' and you want to create
      an account with a username of `repl' such that slave servers can
      use the account to access the master server from any host in your
      domain using a password of `slavepass'.  To create the account,
      this use `GRANT' statement:
           mysql> GRANT REPLICATION SLAVE ON *.*
               -> TO 'repl'@'' IDENTIFIED BY 'slavepass';
      For MySQL versions older than 4.0.2, the `REPLICATION SLAVE'
      privilege does not exist. Grant the `FILE' privilege instead:
           mysql> GRANT FILE ON *.*
               -> TO 'repl'@'' IDENTIFIED BY 'slavepass';
      If you plan to use the `LOAD TABLE FROM MASTER' or `LOAD DATA FROM
      MASTER' statements from the slave host, you will need to grant this
      account additional privileges:
         * Grant the account the `SUPER' and `RELOAD' global privileges.
         * Grant the `SELECT' privilege for all tables that you want to
           load. Any master tables from which the account cannot
           `SELECT' will be ignored by `LOAD DATA FROM MASTER'.
   3. If you are using only `MyISAM' tables, flush all the tables and
      block write statements by executing a `FLUSH TABLES WITH READ
      LOCK' statement.
           mysql> FLUSH TABLES WITH READ LOCK;
      Leave the client running from which you issue the `FLUSH TABLES'
      statement so that the read lock remains in effect. (If you exit
      the client, the lock is released.) Then take a snapshot of the
      data on your master server.
      The easiest way to create a snapshot is to use an archiving
      program to make a binary backup of the databases in your master's
      data directory.  For example, use `tar' on Unix, or
      `PowerArchiver', `WinRAR', `WinZip', or any similar software on
      Windows.  To use `tar' to create an archive that includes all
      databases, change location into the master server's data
      directory, then execute this command:
           shell> tar -cvf /tmp/mysql-snapshot.tar .
      If you want the archive to include only a database called
      `this_db', use this command instead:
           shell> tar -cvf /tmp/mysql-snapshot.tar ./this_db
      Then copy the archive file to the `/tmp' directory on the slave
      server host. On that machine, change location into the slave's
      data directory, and unpack the archive file using this command:
           shell> tar -xvf /tmp/mysql-snapshot.tar
      You may not want to replicate the `mysql' database if the slave
      server has a different set of user accounts from those that exist
      on the master. In this case, you should exclude it from the
      archive. You also need not include any log files in the archive,
      or the `' or `' files.
      While the read lock placed by `FLUSH TABLES WITH READ LOCK' is in
      effect, read the value of the current binary log name and offset
      on the master:
           mysql > SHOW MASTER STATUS;
           | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
           | mysql-bin.003 | 73       | test         | manual,mysql     |
      The `File' column shows the name of the log,  while `Position'
      shows the offset. In this example, the binary log value is
      `mysql-bin.003' and the offset is 73. Record the values. You will
      need to use them later when you are setting up the slave. They
      represent the replication coordinates at which the slave should
      begin processing new updates from the master.
      After you have taken the snapshot and recorded the log name and
      offset, you can re-enable write activity on the master:
           mysql> UNLOCK TABLES;
      If you are using `InnoDB' tables, ideally you should use the
      `InnoDB Hot Backup' tool.  It takes a consistent snapshot without
      acquiring any locks on the master server, and records the log name
      and offset corresponding to the snapshot to be later used on the
      slave.  `InnoDB Hot Backup' is a non-free (commercial) additional
      tool that is not included in the standard MySQL distribution.  See
      the `InnoDB Hot Backup' home page at
      `' for detailed information and
      Without the `Hot Backup' tool, the quickest way to take a binary
      snapshot of `InnoDB' tables is to shut down the master server and
      copy the `InnoDB' data files, log files, and table definition
      files (`.frm' files). To record the current log file name and
      offset, you should issue the following statements before you shut
      down the server:
           mysql> FLUSH TABLES WITH READ LOCK;
           mysql> SHOW MASTER STATUS;
      Then record the log name and the offset from the output of `SHOW
      MASTER STATUS' as was shown earlier. After recording the log name
      and the offset, shut down the server _without_ unlocking the
      tables to make sure that the server goes down with the snapshot
      corresponding to the current log file and offset:
           shell> mysqladmin -u root shutdown
      An alternative that works for both `MyISAM' and `InnoDB' tables is
      to take an SQL dump of the master instead of a binary copy as
      described in the preceding discussion. For this, you can use
      `mysqldump --master-data' on your master and later load the SQL
      dump file into your slave. However, this is slower than doing a
      binary copy.
      If the master has been previously running without `--log-bin'
      enabled, the log name and position values displayed by `SHOW
      MASTER STATUS' or `mysqldump --master-data' will be empty. In that
      case, the values that you will need to use later when specifying
      the slave's log file and position are the empty string (`''') and
   4. Make sure that the `[mysqld]' section of the `my.cnf' file on the
      master host includes a `log-bin' option. The section should also
      have a `server-id=master_id' option, where `master_id' must be a
      positive integer value from 1 to 2^32 - 1. For example:
      If those options are not present, add them and restart the server.
   5. Stop the server that is to be used as a slave server and add the
      following to its `my.cnf' file:
      The `slave_id' value, like the `master_id' value, must be a
      positive integer value from 1 to 2^32 - 1. In addition, it is very
      important that the ID of the slave be different from the ID of the
      master. For example:
      If you are setting up multiple slaves, each one must have a unique
      `server-id' value that differs from that of the master and from
      each of the other slaves.  Think of `server-id' values as
      something similar to IP addresses: These IDs uniquely identify
      each server instance in the community of replication partners.
      If you don't specify a `server-id' value, 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, a master will refuse
      connections from all slaves, and a slave will refuse to connect to
      a master. Thus, omitting `server-id' is good only for backup with a
      binary log.
   6. If you made a binary backup of the master server's data, copy it
      to the slave server's data directory before starting the slave.
      Make sure that the privileges on the files and directories are
      correct. The user that the server MySQL runs as must able to read
      and write the files, just as on the master.
      If you made a backup using `mysqldump', start the slave first (see
      next step).
   7. Start the slave server. If it has been replicating previously,
      start the slave server with the `--skip-slave-start' option so
      that it doesn't immediately try to connect to its master.  You
      also may want to start the slave server with the `--log-warnings'
      option (enabled by default as of MySQL 4.0.19 and 4.1.2), to get
      more messages in the error log about problems (for example,
      network or connection problems).  As of MySQL 4.0.21 and 4.1.3,
      aborted connections are not logged to the error log unless the
      value is greater than 1.
   8. If you made a backup of the master server's data using
      `mysqldump', load the dump file into the slave server:
           shell> mysql -u root -p < dump_file.sql
   9. Execute the following statement on the slave, replacing the option
      values with the actual values relevant to your system:
           mysql> CHANGE MASTER TO
               ->     MASTER_HOST='master_host_name',
               ->     MASTER_USER='replication_user_name',
               ->     MASTER_PASSWORD='replication_password',
               ->     MASTER_LOG_FILE='recorded_log_file_name',
               ->     MASTER_LOG_POS=recorded_log_position;
      The following table shows the maximum length for the string
      `MASTER_HOST'               60
      `MASTER_USER'               16
      `MASTER_PASSWORD'           32
      `MASTER_LOG_FILE'           255
  10. Start the slave threads:
           mysql> START SLAVE;
 After you have performed this procedure, the slave should connect to
 the master and catch up on any updates that have occurred since the
 snapshot was taken.
 If you have forgotten to set the `server-id' value for the master,
 slaves will not be able to connect to it.
 If you have forgotten to set the `server-id' value for the slave, you
 will get the following error in its error log:
      Warning: You should set server-id to a non-0 value if master_host is set;
      we force server id to 2, but this MySQL server will not act as a slave.
 You will also find error messages in the slave's error log if it is not
 able to replicate for any other reason.
 Once a slave is replicating, you will find in its data directory one
 file named `' and another named `'.  The slave
 uses these two files to keep track of how much of the master's binary
 log it has processed. *Do not* remove or edit these files, unless you
 really know what you are doing and understand the implications. Even in
 that case, it is preferred that you use the `CHANGE MASTER TO'
 * The content of `' overrides some options specified
 on the command line or in `my.cnf'.  See  Replication Options
 for more details.
 Once you have a snapshot, you can use it to set up other slaves by
 following the slave portion of the procedure just described. You do not
 need to take another snapshot of the master; you can use the same one
 for each slave.
Info Catalog ( Replication Implementation Details ( Replication ( Replication Compatibility
automatically generated byinfo2html