Friday, 28 December 2012

MySQL: Master-Slave Replication Setup on Linux(rhel 5/5.7)


Set-Up General Information:

Master Server IP: 192.168.1.100
Slave Server IP:   192.168.1.10


Explanations of these settings :
  • server-id : this is an integer id helping to identify the server (must be unique in your replication farm!)
  • master-host : specifies the ip/hostname of the MySQL acting as master for the current server
  • master-user : specifies the user used to make the connection to the master
  • master-password : is the user's password
  • master-port : specifies on which port the master is listening
  • log-bin : needed to start the binary logging process
  • binlog-do-db : specifies on which databases the binary logging must be active (only those databases will be in the binary log)
  • replicate-do-db : which database must be replicated by the server as slave.


Steps:1 Configuration on MASTER SERVER


#yum -y install mysql-server mysql

#rpm -qa |grep mysql

#vim /etc/my.cnf

bind-address = 192.168.1.100

[mysqld]
 


server-id = 1


binlog-do-db = shan

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

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



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

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

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


:wq


#service mysqld start


==> Login  mysql as root and create slave user

       user=slave
       password=slave123

#mysqladmin -u root password root123  ##set root password

#mysql - u root -p
pwd:


mysql> grant replication slave on *.* to 'slave1@'%' identified by 'slave123' ;

mysql>fluesh privileges;

mysql>flush tables with read lock;

mysql>show master status;  ##display  like below pic



Note: write down file name(master-bin.000004) and position number(98).


##export all databases or what is our requirement db's, here i take full master backup.


#mysqldump -u root -p --all-database  --master-data >/root/masterdump.sql


#mysql -u root -p

mysql>unlock tables;

mysql>quit



#scp -r /root/masterdump.sql  root@192.168.1.10:/root/
pwd:

---------------------------------------------------------------------

Step 2: Configuration on SLAVE SERVER

#yum install mysql-server mysql -y

#vim /etc/my.cnf

  
  [mysqld]      

server-id = 2


master-host=192.168.1.100

master-connect-retry=60


master-user=slave1

master-password=slave123


replicate-do-db=shan


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


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

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

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

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


:wq

#service mysqld restart


#mysql -u root -p </root/masterdump.sql ##import master databases


#mysql -u root -p


mysql>slave stop;


myql>change master to master_host='192.168.100', master_user="slave1',
                         master_password='slave123',master_log_file='master-bin.00004',
                                           master_log_pos=98;

mysql>start slave;

mysql>show slave status\G

                                 

Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.1.100

                  Master_User: slave1

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000004

          Read_Master_Log_Pos: 12345100

               Relay_Log_File: mysql-relay-bin.000002

                Relay_Log_Pos: 11381900

        Relay_Master_Log_File: mysql-bin.000004

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: shan

          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: 12345100

              Relay_Log_Space: 11382055

              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:

1 row in set (0.00 sec)


 ---------------------------------------------------------------------------


==>test the replication is working or not.

First check it

Slave_IO_Running=Yes 

Slave_SQL_Running=Yes

it's woking fine......


Know go to master server

mysql>mysql -u root -p

mysql>use shan;

mysql>create table sankar_emp( s int)

mysql>insert into sankar_emp (s) values(10)

mysql>show tables;


===========

Know go to slave server

#mysql -u root -p

mysql>use shan;  ##master db replicate in slave server

mysql>show tabes; ##tables also automatically replicated

mysql> select * from sankar_emp;



************ It's Successfully Working **************
     


















  


2 comments: