How To Set Up Database Replication In MySQL On Ubuntu 12.04

CONFIGURE THE MASTER DATABASE MYSQL :

The First you must login and create new user slave on Master Database Mysql
mysql -u root -p

Create user 'slave' with password='slave' on Master Server  
Mysql> create database test;

mysql> GRANT REPLICATION SLAVE ON *.* TO  'slave'@'192.9.18.25' IDENTIFIED BY slave';




Edit my.cnf fil :
sudo vim /etc/mysql/my.cnf


server-id                   = 1
log_bin                     = /var/log/mysql/mysql-bin.log
expire_logs_days     = 10
max_binlog_size      = 100M
binlog_do_db          = test

Save and restart service mysql
sudo /etc/init.d/mysql restart

Reset master :
mysql> reset master;


Check Master Status :
mysql> show master status;

Result :
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | test         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)



CONFIGURE SLAVE DATABASE MYSQL :
Mysql> create database test;


Edit my.cnf file :
sudo vim /etc/mysql/my.cnf


relay-log               = /var/log/mysql/mysql-relay-bin.log  #if relay-log is empty then please ignore.
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db       = test

Save and restart mysql.
sudo /etc/init.d/mysql restart

Change master to master host=192.9.18.27

mysql> CHANGE MASTER TO MASTER_HOST='192.9.18.27',MASTER_USER='slave', MASTER_PASSWORD='slave', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  107;

Result :
Query OK, 0 rows affected (0.26 sec)



mysql> start slave;
Result:
Query OK, 0 rows affected (0.00 sec)



mysql> SHOW SLAVE STATUS\G;
Result :
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.9.18.27
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000002
             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: 107
              Relay_Log_Space: 556
              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:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

ERROR:
No query specified



Start Slave server
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;
Result :
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first
Query OK, 0 rows affected, 1 warning (0.00 sec)