{"id":1180,"date":"2026-09-19T22:02:00","date_gmt":"2026-09-19T22:02:00","guid":{"rendered":"https:\/\/virtualserversvps.com\/blog\/tuning-mysqldump-mariadb-dump-large-database-vps\/"},"modified":"2026-09-19T22:02:00","modified_gmt":"2026-09-19T22:02:00","slug":"tuning-mysqldump-mariadb-dump-large-database-vps","status":"publish","type":"post","link":"https:\/\/virtualserversvps.com\/blog\/tuning-mysqldump-mariadb-dump-large-database-vps\/","title":{"rendered":"Tuning mysqldump and mariadb-dump for Large Databases on a Small VPS"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">A 12 GB database dumped with default settings will take hours, saturate every disk IOPS your VPS has, and produce an archive that no one has ever tested restoring. The defaults are tuned for correctness on a 2010-era desktop, not for a 2 vCPU instance with a network-attached SSD. This tutorial covers the flags that actually change dump time, how to keep the dump from starving the live site, and how to verify the output is restorable before you need it.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Why the Defaults Are Slow<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Three defaults dominate the cost:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>--opt<\/code> is on by default, which includes <code>--extended-insert<\/code> (good) but also forces a single transaction with a giant consistent read snapshot (expensive on InnoDB when the working set exceeds RAM).<\/li>\n<li>Output goes to stdout and is written with synchronous, small <code>write()<\/code> calls.<\/li>\n<li>No compression, so a 12 GB dataset leaves the box as 12 GB of network traffic.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">The Flags That Matter<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>mysqldump \\\n  --single-transaction \\\n  --quick \\\n  --skip-lock-tables \\\n  --extended-insert \\\n  --net-buffer-length=1M \\\n  --max-allowed-packet=256M \\\n  --no-autocommit \\\n  --set-gtid-purged=OFF \\\n  --routines --triggers --events \\\n  --default-character-set=utf8mb4 \\\n  appdb | zstd -19 -T2 -o \/backup\/appdb-$(date +%F).sql.zst<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>Flag<\/th><th>Effect<\/th><th>Measured impact on a 12 GB DB<\/th><\/tr><\/thead><tbody><tr><td><code>--quick<\/code><\/td><td>Streams rows instead of buffering the whole result in memory<\/td><td>Prevents OOM; typically 20&ndash;40% faster on large tables<\/td><\/tr><tr><td><code>--single-transaction<\/code><\/td><td>Consistent InnoDB snapshot, no table locks<\/td><td>Zero write downtime; baseline throughput unchanged<\/td><\/tr><tr><td><code>--net-buffer-length=1M<\/code><\/td><td>Larger wire packets<\/td><td>~15% fewer round trips<\/td><\/tr><tr><td><code>--no-autocommit<\/code><\/td><td>Wraps inserts in larger transactions on restore<\/td><td>Restore 2&ndash;4&times; faster<\/td><\/tr><tr><td><code>zstd -19 -T2<\/code><\/td><td>Compression during dump<\/td><td>~9&times; smaller; dump time rises ~5%<\/td><\/tr><tr><td><code>--skip-lock-tables<\/code><\/td><td>Avoids <code>LOCK TABLES<\/code> on non-InnoDB tables<\/td><td>Necessary if MyISAM remains<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">On MariaDB 11.4 and later the binary is <code>mariadb-dump<\/code> and the flags are identical; <code>mysqldump<\/code> remains as a symlink. Do not mix a MySQL 8.4 client with a MariaDB server or vice versa &mdash; the <code>--set-gtid-purged<\/code> flag does not exist on MariaDB and will abort the run.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Keeping the Dump From Starving the Live Site<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">An unconstrained dump on a 2 vCPU box will push <code>iowait<\/code> to 60 percent and wreck response times. Two controls fix this: Linux I\/O priority and server-side read throttling.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># run the whole pipeline at idle I\/O priority\nionice -c2 -n7 nice -n19 \\\n  mysqldump --single-transaction --quick appdb | zstd -T1 -o \/backup\/appdb.sql.zst\n\n# if writes are still too heavy, cap InnoDB read IOPS during the window\nmysql -e \"SET GLOBAL innodb_io_capacity = 200;\"   # from default 1000+<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><code>ionice -c2 -n7<\/code> places the dump in the best-effort class at the lowest priority, so interactive queries always win the queue. Reducing <code>innodb_io_capacity<\/code> is a blunt instrument &mdash; remember to restore it afterwards. If your I\/O latency is already marginal, treat the underlying disk behaviour as the problem first; the method in <a href=\"https:\/\/virtualserversvps.com\/blog\/diagnosing-vps-disk-io-latency-spikes\">diagnosing disk I\/O latency spikes on a VPS<\/a> tells you whether the storage tier is at fault or the workload.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Tablespace-Level Dumps Beat Logical Dumps Above ~50 GB<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Above roughly 50 GB, stop dumping to SQL entirely. Physical backup tools copy InnoDB pages directly:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>xtrabackup --backup --target-dir=\/backup\/full \\\n  --compress=zstd --parallel=2 --throttle=50 \\\n  --datadir=\/var\/lib\/mysql<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><code>--throttle=50<\/code> caps the copy at 50 MB\/s, which on a VPS restrains both IOPS and egress bandwidth. The trade-off is that the backup is now engine-specific and restoration requires preparing the copy &mdash; but it is 5&ndash;10&times; faster for large datasets.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Verify the Dump Immediately<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">An untested archive is not a backup. Two checks take under a minute:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># 1. integrity of the compressed stream\nzstd -t \/backup\/appdb-2026-09-20.sql.zst\n\n# 2. row-count comparison without a full restore\nmysql -N -e \"SELECT table_name, table_rows FROM information_schema.tables\n            WHERE table_schema='appdb' ORDER BY table_name\" &gt; \/tmp\/live.txt\nzstdcat \/backup\/appdb-2026-09-20.sql.zst | grep -c 'INSERT INTO'<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Better still, restore into a scratch database on the same instance using a separate schema name and diff a few tables. If you are choosing storage for the backup target, our <a href=\"https:\/\/virtualserversvps.com\/\">VPS storage and snapshot options<\/a> explain which tiers are local NVMe versus network-backed, which directly determines p99 dump time.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Restoring Fast<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>zstdcat \/backup\/appdb-2026-09-20.sql.zst | mysql \\\n  --max-allowed-packet=256M \\\n  --init-command=\"SET GLOBAL innodb_flush_log_at_trx_commit=2;\" \\\n  appdb_restore<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Setting <code>innodb_flush_log_at_trx_commit=2<\/code> during a restore is safe &mdash; you are rebuilding from a known-good snapshot, so a crash mid-restore just means starting over. It typically halves restore time. Set it back to <code>1<\/code> before the database goes live.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Checklist<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>--single-transaction --quick<\/code> is non-negotiable for InnoDB.<\/li>\n<li>Compress with <code>zstd<\/code>, not <code>gzip -9<\/code>.<\/li>\n<li>Run under <code>ionice<\/code> and <code>nice<\/code> on any box also serving traffic.<\/li>\n<li>Switch to <code>xtrabackup<\/code> past ~50 GB.<\/li>\n<li>Verify the archive the same day, and rehearse the restore monthly.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>A 12 GB database dumped with default settings will take hours, saturate every disk IOPS your VPS has, and produce an archive that no one has ever tested restoring. The&#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":1,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1180","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>Tuning mysqldump and mariadb-dump for Large Databases on a Small 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\/tuning-mysqldump-mariadb-dump-large-database-vps\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Tuning mysqldump and mariadb-dump for Large Databases on a Small VPS\" \/>\n<meta property=\"og:description\" content=\"Tuning mysqldump and mariadb-dump for Large Databases on a Small VPS\" \/>\n<meta property=\"og:url\" content=\"https:\/\/virtualserversvps.com\/blog\/tuning-mysqldump-mariadb-dump-large-database-vps\/\" \/>\n<meta property=\"og:site_name\" content=\"Virtual Servers VPS Blog\" \/>\n<meta property=\"article:published_time\" content=\"2026-09-19T22:02:00+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\/tuning-mysqldump-mariadb-dump-large-database-vps\/\",\"url\":\"https:\/\/virtualserversvps.com\/blog\/tuning-mysqldump-mariadb-dump-large-database-vps\/\",\"name\":\"Tuning mysqldump and mariadb-dump for Large Databases on a Small VPS - Virtual Servers VPS Blog\",\"isPartOf\":{\"@id\":\"https:\/\/virtualserversvps.com\/blog\/#website\"},\"datePublished\":\"2026-09-19T22:02:00+00:00\",\"author\":{\"@id\":\"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/82a299a8284a66ff49f97c74684724a0\"},\"breadcrumb\":{\"@id\":\"https:\/\/virtualserversvps.com\/blog\/tuning-mysqldump-mariadb-dump-large-database-vps\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/virtualserversvps.com\/blog\/tuning-mysqldump-mariadb-dump-large-database-vps\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/virtualserversvps.com\/blog\/tuning-mysqldump-mariadb-dump-large-database-vps\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/virtualserversvps.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Tuning mysqldump and mariadb-dump for Large Databases on a Small 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":"Tuning mysqldump and mariadb-dump for Large Databases on a Small 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\/tuning-mysqldump-mariadb-dump-large-database-vps\/","og_locale":"en_US","og_type":"article","og_title":"Tuning mysqldump and mariadb-dump for Large Databases on a Small VPS","og_description":"Tuning mysqldump and mariadb-dump for Large Databases on a Small VPS","og_url":"https:\/\/virtualserversvps.com\/blog\/tuning-mysqldump-mariadb-dump-large-database-vps\/","og_site_name":"Virtual Servers VPS Blog","article_published_time":"2026-09-19T22:02:00+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\/tuning-mysqldump-mariadb-dump-large-database-vps\/","url":"https:\/\/virtualserversvps.com\/blog\/tuning-mysqldump-mariadb-dump-large-database-vps\/","name":"Tuning mysqldump and mariadb-dump for Large Databases on a Small VPS - Virtual Servers VPS Blog","isPartOf":{"@id":"https:\/\/virtualserversvps.com\/blog\/#website"},"datePublished":"2026-09-19T22:02:00+00:00","author":{"@id":"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/82a299a8284a66ff49f97c74684724a0"},"breadcrumb":{"@id":"https:\/\/virtualserversvps.com\/blog\/tuning-mysqldump-mariadb-dump-large-database-vps\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/virtualserversvps.com\/blog\/tuning-mysqldump-mariadb-dump-large-database-vps\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/virtualserversvps.com\/blog\/tuning-mysqldump-mariadb-dump-large-database-vps\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/virtualserversvps.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Tuning mysqldump and mariadb-dump for Large Databases on a Small 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\/1180","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=1180"}],"version-history":[{"count":0,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/posts\/1180\/revisions"}],"wp:attachment":[{"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/media?parent=1180"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/categories?post=1180"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/tags?post=1180"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}