• Mysql Database Replication Master-Slave

    The best advantage of master slave is to use master for all inserts and send some select queries to slave. This practice may increase speed of your application without going into optimization of all queries.

    Master……………………………….>Slave

    Suppose you have the following two Mysql servers :

    Master Server >> IP : 10.0.0.4
    Slave server     >> IP : 10.0.0.5

    Configure The Master:

    Edit /etc/my.cnf
    #skip-networking
    #bind-address            = 127.0.0.1
    log-bin = /var/lib/mysql/mysql-bin.log
    binlog-do-db=DB_NAME
    server-id=1

    # /etc/init.d/mysql restart
    #Then we log into the MySQL database as root and create a user with replication privileges as following:

    mysql> GRANT REPLICATION SLAVE ON *.* TO ‘slave_user‘@’10.0.0.5‘ IDENTIFIED BY ‘Password-Here‘;
    mysql> FLUSH PRIVILEGES;
    mysql> USE DB_NAME;
    mysql> FLUSH TABLES WITH READ LOCK;
    mysql> SHOW MASTER STATUS;

    The last command will show something like this:
    +——————+———–+————–+——————+
    | File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
    +——————+———–+————–+——————+
    | mysql-bin.000001 | 750096359| DB_NAME  |                  |
    +——————+———–+————–+——————+

    mysqldump  DB_NAME  >/backup/DB_NAME.sql
    mysql> UNLOCK TABLES;

    ##move mysql dump file to DB Slave server (10.0.0.5):
    rsync -avprP -e ssh /backup/DB_NAME.sql  10.0.0.5:/backup/

    Configure The Slave :

    login to Mysql server and create the DB :

    mysql> CREATE DATABASE DB_NAME;
    mysql> quit;

    Then restore DB :

    mysql -u root -proot_password  DB_NAME< /backup/DB_NAME.sql

    ##Copy my.cnf from Master DB server and modify the following :
    #server-id=1
    server-id=2
    /etc/init.d/mysql restart

    Finally :

    mysql> SLAVE STOP;
    mysql> CHANGE MASTER TO MASTER_HOST=’10.0.0.4‘, MASTER_USER=’slave_user‘, MASTER_PASSWORD=’Password-Here‘, MASTER_LOG_FILE=’mysql-bin.000001‘, MASTER_LOG_POS=750096359;
    mysql> START SLAVE;
    mysql> show slave status G

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

    Slave_IO_State: Waiting for master to send event
                      Master_Host: 10.0.0.4
                     Master_User: slave_user
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 842111916
                   Relay_Log_File: AKdb2-relay-bin.000002
                    Relay_Log_Pos: 92015810
            Relay_Master_Log_File: mysql-bin.000001
                 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: 842111916
                  Relay_Log_Space: 92015966
                  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)
    mysql>

    That is all what you have to do 🙂

    Categories: Mysql

    Comments are currently closed.