{"id":4121,"date":"2020-12-10T15:57:59","date_gmt":"2020-12-10T14:57:59","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=4121"},"modified":"2020-12-17T18:08:19","modified_gmt":"2020-12-17T17:08:19","slug":"setting-up-replication-with-various-methods","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2020\/12\/10\/setting-up-replication-with-various-methods\/","title":{"rendered":"Setting up Replication with various methods for MySQL 8"},"content":{"rendered":"\n<figure class=\"wp-block-image\"><img data-recalc-dims=\"1\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/Irish_castle_and_montain_by_Olivier_Dasini_1632x918.JPG\" alt=\"Irish Castle and mountains by Olivier DASINI\"\/><\/figure>\n\n\n\n<p>In the world of the Databases, one of the most important value that we are all trying to achieve is High Availability. Not to loose our valuable data or in case one server fails to always have another to step in and take control. <\/p>\n\n\n\n<p>So, it is critical to have along with our primary database, other identical instances, the replicas. <\/p>\n\n\n\n<p>On this blog post, we will see the most famous ways to set up our replication. <\/p>\n\n\n\n<p>MySQL offers a variety of options to achieve replication set up. Here we are going to review how to create a replica based on GTID replication by using one of the following methods:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>mysqldump<\/strong><\/li><li><strong>rsync<\/strong><\/li><li><strong>MySQL Enterprise Backup<\/strong> (MEB)<\/li><li><strong>Clone plugin<\/strong><\/li><\/ul>\n\n\n\n<p><strong>MySQL Shell<\/strong>, using the utilities, is another method. Please <a href=\"http:\/\/dasini.net\/blog\/2020\/12\/17\/mysql-shell-the-new-era\/\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"MySQL SHELL \u2013 The new era\">see this article<\/a>.<\/p>\n\n\n\n\n\n<p>Before we initiate any process we need to make sure that on our my.cnf, primary &amp; replica, have set the following parameters:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>server-id \nlog-bin \nbinlog_format \ngtid-mode=ON\nenforce-gtid-consistency<\/code><\/pre>\n\n\n\n\n\n\n\n<h2 class=\"wp-block-heading\"><strong>1st Replication Method &#8211; mysqldump<\/strong><\/h2>\n\n\n\n<p>So, starting with mysqldump option we need to make sure that we have enabled gtid-purged:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysqldump -u dumpUser -p --routines --events --triggers --single-transaction --set-gtid-purged=ON --all-databases &gt; backup.sql<\/code><\/pre>\n\n\n\n<p>Once this is finished, if we do :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>grep PURGED backup.sql<\/code><\/pre>\n\n\n\n<p>We will receive the corresponding gtid that is needed for setting up our replica. The backup.sql file should be moved to the replica, a simple scp should do. <\/p>\n\n\n\n<p>Moving along to the replica, we perform the following command:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql -u root -p -e \"source path\/backup.sql\"<\/code><\/pre>\n\n\n\n<p>After this import is completed, we simple need to execute the following:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CHANGE MASTER TO MASTER_HOST='primary_IP' ,MASTER_USER='user_to_connect',MASTER_PASSWORD='******', MASTER_AUTO_POSITION=1;\nstart slave;<\/code><\/pre>\n\n\n\n<p>If both the above commands are executed with no issue, perform a show slave status\\G to verify that both Slave_IO_Running and Slave_SQL_Running are set to yes.<\/p>\n\n\n\n\n\n\n\n<h2 class=\"wp-block-heading\"><strong>2nd Replication Method &#8211; rsync<\/strong><\/h2>\n\n\n\n<p>When using the rsync option, the database on the replica must not be running. And on the primary db we will need to have a lock on the tables. As soon as the tables are locked we may proceed with rsyncing the data.<\/p>\n\n\n\n<p>So:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>FLUSH TABLES WITH READ LOCK;\nrsync -axvz \/datadir\/* root@replica:\/datadir<\/code><\/pre>\n\n\n\n<p>Once the copy is completed, we will need to remove the auto.cnf (as this will give the same UUID and we will not be able to set up our replica) and we will also remove mysql.sock.<\/p>\n\n\n\n<p>The next steps will be to start our database and set it up as a replica:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code> systemctl start mysqld\n mysql -uroot -p\n CHANGE MASTER TO MASTER_HOST='primary_IP' ,MASTER_USER='user_to_connect',MASTER_PASSWORD='******',    MASTER_AUTO_POSITION=1;\n start slave;<\/code><\/pre>\n\n\n\n<p>And of course do not forget to reverse the lock command on the primary:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>unlock tables;<\/code><\/pre>\n\n\n\n\n\n\n\n<h2 class=\"wp-block-heading\"><strong>3rd Replication Method &#8211; MEB<\/strong><\/h2>\n\n\n\n<p>MEB, MySQL Enterprise Backup, is a commercially licensed backup utility for MySQL databases. One of the fastest and easiest way to set up your replication while primary db is up and running. <\/p>\n\n\n\n<p>Please note that the db on the replica should not be running and the corresponding paths should be empty, as this may cause confusion. For example, imagine having forgotten a binlog file and binlog_index to trying to read from erroneous binlogs. This will lead to having the replication broken. <\/p>\n\n\n\n<p>So, for mysqlback &amp; restore we can perform: <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysqlbackup -u backupUser -p --backup-dir=\/tmp --backup-image=- --compress backup-to-image | ssh mysql@replica_host \"\/bin\/mysqlbackup --backup-dir=\/tmp --datadir=path_of_data --log-bin=binlog_path --relay-log=relay_log_path --uncompress --backup-image=- copy-back-and-apply-log\"\n<\/code><\/pre>\n\n\n\n<p>In one command we are sending our data to the replica as well. <\/p>\n\n\n\n<p>Once, this is completed we go into the \/tmp folder in the primary node and inside the meta folder, we take note of the command inside the backup_gtid_purged.sql file. This is needed for setting up our replica:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code> reset slave;\n reset master;\n set global.gtid_purged=@@ (this is the command inside the backup_gtid_purged.sql)\n CHANGE MASTER TO MASTER_HOST='primary_IP' ,MASTER_USER='user_to_connect',MASTER_PASSWORD='******', MASTER_AUTO_POSITION=1;\n start slave;<\/code><\/pre>\n\n\n\n<p>Our replica is up and running with no trouble.<\/p>\n\n\n\n\n\n\n\n<h2 class=\"wp-block-heading\"><strong>4th Replication Method &#8211; clone plugin<\/strong><\/h2>\n\n\n\n<p>Last but not least we have the clone method. Clone is initiated at <strong>8.0.17<\/strong> version and it is a really useful tool.<\/p>\n\n\n\n<p> First thing we need to do is to install the clone plugin on both the primary and wannabe replica as well as the user with the proper grants:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>INSTALL PLUGIN clone SONAME 'mysql_clone.so';\nCREATE USER clone_user@'%' IDENTIFIED BY 'xxxxxx';\nGRANT BACKUP_ADMIN, CLONE_ADMIN ON *.* to clone_user@'%';<\/code><\/pre>\n\n\n\n<p>Verify that plugin is indeed active:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT PLUGIN_NAME, PLUGIN_STATUS FROM information_schema.PLUGINS WHERE PLUGIN_NAME = 'clone';\n+-------------+---------------+\n| PLUGIN_NAME | PLUGIN_STATUS |\n+-------------+---------------+\n| clone       | ACTIVE        |\n+-------------+---------------+\n<\/code><\/pre>\n\n\n\n<p>And we will also set up our donor on the replica:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SET GLOBAL clone_valid_donor_list = 'Primary_host_IP:port';<\/code><\/pre>\n\n\n\n<p>Now we need to log in to the replica with the clone_user we created on the above-mentioned steps and execute the following command:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CLONE INSTANCE FROM 'clone_user'@'Primary_host_IP':port IDENTIFIED BY 'xxxxxx';<\/code><\/pre>\n\n\n\n<p>Once this is completed, we log out from clone_user and log in with root and set up our replica:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CHANGE MASTER TO MASTER_HOST='primary_IP' ,MASTER_USER='user_to_connect',MASTER_PASSWORD='******', MASTER_AUTO_POSITION=1;\nstart slave;<\/code><\/pre>\n\n\n\n<p>Please note that the master_user that is being used to set up our replica in all the methods needs to have access on our replica and have the replication_slave privilege. <\/p>\n\n\n\n\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Conclusion<\/strong><\/h2>\n\n\n\n<p>To sum up, MySQL offers a variety of ways to set up tour replication, what you will choose depends on your needs for speed, performance and down time.  <\/p>\n\n\n\n\n\n\n\n<h2 class=\"wp-block-heading\"><strong>References<\/strong><\/h2>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>MySQL Enterprise Backup<\/strong>: <ul><li><a href=\"https:\/\/dev.mysql.com\/doc\/mysql-enterprise-backup\/8.0\/en\/mysqlbackup.html\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/dev.mysql.com\/doc\/mysql-enterprise-backup\/8.0\/en\/mysqlbackup.html<\/a><\/li><\/ul><\/li><li><strong>mysqldump<\/strong>: <ul><li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/mysqldump.html\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/mysqldump.html<\/a><\/li><\/ul><\/li><li><strong>The Clone Plugin<\/strong>: <ul><li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/clone-plugin.html\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/clone-plugin.html<\/a><\/li><\/ul><\/li><li><strong>Setting Up Replication Using GTIDs<\/strong>: <ul><li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/replication-gtids-howto.html\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/replication-gtids-howto.html<\/a><\/li><\/ul><\/li><\/ul>\n\n\n","protected":false},"excerpt":{"rendered":"<p>In the world of the Databases, one of the most important value that we are all trying to achieve is High Availability. Not to loose our valuable data or in case one server fails to always have another to step in and take control.<\/p>\n<p>So, it is critical to have along with our primary database, other identical instances, the replicas.<\/p>\n<p>On this blog post, we will see the most famous ways to set up our replication.<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"categories":[1645,203],"tags":[38,1565,72],"class_list":["post-4121","post","type-post","status-publish","format-standard","hentry","category-backup-restore","category-mysql-en","tag-backup","tag-clone","tag-mysql-replication"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-14t","jetpack-related-posts":[{"id":4194,"url":"https:\/\/dasini.net\/blog\/2021\/01\/19\/mysql-in-azure\/","url_meta":{"origin":4121,"position":0},"title":"MySQL in Azure","author":"Anastasia Papachristopoulou","date":"19 janvier 2021","format":false,"excerpt":"In this article, we are going to see how to set up a MySQL Database instance on Microsoft Azure Cloud.","rel":"","context":"Dans &quot;Azure&quot;","block_context":{"text":"Azure","link":"https:\/\/dasini.net\/blog\/category\/azure\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":4351,"url":"https:\/\/dasini.net\/blog\/2021\/02\/23\/mysql-recovery\/","url_meta":{"origin":4121,"position":1},"title":"MySQL Recovery","author":"Anastasia Papachristopoulou","date":"23 f\u00e9vrier 2021","format":false,"excerpt":"If you are dealing with data, and you most probably are if you are reading this, one of your biggest fears would be not to be able to retrieve them. In a world where data actually surround us, it is critical to be able to retrieve them fast and with\u2026","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql-en\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/02\/IMG-7b894485ba416774df71d5cbbabd964b-V-3.jpg?resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/02\/IMG-7b894485ba416774df71d5cbbabd964b-V-3.jpg?resize=350%2C200 1x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/02\/IMG-7b894485ba416774df71d5cbbabd964b-V-3.jpg?resize=525%2C300 1.5x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/02\/IMG-7b894485ba416774df71d5cbbabd964b-V-3.jpg?resize=700%2C400 2x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/02\/IMG-7b894485ba416774df71d5cbbabd964b-V-3.jpg?resize=1050%2C600 3x"},"classes":[]},{"id":4418,"url":"https:\/\/dasini.net\/blog\/2021\/03\/16\/mysql-in-amazon-rds\/","url_meta":{"origin":4121,"position":2},"title":"MySQL in Amazon (RDS)","author":"Anastasia Papachristopoulou","date":"16 mars 2021","format":false,"excerpt":"Up until now we have seen MDS (MySQL Database Service) and MySQL in Azure. As the Cloud technology keeps moving fast, I thought it would be a good idea to see how to set up a MySQL in Amazon , as a service, that is RDS.","rel":"","context":"Dans &quot;Cloud&quot;","block_context":{"text":"Cloud","link":"https:\/\/dasini.net\/blog\/category\/cloud\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/03\/kefallonia.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/03\/kefallonia.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/03\/kefallonia.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/03\/kefallonia.jpg?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":4151,"url":"https:\/\/dasini.net\/blog\/2020\/12\/17\/mysql-shell-the-new-era\/","url_meta":{"origin":4121,"position":3},"title":"MySQL SHELL &#8211; The new era","author":"Anastasia Papachristopoulou","date":"17 d\u00e9cembre 2020","format":false,"excerpt":"In one of our previous articles - Setting up Replication with various methods for MySQL 8 - we reviewed how to create a replica with multiple tools. Now, it is time to perform the same action but with MySQL Shell.","rel":"","context":"Dans &quot;Backup \/ Restore&quot;","block_context":{"text":"Backup \/ Restore","link":"https:\/\/dasini.net\/blog\/category\/backup-restore\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":6650,"url":"https:\/\/dasini.net\/blog\/2024\/07\/16\/get-your-heatwave-mysql-data-on-premises-with-replication\/","url_meta":{"origin":4121,"position":4},"title":"Get Your HeatWave MySQL Data On-Premises with Replication","author":"Olivier DASINI","date":"16 juillet 2024","format":false,"excerpt":"This article guides you through setting up replication between a HeatWave MySQL instance (source) and an on-premise standalone MySQL instance (replica). It highlights key steps like creating a replication user, securing the connection, and using MySQL Shell utilities for data transfer.","rel":"","context":"Dans &quot;HeatWave&quot;","block_context":{"text":"HeatWave","link":"https:\/\/dasini.net\/blog\/category\/heatwave-en\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/Get-Your-HeatWave-MySQL-Data-On-Premises-with-Replication-1.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/Get-Your-HeatWave-MySQL-Data-On-Premises-with-Replication-1.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/Get-Your-HeatWave-MySQL-Data-On-Premises-with-Replication-1.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/Get-Your-HeatWave-MySQL-Data-On-Premises-with-Replication-1.png?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/Get-Your-HeatWave-MySQL-Data-On-Premises-with-Replication-1.png?resize=1050%2C600&ssl=1 3x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/Get-Your-HeatWave-MySQL-Data-On-Premises-with-Replication-1.png?resize=1400%2C800&ssl=1 4x"},"classes":[]},{"id":4188,"url":"https:\/\/dasini.net\/blog\/2021\/01\/26\/replicate-from-mysql-5-7-to-mysql-database-service\/","url_meta":{"origin":4121,"position":5},"title":"Replicate from MySQL 5.7 to MySQL Database Service","author":"Olivier DASINI","date":"26 janvier 2021","format":false,"excerpt":"MySQL Replication is a very common topology, widely used in various architecture. People use it, among others, for High Availability, Read Scalability or Geographic Redundancy. Another use case is to use MySQL Replication to seamlessly integrate a newer version of the server in your architecture. Let\u2019s say you are running\u2026","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql-en\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/4121","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/comments?post=4121"}],"version-history":[{"count":14,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/4121\/revisions"}],"predecessor-version":[{"id":4186,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/4121\/revisions\/4186"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=4121"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=4121"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=4121"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}