facebook twitter youtube
Replication Query Optimization DB Tuning Server Load and Query Locking Linux Download Interview Questions Basic DB Commands Mysql
in Fresh Replication - 29 Aug, 2013
by Zafar Malik - no comments
Replication setup on running stand alone server with mysql version 5.5 or higher

Easy Steps for Replication Setup on running stand alone server  with mysql version 5.5 or higher:

Special: Assuming 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: Place Read Lock on master to copy data from master server.
mysql> flush tables with read lock; (in one session and hold it till backup is completed)

Step5: Take backup of data from master.
mysqldump -uroot_user -proot_pass –databases db1 db2 db3 > /root/backup/all_db.sql (Assuming you are taking backup from localhost and There should be a directory named backup under root)
mysqldump -h master_server_ip -uroot_user -proot_pass –databases db1 db2 db3 > c:\backup\all_db.sql (If you are taking backup from windows machine. Make sure mysql user should have appropriate global rights to access server.)

Step6: reset to master to get fresh binlog from starting-
mysql>reset master; (in 2nd session)

Step7: unlock tables in 1st session-
mysql> unlock tables; (in 1st session)

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

Step9: Restore master data on slave server.
mysql -uroot_user -proot_pass < /root/backup/all_db.sql (Assuming you are restoring backup from localhost)
mysql -h slave_server_ip -uroot_user -proot_pass < c:\backup\all_db.sql (If you are restoring data from windows machine. Make sure mysql user should have appropriate global rights to access server.)

Step10: 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;

Step11: Check replication
mysql>show slave status;

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

Leave a Reply