mysql replication

#steps to do replicatio in mysql

replication of master server on slave server

step 1
configure master
must enable binary logging and also configure a unique server ID.
this can be done by setting follwing parameters in my.cnf file

[mysqld]
server-id=2
log-bin=mysql-bin

take backup of the databases or tables of which you want to set replication

create user with following command (this will be used by slave ,so rember the password and username :) )

CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';


restart mysql by followig commmand

service mysql restart

mysql>FLUSH TABLES WITH READ LOCK;
take the follwing and write them down somewhere (you will need them later)

mysql>SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 333 | | |
+------------------+----------+--------------+------------------+

step 2

configure slave

put following enteries in my.cnf


[mysqld]
server-id=2


export the backup which you have taken on master


restart the mysql services

on mysql prompt run following command

mysql>CHANGE MASTER TO
MASTER_HOST='192.168.163.130', ##ip of master
MASTER_USER='repl',
MASTER_PASSWORD='slavepass',
MASTER_LOG_FILE='mysql-bin.000007',
MASTER_LOG_POS=333;

mysql>start slave



its done..:)



if u want to replicate a spesific table add following in the server my.cnf

replicate-do-table=mani.abc

mysql Installation on linux

Manualy create a text file named my.cnf with following cotents and place this in /etc


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

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


#########useradd -G {group-name} username if gp is not createed



if mysql is already install as default with centos installation then remove it

cd /var/lib/mysql
rm -rf *
rpm -qa | grep -i mysql
rpm -e





cd /var/run/

mkdir -p mysqld

chown -R mysql:mysql mysqld/

cd /tmp

chmod 777 *.rpm

rpm -Uvh *.rpm

cd /var/lib/mysql

mysql_install_db (this is require to to get the default databases with mysql i.e test and mysql)

/usr/bin/mysqld_safe & (this is required as to up the server)


tail -100f /var/log/mysqld.log (to make sure that mysqld works corectly)


service mysql status
service mysql stop

ps -ef | grep -i mysql
kill -9

ps -ef | grep -i mysql (to see the id of mysql)

service mysql restart

rpm -qa |grep -i mysql (to sse all the installed rpms)