facebook twitter youtube
Replication Query Optimization DB Tuning Server Load and Query Locking Linux Download Interview Questions Basic DB Commands Mysql
in Fresh Replication - 11 Sep, 2013
by Zafar Malik - no comments
Single Database Replication setup on running stand alone server with Mysql Version lower than 5.5

Easy Steps for Single Database Replication Setup on running stand alone server with Mysql Version lower than 5.5:

Special: Assuming Mysql Version is lower than 5.5:

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’;

Step2: Change my.cnf file on master 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
binlog-do-db=mydb
binlog-ignore-db=mysql

[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 mydb > /root/backup/mydb.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 mydb > c:\backup\mydb.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: 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.)

Step9: Change my.cnf file on slave 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.

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

master-host=master_ip
master-port=3306
master-user=rep_user
master-password=repl_pass
master-connect-retry=60
max_relay_log_size = 500M
report-host=slave_ip

replicate-do-db=mydb
replicate-ignore-db=mysql

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

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

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

Step11: Check replication
mysql>show slave status;

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

Note: If show slave status is showing every thing fine but changes on master are not replicating on slave then restart slave service as per below and check again.
mysql>stop slave;
mysql>start slave;

Leave a Reply