Database: October 2003 Archives
Steps for configuring MySQL replication:
We have two boxes www1 and www2
Configuring the master (www1):
1. Edit /etc/my.cnf
2. Make sure that the following lines are present in the [mysqld] section:
log-bin
server-id=##
where ## is a unique number. Lets use 1 here.
3. run: mysql -u root -p
4. at mysql prompt, run the following commands:
mysql> GRANT FILE ON *.* TO repl@"%" IDENTIFIED BY 'replicate';
Here, the username which the slave would use to connect to master is repl and password is replicate;
mysql> FLUSH TABLES WITH READ LOCK;
This flushes all the tables and blocks write queries.
5. We now take a snapshot of the master server(www1).
a) tar -zcvf master_data.tar.gz /usr/local/mysql/var/
b) copy this to www2 ( say in /usr/local/src/ on www2)
6. on www1, run: mysql -u root -p
mysql > SHOW MASTER STATUS;
This will show the status of the master, and would look like the following:
+-----------------+----------+--------------+------------------+
| File | Position | Binlog_do_db | Binlog_ignore_db |
+-----------------+----------+--------------+------------------+
| www1-bin.051 | 702 | | |
+-----------------+----------+--------------+------------------+
Take a note of the values of "File" and "Position"
mysql > UNLOCK TABLES;
This re-enables the write activity
configuring the slave(www2)
1. Edit /etc/my.cnf
2. Make sure that the following lines are present in the [mysqld] section:
server-id=2
3. untar the file master.tar.gz in /usr/local/mysql/var/ (take a backup of the existing files and delete them)
4. restart mysql
5. run: mysql -u root -p
mysql > CHANGE MASTER TO MASTER_HOST='www1',
MASTER_USER='repl',
MASTER_PASSWORD='replicate',
MASTER_LOG_FILE='www1-bin.051',
MASTER_LOG_POS=702;
mysql > START SLAVE;
Replication should be running now.
