Thursday, October 9, 2008

mysql replication

While working in application deployment you might have come across the term called "failover database". That means if any hardware problem in the DB server we can't access the database at all. So we have one more DB server to manage the application. When DB1 have some problem we can manage using DB2.

So to achieve this what i thought was have to take mysqldump and import to second server periodically. But there is the better facility in mysql 5.x version called "mysql replication". But mysql 5.1.x is stable for mysql replication. What mysql replication doing is simply coping the mysql-bin log from source DB server to detination DB server. whenever we do any transaction that will be logged in mysql-bin log. So coping mysql-bin log from one machine to another is the soluction for failover database problem.

For mysql replication just we need to change some configuration in my.cnf file.

you can find this file using the following command

find . | grep my.cnf

i) Edit the my.cnf file in server1[uncomment the line server-id = 1 by default commented]
server-id = 1

ii) Edit the my.cnf file in server2 [Uncomment the following entries and give proper values]
server-id = 2

master-host =
master-user = repl
master-password = password
log-bin = mysql-bin


master-host : DB server1 IP address.
master-user : this user should have all privileges. While create this user use the follwoing command to grant permission.
GRANT ALL PRIVILEGES ON *.* TO 'user'@'host'
IDENTIFIED BY 'some_pass';
master-password: password of the master-user

iii) Restart the mysql server using the following command

/etc/init.d/mysql restart

Whenever we do data manipulation both server will have same data and in sync. Now failover database is ready. This is the way to do mysql replication.


Post a Comment