semicolon.in.th Programming is real fun!

[Master] = Master database server
[Slave] = Slave database server

Scenario
We would like to setup 1:1 Master-Slave MySQL Replication.

Environment
[Master]
OS: Fedora 9
IP: 192.168.0.40

[Slave]
OS: Fedora 9
IP: 192.168.0.41

Step-by-step guide for MyISAM tables
1. At [Master], grant replication privileges for [Slave]

mysql> GRANT REPLICATION SLAVE ON *.*
    -> TO 'repl'@'192.168.0.41' IDENTIFIED BY 'slavepass';

Note: We will use "Username" (repl) and "Password" (slavepass) in step 10.

2. At [Master], add option in [mysqld] section of server configuration file (my.cnf) and restart mysqld

[mysqld]
log-bin=mysql-bin
server-id=1

log-bin = Enable binary log by specify binary log file name prefix (We can disable binary log by comment this option)
server-id = Unique server id for this server in replication group (Value valid between 1 and 2^32-1)

3. At [Slave], add option in [mysqld] section of server configuration file (my.cnf)
and restart mysqld

[mysqld]
server-id=2

server-id = Unique server id for this server (Value valid between 1 and 2^32-1)
Note: We can also specified log-bin option as well as [Master]

4. At [Master], flush unwritten data to disk and lock write to tables

mysql> FLUSH TABLES WITH READ LOCK;

Note: To ensure data integrity when we copy data files in step 6.

5. At [Master], get binary log data

mysql > SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73       |              |                  |
+---------------+----------+--------------+------------------+

Note: We will use "File" and "Position" in step 10.

6. [Master] Make copy of data files by one of these commands

shell> tar cf /tmp/db.tar ./data
shell> zip -r /tmp/db.zip ./data
shell> rsync --recursive ./data /tmp/dbdata

Note: Choose only one command not all three!

7. [Master] Unlock tables (allow write to tables)

mysql> UNLOCK TABLES;

Note: This step can be done immediately after finish copying data files, to release all locks.
So that all client can update data again.

8. [Slave] Shutdown mysqld and transfer data files to it

shell> /etc/init.d/mysqld stop

9. [Slave] Start mysqld with --skip-slave-start option

shell> /etc/init.d/mysqld start --skip-slave-start

10. [Slave] Setting master configuration

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='192.168.0.40',
    ->     MASTER_USER='repl',
    ->     MASTER_PASSWORD='slavepass',
    ->     MASTER_LOG_FILE='mysql-bin.003',
    ->     MASTER_LOG_POS=73;

11. [Slave] Start replication

mysql> START SLAVE;

FAQs
1. How can we delete master binary logs, if they are too large?
Ans We can use PURGE BINARY LOGS command such as

mysql> PURGE BINARY LOGS TO 'mysql-bin.003';
mysql> PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

But be sure to check all active replication slaves for current log positions
(with SHOW SLAVE STATUS command)

2. How can we copy replicated database from active slave?

at Active slave
mysql> STOP SLAVE;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW SLAVE STATUS;

and remember master log file and master log position

at New slave
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='fakehost';
mysql> quit;

> stop mysqld
> copy all database files from Active slave

at Active slave
mysql> UNLOCK TABLES;
mysql> START SLAVE;

Now active slave is ACTIVE again!

at New slave
mysql> CHANGE MASTER TO MASTER_HOST='correct host',
    -> MASTER_LOG_FILE='master log file',
    -> MASTER_LOG_POS=master log pos;
mysql> START SLAVE;

References
http://dev.mysql.com/doc/refman/5.0/en/replication.html
http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html