On the master server :

We have to enable networking ;if it is skipped. Enable binary logging and start a binary log of all databases

Add the following to the my.cnf file and restart MySQL


server-id = 1
log-bin=mysql-bin
binlog-do-db=gnusys_kb
binlog-do-db=gnusys_wiki
binlog-do-db=powerdns


create a user on the master with replication slave privileges on all databases from the slaves IP address

GRANT REPLICATION SLAVE ON *.* TO '*****************'@'ip.ip.ip.ip' IDENTIFIED BY '********';


The next step is to have identical data on both master and slave server .This can be accomplished by preventing all writes on the master for some time and take a snapshot of the databases on the master using mysqldump and restore all of them on the slave

FLUSH TABLES WITH READ LOCK;


Keep this prompt running;inoder that the lock is active

mysqldump db1 –lock-all-tables > db1.june14.sql
mysqldump gnusys_kb –lock-all-tables > powerdns.june14.sql
………………

and on the slave server restore the databases.

The next step is to get some vital information from the master to initiate replication on the slave.On masters mysql prompt run

SHOW MASTER STATUS;
+------------------+----------+---------------------------------------------------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+---------------------------------------------------------------+------------------+
| mysql-bin.000001 | 1547443 | gnusys_kb,gnusys_wiki,powerdns,gnusys_kb,gnusys_wiki,powerdns | |
+------------------+----------+---------------------------------------------------------------+------------------+
1 row in set (0.00 sec)


add the following to the my.cnf of the slave server

#Mysql slave settings
server-id = 2
master-host=174.136.4.248
master-user=******************
master-password=*********
master-connect-retry=60
replicate-do-db=gnusys_kb
replicate-do-db=gnusys_wiki
replicate-do-db=powerdns


Restart slave mysql

Now we remote the write lock on the master.On masters mysql prompt which we kept running

UNLOCK TABLES;


On the slaves mysql prompt run the following

SLAVE STOP;

CHANGE MASTER TO MASTER_HOST='ip.ip.ip.ip', MASTER_USER='*********',MASTER_PASSWORD='*****', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;

START SLAVE;
quit;


Thats it!.

The status of master can be known from

SHOW MASTER STATUS;


and slave can be known by

SHOW SLAVE STATUS;


You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.