facebook twitter youtube
Replication Query Optimization DB Tuning Server Load and Query Locking Linux Download Interview Questions Basic DB Commands Mysql
in Fresh Replication - 26 Aug, 2013
by Zafar Malik - 3 comments
Mysql Replication Easy Steps for mysql version 5.5 or higher

Six Easy Steps for Replication Setup  for mysql version 5.5 or higher:

Special: Assuming Master and Slave both are fresh servers and Mysql Version is 5.5 or higher:

Step1: create users-
rights on slave : GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘rep_user’@'localhost’ IDENTIFIED BY ‘rep_pass’;
rightgs on master : GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘rep_user’@'slave_ip’ IDENTIFIED BY ‘rep_pass’;
rights on master : GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘rep_user’@'localhost’ IDENTIFIED BY ‘rep_pass’;

Step2: Change my.cnf file as per below-
Note: Specifying only default and replication related setting. Rest variable setting, you need to do as per your server configuration and requirement.

Master Server my.cnf -
——————————————————————————
[mysqld]
datadir= /var/lib/mysql/
socket=/var/lib/mysql/mysql.sock
user=mysql
port=3306

#replication section
server-id=1
log-bin = /var/lib/mysql/mysql-bin.log
max_binlog_size = 500M
expire-logs-days = 30

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/lib/mysql

[mysqldump]
quick
——————————————————————————

Slave Server my.cnf -
——————————————————————————
[mysqld]
datadir= /var/lib/mysql/
socket=/var/lib/mysql/mysql.sock
user=mysql
port=3306

#replication slave setting
server-id=2
relay-log=/var/lib/mysql/mysqld-relay-bin

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/lib/mysql

[mysqldump]
quick
——————————————————————————

Step3: Restart mysql service on master to get new cnf configuration-
service mysql restart

Step4: Restart mysql service on slave to get new cnf configuration-
service mysql restart

Step5: execute below command on slave to start replication:
mysql>CHANGE MASTER TO MASTER_HOST=’master_ip’, MASTER_PORT=3306, MASTER_USER=’rep_user’, MASTER_PASSWORD=’rep_pass’;
mysql>start slave;

Step6: Check replication
mysql>show slave status;

Now insert/update some thing on master and check on slave.

Leave a Reply