MySQL Manual | 4.10.4 How To Set Up Replication

4 Database Administration
- 4.10 Replication in MySQL

Previous / Next / Up / Table of Contents

4.10.4 How To Set Up Replication

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

While this method is the most straightforward way to set up a slave, it is not the only one. For example, if you already have a snapshot of the master, and the master already has server id set and binary logging enabled, you can set up a slave without shutting the master down or even blocking the updates. For more details, please see section 4.10.8 Replication FAQ.

If you want to be able to administrate a MySQL replication setup, we suggest that you read this entire chapter through and try all commands mentioned in section 4.10.7 SQL Commands Related to Replication. You should also familiarise yourself with replication startup options in `my.cnf' in section 4.10.6 Replication Options in `my.cnf'.

Make sure you have a recent version of MySQL installed on the master and slave(s), and that these versions match with the above yes/no array. Please, do not report bugs until you have verified that the problem is present in the latest release.
Set up a replication user on the master with the FILE (in MySQL versions older than 4.0.2) or REPLICATION SLAVE privilege in newer MySQL versions. You must also have given this user 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:
```
mysql> GRANT FILE ON *.* TO repl@"%" IDENTIFIED BY '<password>'; # master < 4.0.2
```
```
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@"%" IDENTIFIED BY '<password>'; # master >= 4.0.2
```
If you plan to use the LOAD TABLE FROM MASTER or LOAD DATA FROM MASTER commands, you will also need to grant, on the master, to the above user, the REPLICATION CLIENT (or SUPER if the master is older than 4.0.13) privilege, the RELOAD privilege, and SELECT privileges on all tables you want to load. All master tables on which the user can't SELECT will be ignored by LOAD DATA FROM MASTER.
If you are using MyISAM tables, flush all the tables and block write queries by executing FLUSH TABLES WITH READ LOCK command.
```
mysql> FLUSH TABLES WITH READ LOCK;
```
and then take a snapshot of the data on your master server. The easiest way to do this is to simply use an archiving program (tar on Unix, PowerArchiver, WinRAR, WinZIP or any similar software on Windows) to produce an archive of the databases in your master's data directory. Include all the databases you want to replicate.
```
tar -cvf /tmp/mysql-snapshot.tar /path/to/data-dir
```
If you want to replicate only a database called this_db, you can do just this:
```
tar -cvf /tmp/mysql-snapshot.tar /path/to/data-dir/this_db
```
You may not want to replicate the mysql database, then you can exclude it from the archive too. Into the archive you needn't copy the master's binary logs, error log, `master.info' / `relay-log.info' / relay logs (if the master is itself a slave of another machine). You can exclude all this from the archive. After or during the process of taking a snapshot, read the value of the current binary log name and the offset on the master:
```
mysql > SHOW MASTER STATUS;
+---------------+----------+--------------+-------------------------------+
| File          | Position | Binlog_do_db | Binlog_ignore_db              |
+---------------+----------+--------------+-------------------------------+
| mysql-bin.003 | 73       | test,bar     | foo,manual,sasha_likes_to_run |
+---------------+----------+--------------+-------------------------------+
1 row in set (0.06 sec)
```
The File column shows the name of the log, while Position shows the offset. In the above 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. Once 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 that is available to those who purchase MySQL commercial licenses, support, or the backup tool itself. It will take a consistent snapshot without acquiring any locks on the master server, and record the log name and offset corresponding to the snapshot to be later used on the slave. More information about the tool is avalaible at http://www.innodb.com/hotbackup.html. Without the hot backup tool, the quickest way to take a snapshot of InnoDB tables is to shut the master server down and copy the InnoDB datafiles and logs, and the table definition files (.frm). To record the current log file name and offset, you should do the following before you shut down the server:
```
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
```
And then record the log name and the offset from the output of SHOW MASTER STATUS as was shown earlier. Once you have recorded the log name and the offset, shut the server down without unlocking the tables to make sure it goes down with the snapshot corresponding to the current log file and offset:
```
shell> mysqladmin -uroot shutdown
```
An alternative for both MyISAM and InnoDB tables is taking an SQL dump of the master instead of a binary copy like above; for this you can use mysqldump --master-data on your master and later run this SQL dump into your slave. This is however slower than doing a binary copy. If the master has been previously running without log-bin enabled, the values of log name and position displayed by SHOW MASTER STATUS or mysqldump will be empty. In that case, record empty string ('') for the log name, and 4 for the offset.
Make sure that `my.cnf' on the master has log-bin if it is not there already and server-id=unique number in the [mysqld] section. If those options are not present, add them and restart the server. 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
```
Stop the slave server(s). Add the following to `my.cnf' on the slave(s):
```
server-id=<some unique number between 1 and 2^32-1 that is different from
 that of the master>
```
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.
Start the slave server(s). If it has been replicating previously, start the slave server with option skip-slave-start. You may want to start the slave server with option log-warnings, this way you will get more messages about network/connection problems for example.
Copy the snapshot data into your data directory on your slave(s) (or execute the output of the above mysqldump into the mysql). 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.
Execute the following command on the slave(s):
```
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 offset>;
```
replacing the values in <> with the actual values relevant to your system. The maximum string length for the above variables are:

MASTER_HOST 60

MASTER_USER 16

MASTER_PASSWORD 32

MASTER_LOG_FILE 255
Start the slave threads:
```
mysql> START SLAVE; 
```

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 forgotten 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' and one called `relay-log.info' in the data directory. These two files are used by the slave 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. Even in that case, it is preferred that you use CHANGE MASTER TO command. NOTE: the content of `master.info' overrides some options specified on the command-line or in `my.cnf' (see section 4.10.6 Replication Options in `my.cnf' for more details).

Now that you have a snapshot, you can use it to set up other slaves. To do so, follow the slave portion of the procedure described above. You do not need to take another snapshot of the master.

User Comments

Posted by George Thiruva on Friday June 28 2002, @9:39am

MASTER_HOST	60
MASTER_USER	16
MASTER_PASSWORD	32
MASTER_LOG_FILE	255