{"id":346,"date":"2026-11-08T13:50:00","date_gmt":"2026-11-08T13:50:00","guid":{"rendered":"https:\/\/backendside.com\/blog\/?p=346"},"modified":"2026-11-21T12:02:03","modified_gmt":"2026-11-21T12:02:03","slug":"mysql-backups-mysqldump-binlog-point-in-time-recovery","status":"publish","type":"post","link":"https:\/\/backendside.com\/blog\/2026\/11\/08\/mysql-backups-mysqldump-binlog-point-in-time-recovery\/","title":{"rendered":"Backups That Actually Restore: mysqldump, Binary Logs and Point-in-Time Recovery"},"content":{"rendered":"<p class=\"lead\">Everyone has backups until they need one. Then the file turns out to be from March, or it is half a dump because the disk filled up, or it restores fine but nobody knows how to replay the six hours of changes since it was taken. A good MySQL or MariaDB backup strategy answers two questions in advance: <strong>how much data can we afford to lose<\/strong>, and <strong>how fast must we be back<\/strong>. This guide covers the tools that answer them &mdash; logical dumps, binary logs and point-in-time recovery &mdash; and the restore drill that proves they work.<\/p>\n<h2>Logical vs physical backups<\/h2>\n<p>There are two fundamentally different ways to back up a MySQL-family server:<\/p>\n<table>\n<thead>\n<tr>\n<th><\/th>\n<th>Logical (SQL dump)<\/th>\n<th>Physical (file copy)<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>What it is<\/td>\n<td>SQL statements that recreate schema and data<\/td>\n<td>A consistent copy of the data files<\/td>\n<\/tr>\n<tr>\n<td>Tools<\/td>\n<td><code>mysqldump<\/code> \/ <code>mariadb-dump<\/code>, MySQL Shell dump utilities<\/td>\n<td><code>mariabackup<\/code> (MariaDB), Percona XtraBackup or MySQL Enterprise Backup (MySQL)<\/td>\n<\/tr>\n<tr>\n<td>Speed on large data<\/td>\n<td>Slow to restore &mdash; every row is re-inserted and indexes rebuilt<\/td>\n<td>Fast &mdash; files are copied back<\/td>\n<\/tr>\n<tr>\n<td>Portability<\/td>\n<td>Across versions, and often between MySQL and MariaDB<\/td>\n<td>Same engine and closely matching version only<\/td>\n<\/tr>\n<tr>\n<td>Granularity<\/td>\n<td>Easy to restore one table or database<\/td>\n<td>Usually all or nothing<\/td>\n<\/tr>\n<tr>\n<td>Best for<\/td>\n<td>Small to medium databases, migrations, extra copies<\/td>\n<td>Large databases where restore time matters<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>For databases up to a few tens of gigabytes, logical dumps plus binary logs are simple and dependable. Beyond that, restore time usually pushes you towards physical backups. Either way, the binary-log technique below is the same.<\/p>\n<h2>Taking a consistent dump<\/h2>\n<p>The single most important flag is <code>--single-transaction<\/code>. It makes the dump read from one consistent snapshot of InnoDB tables without locking them, so the application keeps running and the dump does not mix data from different moments.<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code># MySQL 8.x\nmysqldump --single-transaction --routines --triggers --events \n  --source-data=2 --hex-blob \n  --databases shop &gt; shop-2026-11-08.sql\n\n# MariaDB (mariadb-dump; --master-data is the MariaDB spelling)\nmariadb-dump --single-transaction --routines --triggers --events \n  --master-data=2 --hex-blob \n  --databases shop &gt; shop-2026-11-08.sql<\/code><\/pre>\n<p>What the flags buy you:<\/p>\n<ul>\n<li><code>--routines --triggers --events<\/code> &mdash; include stored procedures, functions, triggers and scheduled events. Triggers are on by default; routines and events are not, and forgetting them is a classic surprise at restore time.<\/li>\n<li><code>--source-data=2<\/code> \/ <code>--master-data=2<\/code> &mdash; writes the binary log file name and position at the moment of the snapshot into the dump as a comment. You need this for point-in-time recovery. (MySQL renamed the option in 8.0.26; MariaDB keeps the older name.) It requires binary logging to be enabled on the server.<\/li>\n<li><code>--hex-blob<\/code> &mdash; writes binary columns as hex so they survive any character-set handling.<\/li>\n<li><code>--databases<\/code> &mdash; includes the <code>CREATE DATABASE<\/code> and <code>USE<\/code> statements, so the file restores on its own.<\/li>\n<\/ul>\n<p>Avoid passing the password on the command line, where other users can see it in the process list; use an option file with restricted permissions instead. Compress on the fly (<code>| gzip &gt; shop.sql.gz<\/code>), and copy the result <strong>off the server<\/strong> &mdash; a backup that lives on the same disk as the database disappears with it.<\/p>\n<div style=\"border:1px solid #c5d3f8;background:linear-gradient(135deg,#eef2fd 0%,#ffffff 72%);border-radius:14px;padding:1.5rem 1.65rem;margin:2rem 0;\">\n<div style=\"font-size:.7rem;font-weight:700;letter-spacing:.08em;text-transform:uppercase;color:#2d5be3;margin-bottom:.55rem;\">&#128295; BackendSide Tool<\/div>\n<h4 style=\"margin:0 0 .45rem;font-size:1.15rem;color:#1a1916;font-weight:700;\">MyDBDesk &mdash; SQL Dumps on a Schedule, From Your Desktop<\/h4>\n<p style=\"margin:0 0 1.05rem;color:#3d3c38;font-size:.92rem;line-height:1.65;\">Not every database has an ops team behind it. <strong>MyDBDesk<\/strong> dumps a whole database as SQL &mdash; with batched INSERTs and transaction-wrapped dumps &mdash; and its dumps restore even when tables reference each other. <strong>Scheduled exports<\/strong> repeat on their own, every 15 minutes through weekly, for a whole database or a single table; <strong>Keep<\/strong> holds only the newest few files so a schedule cannot fill your disk, <strong>Run now<\/strong> tests a job on the spot, and each job shows how its last run went. Jobs run while MyDBDesk is open &mdash; a handy extra copy alongside server-side backups.<\/p>\n<p>  <a href=\"https:\/\/backendside.com\/mydbdesk.php\" style=\"display:inline-flex;align-items:center;gap:.4rem;background:#2d5be3;color:#ffffff;font-weight:600;font-size:.85rem;padding:.6rem 1.2rem;border-radius:6px;text-decoration:none;\">Explore MyDBDesk &rarr;<\/a>\n<\/div>\n<h2>Binary logs: the changes between dumps<\/h2>\n<p>A nightly dump means you can lose up to a day of data. The <strong>binary log<\/strong> closes that gap: it records every change to the data, in order, as it happens. Restore last night&rsquo;s dump, replay the binary log up to a chosen moment, and you are back to that exact point.<\/p>\n<p>Binary logging is <strong>on by default in MySQL 8<\/strong> and <strong>off by default in MariaDB<\/strong>. Check, and enable it if needed in the server configuration file:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>-- Is it on?\nSHOW VARIABLES LIKE 'log_bin';\nSHOW BINARY LOGS;\n\n# my.cnf \/ my.ini  [mysqld] section\nlog_bin                    = binlog\nserver_id                  = 1\nbinlog_format              = ROW\nbinlog_expire_logs_seconds = 1209600   # keep 14 days<\/code><\/pre>\n<p>Keep binary logs for at least as long as the gap between two full backups &mdash; ideally longer &mdash; and copy them off the server too. <code>ROW<\/code> format records the actual changed rows, which is the safest choice for recovery.<\/p>\n<h2>Point-in-time recovery, step by step<\/h2>\n<p>Scenario: at 14:32 someone runs <code>DELETE FROM orders;<\/code> without a <code>WHERE<\/code>. Your last dump was at 02:00. Here is how to get back to 14:31:59 with nothing lost.<\/p>\n<ol>\n<li><strong>Stop the damage.<\/strong> Keep the application from writing more changes if you can, and copy the current binary logs somewhere safe before anything rotates them.<\/li>\n<li><strong>Find the starting point.<\/strong> The dump records where it was taken:\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>grep -m1 \"CHANGE\" shop-2026-11-08.sql\n-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000187', SOURCE_LOG_POS=4211;\n--   (older versions and MariaDB write CHANGE MASTER TO MASTER_LOG_FILE=...)<\/code><\/pre>\n<\/li>\n<li><strong>Find the bad statement.<\/strong> Decode the logs and search near the time:\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>mysqlbinlog --base64-output=DECODE-ROWS -v \n  --start-datetime=\"2026-11-08 14:25:00\" binlog.000189 | less\n# note the \"# at NNNN\" position just before the DELETE<\/code><\/pre>\n<\/li>\n<li><strong>Restore the dump<\/strong> onto a server (ideally a fresh one, not the damaged production instance):\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>mysql &lt; shop-2026-11-08.sql<\/code><\/pre>\n<\/li>\n<li><strong>Replay everything up to the bad statement<\/strong>, starting at the position from step 2:\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>mysqlbinlog --start-position=4211 binlog.000187 binlog.000188 &gt; replay.sql\nmysqlbinlog --stop-position=88342 binlog.000189 &gt;&gt; replay.sql\nmysql &lt; replay.sql<\/code><\/pre>\n<\/li>\n<li><strong>Verify<\/strong> row counts and recent records, then switch the application over &mdash; or copy just the recovered table back into production.<\/li>\n<\/ol>\n<p>On MariaDB the tools are named <code>mariadb-binlog<\/code> and <code>mariadb<\/code> (the <code>mysql*<\/code> names usually still work as aliases). Note that <code>--start-position<\/code> applies to the first file listed and <code>--stop-position<\/code> to the last, which is why the example decodes the final file separately. Using <code>--stop-datetime<\/code> instead of a position is simpler but less precise if several statements share the same second.<\/p>\n<h2>The restore drill nobody does<\/h2>\n<p>A backup job that has reported &ldquo;success&rdquo; every night for a year can still produce files that do not restore: truncated by a full disk, missing routines, referencing a collation the target server does not know, or encrypted with a key nobody can find. The only proof is a restore. At least monthly:<\/p>\n<ol>\n<li>Take the latest backup from its <em>off-site<\/em> location, as you would in a real incident.<\/li>\n<li>Restore it to a scratch server.<\/li>\n<li>Check that tables, row counts, routines and triggers are all present, and that the application can read the data.<\/li>\n<li>Time it. That number is your real recovery time, and it is often longer than people assume.<\/li>\n<\/ol>\n<div style=\"border:1px solid #c5d3f8;background:linear-gradient(135deg,#eef2fd 0%,#ffffff 72%);border-radius:14px;padding:1.5rem 1.65rem;margin:2rem 0;\">\n<div style=\"font-size:.7rem;font-weight:700;letter-spacing:.08em;text-transform:uppercase;color:#2d5be3;margin-bottom:.55rem;\">&#128295; BackendSide Tool<\/div>\n<h4 style=\"margin:0 0 .45rem;font-size:1.15rem;color:#1a1916;font-weight:700;\">MyDBDesk &mdash; A Throwaway Server for Restore Drills<\/h4>\n<p style=\"margin:0 0 1.05rem;color:#3d3c38;font-size:.92rem;line-height:1.65;\">The hardest part of a restore drill is having somewhere safe to restore to. Click <strong>Local Server<\/strong> in <strong>MyDBDesk<\/strong> and it runs a portable MariaDB 11.4 on your own PC, listening only on 127.0.0.1, with a connection added automatically. Then run your <code>.sql<\/code> dump straight from disk &mdash; large files never load into the editor &mdash; and browse the restored tables in the grid to confirm the data is really there.<\/p>\n<p>  <a href=\"https:\/\/backendside.com\/mydbdesk.php\" style=\"display:inline-flex;align-items:center;gap:.4rem;background:#2d5be3;color:#ffffff;font-weight:600;font-size:.85rem;padding:.6rem 1.2rem;border-radius:6px;text-decoration:none;\">Explore MyDBDesk &rarr;<\/a>\n<\/div>\n<h2>A sensible baseline strategy<\/h2>\n<ul>\n<li><strong>Nightly full backup<\/strong> (logical or physical, depending on size), compressed and copied off the server.<\/li>\n<li><strong>Binary logs<\/strong> enabled in <code>ROW<\/code> format, retained beyond the full-backup interval and shipped off the server regularly.<\/li>\n<li><strong>The 3-2-1 rule<\/strong>: three copies, on two kinds of storage, one off-site.<\/li>\n<li><strong>A monthly restore drill<\/strong>, timed and written down.<\/li>\n<li><strong>Extra ad-hoc dumps<\/strong> before risky changes: migrations, bulk updates, version upgrades.<\/li>\n<\/ul>\n<p>Replication is not on this list for a reason. A replica protects you from a dead server, but it faithfully copies a <code>DELETE<\/code> without a <code>WHERE<\/code> within milliseconds. Replicas are for availability; backups are for recovery.<\/p>\n<h2>Key takeaways<\/h2>\n<ul>\n<li>Decide <strong>how much data you can lose and how fast you must recover<\/strong> before picking tools.<\/li>\n<li>Use <code>--single-transaction<\/code> for consistent, non-blocking dumps, and include <code>--routines --events<\/code>.<\/li>\n<li>Record the binary log position in each dump (<code>--source-data<\/code> on MySQL, <code>--master-data<\/code> on MariaDB).<\/li>\n<li><strong>Binary logs<\/strong> turn a nightly backup into recovery to any moment; MariaDB needs them switched on.<\/li>\n<li>Point-in-time recovery = <strong>restore the dump, then replay the binlog up to just before the mistake<\/strong>.<\/li>\n<li>Store backups off the server, and <strong>prove them with regular, timed restore drills<\/strong>. Replication is not a backup.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>A backup you have never restored is a hope, not a backup. How to take consistent dumps of MySQL and MariaDB, keep binary logs, recover to the minute before a bad DELETE, and prove the whole thing works with a restore drill.<\/p>\n","protected":false},"author":1,"featured_media":431,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,2],"tags":[29,24,25,23],"class_list":["post-346","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","category-windows","tag-backups","tag-mariadb","tag-mydbdesk","tag-mysql"],"_links":{"self":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/346","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/comments?post=346"}],"version-history":[{"count":1,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/346\/revisions"}],"predecessor-version":[{"id":432,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/346\/revisions\/432"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/media\/431"}],"wp:attachment":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/media?parent=346"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/categories?post=346"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/tags?post=346"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}