MySQL Replication Setup:

Here is a simple demonstration of MySQL Replication setup. For my case, I’ve installed MySQL 5.1 database on two servers.
Here are the steps:

1. Enable Binary Logging & Assigning Unique Server ID (Master)

On a replication master, you must enable binary logging and establish a unique server ID. Binary logging must be enabled on the master because the binary log is the basis for sending data changes from the master to its slaves. If binary logging is not enabled, replication will not be possible.

Each server within a replication group must be configured with a unique server ID. This ID is used to identify individual servers within the group, and must be a positive integer between 1 and (232)–1. How you organize and select the numbers is entirely up to you.

Edit your my.cnf file like this: 

server-id                       = 1

log-bin                        = /u01/app/mysql/mysqllogs/bin-log

2. Restart mysql (as root)

service mysqld restart

3. Create REPLICANT user on MASTER

Create the REPLICANT user with REPLICATION SLAVE privilege. This user would pull data from SLAVE conneting to MASTER DB.

create user 'replicant'@%' identified by 'password';

GRANT REPLICATION SLAVE ON *.* TO 'replicant'@'%';

4.  Transferring DUMPS to SLAVE with Master DB State Information:

Stop the production server, and obtain its state information. Most importantly, note the File and Position values:mysql> FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS;

+---------------+----------+--------------+------------------+

| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+---------------+----------+--------------+------------------+

| mysql-bin.003 | 240      | test         | manual,mysql     |

+---------------+----------+--------------+------------------+

While the database has the READ LOCK applied, export the data using mysqldump, including master data information:

mysqldump --all-databases --master-data > dbdump.db

Then unlock the master database:

mysql> UNLOCK TABLES;

5. Setting the Replication Slave Configuration

On a replication slave, you must establish a unique server ID. Edit the my.cnf like this:

# Slave Config

[mysqld]

server-id                      = 2

read-only                      = 1

relay-log                      = /u01/app/mysql/mysqllogs/relay-log

relay-log-space-limit          = 16G

master-host                    =

master-user                    = replicant

master-password                =

master-connect-retry           = 30

After making the changes, restart the server. 

6.  Importing dumps into SLAVE DB:

Transfer the data (dbdump.db) file from Master and load it into the database:

shell> mysql < fulldb.dump

Set the file information – this needs to match the values you observed on the master when you locked the tables above. In this example the filename was mysql-bin.003 and the log position was 240:

mysql> change master to

MASTER_LOG_FILE='mysql-bin.003',

MASTER_LOG_POS=240;

7.  Start the slave:

mysql> start slave;

mysql> show slave status \G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.13.60

                  Master_User: replicant

                  Master_Port: 3306

                Connect_Retry: 30

              Master_Log_File: bin-log.000016

          Read_Master_Log_Pos: 462

               Relay_Log_File: relay-log.000040

                Relay_Log_Pos: 413

        Relay_Master_Log_File: bin-log.000016

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB:

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

              Relay_Log_Space: 705

              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

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

1 row in set (0.00 sec)

Thats it!! Now you can create table/database and see the reflection on slave database!