How to configure Master-Slave MySQL Replication on CentOS/RHEL 5/6/7

Share It!

MySQL replication is a procedure that permits you to easily keep up multiple copies of a MySQL data by having them replicated consequently from an master to a slave database. This can helpful for some reasons including facilitating a backup for the data, a way to analyze it without utilizing the main database.

Replication1

MySQL replication for the most part comprises of three-section process:

  • The master server records all data changes to its binary logs and send it to the slave using a thread called Binlog dump thread once the slave connects to the master.
  • The slave copies the binary log events sent by the master’s binlog dump thread to its relay logs using a thread called Slave I/O thread.
  • The slave applies these changes from the relay logs to its data by replaying (executing) all events using a thread called Slave SQL thread.

Server Details:

Master Server: 192.168.10.4
Slave Server: 192.168.10.50
Database Name: techoism

Basic Configuration:

  • Install below repository on Master and Slave server:
    # rpm -Uvh http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
    # rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
    

    Top 5 Yum Repositories for CentOS/RHEL 7/6/5

  • Disabled selinux from Master and Slave server (If it is in Enforcing or Permissive mode)
    # vim /etc/selinux/config
    OR
    # vim /etc/sysconfig/selinux
    
    SELINUX=disabled
    
  • After disabled the selinux reboot both the server.
  • Comment old_passwords line from both database server (If It is not commented)
    # vim /etc/my.cnf
    
    # old_passwords=1
    
  • Restart MySQL from both server so that configuration changes take place:
    For CentOS/RHEL 5/6:
    # /etc/init.d/mysql restart
    For CentOS/RHEL 7:
    # systemctl restart mysqld
    
  • Install php mysql module using below command:
    # yum install php-mysql --enablerepo=remi
    
  • Restart Apache Service from both server so that changes will reflect:
    For CentOS/RHEL 5/6:
    # /etc/init.d/httpd restart
    For CentOS/RHEL 7:
    # systemctl restart httpd
    
  • Master Server Configuration:

  • Create new mysql user and provide Grant privileges to that user to access the database.
    mysql> CREATE USER 'new_user'@'%' IDENTIFIED BY 'secretpassword';
    mysql> GRANT ALL PRIVILEGES ON techoism.* TO 'username'@'%' WITH GRANT OPTION;
    mysql> FLUSH PRIVILEGES
    
  • Check mysql connectivity with Slave server
    # telnet slave_server_ip 3306
    
  • Start iptables service (if connectivity with server will fail)
    For CentOS/RHEL 5/6
    # /etc/init.d/iptables stop
    For CentOS/RHEL 7
    # systemctl stop firewalld
    
  • Add the following variables to the MySQL configuration file
    # vim /etc/my.cnf
    
    [mysqld]
    log-bin=mysql-bin
    binlog-do-db=techoism
    server-id=1
    innodb_flush_log_at_trx_commit=1
    sync_binlog=1
    
  • Run below command to configure replication:
    mysql>GRANT REPLICATION SLAVE ON techoism.* TO 'new_user'@'192.168.10.50' IDENTIFIED BY 'secretpassword';
    mysql>FLUSH PRIVILEGES;
    mysql> use techoism;
    mysql> FLUSH TABLES WITH READ LOCK;
    mysql> exit;
    
  • Restart MySQL so that configuration changes take place:
    For CentOS/RHEL 5/6:
    # /etc/init.d/mysql restart
    For CentOS/RHEL 7:
    # systemctl restart mysqld
    
  • Check Master Status using following command.
    mysql> SHOW MASTER STATUS\G
    
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000002 |      107 | techoism     |                  |
    +------------------+----------+--------------+------------------+
    
  • Take a full backup from the master’s database:
    # mysqldump -u username -p techoism > techoism.sql
    

    Note: If we want to take dump without triggers then use below command

    # mysqldump -u username -p --skip-triggers techoism > techoism.sql
    
  • Unlock database tables:
    mysql> UNLOCK TABLES;
    
  • Copy database on slave
    # scp techoism.sql root@slave_server_ip:/home/
    
  • Slave Server Configuration:

  • Check connectivity with master server
    # telnet server_ip 3306
    
  • Start iptables service (if connectivity with server will fail)
    For CentOS/RHEL 5/6
    # /etc/init.d/iptables stop
    For CentOS/RHEL 7
    # systemctl stop firewalld
    
  • Add the following variables to the MySQL Configuration file:
     # vim /etc/my.cnf
    
    [mysqld]
    server-id=2
    replicate-do-db=techoism
    
  • Restart Mysql Service
    For CentOS/RHEL 5/6
    # /etc/init.d/mysqld restart
    For CentOS/RHEL 7
    # systemctl restart mysqld
    
  • Create new database and restore database using below steps:
    # mysql -u root -p -e "create database techoism"
    # cd /home
    # mysql -u root -p techoism < techoism.sql
    
  • Run below command to configure replication
    # CHANGE MASTER TO MASTER_HOST='192.166.10.4',
    >MASTER_USER='repl_50',
    >MASTER_PASSWORD='secretpassword',
    >MASTER_LOG_FILE='mysql-bin.000002',
    >MASTER_LOG_POS=107;
    
  • Start Slave
    mysql> SLAVE START;
    
  • Check status of slave
    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.