MariaDB Point-in-Time Recovery on AlmaLinux 9

Why point-in-time recovery matters on a production VPS
MariaDB point-in-time recovery lets you roll a database back to the minute before a bad deploy, broken script, or accidental delete. On a busy production VPS, a nightly dump is not enough when data changes all day.
This tutorial builds a working recovery path on AlmaLinux 9 with binary logs enabled, regular full backups, and a verified restore. It assumes a fresh server and uses Hostperl VPS hosting for workloads that need predictable disk I/O and enough room for backup storage. If you are planning a larger database footprint, keep an eye on Hostperl VPS hosting and the larger capacity choices in the Hostperl knowledge base such as RHEL cloud VPS sizing for production workloads in 2026.
The recovery flow is straightforward: install MariaDB, turn on binary logging, create a backup user, take a base backup, simulate a change, restore to a target time, and confirm the data is correct. You will also check logs, validate the configuration, and recover safely if something goes wrong.
What you need before you begin
- AlmaLinux 9 on a VPS or dedicated server
- Root SSH access for the first login
- A second terminal for testing the non-root account
- About 2x your database size free on disk while you test backups
If you are still choosing server size for database-heavy work, Hostperl’s managed VPS hosting is a practical starting point for smaller teams, while larger production databases may belong on dedicated hardware.
Connect to the server and confirm the operating system
On your local computer, connect with SSH first:
ssh root@203.0.113.10203.0.113.10 is a reserved documentation address. Replace it with the real public IP assigned to your Hostperl server.
After you log in, confirm the OS before using the RHEL-family commands in this guide:
cat /etc/os-releaseYou should see AlmaLinux 9 in the output. If you are on a different distribution, use the matching package manager and service names instead of copying blindly.
Create a sudo user and keep the root session open
On the VPS as root, create a non-root administrator called deploy. Keep the root session open until you verify the new login from a second terminal.
useradd -m -G wheel deploy
passwd deployThis creates the account, adds it to the wheel group, and sets a password for the first SSH test. If you want key-only login later, you can lock the password after the account works.
Now create the SSH directory and install your public key. Replace the key text with your own public key string:
install -d -m 700 -o deploy -g deploy /home/deploy/.ssh
cat > /home/deploy/.ssh/authorized_keys <<'EOF'
ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIExampleKeyReplaceWithYourOwnKey deploy@example.com
EOF
chown deploy:deploy /home/deploy/.ssh/authorized_keys
chmod 600 /home/deploy/.ssh/authorized_keysOpen a second terminal and test the new account before you change any SSH policy:
ssh deploy@203.0.113.10Then confirm sudo works:
sudo -v
whoamiYou should see deploy as the username and no errors from sudo. Only after this test should you consider locking the root password or disabling password SSH.
Update AlmaLinux and install MariaDB
On the VPS as root or as deploy with sudo, update the server first. That keeps the database install separate from stale packages.
dnf update -y
rebootReconnect after the reboot, then install MariaDB server and client tools:
dnf install -y mariadb-server mariadbCheck the version so you know exactly what you are running:
mariadb --versionOn AlmaLinux 9, the service name is mariadb. Enable it now so it starts after a reboot:
systemctl enable --now mariadbConfirm the service is active:
systemctl status mariadb --no-pagerIf the service fails, inspect the log right away:
journalctl -u mariadb -b --no-pagerRun the base hardening step for a fresh MariaDB install
On the VPS as root, secure the default installation. This removes test data and closes common default risks.
mariadb-secure-installationUse a strong root password if your packaging flow asks for one, remove anonymous users, disallow remote root login, and remove the test database. When the tool finishes, log in locally to confirm access:
mariadb -u root -pYou should reach the MariaDB prompt. Exit cleanly when done:
exitEnable binary logging for MariaDB point-in-time recovery
Binary logs are the key to MariaDB point-in-time recovery. They record every change after the last full backup, so you can replay changes up to the exact moment you want.
On the VPS as root, open the MariaDB server configuration file:
nano /etc/my.cnf.d/mariadb-server.cnfAdd or update the [mysqld] section with the following content. Keep the file minimal and explicit:
[mysqld]
bind-address=127.0.0.1
log_bin=/var/lib/mysql/mysql-bin
server_id=1
binlog_format=ROW
expire_logs_days=7
Save the file and exit nano with Ctrl+O, Enter, then Ctrl+X.
Restart the service after you save the file:
systemctl restart mariadbThen confirm binary logging is active:
mariadb -u root -p -e "SHOW VARIABLES LIKE 'log_bin'; SHOW VARIABLES LIKE 'binlog_format'; SHOW VARIABLES LIKE 'server_id';"You should see ON for log_bin and ROW for binlog_format. If you do not, recheck the config file path and the restart output.
Create a backup user and a test database
On the VPS as root, create a database and a user that will be used for the smoke test. This gives you something realistic to recover.
mariadb -u root -pInside the MariaDB prompt, run the following SQL exactly:
CREATE DATABASE appdb;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'ChangeThisPasswordNow!';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;
USE appdb;
CREATE TABLE orders (
id INT NOT NULL AUTO_INCREMENT,
customer_name VARCHAR(100) NOT NULL,
amount DECIMAL(10,2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
INSERT INTO orders (customer_name, amount) VALUES ('Aroha', 149.50), ('Mason', 82.00);
EXIT;Change the password to a real secret before any production use. The point here is to create predictable data for the restore drill.
Now create a backup destination with restrictive permissions:
mkdir -p /var/backups/mariadb
chown root:root /var/backups/mariadb
chmod 700 /var/backups/mariadbTake a full backup with binary log coordinates
On the VPS as root, take the first base backup. This backup is the restore anchor for every later recovery.
mariadb-dump -u root -p --single-transaction --routines --events --triggers --all-databases --master-data=2 | gzip > /var/backups/mariadb/full-$(date +%F-%H%M).sql.gzList the backup to confirm it exists and is not empty:
ls -lh /var/backups/mariadb/
gzip -t /var/backups/mariadb/full-*.sql.gzOpen the dump file header if you want the binary log file and position that were captured with the backup:
zcat /var/backups/mariadb/full-*.sql.gz | head -n 30You should see CHANGE MASTER TO comments that record the log file and position. Keep those values for recovery planning.
Simulate a bad change and capture the recovery point
To prove the recovery process, make a harmless but visible change after the backup. On a real incident, this would be a bad delete, corrupt import, or mistaken update.
mariadb -u root -p -e "USE appdb; INSERT INTO orders (customer_name, amount) VALUES ('TestRestore', 25.00); SELECT * FROM orders;"Record the current binary log file and position before the next change if you want a precise stop point:
mariadb -u root -p -e "SHOW MASTER STATUS\G"For this drill, we will stop just before a later unwanted change. Make one more insert that we will remove during recovery:
mariadb -u root -p -e "USE appdb; INSERT INTO orders (customer_name, amount) VALUES ('BadRow', 9999.99); SELECT * FROM orders;"Restore to a point in time
On the VPS as root, stop MariaDB before restoring. That avoids file contention while you rebuild the database state.
systemctl stop mariadbMove the current data directory aside. This is safer than deleting it because you can roll back if needed:
mv /var/lib/mysql /var/lib/mysql.bak.$(date +%F-%H%M)
mkdir -p /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
chmod 750 /var/lib/mysqlRestore the full backup first:
zcat /var/backups/mariadb/full-*.sql.gz | mariadb -u rootNow decide how far to replay the binary log. If you want to stop before the bad row, restore up to the safe point using the binary log file captured earlier. Replace the example log name and stop position with your real values from SHOW MASTER STATUS or the backup header:
mysqlbinlog --stop-position=1234 /var/lib/mysql/mysql-bin.000001 | mariadb -u rootIf you want to stop by time instead, use the event timestamp from before the bad change:
mysqlbinlog --stop-datetime="2026-11-15 12:30:00" /var/lib/mysql/mysql-bin.000001 | mariadb -u rootStart MariaDB again:
systemctl start mariadbVerify the restored data
On the VPS as root, confirm the row you wanted is present and the unwanted one is gone. This is the real proof of MariaDB point-in-time recovery.
mariadb -u root -p -e "SELECT id, customer_name, amount, created_at FROM appdb.orders ORDER BY id;"You should see the backup-era rows and any replayed safe rows, but not the unwanted BadRow if you stopped at the right point.
Also confirm the service starts cleanly and listens on the local socket:
systemctl is-active mariadb
ss -ltnp | grep 3306 || trueSet up a simple backup rotation script
A one-time backup is not enough for production. Use a small shell script so your team can run the same backup routine every day.
On the VPS as root, create the script:
cat > /usr/local/sbin/mariadb-backup.sh <<'EOF'
#!/bin/bash
set -euo pipefail
BACKUP_DIR=/var/backups/mariadb
FILE="$BACKUP_DIR/full-$(date +%F-%H%M).sql.gz"
mariadb-dump -u root -p'ChangeThisPasswordNow!' --single-transaction --routines --events --triggers --all-databases --master-data=2 | gzip > "$FILE"
gzip -t "$FILE"
EOF
chmod 700 /usr/local/sbin/mariadb-backup.shReplace the password with a secure secret and consider using a dedicated backup account with limited privileges instead of the root password. Test the script manually:
/usr/local/sbin/mariadb-backup.sh
ls -lh /var/backups/mariadb/For scheduled execution, create a cron entry:
cat > /etc/cron.d/mariadb-backup <<'EOF'
15 2 * * * root /usr/local/sbin/mariadb-backup.sh
EOFConfirm cron will pick up the file on the next minute boundary by checking the system cron service and logs:
systemctl status crond --no-pager
journalctl -u crond -b --no-pager | tail -n 20Firewall and network checks
If MariaDB is only for local applications, keep port 3306 closed to the public internet. On AlmaLinux, firewalld is usually present, but you do not need to open the database port for local-only use.
systemctl status firewalld --no-pager
firewall-cmd --list-allIf you must allow a controlled application server, open 3306 only for that source and not for the world. Replace the example source address with your app server IP:
firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="198.51.100.25" port port="3306" protocol="tcp" accept'
firewall-cmd --reloadThen confirm the rule is loaded:
firewall-cmd --list-rich-rulesRollback if the restore goes wrong
If MariaDB refuses to start after the restore, stop the service and move the original data directory back into place. This is why the tutorial renamed the old datadir instead of deleting it.
systemctl stop mariadb
rm -rf /var/lib/mysql
mv /var/lib/mysql.bak.* /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
systemctl start mariadbThen inspect the log if startup still fails:
journalctl -u mariadb -b --no-pager | tail -n 50Common clues include a permission mismatch on /var/lib/mysql, a bad binary log replay command, or a dump created from the wrong server version.
Troubleshooting the most likely failures
MariaDB does not start after editing the config. Check the exact error and the config file path.
journalctl -u mariadb -b --no-pager | tail -n 50
ls -l /etc/my.cnf.d/
If the log mentions an unknown option, remove or correct that line in /etc/my.cnf.d/mariadb-server.cnf, then restart MariaDB.
The backup file is corrupted or empty. Test the gzip archive and check disk space.
gzip -t /var/backups/mariadb/full-*.sql.gz
df -h /var/backups/mariadbIf the archive test fails, rerun the backup and watch for permission errors or a full filesystem.
The restored data still includes the unwanted change. Recheck the stop position or stop time passed to mysqlbinlog.
mysqlbinlog /var/lib/mysql/mysql-bin.000001 | lessFind the event just before the bad row and use an earlier stop point. The goal is to replay enough history to keep valid data, but not the mistake.
Binary logs are missing after restart. Confirm log_bin is still enabled and that the files exist in /var/lib/mysql.
mariadb -u root -p -e "SHOW VARIABLES LIKE 'log_bin'; SHOW BINARY LOGS;"If SHOW BINARY LOGS returns nothing useful, recheck file ownership and the configuration restart sequence.
If your database now carries real customer traffic, Hostperl VPS hosting gives you room to keep backups, logs, and test restores without crowding the database disk. For larger production databases or heavier restore windows, a dedicated server can give you more predictable I/O and backup headroom. See Hostperl VPS and dedicated server hosting for deployment options that fit your recovery plan.
FAQ
Can MariaDB point-in-time recovery work without binary logs?
No. A full backup gives you the base state, but binary logs are what let you replay changes up to the failure point.
Should I keep binary logs forever?
No. Keep them long enough to cover your recovery objective, then expire or rotate them so they do not fill the disk.
Is a nightly dump enough for production?
Not for active databases. If data changes throughout the day, you need binary logs or another continuous recovery method.
Can I test this safely on a live server?
Yes, if you use a staging database or a small test dataset first. Always keep a backup copy and the original data directory until the restore is verified.
What should I check after reboot?
Run systemctl status mariadb, confirm the backup cron exists, and make sure binary logging is still on.
Final verification
Before you close the ticket, verify the service, the data, and the backup path from both server and client angles. A recovery process is only useful if you can repeat it under pressure.
On the VPS as root:
systemctl status mariadb --no-pager
mariadb -u root -p -e "SELECT COUNT(*) AS rows_in_orders FROM appdb.orders;"
ls -lh /var/backups/mariadb/
On your local computer, confirm SSH access still works after the changes:
ssh deploy@203.0.113.10Replace the example IP with your server’s real public address. If the login succeeds and the database query returns the expected rows, your MariaDB point-in-time recovery path is ready for production use.
