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 **************
     


















  


4 comments:

  1. www.ioisoftwares.blogspot.in for free softwares and games all are full versions

    ReplyDelete
  2. There are a lot of people who aren’t sure whether or not they need to replace their boiler. If your boiler is out of warranty, then it’s absolutely worth considering a replacement. The age concern boiler grants are a great way to save some cash on your new boiler. There is a £300 grant towards the cost of a new boiler for anyone aged 60 and over.
    age concern boiler grants

    ReplyDelete
  3. Cambridge English Academy provides you services like ielts, oet, pte, english academy, study abroad consultancy, in which you get an affordable price and highly educated teachers teach you so that your dream is fulfilled and future is bright and secure in which our company Cambridge English Academy provides you good courses with variety of offers Best IELTS Coaching In Noida city both online and offline coaching is provided by Cambridge English Academy and to know more visit the website to take a demo or call

    to take a demo
    website = https://cambridgeenglish.org.in
    for call = 9667728146

    ReplyDelete