MySQL - Replication

Hi all,
Mysql replication helps to replicate data from one mysql server (called "Master") to another server or servers (called "slave/slaves" which locates either same machine where Master locates or a remote machine).
This is useful in certain occations like when we have to keep a backup of a database while restricting read /write permission of one so that data is secure in that particular database and also when we have to reduce the load over one database so that we get the maximum efficiency. You can replicate all database or a particuler database/s or even a table. So here are the steps to do that.
N.B : If you want to replicate in one machine you have to install 2 mysql servers that is running in two ports.

I assume that you are aware of installing mysql.Here are the steps to setup replication in a one machine.
When you install and run mysql it will ues its default configuration file "my.cnf". So if you want to add extra features you have create your own "conf_name.cnf" file and locate it in /etc/. When you start mysql you have show that file as well otherwise it will take its default values.

1. Setup Master


I) Creating .cnf file

Go to /usr/local/mysql1/supportfiles/

copy my-medium.cnf to /etc/mymaster.cnf


II) Configuring

edit /etc/mymaster.cnf

add following lines

binlog-do-db=example_db (the database you wish to replicate)

server-id=1

socket = /tmp/mysql_sockname_1.sock

III) Enabling logs

log-bin=/var/log/mysql/master-bin.log

log-bin-index = /var/log/mysql/master-log-bin.index

log-error = /var/log/mysql/master-error.log

sync_binlog=1

IV) Set data directory

datadir=/usr/local/your_mysql_master_directory/data

basedir=/usr/local/your_mysql_master_directory/

V) Start Mysql master

Go to /usr/local/your_mysql_master_directory/

Login with root

enter ./bin/mysqld_safe –defaults-file=/etc/mymaster.cnf –user=mysql &

Then mysql -uroot -p –port=3306 –socket=/tmp/mysql_sockname_1.sock

Now you have started mysql master

Setup slave

I) Creating .cnf file

Go to /usr/local/mysql1/supportfiles/

copy my-medium.cnf to /etc/myslavecnf


II) Configuring

edit /etc/myslave.cnf

add following lines

N.B Uncomment server id = 1

add server-id=2

port = 3308

socket = /tmp/mysql_sockname_2.sock


III) Enabling logs

log-bin=/var/log/mysql_slave/master-bin.log

log-bin-index = /var/log/mysql_slave/master-log-bin.index

log-error = /var/log/mysql_slave/master-error.log

sync_binlog=1

master-connect-retry=60


IV) Start server

./bin/mysqld_safe --defaults-file=/etc/my_voting.cnf --user=mysql &

mysql -uroot -p –port=3308 –socket=/tmp/mysql_sockname_2.sock


In master

GRANT REPLICATION SLAVE ON *.* TO 'some_name '@'%' IDENTIFIED By 'password';

FLUSH PRIVILEGES;

show master status;


+---------------+----------+--------------+------------------+
| File | Position | Binlog_do_db | Binlog_ignore_db |
+---------------+----------+--------------+------------------+
| mysql-bin.006 | 183 | example_db |
| +---------------+----------+--------------+------------------+
1 row in set (0.00 sec) In slave

CHANGE MASTER TO MASTER_HOST='127.0.01, MASTER_USER='some_name ', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;

START SLAVE;