{"id":609,"date":"2020-11-19T14:45:35","date_gmt":"2020-11-19T14:45:35","guid":{"rendered":"https:\/\/dft.wiki\/?p=609"},"modified":"2026-05-24T19:07:18","modified_gmt":"2026-05-24T23:07:18","slug":"mysql-database-synchronization","status":"publish","type":"post","link":"https:\/\/dft.wiki\/?p=609","title":{"rendered":"MySQL Database Replication"},"content":{"rendered":"<p>Synchronize or Replicate a database protects the integrity of the data over the different servers, provides high availability, and improves capacity, reliability, fault-tolerance, and accessibility.<\/p>\n<p><strong>Models<\/strong><\/p>\n<p>Master-to-Slave: Master feeds Slave with real-time updates.<\/p>\n<p>Master-to-Master: Both servers feed each other with the latest updates.<\/p>\n<p><strong>Note:<\/strong> Master-to-Master should not be used in high transactional applications on MySQL versions before 8. Prefer Master-to-Slave and have multiple failover\/read-only copies of your data.<\/p>\n<hr \/>\n<p><strong>Configuring Master-to-Slave<\/strong><\/p>\n<ul>\n<li>Master IP: 10.0.0.1<\/li>\n<li>Slave IP: 10.0.0.2<\/li>\n<\/ul>\n<p><strong>BOTH<\/strong><\/p>\n<pre>sudo apt update\r\nsudo apt install mysql-server mysql-client -y<\/pre>\n<p><strong>MASTER<\/strong><\/p>\n<pre>sudo nano \/etc\/mysql\/mysql.conf.d\/mysqld.cnf<\/pre>\n<p>Under <strong>[mysqld]<\/strong> change the following lines\u00a0and uncomment or add the other lines if necessary:<\/p>\n<pre>bind-address = <strong>0.0.0.0\r\n<\/strong>server-id = <strong>1\r\n<\/strong>log_bin = \/var\/log\/mysql\/mysql-bin.log\r\n<\/pre>\n<p>Restart and check if it is running.<\/p>\n<pre>sudo systemctl <strong>restart<\/strong> mysql\r\nsudo systemctl <strong>status<\/strong> mysql<\/pre>\n<p>Create a user to the slave connect to the master:<\/p>\n<pre>sudo mysql -u root -p\r\nmysql&gt; CREATE USER '<span style=\"color: #ff0000;\"><strong>repli_user<\/strong><\/span>'@'<strong>%<\/strong>' IDENTIFIED BY '<span style=\"color: #ff0000;\"><strong>strong_<\/strong><strong>password<\/strong><\/span>';\r\nmysql&gt; GRANT REPLICATION SLAVE ON *.* TO '<strong><span style=\"color: #ff0000;\">repli_user<\/span><\/strong>'@'<strong>%<\/strong>';\r\nmysql&gt; FLUSH PRIVILEGES;\r\nmysql&gt; FLUSH TABLES WITH READ LOCK;\r\nmysql&gt; SHOW MASTER STATUS;<\/pre>\n<p>Note the two values acquired (<span style=\"color: #ff0000;\"><strong>mysql-bin.000004<\/strong><\/span> and <strong><span style=\"color: #ff0000;\">731<\/span><\/strong>):<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1611\" src=\"https:\/\/dft.wiki\/wp-content\/uploads\/sites\/15\/2020\/11\/Screenshot-from-2021-03-25-16-34-29.png\" alt=\"\" width=\"665\" height=\"97\" srcset=\"https:\/\/dft.wiki\/wp-content\/uploads\/sites\/15\/2020\/11\/Screenshot-from-2021-03-25-16-34-29.png 665w, https:\/\/dft.wiki\/wp-content\/uploads\/sites\/15\/2020\/11\/Screenshot-from-2021-03-25-16-34-29-300x44.png 300w\" sizes=\"auto, (max-width: 665px) 100vw, 665px\" \/><\/p>\n<p><strong>SLAVE<\/strong><\/p>\n<pre>sudo nano \/etc\/mysql\/mysql.conf.d\/mysqld.cnf<\/pre>\n<p>Under <strong>[mysqld]<\/strong> change the following lines\u00a0and uncomment or add the other lines if necessary:<\/p>\n<pre>bind-address = <strong>0.0.0.0\r\n<\/strong>server-id = <strong>2\r\n<\/strong>log_bin = \/var\/log\/mysql\/mysql-bin.log<\/pre>\n<p>Restart and check if it is running.<\/p>\n<pre>sudo systemctl <strong>restart<\/strong> mysql\r\nsudo systemctl <strong>status<\/strong> mysql<\/pre>\n<p>Log into MySQL shell, configure the slave to connect to the master with the created credentials, and insert the preview acquired data:<\/p>\n<pre>sudo mysql -u root -p\r\nmysql&gt; <strong>STOP<\/strong> SLAVE;\r\nmysql&gt; CHANGE MASTER TO MASTER_HOST='<span style=\"color: #ff0000;\"><strong>10.0.0.1<\/strong><\/span>', MASTER_USER='<span style=\"color: #ff0000;\"><strong>repli_user<\/strong><\/span>', MASTER_PASSWORD='<span style=\"color: #ff0000;\"><strong>strong_password<\/strong><\/span>', MASTER_LOG_FILE='<span style=\"color: #ff0000;\"><strong>mysql-bin.000004<\/strong><\/span>', MASTER_LOG_POS=<span style=\"color: #ff0000;\"><strong>731<\/strong><\/span>;\r\nmysql&gt; <strong>START<\/strong> SLAVE;\r\nmysql&gt; SHOW SLAVE STATUS\\G;<\/pre>\n<p>Check if the status:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1616\" src=\"https:\/\/dft.wiki\/wp-content\/uploads\/sites\/15\/2020\/11\/Screenshot-from-2021-03-26-09-59-06.png\" alt=\"\" width=\"515\" height=\"251\" srcset=\"https:\/\/dft.wiki\/wp-content\/uploads\/sites\/15\/2020\/11\/Screenshot-from-2021-03-26-09-59-06.png 515w, https:\/\/dft.wiki\/wp-content\/uploads\/sites\/15\/2020\/11\/Screenshot-from-2021-03-26-09-59-06-300x146.png 300w\" sizes=\"auto, (max-width: 515px) 100vw, 515px\" \/><\/p>\n<p>Up to this point, it will work everything should work perfectly for Ubuntu 18.04 with MySQL 5.7 (if you got it successful just to <strong>Testing<\/strong> step)\u00a0but for Ubuntu 20.04 MySQL 8.0 you should get the following result:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1617\" src=\"https:\/\/dft.wiki\/wp-content\/uploads\/sites\/15\/2020\/11\/Screenshot-from-2021-03-26-10-23-13.png\" alt=\"\" width=\"641\" height=\"651\" srcset=\"https:\/\/dft.wiki\/wp-content\/uploads\/sites\/15\/2020\/11\/Screenshot-from-2021-03-26-10-23-13.png 641w, https:\/\/dft.wiki\/wp-content\/uploads\/sites\/15\/2020\/11\/Screenshot-from-2021-03-26-10-23-13-295x300.png 295w\" sizes=\"auto, (max-width: 641px) 100vw, 641px\" \/><\/p>\n<p>This issue may be fixed by issuing the following command on the console of the slave server to import the public key from the server:<\/p>\n<pre>mysql --ssl-mode=DISABLED -h <strong>10.0.0.1<\/strong> -u <strong>repli_user<\/strong> -p <span style=\"color: #ff0000;\"><strong>--get-server-public-key<\/strong><\/span><\/pre>\n<p>Then check it again:<\/p>\n<pre>sudo mysql -u root -p\r\nmysql&gt; SHOW SLAVE STATUS\\G;<\/pre>\n<p>Testing:<\/p>\n<p>Create a database on the Master and showing it on the Slave.<\/p>\n<pre>mysql&gt; CREATE DATABASE <strong>ABC<\/strong>;\r\nmysql&gt; SHOW DATABASES;<\/pre>\n<pre>mysql&gt; SHOW DATABASES;<\/pre>\n<hr \/>\n<p><strong>Configuring Master-to-Master<\/strong><\/p>\n<p>Do all the steps from Master-to-Slave and ensure it was successful to replicate.<\/p>\n<p><strong>SECOND MASTER<\/strong> (previously called SLAVE)<\/p>\n<p>To transform the slave into a second master create a user to the first master and connect to it:<\/p>\n<pre>sudo mysql -u root -p\r\nmysql&gt; CREATE USER '<span style=\"color: #ff0000;\"><strong>repli_user<\/strong><\/span>'@'<strong>%<\/strong>' IDENTIFIED BY '<span style=\"color: #ff0000;\"><strong>strong_<\/strong><strong>password<\/strong><\/span>';\r\nmysql&gt; GRANT REPLICATION SLAVE ON *.* TO '<strong><span style=\"color: #ff0000;\">repli_user<\/span><\/strong>'@'<strong>%<\/strong>';\r\nmysql&gt; FLUSH PRIVILEGES;\r\nmysql&gt; FLUSH TABLES WITH READ LOCK;\r\nmysql&gt; SHOW MASTER STATUS;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1620\" src=\"https:\/\/dft.wiki\/wp-content\/uploads\/sites\/15\/2020\/11\/Screenshot-from-2021-03-26-11-54-03.png\" alt=\"\" width=\"689\" height=\"121\" srcset=\"https:\/\/dft.wiki\/wp-content\/uploads\/sites\/15\/2020\/11\/Screenshot-from-2021-03-26-11-54-03.png 689w, https:\/\/dft.wiki\/wp-content\/uploads\/sites\/15\/2020\/11\/Screenshot-from-2021-03-26-11-54-03-300x53.png 300w\" sizes=\"auto, (max-width: 689px) 100vw, 689px\" \/><\/p>\n<p><strong>FIRST MASTER<\/strong><\/p>\n<pre>sudo mysql -u root -p\r\nmysql&gt; <strong>STOP<\/strong> SLAVE;\r\nmysql&gt; CHANGE MASTER TO MASTER_HOST='<span style=\"color: #ff0000;\"><strong>10.0.0.2<\/strong><\/span>', MASTER_USER='<span style=\"color: #ff0000;\"><strong>repli_user<\/strong><\/span>', MASTER_PASSWORD='<span style=\"color: #ff0000;\"><strong>strong_password<\/strong><\/span>', MASTER_LOG_FILE='<span style=\"color: #ff0000;\"><strong>mysql-bin.000002<\/strong><\/span>', MASTER_LOG_POS=<span style=\"color: #ff0000;\"><strong>1045<\/strong><\/span>;\r\nmysql&gt; <strong>START<\/strong> SLAVE;\r\nmysql&gt; SHOW SLAVE STATUS\\G;<\/pre>\n<p>If necessary apply the same solution used on the Second Master (Slave) server to the First Master:<\/p>\n<pre>mysql --ssl-mode=DISABLED -h <strong>10.0.0.2<\/strong> -u <strong>repli_user<\/strong> -p <span style=\"color: #ff0000;\"><strong>--get-server-public-key<\/strong><\/span><\/pre>\n<hr \/>\n<p><strong>DISABLING REPLICATION<\/strong><\/p>\n<pre>RESET SLAVE<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Synchronize or Replicate a database protects the integrity of the data over the different servers, [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"class_list":["post-609","post","type-post","status-publish","format-standard","hentry","category-web"],"_links":{"self":[{"href":"https:\/\/dft.wiki\/index.php?rest_route=\/wp\/v2\/posts\/609","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dft.wiki\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dft.wiki\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dft.wiki\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dft.wiki\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=609"}],"version-history":[{"count":13,"href":"https:\/\/dft.wiki\/index.php?rest_route=\/wp\/v2\/posts\/609\/revisions"}],"predecessor-version":[{"id":5556,"href":"https:\/\/dft.wiki\/index.php?rest_route=\/wp\/v2\/posts\/609\/revisions\/5556"}],"wp:attachment":[{"href":"https:\/\/dft.wiki\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=609"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dft.wiki\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=609"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dft.wiki\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=609"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}