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 Section 6.10, “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 Section 13.6.1, “SQL Statements for Controlling Master Servers”, and Section 13.6.2, “SQL Statements for Controlling Slave Servers”. You should also familiarize yourself with the replication startup options described in Section 6.8, “Replication Startup Options”.
Note: This procedure and some of
the replication SQL statements shown in later sections require the
SUPER
privilege.
Make sure that the versions of MySQL installed on the master and slave are compatible according to the table shown in Section 6.5, “Replication Compatibility Between MySQL Versions”. 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.
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 Section 5.9, “MySQL User Account Management”.
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.
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=
option, where master_id
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.
Note: 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.)
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.
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.
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.
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
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 |
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.
Note: The content of
master.info
overrides some of the server
options specified on the command line or in
my.cnf
. See
Section 6.8, “Replication Startup 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.