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