Introduction<\/h3>\n

When you are running a critical website, it is important to make sure that you have at least one redundant backup server. This ensures that your database is syncing in real-time. MySQL refers to database syncing as replication. This short tutorial provides instructions on how to setup a master-slave MySQL replication.<\/p>\n

Setup the master node<\/h3>\n

Edit\u00a0\/etc\/mysql\/my.cnf<\/code>\u00a0to disable IP binding.<\/p>\n

Comment out the following lines:<\/p>\n

bind-address = 127.0.0.1\r\nskip-networking\r\n<\/code><\/pre>\n
Create new settings for replication by running the following commands:<\/p>\n
cat >\/etc\/mysql\/conf.d\/replication.cnf <<EOF\r\n[mysqld]\r\nserver-id = 100\r\nlog_bin = \/var\/log\/mysql\/mysql-bin.log\r\nbinlog-do-db = YOUR_DATABASE_ONE\r\nbinlog-do-db = YOUR_DATABASE_TWO\r\nEOF\r\n<\/code><\/pre>\n
Restart MySQL server.<\/p>\n
\/etc\/init.d\/mysql restart\r\n<\/code><\/pre>\n
Create a slave user in MySQL by running following commands in the MySQL console.<\/p>\n
CREATE USER 'slave'@'SLAVE_SERVER_IP_ADDRESS' identified by 'YOUR_SLAVE_PASSWORD';\r\nGRANT ALL ON *.* TO 'slave'@'SLAVE_SERVER_IP_ADDRESS';\r\nFLUSH PRIVILEGES;\r\n<\/code><\/pre>\n
Now, lock write access to your database:<\/p>\n
FLUSH TABLES WITH READ LOCK;\r\n<\/code><\/pre>\n
Get master node status:<\/p>\n
SHOW MASTER STATUS;\r\n<\/code><\/pre>\n
Note:<\/strong>\u00a0Write down the values of the “File” and “Position” fields as we will need to reference them later for the slave node.<\/p>\n
Open another SSH session and dump out your database using following command:<\/p>\n
mysqldump -u MYSQL_USERNAME -pMYSQL_PASSWORD --databases YOUR_DATABASE_ONE YOUR_DATABASE_TWO > database.sql\r\n<\/code><\/pre>\n
Return to previous SSH session and issue the following command in MySQL console to unlock write access:<\/p>\n
UNLOCK TABLES;\r\n<\/code><\/pre>\n
Transfer the\u00a0database.sql<\/code>\u00a0created in previous step to slave node.<\/p>\n
Setup the slave node<\/h3>\n
Edit\u00a0\/etc\/mysql\/my.cnf<\/code>\u00a0to disable IP binding.<\/p>\n
Comment out the following lines:<\/p>\n
bind-address = 127.0.0.1\r\nskip-networking\r\n<\/code><\/pre>\n
Create new settings for replication by running following command:<\/p>\n
cat >\/etc\/mysql\/conf.d\/replication.cnf <<EOF\r\n[mysqld]\r\nserver-id = 101\r\nlog_bin = \/var\/log\/mysql\/mysql-bin.log\r\nbinlog-do-db = YOUR_DATABASE_ONE\r\nbinlog-do-db = YOUR_DATABASE_TWO\r\nEOF\r\n<\/code><\/pre>\n
Restart MySQL server.<\/p>\n
\/etc\/init.d\/mysql restart\r\n<\/code><\/pre>\n
Import\u00a0database.sql<\/code>\u00a0created from master node just now by using this command:<\/p>\n
mysql -u MYSQL_USERNAME-pMYSQL-PASSWORD < database.sql\r\n<\/code><\/pre>\n
Now, lets start the replication. Open MySQL console, run following commands:<\/p>\n
SLAVE STOP;\r\nCHANGE MASTER TO MASTER_HOST='MASTER_SERVER_IP_ADDRESS', MASTER_USER='slave', MASTER_PASSWORD='YOUR_SLAVE_PASSWORD', MASTER_LOG_FILE='FILE_VALUE_FROM_MASTER', MASTER_LOG_POS=POSITION_VALUE_FROM_MASTER;\r\nSLAVE START;\r\n<\/code><\/pre>\n
Note:<\/strong>\u00a0The value for\u00a0MASTER_LOG_FILE<\/code>\u00a0and\u00a0MASTER_LOG_POS<\/code>\u00a0is “File” and “Position” that we wrote down from the master node setup.<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"open","ping_status":"closed","template":"","format":"standard","manualknowledgebasecat":[110,233,242,247],"manual_kb_tag":[393],"_links":{"self":[{"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/manual_kb\/2669"}],"collection":[{"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/manual_kb"}],"about":[{"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/types\/manual_kb"}],"author":[{"embeddable":true,"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/comments?post=2669"}],"version-history":[{"count":1,"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/manual_kb\/2669\/revisions"}],"predecessor-version":[{"id":2670,"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/manual_kb\/2669\/revisions\/2670"}],"wp:attachment":[{"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/media?parent=2669"}],"wp:term":[{"taxonomy":"manualknowledgebasecat","embeddable":true,"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/manualknowledgebasecat?post=2669"},{"taxonomy":"manual_kb_tag","embeddable":true,"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/manual_kb_tag?post=2669"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}