{"id":568,"date":"2026-07-22T05:02:41","date_gmt":"2026-07-22T05:02:41","guid":{"rendered":"https:\/\/virtualserversvps.com\/blog\/?p=568"},"modified":"2026-07-24T22:16:43","modified_gmt":"2026-07-24T22:16:43","slug":"test-new-article","status":"publish","type":"post","link":"https:\/\/virtualserversvps.com\/blog\/test-new-article\/","title":{"rendered":"How to Set Up MySQL Database Replication on a VPS"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Running a production database on a single VPS is risky. If that server goes down, your entire application goes with it. Database replication solves this by maintaining synchronized copies of your data across multiple VPS instances. This tutorial walks through configuring MySQL master-slave replication on two Ubuntu VPS nodes, complete with monitoring, failover considerations, and security hardening.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Why Database Replication Matters on a VPS<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">VPS instances, especially at lower price points, share hypervisor resources with neighboring tenants. While reputable providers minimize noise, resource contention can still cause brief service interruptions. Database replication gives you:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>High availability<\/strong> \u2014 If the primary fails, the replica can be promoted to master with minimal downtime.<\/li><li><strong>Read scalability<\/strong> \u2014 Offload read queries to replica servers to keep the master focused on writes.<\/li><li><strong>Disaster recovery<\/strong> \u2014 A replica on a separate VPS (ideally in a different datacenter) protects against full server failure.<\/li><li><strong>Backup isolation<\/strong> \u2014 Run backups against the replica to avoid performance impact on production traffic.<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Before diving in, review the <a href=\"https:\/\/virtualserversvps.com\/#features\">VPS comparison table<\/a> to choose providers with adequate RAM and disk I\/O for database workloads.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Prerequisites<\/h2>\n\n\n\n<ul class=\"wp-block-list\"><li>Two Ubuntu 22.04 or 24.04 VPS instances (one master, one replica)<\/li><li>MySQL 8.0+ installed on both (or MariaDB 10.6+)<\/li><li>Root or sudo access on both servers<\/li><li>Network connectivity between both VPS instances (private network preferred)<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">For this guide we recommend at least 2 GB RAM per VPS. <a href=\"https:\/\/interserver.net\/vps?id=1067805&#038;sid=virtualserversvps\" target=\"_blank\">InterServer VPS plans<\/a> offer NVMe storage and dedicated CPU resources ideal for database workloads.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 1: Configure the Master Server<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">SSH into your master VPS and edit the MySQL configuration:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo nano \/etc\/mysql\/mysql.conf.d\/mysqld.cnf<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Add or uncomment the following lines under the <code>[mysqld]<\/code> section:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>[mysqld]\nserver-id = 1\nlog_bin = \/var\/log\/mysql\/mysql-bin.log\nbinlog_do_db = your_database_name\nbind-address = 0.0.0.0<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Replace <code>your_database_name<\/code> with the actual database you want to replicate. Restart MySQL:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo systemctl restart mysql<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step 2: Create a Replication User<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Log into the MySQL shell on the master and create a dedicated replication user:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mysql -u root -p\n\nCREATE USER 'replica'@'%' IDENTIFIED BY 'strong_password_here';\nGRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';\nFLUSH PRIVILEGES;\n\nSHOW MASTER STATUS;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Note the <code>File<\/code> and <code>Position<\/code> values from <code>SHOW MASTER STATUS;<\/code> \u2014 you will need them on the replica.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 3: Configure the Replica Server<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">On the replica VPS, edit the MySQL configuration:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo nano \/etc\/mysql\/mysql.conf.d\/mysqld.cnf<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>[mysqld]\nserver-id = 2\nlog_bin = \/var\/log\/mysql\/mysql-bin.log\nrelay_log = \/var\/log\/mysql\/mysql-relay-bin.log\nread_only = 1<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The <code>read_only = 1<\/code> prevents accidental writes to the replica. Restart MySQL:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo systemctl restart mysql<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step 4: Import the Master Database to the Replica<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">On the master, export the database:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysqldump -u root -p --databases your_database_name --master-data > master_dump.sql<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Copy this dump to the replica (via SCP):<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>scp master_dump.sql user@replica_ip:\/tmp\/<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Import it on the replica:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql -u root -p < \/tmp\/master_dump.sql<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step 5: Start Replication<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">On the replica, configure and start the replication process:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mysql -u root -p\n\nCHANGE REPLICATION SOURCE TO\n  SOURCE_HOST='master_ip',\n  SOURCE_USER='replica',\n  SOURCE_PASSWORD='strong_password_here',\n  SOURCE_LOG_FILE='mysql-bin.000001',\n  SOURCE_LOG_POS=157;\n\nSTART REPLICA;\n\nSHOW REPLICA STATUS\\G<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Check that <code>Replica_IO_Running<\/code> and <code>Replica_SQL_Running<\/code> both show <code>Yes<\/code>. If <code>Seconds_Behind_Source<\/code> is 0 or a low number, replication is working.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 6: Monitor and Verify Replication<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Create a monitoring script that checks replica lag:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>#!\/bin\/bash\n# check_replication.sh\nSTATUS=$(mysql -u root -p'your_password' -e \"SHOW REPLICA STATUS\\G\" 2>\/dev\/null)\n\nIO_RUNNING=$(echo \"$STATUS\" | grep \"Replica_IO_Running\" | awk '{print $2}')\nSQL_RUNNING=$(echo \"$STATUS\" | grep \"Replica_SQL_Running\" | awk '{print $2}')\nSECONDS_BEHIND=$(echo \"$STATUS\" | grep \"Seconds_Behind_Source\" | awk '{print $2}')\n\nif [ \"$IO_RUNNING\" != \"Yes\" ] || [ \"$SQL_RUNNING\" != \"Yes\" ]; then\n  echo \"Replication is DOWN!\" | mail -s \"CRITICAL: MySQL Replication Failure\" admin@example.com\nelif [ \"$SECONDS_BEHIND\" -gt 300 ]; then\n  echo \"Replica lag exceeds 5 minutes ($SECONDS_BEHIND seconds)\" | mail -s \"WARNING: MySQL Replication Lag\" admin@example.com\nelse\n  echo \"Replication OK. Lag: ${SECONDS_BEHIND}s\"\nfi<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Add this to cron to run every 5 minutes:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>*\/5 * * * * \/usr\/local\/bin\/check_replication.sh<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Security Hardening for Replication<\/h2>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>Use private networking<\/strong> \u2014 Configure both VPS instances on the same private network so replication traffic never touches the public internet.<\/li><li><strong>Encrypt replication<\/strong> \u2014 MySQL 8.0 supports TLS for replication. Add <code>SOURCE_SSL=1<\/code> to your CHANGE command.<\/li><li><strong>Firewall the replica<\/strong> \u2014 Only allow MySQL connections from the master IP: <code>sudo ufw allow from master_ip to any port 3306<\/code>.<\/li><li><strong>Use strong passwords<\/strong> \u2014 The replication user should have a randomly generated password stored in a secrets manager.<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Automated Failover with Orchestrator<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">For production environments, manual failover is too slow. <a href=\"https:\/\/github.com\/openark\/orchestrator\" target=\"_blank\">Orchestrator<\/a> is an open-source MySQL replication management tool that detects master failures and promotes the best replica automatically. Install it on a third management VPS:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo apt install -y orchestrator orchestrator-cli<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Orchestrator also provides a web UI for visualizing replication topology. For managed database solutions that handle replication automatically, <a href=\"https:\/\/cloudways.com\/en\/?id=2010927&#038;data1=virtualserversvps\" target=\"_blank\">Cloudways managed hosting<\/a> includes built-in database replication and automated backups.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">MySQL replication transforms a single-point-of-failure database into a resilient, scalable data layer. By following this guide, you now have a working master-replica setup with monitoring, security hardening, and a path to automated failover. Start with asynchronous replication for simplicity, then graduate to semi-synchronous or Group Replication as your requirements grow. For help choosing a VPS configuration that supports database workloads, check our <a href=\"https:\/\/virtualserversvps.com\/#features\">VPS provider comparison table<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Running a production database on a single VPS is risky. If that server goes down, your entire application goes with it. Database replication solves this by maintaining synchronized copies of&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":0,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-568","post","type-post","status-publish","format-standard","hentry","category-vps-guides-tutorials"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v26.1 (Yoast SEO v26.1) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How to Set Up MySQL Database Replication on a VPS - Virtual Servers VPS Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/virtualserversvps.com\/blog\/test-new-article\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Set Up MySQL Database Replication on a VPS\" \/>\n<meta property=\"og:description\" content=\"How to Set Up MySQL Database Replication on a VPS\" \/>\n<meta property=\"og:url\" content=\"https:\/\/virtualserversvps.com\/blog\/test-new-article\/\" \/>\n<meta property=\"og:site_name\" content=\"Virtual Servers VPS Blog\" \/>\n<meta property=\"article:published_time\" content=\"2026-07-22T05:02:41+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2026-07-24T22:16:43+00:00\" \/>\n<meta name=\"author\" content=\"Virtual-Servers-Vps-Editor\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Virtual-Servers-Vps-Editor\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/virtualserversvps.com\/blog\/test-new-article\/\",\"url\":\"https:\/\/virtualserversvps.com\/blog\/test-new-article\/\",\"name\":\"How to Set Up MySQL Database Replication on a VPS - Virtual Servers VPS Blog\",\"isPartOf\":{\"@id\":\"https:\/\/virtualserversvps.com\/blog\/#website\"},\"datePublished\":\"2026-07-22T05:02:41+00:00\",\"dateModified\":\"2026-07-24T22:16:43+00:00\",\"author\":{\"@id\":\"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/82a299a8284a66ff49f97c74684724a0\"},\"breadcrumb\":{\"@id\":\"https:\/\/virtualserversvps.com\/blog\/test-new-article\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/virtualserversvps.com\/blog\/test-new-article\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/virtualserversvps.com\/blog\/test-new-article\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/virtualserversvps.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Set Up MySQL Database Replication on a VPS\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/virtualserversvps.com\/blog\/#website\",\"url\":\"https:\/\/virtualserversvps.com\/blog\/\",\"name\":\"Virtual Servers VPS Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/virtualserversvps.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/82a299a8284a66ff49f97c74684724a0\",\"name\":\"Virtual-Servers-Vps-Editor\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d820b15f1cd028e97610d9adf536df7be5cb6423869967037d468d5355fa003f?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d820b15f1cd028e97610d9adf536df7be5cb6423869967037d468d5355fa003f?s=96&d=mm&r=g\",\"caption\":\"Virtual-Servers-Vps-Editor\"},\"sameAs\":[\"https:\/\/virtualserversvps.com\/blog\"],\"url\":\"https:\/\/virtualserversvps.com\/blog\/author\/virtualserversvps\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How to Set Up MySQL Database Replication on a VPS - Virtual Servers VPS Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/virtualserversvps.com\/blog\/test-new-article\/","og_locale":"en_US","og_type":"article","og_title":"How to Set Up MySQL Database Replication on a VPS","og_description":"How to Set Up MySQL Database Replication on a VPS","og_url":"https:\/\/virtualserversvps.com\/blog\/test-new-article\/","og_site_name":"Virtual Servers VPS Blog","article_published_time":"2026-07-22T05:02:41+00:00","article_modified_time":"2026-07-24T22:16:43+00:00","author":"Virtual-Servers-Vps-Editor","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Virtual-Servers-Vps-Editor","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/virtualserversvps.com\/blog\/test-new-article\/","url":"https:\/\/virtualserversvps.com\/blog\/test-new-article\/","name":"How to Set Up MySQL Database Replication on a VPS - Virtual Servers VPS Blog","isPartOf":{"@id":"https:\/\/virtualserversvps.com\/blog\/#website"},"datePublished":"2026-07-22T05:02:41+00:00","dateModified":"2026-07-24T22:16:43+00:00","author":{"@id":"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/82a299a8284a66ff49f97c74684724a0"},"breadcrumb":{"@id":"https:\/\/virtualserversvps.com\/blog\/test-new-article\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/virtualserversvps.com\/blog\/test-new-article\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/virtualserversvps.com\/blog\/test-new-article\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/virtualserversvps.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to Set Up MySQL Database Replication on a VPS"}]},{"@type":"WebSite","@id":"https:\/\/virtualserversvps.com\/blog\/#website","url":"https:\/\/virtualserversvps.com\/blog\/","name":"Virtual Servers VPS Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/virtualserversvps.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/82a299a8284a66ff49f97c74684724a0","name":"Virtual-Servers-Vps-Editor","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/d820b15f1cd028e97610d9adf536df7be5cb6423869967037d468d5355fa003f?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d820b15f1cd028e97610d9adf536df7be5cb6423869967037d468d5355fa003f?s=96&d=mm&r=g","caption":"Virtual-Servers-Vps-Editor"},"sameAs":["https:\/\/virtualserversvps.com\/blog"],"url":"https:\/\/virtualserversvps.com\/blog\/author\/virtualserversvps\/"}]}},"_links":{"self":[{"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/posts\/568","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/comments?post=568"}],"version-history":[{"count":3,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/posts\/568\/revisions"}],"predecessor-version":[{"id":709,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/posts\/568\/revisions\/709"}],"wp:attachment":[{"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/media?parent=568"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/categories?post=568"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/tags?post=568"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}