Friday, July 20, 2012

MYSQL DATABASE REPLICATION BETWEEN TWO DATABASE SERVER ON DIFFERENT MACHINE

1:- Open /etc/my.conf OR /etc/mysql/my.cnf file on both the server(server1 and server2).
2:- Edit file to make changes in my.cnf(on the on master1/slave2 side server)
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
###Setting made by sourabh for master configuration###
log-bin=/var/lib/mysql/mysql-bin.log
binlog-do-db=alfresco
server-id=2
binlog-ignore-db=mysql
binlog-ignore-db=test
#information for becoming slave.
master-host=10.167.19.21
master-user=root
master-password=
master-connect-retry=20
replicate-do-db=alfresco
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

3:- Edit file to make changes in my.cnf(on the on master2/slave1 side server)
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
###Setting made by sourabh for master configuration###
log-bin=/var/lib/mysql/mysql-bin.log
binlog-do-db=alfresco
server-id=1
binlog-ignore-db=mysql
binlog-ignore-db=test
#information for becoming slave.
master-host=10.167.19.19
master-user=root
master-password=
master-connect-retry=20
replicate-do-db=alfresco
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
4:- Create database alfresco on server1 and server2 with exactly same tables and value For this :-use (mysqldump -u root -p alfresco>alfresco.sql)  to create sql file andgenerate database on another server using (mysql -u root -p alfresco<lfresco.sql) with that sql file.
5:- Execute command on both server mysql command prompt:- GRANT REPLICATION SLAVE ON *.* TO 'anotherServerMysqlUser'@'anotherServerIP' IDENTIFIED BY'anotherServerMysqlPassword';
6:- Execute command on both server mysql command prompt:- FLUSH PRIVILEGES;
7:- Restart mysql server on both machines.
8:- On server1 and server2 execute command :-
USE alfresco;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
9:- Configuration like this appears:-
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_do_db | Binlog_ignore_db |
+---------------+----------+--------------+------------------+
| mysql-bin.006 | 183      | alfresco     | mysql , test     |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)
10:- Save the configuration on both servers and quit (using command :-( quit;) )
11:- On both server execute command sequentially:-
:-    SLAVE STOP;
:-    CHANGE MASTER TO MASTER_HOST='anotherServerIP', MASTER_USER='anotherServerMysqlUserName', MASTER_PASSWORD='anotherServerMysqlPAssword', MASTER_LOG_FILE='mysql- bin.006', MASTER_LOG_POS=183;
:- MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.100).
:- MASTER_USER is the user we granted replication privileges on the master.
:- MASTER_PASSWORD is the password of MASTER_USER on the master.
:- MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.
:- MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master
:-    START SLAVE;
:-    quit;
12:- See the magic. Laughing

No comments:

Post a Comment