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 the section called “Replication Compatibility Between MySQL Versions”.

Please do not report bugs until you have verified that the problem is present in the latest 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 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'@'%.mydomain.com' 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'@'%.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 will need to grant this account additional privileges:

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 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, 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 http://www.innodb.com/manual.php for detailed information and screenshots.

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

[mysqld]
log-bin
server-id=1

If those options are not present, add them and restart the server.

Stop the server that is to be used as a slave server and add the following 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 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:

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

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

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.

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 length for the string options:

Start the slave threads:

mysql> START SLAVE;