MYSQL MASTER SLAVE REPLICATION

MYSQL MASTER-SLAVE REPLICATION ON CENTOS 6.X

MySQL Replication is a method/process which is used to maintain multiple copies of MySQL data by automatically copying data from Master to Slave Databases.

In this tutorial, We are going to see how the data is automatically copying to Slave databases whenever we update data into master Database.

We need a minimum of 2 servers to Setup Master/Slave MySQL Database Setup.

For Example

In this example, We are having two servers which act as Master and Slave.

Master database : 10.0.0.5 

Slave Database : 10.0.0.55

Master Database Setup

We must have a user with sudo privileges to install the following commands:

Download the repository:

wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm

rpm -Uvh mysql-community-release-el6-5.noarch.rpm

Install the Mysql packages using the below command:

yum install mysql-server -y

chmod 1777 /tmp/

Mysql database runs/uses port number 3306 to communicate with databases/servers.

So, We need to allow the port number in IPTABLES:

#vi /etc/sysconfig/iptables (open the file and add the below lines)

-A INPUT -p UDP -m state –state NEW –dport 3306 -j ACCEPT

-A INPUT -p UDP -m state –state NEW –dport 3306 -j ACCEPT

save and close it.

Now restart IPTABLES to update the rules inserted.

#service iptables restart

Next, We need to run a script mysql_secure_installation which helps in improving security and to remove some defaults which are dangerous to the Production environment.

#mysql_secure_installation

It will prompt to setup the root password for the MySQL Database followed by asking, Do you want to change the root password, Type “yes” and enter and New Password and confirm.

Also, You will be asked series of questions, You should answer Y(yes) to all of the remaining questions.

Configure MySQL Master Database

Open my.cnf configuration file:

#vi /etc/my.cnf (add the below lined under [mysqld] section)

server-id = 1 (ID = 1 represents Master)

binlog-do-db = rahul (rahul is db name to be replicated to slave db)

expire-logs-days = 7

relay-log = /var/lib/mysql/mysql-relay-bin

relay-log-index = /var/lib/mysql/mysql-relay-bin.index

log-error = /var/lib/mysql/mysql.err

master-info-file = /var/lib/mysql/mysql-master.info

relay-log-info-file = /var/lib/mysql/mysql-relay-log.info

log-bin = mysql-bin

Restart the MySQL service.

#service mysqld start

Make MySQL run even after reboot.

#chkconfig mysqld on

Try login-in to MySQL Database provided the root password you use:

#mysql -u root -p

Now, We have to create a Replication User and grant privileges for replication

>grant replication slave on *.* to ‘rahul’@’%’ identified by ‘[email protected]’;

>flush privileges;

>flush tables with read lock;

>show master status;

+——————+———-+————–+——————+

|File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————+———-+————–+——————+

|mysql-bin.000004 | 383 | rahul |   |

+——————+———-+————–+——————+

Note down File(mysql-bin.000004) & Position (383) which will be used while configuring slave server.

Backup MySQL Master Database

Next, apply READ LOCK to databases to export all databases and MySQL information using mysqldump command.

#mysqldump -u root -p –all-databases –master-data –single-transaction > all-database.sql

>unlock tables;

>\q (exit)

Copy Masterdatabase.sql file to Slave Server

#scp -r all-databases.sql [email protected]:/root/

MySQL Slave Server Configuration

We have to follow the same procedure for installing MySQL in Slave Database server.

#yum install mysql-server mysql -y

#service mysqld start

#chkconfig mysqld on

#mysql_secure_installation

#vi /etc/my.cnf server-id = 2 (ID =2 represents slave)

master-host=10.0.0.5(masterip)

master-connect-retry=60

master-user=rahul(master server DB user)

[email protected](password for master server DB user)

replicate-do-db=rahul(master DB name)

relay-log = /var/lib/mysql/mysql-relay-bin

relay-log-index = /var/lib/mysql/mysql-relay-bin.index

log-error = /var/lib/mysql/mysql.err

master-info-file = /var/lib/mysql/mysql-master.info

relay-log-info-file = /var/lib/mysql/mysql-relay-log.info

log-bin = mysql-bin

Import the Master Database

Now restore the database which we have copied from MySQL Master Database Server

#mysql -u root -p < all-databases.sql

#service mysqld restart

#chkconfig mysqld on

#mysql -u root -p

In this step, We are going to use MySQL Master Database server’s File and Position which we have already noted.Before that stop the slave and execute the below command:

>slave stop;

>change master to master_host=’masterserverip’, master_user=’rahul’, master_password=’[email protected]’, master_log_file=’mysql-bin.000004’, master_log_pos=’383’;

>slave start;

Check the slave status using the below command:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: root
                  Master_Port: 13000
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 1307
               Relay_Log_File: slave-relay-bin.000003
                Relay_Log_Pos: 1508
        Relay_Master_Log_File: master-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: 1307
              Relay_Log_Space: 1858
              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
                  Master_UUID: 3e11fa47-71ca-11e1-9e33-c80aa9429562
             Master_Info_File: /var/mysqld.2/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 10
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-5
            Executed_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-5
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_name:
           Master_TLS_Version: TLSv1.2

Testing of Master-Slave Replication

Master Side

#mysql -u root -p

>create database rahul;

>use rahul;

>create table sample (c int);

>insert into sample (c) values (1);

>select * from sample;

+——+

| c |

+——+

| 1 |

+——+

1 row in set (0.00 sec)

Slave side

#mysql -u root -p

>use rahul;

>select * from sample;

+——+

| c |

+——+

| 1 |

+—–-+

1 row in set (0.00 sec)

That’s it.The table created in Master DB Server is automatically Replicated to Slave DB Server.