[MySQL]Step by Step to Set up MySQL v4.x Replication

==
== Step by Step to Set up MySQL Replication on FC4
==
== MySQL Version: 4.1.11
== Fedora Core 4
==
== master: mysql1
== slave : mysql2
==
== It is a good practice, even with these old releases.
==
==============================================
== Configure Master Node
===============================================
--
--1. Update my.cnf
--
vi /etc/my.cnf

[mysqld]
log-bin ==> trun on binary log for replication
server-id=1 ==> unique against all slaves
binlog-do-db = mysql ==> database needs to generate binary log
binlog-do-db = test ==> reapet this for more databases

[mysqld_safe]
err-log=/var/log/mysqld.log ==> MySQL log file
pid-file=/var/run/mysqld/mysqld.pid ==> to save MySQL pid

--
--2. Restart master mysql
--
$ ps -efgrep mysqld
$ KILL -9 pid
$ /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid &

or
$ /etc/init.d/mysqld restart

--check if the log file is generated under datadir directory
$ ls -ltr datadir
or
mysql> show master logs;

--
--3. Create replcation account, which is used to access master from slave.
--
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'mysql2.domain' IDENTIFIED BY 'password';

mysql> FLUSH PRIVILEGES;

mysql> select * from mysql.user where user='repl'\G;

--
--4. Verfiy access in slave node
--
$ mysql -u repl -p -h mysql1

--
--5. Take database snapshot on master node
--
-- Closes all open tables and locks all tables for all databases with a read lock.
mysql> FLUSH TABLES WITH READ LOCK;

mysql> show master status;
+-------------------+----------+-------------------+------------------+
File Position Binlog_Do_DB Binlog_Ignore_DB
+-------------------+----------+-------------------+------------------+
mysql1-bin.000010 79 mysql,test
+-------------------+----------+-------------------+------------------+
1 row in set (0.00 sec)

--
--tar database files for each database
--
$ cd datadir/mysql
$ tar -cvf /tmp/mysql-snapshot.tar .
$ cd datadir/test
$ tar -cvf /tmp/test-snapshot.tar .

--re-enable write activities
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)


===========================================
== Configure Slave - mysql2
===========================================
--
--1. restore BACKUP SNAPSHOT to datadir
--
$ cd datadir/mysql
$ tar -xvf mysql-snapshot.tar
$ cd datadir/test
$ tar -xvf test-snapshot.tar

--
--2. Verfiy master database access from slave node
-- make sure access is successful before continuing
$ mysql -u repl -p -h mysql1

--Check mysql log if failed
$ tail -f /var/log/mysqld.log

--
--3. Configure my.cnf for replication
--
vi /etc/my.cnf

[mysqld]
server-id=2 ==> unique among master and slaves
replicate-do-db = mysql ==> database needs to be replicated
replicate-do-db = test ==>

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Note:
without setting replicate-do-db, I got the following error:
070703 15:26:29 [ERROR] Slave I/O thread: error connecting to master 'repl@mysql1:3306': Error: 'Can't connect to MySQL server on 'mysql1' (13)' errno: 2003 retry-time: 60 retries: 86400

--
--4. Start slave mysql
--
/usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid &
or
$ /etc/init.d/mysqld start

--
--5. Set up master info for replication
--
$ mysql -u root -p mysql

--set master
--log file and position from above result of "show master status;"
--
CHANGE MASTER TO
MASTER_HOST='mysql1',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql1-bin.000010',
MASTER_LOG_POS=79;

--
--6. start slave I/O and SQL Thread
-- stop slave
mysql> START SLAVE;

--
--7. Verify slave is running
--
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysql1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql1-bin.000013 ==> read by I/O Thread
Read_Master_Log_Pos: 139
Relay_Log_File: mysql2-relay-bin.000001 ==> read by SQL Thread
Relay_Log_Pos: 1054
Relay_Master_Log_File: mysql1-bin.000013
Slave_IO_Running: Yes ==> make sure I/O thread is running
Slave_SQL_Running: Yes ==> make sure SQL thread is running
Replicate_Do_DB: mysql,test ==> replicated databases
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 139
Relay_Log_Space: 1054
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)

ERROR:
No query specified

--
--8. Check mysql log
--
$ tail -f /var/log/mysqld.log