{"id":1950,"date":"2025-11-16T22:33:29","date_gmt":"2025-11-16T19:33:29","guid":{"rendered":"https:\/\/www.dchost.com\/blog\/the-little-freeze-that-saves-your-data-application%e2%80%91consistent-hot-backups-with-lvm-snapshots-for-mysql-and-postgresql\/"},"modified":"2025-11-16T22:33:29","modified_gmt":"2025-11-16T19:33:29","slug":"the-little-freeze-that-saves-your-data-application%e2%80%91consistent-hot-backups-with-lvm-snapshots-for-mysql-and-postgresql","status":"publish","type":"post","link":"https:\/\/www.dchost.com\/blog\/en\/the-little-freeze-that-saves-your-data-application%e2%80%91consistent-hot-backups-with-lvm-snapshots-for-mysql-and-postgresql\/","title":{"rendered":"The Little Freeze That Saves Your Data: Application\u2011Consistent Hot Backups with LVM Snapshots for MySQL and PostgreSQL"},"content":{"rendered":"<div class=\"dchost-blog-content-wrapper\"><p>So there I was, late on a Wednesday night, staring at a server that absolutely refused to bring MySQL back up after a restore. The backup had run. The files looked fine. But the database? Grumpy. The logs were a soup of partial writes and half\u2011finished transactions. Ever had that feeling where your heart sinks because you realize the backup did its job, but you didn\u2019t quite do yours? That was me, nursing a coffee and thinking about consistency.<\/p>\n<p>Here\u2019s the thing: backups aren\u2019t just about copying files. They\u2019re about copying the <strong>right state<\/strong> of those files. And for databases like MySQL and PostgreSQL, that means application\u2011consistent backups\u2014hot backups that won\u2019t make your database cry when you restore them. In this guide, I\u2019ll walk you through the practical, low\u2011drama way to do that with LVM snapshots and <code>fsfreeze<\/code>, plus a sprinkle of database magic. We\u2019ll cover what \u201capplication\u2011consistent\u201d really means, why pausing the filesystem for a hot second works so well, and how to wire it all together with scripts you can actually run in production.<\/p>\n<div id=\"toc_container\" class=\"toc_transparent no_bullets\"><p class=\"toc_title\">\u0130&ccedil;indekiler<\/p><ul class=\"toc_list\"><li><a href=\"#Why_ApplicationConsistent_Beats_CopyWhateversThere\"><span class=\"toc_number toc_depth_1\">1<\/span> Why Application\u2011Consistent Beats \u201cCopy\u2011Whatever\u2019s\u2011There\u201d<\/a><\/li><li><a href=\"#The_Core_Idea_Quiesce_Snap_Unquiesce\"><span class=\"toc_number toc_depth_1\">2<\/span> The Core Idea: Quiesce, Snap, Unquiesce<\/a><\/li><li><a href=\"#What_ApplicationConsistent_Looks_Like_for_MySQL\"><span class=\"toc_number toc_depth_1\">3<\/span> What \u201cApplication\u2011Consistent\u201d Looks Like for MySQL<\/a><ul><li><a href=\"#MySQL_A_Minimal_Script_You_Can_Adapt\"><span class=\"toc_number toc_depth_2\">3.1<\/span> MySQL: A Minimal Script You Can Adapt<\/a><\/li><\/ul><\/li><li><a href=\"#What_ApplicationConsistent_Looks_Like_for_PostgreSQL\"><span class=\"toc_number toc_depth_1\">4<\/span> What \u201cApplication\u2011Consistent\u201d Looks Like for PostgreSQL<\/a><ul><li><a href=\"#PostgreSQL_A_Minimal_Script_You_Can_Adapt\"><span class=\"toc_number toc_depth_2\">4.1<\/span> PostgreSQL: A Minimal Script You Can Adapt<\/a><\/li><\/ul><\/li><li><a href=\"#LVM_Snapshots_and_fsfreeze_The_Nuts_and_Bolts\"><span class=\"toc_number toc_depth_1\">5<\/span> LVM Snapshots and fsfreeze: The Nuts and Bolts<\/a><\/li><li><a href=\"#A_Full_Recipe_From_Snapshot_to_Offsite_Backup\"><span class=\"toc_number toc_depth_1\">6<\/span> A Full Recipe: From Snapshot to Off\u2011site Backup<\/a><ul><li><a href=\"#Shipping_the_Snapshot_Copy\"><span class=\"toc_number toc_depth_2\">6.1<\/span> Shipping the Snapshot Copy<\/a><\/li><li><a href=\"#Retention_and_Rotation\"><span class=\"toc_number toc_depth_2\">6.2<\/span> Retention and Rotation<\/a><\/li><\/ul><\/li><li><a href=\"#Testing_Restores_The_Habit_That_Pays_Dividends\"><span class=\"toc_number toc_depth_1\">7<\/span> Testing Restores (The Habit That Pays Dividends)<\/a><\/li><li><a href=\"#Common_Pitfalls_And_How_to_Dodge_Them\"><span class=\"toc_number toc_depth_1\">8<\/span> Common Pitfalls (And How to Dodge Them)<\/a><\/li><li><a href=\"#Security_and_Shipping_Dont_Forget_the_Boring_Stuff\"><span class=\"toc_number toc_depth_1\">9<\/span> Security and Shipping: Don\u2019t Forget the Boring Stuff<\/a><\/li><li><a href=\"#Doing_Backups_on_Replicas_The_Unsung_Hero_Move\"><span class=\"toc_number toc_depth_1\">10<\/span> Doing Backups on Replicas (The Unsung Hero Move)<\/a><\/li><li><a href=\"#Alternative_ZFS_Snapshots_If_Youre_Already_There\"><span class=\"toc_number toc_depth_1\">11<\/span> Alternative: ZFS Snapshots (If You\u2019re Already There)<\/a><\/li><li><a href=\"#Putting_It_All_Together_A_Friendly_EndtoEnd_Bash_Script\"><span class=\"toc_number toc_depth_1\">12<\/span> Putting It All Together: A Friendly, End\u2011to\u2011End Bash Script<\/a><\/li><li><a href=\"#Performance_and_Snapshot_Sizing\"><span class=\"toc_number toc_depth_1\">13<\/span> Performance and Snapshot Sizing<\/a><\/li><li><a href=\"#When_to_Use_This_vs_Logical_Backups\"><span class=\"toc_number toc_depth_1\">14<\/span> When to Use This vs. Logical Backups<\/a><\/li><li><a href=\"#Bonus_Shipping_to_S3MinIO_the_Clean_Way\"><span class=\"toc_number toc_depth_1\">15<\/span> Bonus: Shipping to S3\/MinIO the Clean Way<\/a><\/li><li><a href=\"#Troubleshooting_Checklist\"><span class=\"toc_number toc_depth_1\">16<\/span> Troubleshooting Checklist<\/a><\/li><li><a href=\"#WrapUp_Give_Your_Future_Self_the_Gift_of_Calm_Restores\"><span class=\"toc_number toc_depth_1\">17<\/span> Wrap\u2011Up: Give Your Future Self the Gift of Calm Restores<\/a><\/li><\/ul><\/div>\n<h2 id=\"section-1\"><span id=\"Why_ApplicationConsistent_Beats_CopyWhateversThere\">Why Application\u2011Consistent Beats \u201cCopy\u2011Whatever\u2019s\u2011There\u201d<\/span><\/h2>\n<p>Think of your database like a cashier closing a register. If you yank the drawer while they\u2019re making change, you\u2019ll have coins all over the floor. That\u2019s a crash\u2011consistent backup: the files are copied, but the state is mid\u2011transaction. You might recover, or you might spend your morning sweeping up. An <strong>application\u2011consistent backup<\/strong>, on the other hand, is like saying, \u201cHey, pause for two seconds, finish what you\u2019re doing, and then keep going.\u201d When you restore, everything lines up: WAL\/redo logs, data files, metadata\u2014clean and calm.<\/p>\n<p>I\u2019ve seen both sides: backups that technically worked but restored into hours of crash recovery, and backups that restored like a warm knife into butter because we took a moment to coordinate with the database. That tiny moment is the whole trick. And the nicest part? You don\u2019t need fancy enterprise software to get there. Linux gives you LVM snapshots and <code>fsfreeze<\/code>; MySQL and PostgreSQL give you a couple of commands to quiesce safely. Combine them, and you\u2019ve got hot backups that act like they were taken during a maintenance window\u2014without the downtime.<\/p>\n<h2 id=\"section-2\"><span id=\"The_Core_Idea_Quiesce_Snap_Unquiesce\">The Core Idea: Quiesce, Snap, Unquiesce<\/span><\/h2>\n<p>Here\u2019s the flow, at a human level: we ask the database to get into a backup\u2011friendly state (just for a moment), we freeze the filesystem so it stops changing, we take an LVM snapshot (a copy\u2011on\u2011write view of the volume), and then we immediately unfreeze and let the database continue. The lock is measured in seconds, not minutes. The snapshot becomes our source for a backup copy that we can mount somewhere else and copy off, without touching the live database.<\/p>\n<p>If you\u2019ve never used LVM snapshots, think of them like a time machine bookmark. The snapshot itself is tiny at first; it only grows by storing blocks that change after the snapshot. <code>fsfreeze<\/code> is the pause button\u2014Linux tells the filesystem to flush and stop accepting new writes until we unfreeze. The result is a tidy point\u2011in\u2011time view of your database <strong>as the database intended it<\/strong>.<\/p>\n<p>Quick caveat: if you\u2019re on ZFS, you don\u2019t need LVM for this. ZFS has its own snapshot magic and even send\/receive for shipping backups. If that\u2019s you, you might enjoy my piece on snapshots and replication in <a href=\"https:\/\/www.dchost.com\/blog\/en\/ofiste-bir-aksam-disk-isiginin-ritmi-ve-zfs-ile-barisma\/\">ZFS on Linux for Servers: the calm, no\u2011drama guide<\/a>. The philosophy is the same\u2014clean, atomic snapshots\u2014but the commands are different.<\/p>\n<h2 id=\"section-3\"><span id=\"What_ApplicationConsistent_Looks_Like_for_MySQL\">What \u201cApplication\u2011Consistent\u201d Looks Like for MySQL<\/span><\/h2>\n<p>Let\u2019s talk MySQL first. Most production setups use InnoDB, which is transactional and pretty resilient, but we still want to help it line up the data and log files cleanly at the moment of the snapshot. The classic approach is:<\/p>\n<p>One, request a global read lock with <strong>FLUSH TABLES WITH READ LOCK<\/strong> to briefly halt writes. Two, flush logs so we have a clean reference point. Three, freeze the filesystem and create the LVM snapshot. Four, unfreeze and unlock. The locking window is tiny\u2014usually a couple of seconds if the snapshot size is reasonable and the system isn\u2019t overloaded.<\/p>\n<p>Yes, you can sometimes \u201cget away with\u201d only freezing the filesystem if InnoDB is running with safe flush settings, but I\u2019ve learned the hard way that the extra two seconds of read lock are worth it for restores that just work. If you\u2019re running replication, you can do this on a replica to keep production writes uninterrupted\u2014more on that later.<\/p>\n<h3><span id=\"MySQL_A_Minimal_Script_You_Can_Adapt\">MySQL: A Minimal Script You Can Adapt<\/span><\/h3>\n<p>Assumptions: your MySQL data dir is <code>\/var\/lib\/mysql<\/code>, the logical volume is <code>vg0\/mysql<\/code>, and you\u2019re snapshotting 20G of COW space. Adjust to your environment.<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\"># Variables\nMYSQL_SOCK=\/var\/run\/mysqld\/mysqld.sock\nMYSQL_CLI=&quot;mysql --protocol=socket --socket=$MYSQL_SOCK -uroot&quot;\nDATA_MNT=\/var\/lib\/mysql\nVG=vg0\nLV=mysql\nSNAP_NAME=mysql_snap_$(date +%Y%m%d%H%M%S)\nSNAP_SIZE=20G\nSNAP_MNT=\/mnt\/mysql-snap\n\nset -euo pipefail\n\n# 1) Ask MySQL to enter a backup-friendly state (brief, safe, coordinated)\n$MYSQL_CLI &lt;&lt;'SQL'\nFLUSH TABLES WITH READ LOCK;\nFLUSH LOGS;\n-- Keep the client open for this session while we snapshot.\n-- We'll UNLOCK after snapshot completes using the same session if possible.\nSQL\n\n# This opens a second mysql client to keep the lock alive while we run commands.\n# We use a FIFO trick to control when the session ends.\nLOCK_FIFO=$(mktemp -u)\nmkfifo &quot;$LOCK_FIFO&quot;\n( $MYSQL_CLI &lt;&lt;SQL\nSYSTEM echo &quot;Lock in place...&quot;;\nFLUSH TABLES WITH READ LOCK;\nFLUSH LOGS;\nSYSTEM echo &quot;Freezing FS...&quot;;\nSYSTEM fsfreeze -f $DATA_MNT;\nSYSTEM lvcreate -L $SNAP_SIZE -s -n $SNAP_NAME $VG\/$LV;\nSYSTEM fsfreeze -u $DATA_MNT;\nUNLOCK TABLES;\nSQL\n) &lt; &quot;$LOCK_FIFO&quot; &amp;\nLOCK_PID=$!\n\n# Feed and close FIFO (so the subshell runs). This pattern ensures ordering.\necho done &gt; &quot;$LOCK_FIFO&quot;\nrm -f &quot;$LOCK_FIFO&quot;\nwait $LOCK_PID\n\n# 2) Mount snapshot read-only elsewhere for copying\nmkdir -p &quot;$SNAP_MNT&quot;\nmount -o ro \/dev\/$VG\/$SNAP_NAME &quot;$SNAP_MNT&quot;\n\necho &quot;Snapshot mounted at $SNAP_MNT&quot;\n# From here, rsync or tar to your backup store.\n# Example: rsync -aH --numeric-ids &quot;$SNAP_MNT&quot;\/ \/backup\/mysql\/$(date +%F)\/\n# After copying:\n# umount &quot;$SNAP_MNT&quot; &amp;&amp; lvremove -y \/dev\/$VG\/$SNAP_NAME\n<\/code><\/pre>\n<p>There are a few ways to structure the lock so it persists just long enough. The pattern above uses a single client session to lock, run filesystem operations via the client\u2019s <code>SYSTEM<\/code> command, then unlock. If your environment restricts the <code>SYSTEM<\/code> command in the client, you can do a similar orchestration by capturing a connection ID with <code>SELECT CONNECTION_ID()<\/code> and later <code>KILL<\/code>ing it (which releases the lock), but that\u2019s a little more advanced. The core idea is the same: lock, freeze, snapshot, unfreeze, unlock.<\/p>\n<p>When restoring, InnoDB will see a clean state, with redo logs aligned to the data files. If you use binary logs for replication, your <strong>FLUSH LOGS<\/strong> step also gives you a neat cutover point.<\/p>\n<h2 id=\"section-4\"><span id=\"What_ApplicationConsistent_Looks_Like_for_PostgreSQL\">What \u201cApplication\u2011Consistent\u201d Looks Like for PostgreSQL<\/span><\/h2>\n<p>PostgreSQL has a friendly concept called \u201cbackup mode.\u201d We tell Postgres we\u2019re starting a base backup, it writes a label and coordinates WAL, and then we stop the backup after the snapshot. On modern versions (15+), you\u2019ll see <code>pg_backup_start()<\/code> and <code>pg_backup_stop()<\/code>; on older setups, it\u2019s <code>pg_start_backup()<\/code> and <code>pg_stop_backup()<\/code>. Both get the job done. The other key piece is WAL archiving\u2014ensure <code>archive_mode<\/code> is enabled so the WAL segments that cover your snapshot are preserved until the stop command completes.<\/p>\n<p>What I love about this flow is how predictable it is. Postgres is very explicit: \u201cOkay, I know you\u2019re taking a backup; here\u2019s what I\u2019ll make sure to keep around.\u201d When you restore, you have everything you need for a consistent startup, and if you want, you can even replay to a specific point in time.<\/p>\n<h3><span id=\"PostgreSQL_A_Minimal_Script_You_Can_Adapt\">PostgreSQL: A Minimal Script You Can Adapt<\/span><\/h3>\n<p>Assumptions: data dir on <code>\/var\/lib\/postgresql\/14\/main<\/code> (adjust for your version), logical volume <code>vg0\/pgdata<\/code>, snapshot size 20G.<\/p>\n<pre class=\"language-sql line-numbers\"><code class=\"language-sql\"># Variables\nPSQL=&quot;psql -U postgres -h \/var\/run\/postgresql&quot;\nDATA_MNT=\/var\/lib\/postgresql\/14\/main\nVG=vg0\nLV=pgdata\nSNAP_NAME=pg_snap_$(date +%Y%m%d%H%M%S)\nSNAP_SIZE=20G\nSNAP_MNT=\/mnt\/pg-snap\n\nset -euo pipefail\n\n# 1) Tell Postgres to begin a backup (non-exclusive, fast)\n$PSQL -c &quot;SELECT CASE WHEN current_setting('server_version_num')::int &gt;= 150000\n  THEN pg_backup_start('lvm', true)\n  ELSE pg_start_backup('lvm', true)\nEND;&quot;\n\n# 2) Freeze FS, snapshot, unfreeze\nfsfreeze -f &quot;$DATA_MNT&quot;\nlvcreate -L &quot;$SNAP_SIZE&quot; -s -n &quot;$SNAP_NAME&quot; &quot;$VG\/$LV&quot;\nfsfreeze -u &quot;$DATA_MNT&quot;\n\n# 3) Stop backup\n$PSQL -c &quot;SELECT CASE WHEN current_setting('server_version_num')::int &gt;= 150000\n  THEN pg_backup_stop()\n  ELSE pg_stop_backup()\nEND;&quot;\n\n# 4) Mount snapshot read-only to copy\nmkdir -p &quot;$SNAP_MNT&quot;\nmount -o ro \/dev\/$VG\/$SNAP_NAME &quot;$SNAP_MNT&quot;\n\necho &quot;Snapshot mounted at $SNAP_MNT&quot;\n# Now copy to backup store, e.g. rsync\/tar as needed\n<\/code><\/pre>\n<p>If you haven\u2019t set up archiving, do that first. It\u2019s a simple setting that makes your entire backup story more reliable. The official <a href=\"https:\/\/www.postgresql.org\/docs\/current\/continuous-archiving.html\" rel=\"nofollow noopener\" target=\"_blank\">PostgreSQL documentation on continuous archiving and backups<\/a> is excellent for a quick sanity check on your configuration.<\/p>\n<h2 id=\"section-5\"><span id=\"LVM_Snapshots_and_fsfreeze_The_Nuts_and_Bolts\">LVM Snapshots and fsfreeze: The Nuts and Bolts<\/span><\/h2>\n<p>Let\u2019s demystify what\u2019s happening on the Linux side. When you run <code>fsfreeze<\/code>, the filesystem flushes all in\u2011flight writes to disk and then pauses new writes. It\u2019s not killing your app; it\u2019s just telling the kernel to hold writes for a brief moment. That gives you a stable on\u2011disk image to snapshot. Then, <code>lvcreate -s<\/code> creates a snapshot logical volume that references the original volume. New writes on the origin volume are stashed in the COW area, so the snapshot keeps seeing the old blocks. The snapshot is read\u2011only by design in our use case\u2014perfect for a clean backup source.<\/p>\n<p>On ext4 and XFS, <code>fsfreeze<\/code> is well supported. If you\u2019re curious, the <a href=\"https:\/\/man7.org\/linux\/man-pages\/man8\/fsfreeze.8.html\" rel=\"nofollow noopener\" target=\"_blank\">fsfreeze manual page<\/a> has a short, readable description of what it actually does. For LVM specifics like how snapshot size interacts with write rates (and why too small is a bad idea), the <a href=\"https:\/\/access.redhat.com\/documentation\/en-us\/red_hat_enterprise_linux\/7\/html\/logical_volume_manager_administration\/lvmsnapshots\" rel=\"nofollow noopener\" target=\"_blank\">Red Hat LVM snapshots guide<\/a> is a solid reference.<\/p>\n<p>One gotcha I see a lot: make sure your database\u2019s entire data directory (and logs if you\u2019re keeping them on disk nearby) live on the same logical volume that you snapshot. If pieces are scattered across volumes, your snapshot won\u2019t be consistent. My rule of thumb is to keep database storage simple and deliberate\u2014one volume for the database data, optionally a separate one for WAL\/binlogs, but then snapshot them together in a coordinated way or co\u2011locate them if that\u2019s simpler for your setup.<\/p>\n<h2 id=\"section-6\"><span id=\"A_Full_Recipe_From_Snapshot_to_Offsite_Backup\">A Full Recipe: From Snapshot to Off\u2011site Backup<\/span><\/h2>\n<p>Let\u2019s walk through the full dance in plain English, the way I do it on actual servers.<\/p>\n<p>First, prep your environment. Confirm that your database data directory is on LVM. <code>lsblk -f<\/code> and <code>df -h<\/code> are your friends. Check that <code>fsfreeze<\/code> is available (it usually is on modern distros), and test mounting a dummy snapshot on a non\u2011critical volume if you\u2019ve never done it before. For PostgreSQL, sanity\u2011check archiving. For MySQL, ensure your storage engine is InnoDB for the tables that matter.<\/p>\n<p>Second, coordinate with the database. For MySQL, that means a brief lock + log flush; for PostgreSQL, backup start. Third, freeze the filesystem and create the snapshot. Fourth, unfreeze and release locks immediately. Fifth, mount the snapshot read\u2011only under <code>\/mnt\/whatever<\/code>, and perform your file copy to a backup location.<\/p>\n<p>Where should the backup go? I love shipping to object storage. If you run your own, MinIO is a wonderful S3\u2011compatible option, and it runs beautifully even on a modest <a href=\"https:\/\/www.dchost.com\/vps\">VPS<\/a>. If that sparks ideas, I\u2019ve got a step\u2011by\u2011step on TLS and erasure coding in <a href=\"https:\/\/www.dchost.com\/blog\/en\/vps-uzerinde-minio-ile-s3%e2%80%91uyumlu-depolama-nasil-uretim%e2%80%91hazir-kurulur-erasure-coding-tls-ve-policyleri-tatli-tatli-anlatiyorum\/\">Production\u2011Ready MinIO on a VPS<\/a>. The point is: get the snapshot copy off the box. Local is fine for fast restores; off\u2011site is insurance.<\/p>\n<h3><span id=\"Shipping_the_Snapshot_Copy\">Shipping the Snapshot Copy<\/span><\/h3>\n<p>Once the snapshot is mounted read\u2011only, you can use your favorite tooling: <code>rsync<\/code>, <code>tar<\/code> to a compressed archive, or even a streaming upload directly to S3 with <code>aws s3 cp -<\/code>. I usually prefer <code>rsync -aH --numeric-ids<\/code> to keep permissions intact, then a separate process to upload or sync to object storage. Some folks like to create a file\u2011level archive (<code>tar.gz<\/code>) of the snapshot, then upload the single file; others push directories as\u2011is. Both are fine\u2014do whichever makes restores feel easy for you.<\/p>\n<h3><span id=\"Retention_and_Rotation\">Retention and Rotation<\/span><\/h3>\n<p>Here\u2019s a nice trick: keep yesterday\u2019s backup mounted somewhere safe for quick diffs, but make sure to clean up old snapshots (<code>lvremove<\/code>) to avoid slowly filling the COW space. For object storage, write a lightweight retention policy: keep dailies for a week, weeklies for a month, monthlies for a year. Simplicity scales.<\/p>\n<h2 id=\"section-7\"><span id=\"Testing_Restores_The_Habit_That_Pays_Dividends\">Testing Restores (The Habit That Pays Dividends)<\/span><\/h2>\n<p>If there\u2019s one drum I beat over and over: practice restores like you practice fire drills. Every so often, spin up a test VM, fetch last night\u2019s backup, and actually start the database. You\u2019ll catch things in minutes that would take hours to diagnose in a crisis. For MySQL, point <code>mysqld<\/code> at a throwaway data directory restored from the backup and start it on a random port with networking disabled. For PostgreSQL, initialize a test cluster, replace data files with the backup, ensure WAL is present, and bring it up to see it recover.<\/p>\n<p>A fun real\u2011world trick I picked up: restore to a new server and run your application\u2019s smoke tests against it. If that sounds like overkill, it probably is\u2014until you need it. You don\u2019t have to do this every day, but a quarterly check can save you from surprises. If your stack leans heavily on multi\u2011region or replicas, I\u2019ve written about pragmatic approaches to staying online during ugly days in <a href=\"https:\/\/www.dchost.com\/blog\/en\/cok-bolgeli-mimariler-nasil-kurulur-dns-geo%e2%80%91routing-ve-veritabani-replikasyonu-ile-korkusuz-felaket-dayanikliligi\/\">a friendly guide to multi\u2011region architectures with DNS geo\u2011routing<\/a>.<\/p>\n<h2 id=\"section-8\"><span id=\"Common_Pitfalls_And_How_to_Dodge_Them\">Common Pitfalls (And How to Dodge Them)<\/span><\/h2>\n<p>I\u2019ve made enough mistakes here to fill a small notebook. Here are the ones I look out for, so you don\u2019t have to learn the hard way.<\/p>\n<p>One, snapshot too small. If your snapshot COW space is undersized and the system writes heavily while you\u2019re copying, the snapshot can become invalid. Err on the generous side for snapshot size, especially during busy hours. Two, scattered files. If key files are on different volumes, the snapshot won\u2019t be consistent. Keep your database layout tidy or snapshot the relevant volumes in a coordinated sequence with the same freeze window.<\/p>\n<p>Three, missing WAL\/binlogs. PostgreSQL without archiving or MySQL without flushed logs is a recipe for trouble during restore. The whole point of application\u2011consistent backups is lining these up. Four, forgetting to unfreeze. Always verify <code>fsfreeze -u<\/code> succeeded\u2014if your monitoring starts yelling that disk writes are stuck, this is the reason. Five, restoring permissions. Make sure you preserve ownership and modes during backup and restore. <code>rsync -aH --numeric-ids<\/code> helps; same goes for extracting tar archives with <code>--same-owner<\/code>.<\/p>\n<p>And six, assuming containerization changes the rules. If your DB runs in a container but the storage is on the host\u2019s LVM, you still freeze and snapshot on the host. If your storage layer is something like Longhorn or a cloud volume with its own snapshot API, use that layer\u2019s snapshot mechanics instead of LVM. The principle remains: coordinate with the database, then snapshot the storage.<\/p>\n<h2 id=\"section-9\"><span id=\"Security_and_Shipping_Dont_Forget_the_Boring_Stuff\">Security and Shipping: Don\u2019t Forget the Boring Stuff<\/span><\/h2>\n<p>Backups contain your crown jewels. Protect them. Encrypt at rest if you\u2019re storing snapshots locally for any duration. When shipping to object storage, use TLS and bucket policies that keep the blast radius small. Least privilege isn\u2019t glamorous, but it\u2019s the reason you sleep well. If you need a gentle tour through hardening a machine that actually aligns with how people work day\u2011to\u2011day, my calm guide on <a href=\"https:\/\/www.dchost.com\/blog\/en\/vps-sunucu-guvenligi-nasil-saglanir-kapiyi-acik-birakmadan-yasamanin-sirri\/\">securing a VPS server for real people<\/a> is a nice companion read.<\/p>\n<p>For some shops, LUKS on the underlying block device makes sense, and snapshots inherit that encryption (since it\u2019s at the block level). Just make sure your recovery playbook includes how to unlock that encryption on a new host\u2014you don\u2019t want to be looking up luksOpen syntax at 3 a.m.<\/p>\n<h2 id=\"section-10\"><span id=\"Doing_Backups_on_Replicas_The_Unsung_Hero_Move\">Doing Backups on Replicas (The Unsung Hero Move)<\/span><\/h2>\n<p>If your workload is high\u2011traffic and you\u2019re worried about even a 2\u2011second read lock, take snapshots on a replica. For MySQL, a read\u2011only replica is ideal; apply the same FTWRL + snapshot dance there. For Postgres, a hot standby works well\u2014coordinate backup start\/stop via the primary if needed, or snapshot the standby with WAL shipping intact. This offloads the tiny backup pause from your primary and gives you more flexibility with snapshot timing.<\/p>\n<p>It pairs beautifully with a multi\u2011region mindset. Even if you\u2019re not ready for multi\u2011region failover today, practicing backups and restores on a replica in a different AZ or region makes your recovery muscle stronger. If you\u2019re curious about how I approach the bigger architecture conversation without losing my mind in the details, here\u2019s that <a href=\"https:\/\/www.dchost.com\/blog\/en\/cok-bolgeli-mimariler-nasil-kurulur-dns-geo%e2%80%91routing-ve-veritabani-replikasyonu-ile-korkusuz-felaket-dayanikliligi\/\">friendly multi\u2011region guide<\/a> again for weekend reading.<\/p>\n<h2 id=\"section-11\"><span id=\"Alternative_ZFS_Snapshots_If_Youre_Already_There\">Alternative: ZFS Snapshots (If You\u2019re Already There)<\/span><\/h2>\n<p>I mentioned this earlier, but it\u2019s worth repeating: if your database volumes live on ZFS, consider using native ZFS snapshots and send\/receive instead of LVM. ZFS snapshots are atomic and cheerful under pressure, and the tooling for replication is excellent. I\u2019ve written a full tour of how I use ARC, ZIL\/SLOG, snapshots, and send\/receive\u2014if you\u2019re planning or already running ZFS, bookmark the <a href=\"https:\/\/www.dchost.com\/blog\/en\/ofiste-bir-aksam-disk-isiginin-ritmi-ve-zfs-ile-barisma\/\">calm, no\u2011drama ZFS guide<\/a> for later.<\/p>\n<h2 id=\"section-12\"><span id=\"Putting_It_All_Together_A_Friendly_EndtoEnd_Bash_Script\">Putting It All Together: A Friendly, End\u2011to\u2011End Bash Script<\/span><\/h2>\n<p>Here\u2019s a more complete example that wraps both MySQL and PostgreSQL logic. It\u2019s not meant to be a one\u2011size\u2011fits\u2011all solution\u2014think of it as a starting point you can bend to your world. I\u2019ve commented it heavily so future\u2011you will thank past\u2011you.<\/p>\n<pre class=\"language-sql line-numbers\"><code class=\"language-sql\">#!\/usr\/bin\/env bash\n# app_consistent_lvm_backup.sh\n# Usage examples:\n#   MYSQL: DATA_MNT=\/var\/lib\/mysql VG=vg0 LV=mysql DB=mysql .\/app_consistent_lvm_backup.sh\n#   POSTGRES: DATA_MNT=\/var\/lib\/postgresql\/14\/main VG=vg0 LV=pgdata DB=postgres .\/app_consistent_lvm_backup.sh\n\nset -euo pipefail\n\nDB=${DB:-mysql}                 # mysql | postgres\nDATA_MNT=${DATA_MNT:?must set}\nVG=${VG:?must set}\nLV=${LV:?must set}\nSNAP_SIZE=${SNAP_SIZE:-20G}\nSNAP_NAME=${SNAP_NAME:-snap_$(date +%Y%m%d%H%M%S)}\nSNAP_DEV=\/dev\/$VG\/$SNAP_NAME\nSNAP_MNT=${SNAP_MNT:-\/mnt\/$SNAP_NAME}\n\ncleanup() {\n  # Child scripts can trap here if they want extra cleanup\n  true\n}\ntrap cleanup EXIT\n\nbegin_mysql() {\n  local mysql_cli=${MYSQL_CLI:-&quot;mysql -uroot --protocol=socket --socket=\/var\/run\/mysqld\/mysqld.sock&quot;}\n  $mysql_cli &lt;&lt;SQL\nFLUSH TABLES WITH READ LOCK;\nFLUSH LOGS;\nSQL\n}\n\nend_mysql() {\n  local mysql_cli=${MYSQL_CLI:-&quot;mysql -uroot --protocol=socket --socket=\/var\/run\/mysqld\/mysqld.sock&quot;}\n  $mysql_cli -e &quot;UNLOCK TABLES;&quot;\n}\n\nbegin_postgres() {\n  local psql=${PSQL:-&quot;psql -U postgres -h \/var\/run\/postgresql&quot;}\n  $psql -c &quot;SELECT CASE WHEN current_setting('server_version_num')::int &gt;= 150000 THEN pg_backup_start('lvm', true) ELSE pg_start_backup('lvm', true) END;&quot;\n}\n\nend_postgres() {\n  local psql=${PSQL:-&quot;psql -U postgres -h \/var\/run\/postgresql&quot;}\n  $psql -c &quot;SELECT CASE WHEN current_setting('server_version_num')::int &gt;= 150000 THEN pg_backup_stop() ELSE pg_stop_backup() END;&quot;\n}\n\nfreeze_and_snapshot() {\n  echo &quot;Freezing filesystem at $DATA_MNT&quot;\n  fsfreeze -f &quot;$DATA_MNT&quot;\n  lvcreate -L &quot;$SNAP_SIZE&quot; -s -n &quot;$SNAP_NAME&quot; &quot;$VG\/$LV&quot;\n  fsfreeze -u &quot;$DATA_MNT&quot;\n}\n\nmount_snapshot() {\n  mkdir -p &quot;$SNAP_MNT&quot;\n  mount -o ro &quot;$SNAP_DEV&quot; &quot;$SNAP_MNT&quot;\n  echo &quot;Snapshot mounted at $SNAP_MNT&quot;\n}\n\ncase &quot;$DB&quot; in\n  mysql)\n    begin_mysql\n    freeze_and_snapshot\n    end_mysql\n    ;;\n  postgres)\n    begin_postgres\n    freeze_and_snapshot\n    end_postgres\n    ;;\n  *)\n    echo &quot;Unknown DB: $DB&quot; &gt;&amp;2\n    exit 1\n    ;;\nesac\n\nmount_snapshot\n\necho &quot;Now copy from $SNAP_MNT to your backup destination (rsync\/tar\/s3).&quot;\necho &quot;When done: umount $SNAP_MNT &amp;&amp; lvremove -y $SNAP_DEV&quot;\n<\/code><\/pre>\n<p>Remember to run as root (or via sudo) because both <code>fsfreeze<\/code> and <code>lvcreate<\/code> want elevated privileges. Also, if your system has AppArmor\/SELinux policies that restrict the <code>mysql<\/code> client\u2019s <code>SYSTEM<\/code> command or socket access, adjust accordingly\u2014sometimes it\u2019s cleaner to keep the orchestration outside the client and use shorter lock windows with careful sequencing.<\/p>\n<h2 id=\"section-13\"><span id=\"Performance_and_Snapshot_Sizing\">Performance and Snapshot Sizing<\/span><\/h2>\n<p>How big should the snapshot be? It depends on how long your copy takes and how write\u2011heavy your workload is. Snapshots store changed blocks since the snapshot was taken\u2014so if your copy runs for 45 minutes and you\u2019re writing heavily, the snapshot grows. If it fills up, the snapshot becomes invalid. I typically start with something like 10\u201320% of the origin LV size for a daily backup window in a moderately busy system, then watch and tune. If you\u2019re copying to a local SSD and then pushing to object storage asynchronously, you can get away with smaller snapshots because your copy finishes faster.<\/p>\n<p>And yes, snapshots have overhead on the origin volume. It\u2019s usually modest but measurable. I try to schedule backups during periods when the system can spare a bit of I\/O contention. If you can\u2019t, that\u2019s when backing up on a replica shines\u2014you free the primary from that extra load entirely.<\/p>\n<h2 id=\"section-14\"><span id=\"When_to_Use_This_vs_Logical_Backups\">When to Use This vs. Logical Backups<\/span><\/h2>\n<p>There\u2019s a place for <code>mysqldump --single-transaction<\/code> and <code>pg_dump<\/code> too. Logical backups are great for portability (migrate versions, move schemas, etc.) and for lower\u2011impact streaming. But when you want <strong>fast restores<\/strong> with the exact binary state\u2014including indexes and tuning\u2014file\u2011level backups from consistent snapshots are hard to beat. In my playbook, I keep both: nightly application\u2011consistent snapshots for fast recovery, and periodic logical backups for migration and schema insurance.<\/p>\n<h2 id=\"section-15\"><span id=\"Bonus_Shipping_to_S3MinIO_the_Clean_Way\">Bonus: Shipping to S3\/MinIO the Clean Way<\/span><\/h2>\n<p>Once you\u2019ve got your snapshot mounted, you can pipe a tar straight to object storage, avoid writing big archives to disk, and keep things nice and tidy:<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">cd \/mnt\/mysql-snap\n# Stream backup directly to S3\nsudo tar -I 'gzip -1' -cf - . | aws s3 cp - s3:\/\/my-backups\/mysql\/$(date +%F)\/mysql.tar.gz\n\n# Or to MinIO using mc (MinIO client)\n# mc cp -r \/mnt\/pg-snap myminio\/backups\/pg\/$(date +%F)\/\n<\/code><\/pre>\n<p>MinIO has been a rock\u2011solid piece in my stack. If you want a friendly walkthrough of TLS, erasure coding, and bucket policies that don\u2019t become a second job, here\u2019s that deeper dive: <a href=\"https:\/\/www.dchost.com\/blog\/en\/vps-uzerinde-minio-ile-s3%e2%80%91uyumlu-depolama-nasil-uretim%e2%80%91hazir-kurulur-erasure-coding-tls-ve-policyleri-tatli-tatli-anlatiyorum\/\">Production\u2011Ready MinIO on a VPS<\/a>.<\/p>\n<h2 id=\"section-16\"><span id=\"Troubleshooting_Checklist\">Troubleshooting Checklist<\/span><\/h2>\n<p>If something feels off, I run through this quick checklist:<\/p>\n<p>Is <code>fsfreeze<\/code> supported and returning success? Did the <code>lvcreate -s<\/code> command complete quickly, or hang (which would extend the lock window)? Is the snapshot mounted read\u2011only as expected? Do you see WAL\/binlog files aligned with your backup time? Did you verify permissions and SELinux contexts on restore? Finally, does the database actually start from the backup on a clean test VM? Nothing beats that last one.<\/p>\n<p>Also, keep logs! Write the timestamp of your backup start\/stop and snapshot name to a simple log file. When you\u2019re in detective mode, those tiny breadcrumbs save time.<\/p>\n<h2 id=\"section-17\"><span id=\"WrapUp_Give_Your_Future_Self_the_Gift_of_Calm_Restores\">Wrap\u2011Up: Give Your Future Self the Gift of Calm Restores<\/span><\/h2>\n<p>I\u2019ve lost count of how many times a tiny bit of coordination saved me hours on the restore side. Application\u2011consistent backups with LVM snapshots and <code>fsfreeze<\/code> are the kind of technique you implement once, then quietly rely on for years. You get the speed of file\u2011level restores, the safety of database\u2011aware snapshots, and the peace of mind that your backups aren\u2019t a Schr\u00f6dinger\u2019s cat situation.<\/p>\n<p>If you\u2019re already using another snapshotting filesystem like ZFS, lean into it. If you\u2019re scaling backups to replicas or multi\u2011region, build it into your routine. And whatever you do, practice restores\u2014just a little\u2014so they\u2019re muscle memory. If you want to go even deeper on storage internals, my longform write\u2011up on <a href=\"https:\/\/www.dchost.com\/blog\/en\/ofiste-bir-aksam-disk-isiginin-ritmi-ve-zfs-ile-barisma\/\">ZFS snapshots and send\/receive<\/a> pairs nicely with this approach, and if you\u2019re thinking about resilient topologies, that <a href=\"https:\/\/www.dchost.com\/blog\/en\/cok-bolgeli-mimariler-nasil-kurulur-dns-geo%e2%80%91routing-ve-veritabani-replikasyonu-ile-korkusuz-felaket-dayanikliligi\/\">multi\u2011region architecture story<\/a> has plenty of field notes.<\/p>\n<p>Hope this helped connect the dots. If it saves you a late\u2011night headache sometime, we both win. See you in the next post\u2014may your snapshots be small, your restores be boring, and your coffee still warm when it\u2019s over.<\/p>\n<\/div>","protected":false},"excerpt":{"rendered":"<p>So there I was, late on a Wednesday night, staring at a server that absolutely refused to bring MySQL back up after a restore. The backup had run. The files looked fine. But the database? Grumpy. The logs were a soup of partial writes and half\u2011finished transactions. Ever had that feeling where your heart sinks [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":1951,"comment_status":"","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26],"tags":[],"class_list":["post-1950","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-teknoloji"],"_links":{"self":[{"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/posts\/1950","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/comments?post=1950"}],"version-history":[{"count":0,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/posts\/1950\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/media\/1951"}],"wp:attachment":[{"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/media?parent=1950"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/categories?post=1950"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/tags?post=1950"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}