(mysql.info) replication-howto
Info Catalog
(mysql.info) replication-implementation-details
(mysql.info) replication
(mysql.info) replication-compatibility
6.4 How to Set Up Replication
=============================
This section briefly describes how to set up complete replication of a
MySQL server. It assumes that you want to replicate all databases on
the master and have not previously configured replication. You must
shut down your master server briefly to complete the steps outlined
here.
This procedure is written in terms of setting up a single slave, but
you can repeat it to set up multiple slaves.
Although 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 already 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 the replication startup
options described in replication-options.
* This procedure and some of the replication SQL statements shown
in later sections require the `SUPER' privilege.
1. Make sure that the versions of MySQL installed on the master and
slave are compatible according to the table shown in
replication-compatibility. Ideally, you should use the most
recent version of MySQL on both master and slave.
If you encounter a problem, please do not report it as a bug until
you have verified that the problem is present in the latest MySQL
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 `mydomain.com' and that 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, use this `GRANT' statement:
mysql> GRANT REPLICATION SLAVE ON *.*
-> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
If you plan to use the `LOAD TABLE FROM MASTER' or `LOAD DATA FROM
MASTER' statements from the slave host, you must 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'.
For additional information about setting up user accounts and
privileges, see user-account-management.
3. Flush all the tables and block write statements by executing a
`FLUSH TABLES WITH READ LOCK' statement:
mysql> FLUSH TABLES WITH READ LOCK;
For `InnoDB' tables, note that `FLUSH TABLES WITH READ LOCK' also
blocks `COMMIT' operations. When you have acquired a global read
lock, you can start a filesystem snapshot of your `InnoDB' tables.
Internally (inside the `InnoDB' storage engine) the snapshot won't
be consistent (because the `InnoDB' caches are not flushed), but
this is not a cause for concern, because `InnoDB' resolves this at
startup and delivers a consistent result. This means that `InnoDB'
can perform crash recovery when started on this snapshot, without
corruption. However, there is no way to stop the MySQL server
while insuring a consistent snapshot of your `InnoDB' tables.
Leave running the client 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 `master.info' or `relay-log.info' 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 and `Position' shows
the offset within the file. In this example, the binary log file is
`mysql-bin.003' and the offset is 73. Record these values. You
need 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.
If the master has been running previously without binary logging
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 need to use later when specifying the
slave's log file and position are the empty string (`''') and `4'.
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, which 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. `Hot Backup' is an additional non-free (commercial)
tool that is not included in the standard MySQL distribution. See
the ``InnoDB' Hot Backup' home page at
`http://www.innodb.com/manual.php' for detailed information.
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 format 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.
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 232 - 1. For example:
[mysqld]
log-bin=mysql-bin
server-id=1
If those options are not present, add them and restart the server.
The server cannot act as a replication master unless binary
logging is enabled.
* For the greatest possible durability and consistency in a
replication setup using `InnoDB' with transactions, you should use
`innodb_flush_log_at_trx_commit=1', `sync_binlog=1', and, before
MySQL 5.0.3, `innodb_safe_binlog', in the master `my.cnf' file.
(`innodb_safe_binlog' is not needed from 5.0.3 on.)
5. Stop the server that is to be used as a slave and add the
following lines to its `my.cnf' file:
[mysqld]
server-id=SLAVE_ID
The SLAVE_ID value, like the MASTER_ID value, must be a positive
integer value from 1 to 232 - 1. In addition, it is necessary that
the ID of the slave be different from the ID of the master. For
example:
[mysqld]
server-id=2
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 do not specify a `server-id' value, it is set to 1 if you
have not defined `master-host'; otherwise it is set to 2. Note
that in the case of `server-id' omission, a master refuses
connections from all slaves, and a slave refuses 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 system account that you use to run the slave server
must be able to read and write the files, just as on the master.
If you made a backup using `mysqldump', start the slave first. The
dump file is loaded in a later 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 to
get more messages in the error log about problems (for example,
network or connection problems). The option is enabled by default,
but aborted connections are not logged to the error log unless the
option 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 allowable length for the
string-valued options:
`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' option for the master,
slaves cannot connect to it.
If you have forgotten to set the `server-id' option for the slave, you
get the following error in the slave's error log:
Warning: You should set server-id to a non-0 value if master_host
is set; we will force server id to 2, but this MySQL server will
not act as a slave.
You 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 can find in its data directory one
file named `master.info' and another named `relay-log.info'. 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
know exactly what you are doing and fully understand the implications.
Even in that case, it is preferred that you use the `CHANGE MASTER TO'
statement to change replication parameters. The slave will use the
values specified in the statement to update the status files
automatically.
* The content of `master.info' overrides some of the server
options specified on the command line or in `my.cnf'. See
replication-options, for more details.
Once you have a snapshot of the master, 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
(mysql.info) replication-implementation-details
(mysql.info) replication
(mysql.info) replication-compatibility
automatically generated byinfo2html