Configure Master and Slave database replication using mysqlnd plugin

Share It!

Mysql replication is a process that allows you to maintain the multiple copy of mysql data. With the help of mysqlnd plugin we can divide the load of mysql. We can send select query to slave server and other queries to master server.

MySQL_Replication_opt

Server Details:

Web Server: 192.168.0.152
Master Server: 192.168.0.150
Slave Server: 192.168.0.151
MyDB: techoism

Step 1: First install below repository on master and slave server.

CentOS/RHEL 6, 32 Bit (i686):
# rpm -Uvh http://packages.sw.be/rpmforge-release/rpmforge-release-0.5.3-1.el6.rf.i686.rpm

CentOS/RHEL 6, 32 Bit (i386):
# rpm -Uvh http://download.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpm

CentOS/RHEL 6, 64 Bit (x86_64):
# rpm -Uvh http://packages.sw.be/rpmforge-release/rpmforge-release-0.5.3-1.el6.rf.x86_64.rpm

CentOS/RHEL 6, 64 Bit x86_64):
# rpm -Uvh http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

Step 2: Follow below steps on Master and Slave Server:

  • Comment old_passwords line from both database server configuration file. (If It is not commented)
  • # vim /etc/my.cnf
    # #old_passwords=1
    
  • Restart Mysql service using following command.
    # /etc/init.d/mysql restart
    
  • Step 3: If we are using different server for database then we will follow below steps on webserver. We don’t need to follow these steps on any database server.

  • Remove php-mysql package using following command (If It is already installed)
    # yum remove php-mysql
    
  • Install mysqlnd package using below command.
    # yum install php-mysqlnd --enablerepo=remi
    
  • Also Install php-pecl-apc package using following command.
    # yum install php-pecl-apc --enablerepo=remi
    
  • Edit mysqlnd_ms.ini file and add following entries on file:
    #  vim /etc/php.d/mysqlnd_ms.ini
    #  mysqlnd_ms.config_file=/etc/mysqlnd_ms_php.ini
    #  mysqlnd_ms.enable=1
    #  extension=mysqlnd_ms.so
    
  • Edit /etc/mysqlnd_ms_php.ini file and copy below lines: (Don’t give any space in below entries and Copy as It Is)
    #
    {
        "techoism_db_repl": {
            "master": {
                "master_0": {
                    "host": "master_db_IP",
                    "port": "3306"
                }
            },
            "slave": {
                "slave_0": {
                    "host": "slave_db_IP",
                    "port": "3306"
                }
           }
        }
    }
    
  • Restart apache service using following command.
    # /etc/init.d/httpd restart
    
  • We need to define techoism_db_repl value in database config file of webserver in place of a hostname.

    Step 4: On Master Server

  • First, we create new mysql user with Grant Privileges
    # mysql> CREATE USER 'new_user'@'%' IDENTIFIED BY 'secretpassword';
    # mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;
    
  • Run below command to setup Master-Slave Replication:
    # mysql>GRANT REPLICATION SLAVE ON *.* TO 'new_user'@'Slave_Server_IP' IDENTIFIED BY 'Password';
    # mysql>FLUSH PRIVILEGES;
    
  • Block write statement on all the tables, so no changes made until we take backup of database.
    # mysql> use techoism;
    # mysql>FLUSH TABLES WITH READ LOCK;
    # mysql>exit;
    
  • Edit MySQL configuration file and add the following lines under [mysqld] section.
    # vim /etc/my.cnf
    
    [mysqld]
    binlog_format = row
    relay_log = mysql-relay-bin
    log_slave_updates = 1
    log-bin=mysql-bin
    binlog-do-db=techoism
    server-id=1
    innodb_flush_log_at_trx_commit=1
    sync_binlog=1
    
  • Restart master mysql server to see effect.
    # service mysqld restart
  • Check the binary log file and position using below command.
    # mysql>SHOW MASTER STATUS;
    
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000002 |      107 | techoism     |                  |
    +------------------+----------+--------------+------------------+
    

    The result is showing that the current binary file is mysql-bin.000002 and position is 107. Note down these values to use on slave server.

  • Take the backup of database and copy database it to slave server.
    # mysqldump -u root -p techoism > techoism.sql
    # scp techoism.sql 192.168.0.151:/opt
    
  • After taking backup remove the READ LOCK from mysql database tables.
    # mysql>UNLOCK TABLES;
    

    Step 5: On Slave Server

  • Edit mysql configuration file and add following lines under [mysqld] section:
    [mysqld]
    server-id=2
    replicate-do-db=techoism
    
  • Restart mysql service to see effect.
    # service mysqld restart
    
  • Restore database on using following command:
    # mysql -u new_user -p techoism < techoism.sql 
    
  • Run below command to configure replication:
    # mysql -u root -p 
    # mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.150',
        > MASTER_USER='new_user',
        > MASTER_PASSWORD='secretpassword',
        > MASTER_LOG_FILE='mysql-bin.000002',
        > MASTER_LOG_POS=107;
    
  • Finally start the slave:
    # mysql> SLAVE START;
    
  • Check Slave status using below command:
    # mysql> show slave status\G
    
    *************************** 1. row ***************************
                   Slave_IO_State:
                      Master_Host: 192.168.1.150
                      Master_User: new_user
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000002
              Read_Master_Log_Pos: 107
                   Relay_Log_File: mysqld-relay-bin.000001
                    Relay_Log_Pos: 4
            Relay_Master_Log_File: mysql-bin.000002
                 Slave_IO_Running: No
                Slave_SQL_Running: No
                  Replicate_Do_DB: techoism
              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: 107
                  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: NULL
    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>
    
  • Leave a Reply

    Your email address will not be published.