{"id":2165,"date":"2013-07-07T11:36:44","date_gmt":"2013-07-07T03:36:44","guid":{"rendered":"http:\/\/rmohan.com\/?p=2165"},"modified":"2013-07-07T11:36:44","modified_gmt":"2013-07-07T03:36:44","slug":"database-replication-in-mysql","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=2165","title":{"rendered":"Database Replication In MySQL"},"content":{"rendered":"

Database Replication In\u00a0MySQL<\/h1>\n

 <\/p>\n

Configure the MySQL Master<\/em>\u00a0Server<\/em><\/strong><\/p>\n

Step 1 : edit\u00a0\/etc\/mysql\/my.cnf file.\u00a0<\/strong><\/p>\n

#skip-networking<\/p>\n

#bind-address\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0= 127.0.0.1<\/p>\n

(add below line in \/etc\/mysql\/my.cnf file)<\/p>\n

server-id\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0= 1<\/p>\n

log_bin\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0= \/var\/log\/mysql\/mysql-bin.log<\/p>\n

binlog_do_db\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0= replicationdb<\/p>\n

step 2 : \u00a0Restart Mysql server<\/strong><\/p>\n

#\/etc\/init.d\/mysql restart<\/p>\n

Step 3 : create a user with replication privileges:<\/strong><\/p>\n

#mysql -u root -p<\/p>\n

mysql> GRANT REPLICATION SLAVE ON *.* TO \u2019replicationuser\u2019@\u2019%\u2019 IDENTIFIED BY \u2018<some_password>\u2019;<\/p>\n

mysql>FLUSH PRIVILEGES;<\/p>\n

mysql>USE replicationdb;<\/p>\n

mysql>FLUSH TABLES WITH READ LOCK;<\/p>\n

mysql>SHOW MASTER STATUS;<\/p>\n

Result of above command:<\/p>\n

+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014\u2014-+\u2014\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014\u2014\u2014+<\/p>\n

| File\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| Position | Binlog_do_db | Binlog_ignore_db |<\/p>\n

+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014\u2014-+\u2014\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014\u2014\u2014+<\/p>\n

| mysql-bin.005 | 180\u00a0\u00a0 \u00a0\u00a0\u00a0| replicationdb\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|<\/p>\n

+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014\u2014-+\u2014\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014\u2014\u2014+<\/p>\n

1 row in set (0.00 sec)<\/p>\n

Please remeber above information will need in slave server configuration<\/p>\n

mysql>quit;<\/p>\n

Step 4 : Dump replicationdb from the Master server<\/strong><\/p>\n

#mysqldump -u root -p<password> replicationdb > replicationdb.sql<\/p>\n

Above command create \u00a0dump of replicationdb\u00a0in the file replication.sql. Transfer this file to your slave server!<\/p>\n

Step \u00a05 : Unlock the tables<\/strong><\/p>\n

#mysql -u root -p<\/p>\n

mysql>UNLOCK TABLES;<\/p>\n

mysql>quit;<\/p>\n

 <\/p>\n

Configure The Slave Server<\/em><\/strong><\/p>\n

Step 1 : Create the database replicationdb<\/strong><\/p>\n

#mysql -u root -p
\nmysql>CREATE DATABASE replicationdb;
\nmysql>quit;<\/p>\n

Step 2 : Extract the dump database in replicationdb database<\/strong><\/p>\n

#mysql -u root -p<password> replicationdb < \/path\/to\/replicationdb.sql<\/p>\n

Step 3 : Add the below lines into\u00a0\/etc\/mysql\/my.cnf<\/p>\n

server-id=2<\/p>\n

master-host=192.168.10.175<\/p>\n

master-user=replicationuser<\/p>\n

master-password=secret<\/p>\n

replicate-do-db=replicationdb<\/p>\n

Step 3 : Restart MySQL:<\/strong><\/p>\n

# \/etc\/init.d\/mysql restart<\/p>\n

Step 4 : \u00a0Allow slave user to connet to Remote Master server<\/strong><\/div>\n
# mysql -u root -p<\/div>\n

mysql>SLAVE STOP;<\/p>\n

mysql>CHANGE MASTER TO MASTER_HOST=\u2019192.168.10.175?, MASTER_USER=\u2019replicationuser\u2019, MASTER_PASSWORD=\u2019<some_password>\u2019, MASTER_LOG_FILE=\u2019mysql-bin.005?, MASTER_LOG_POS=180;<\/p>\n

mysql>START SLAVE;<\/p>\n

mysql>quit;<\/p>\n

Configuration is Complete now! \u00a0So whenever replicationdb\u00a0is updated on the master server, all changes will be replicated to replicationdb\u00a0on the slave server.<\/p>\n","protected":false},"excerpt":{"rendered":"

Database Replication In MySQL <\/p>\n

 <\/p>\n

Configure the MySQL Master Server<\/p>\n

Step 1 : edit \/etc\/mysql\/my.cnf file. <\/p>\n

#skip-networking<\/p>\n

#bind-address = 127.0.0.1<\/p>\n

(add below line in \/etc\/mysql\/my.cnf file)<\/p>\n

server-id = 1<\/p>\n

log_bin = \/var\/log\/mysql\/mysql-bin.log<\/p>\n

binlog_do_db = replicationdb<\/p>\n

step 2 : Restart Mysql server<\/p>\n

#\/etc\/init.d\/mysql restart<\/p>\n

Step 3 : create a user with replication privileges:<\/p>\n

#mysql -u […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/2165"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2165"}],"version-history":[{"count":1,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/2165\/revisions"}],"predecessor-version":[{"id":2166,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/2165\/revisions\/2166"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2165"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2165"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2165"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}