ervan:~# tail -f /var/log/blog

MySQL Master Slave Replication is one way to spread the load across multiple servers.Replication allows you to take one database, make an exact copy of it on another server, and set one of them (the slave) to take all its updates from the other (the master). Its just an one-way communication (Master to Slave), Slave just receiving any event or update from Master, so if Master updated Slave will automaticly updated but if the update did on Slave ofcourse Master is won’t updated. If you want update Multiple Server simultaneously each other, you must try MySQL Master Master Replication.

Master address 192.168.10.1
Slave address 192.168.10.2

Setup Master

linux:~# vi /etc/my.cnf
          log-bin=mysql-bin
          server-id=1
          binlog-ignore-db="mysql"

* Restart MySQL Master Service

linux:~# killall mysql mysqld mysqld_safe
linux:~# /usr/local/mysql/bin/mysqld_safe --user=mysql &

* Create an user on the Master server that allows replication on the Slave.

mysql> GRANT REPLICATION SLAVE ON *.* TO replicator@"192.168.10.2" IDENTIFIED BY 'password@@@';
mysql> flush privileges;
mysql> quit

* Export database that you want to put in the Slave server. (in this case, i want to export wordpress database)

linux:~# mysqldump -u root -p wordpress > wordpress.sql
Enter password :

* And then lets see the Master status

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |   141741 |              | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> exit
Bye

Note the File row and Position row for use on the Slave server.

* Copy your exported database file to the Slave server.

linux # scp database.sql 192.168.10.2:/root/

Setup Slave

linux2:~# vi /etc/my.cnf
server-id     = 2
master-host = 192.168.10.1
master-user = replicator
master-password = password@@@
master-port = 3306

* Restart MySQL Slave service

linux:~# killall mysql mysqld mysqld_safe
linux:~# /usr/local/mysql/bin/mysqld_safe --user=mysql &

* Import wordpress database that we’ve export on the Master before. If the database doesn’t exist you need to create it first.

linux2:~# mysql -u root -p
Password:
mysql> create database wordpress;
mysql> exit
Bye
linux2:~# mysql -u root -p wordpress < wordpress.sql

* Now configure replication and start the Slave service.

mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.1', MASTER_USER='replicator', MASTER_PASSWORD='password@@@', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=141741;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.1
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 216485
               Relay_Log_File: ervan-relay-bin.000015
                Relay_Log_Pos: 18197
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              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: 4
            Relay_Log_Space: 3630
            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: 1519187

Slave_IO_State status information should identify "Waiting for master to send event". If it stops at "Connecting to Master" check your log file. By default it is located in /var/log/mysqld.log but may be different on your system. Check your my.cnf file for the exact location of your log file.