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=slave1 

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