Automating MySQL backups with mysqldump and crontab lets a Linux server perform recurring database backups without manual intervention. However, a backup is only useful when the resulting file, such as .sql.gz, can be restored. Protect database credentials, detect failed commands, store backups securely, and periodically test recovery.

The earlier tutorial covered mysqldump, gzip compression, scheduling with crontab -e, and the backupsekarang.sh script. This article maintains that concept while correcting unsafe practices, such as writing the database password directly after the option -p or creating a file name with an incorrect percent sign in the crontab. For consistent recovery, also learn the steps for creating a VPS web server.

What Is Mysqldump and Why Is It Still Useful?

mysqldump is a tool logical backup MySQL: Data and table definitions are exported as SQL commands that can be used to recreate the structure and import data. This method simplifies transfer between systems and is quite practical for databases of reasonable size. For large databases, consider dump duration, I/O load, and recovery time; MySQL also has a parallel dump utility in the MySQL Shell.

Flag --single-transaction useful for obtaining a consistent view of data on InnoDB tables when conditions are met, but is not a guarantee of consistency for MyISAM or concurrent schema changes. Use --quick To read rows incrementally, discuss the need to include stored routines, events, and triggers with the database administrator. Server permissions and service settings can be compared to the Webuzo VPS administration tutorial.

Automatic Backup of MySql Database With mysqldump and Crontab
Image of old documentation regarding scheduled MySQL database backups.

WordPress plugins can change data and schemas that need to be considered in the backup process.

Checking bot access after recovery is also related to website crawling.

Check the sitemap function if the website is moved to a new domain or host.

Security Preparation and Backup Account

Use a dedicated backup database user with the least necessary permissions. Permissions such as SELECT, SHOW VIEW, and TRIGGER may be required depending on the objects being exported; do not always use the root account. Specify the backup directory. /var/backups/mysql outside the document root, then ensure that only authorized users can read the dump results.

In the old example, the password was written directly to the command line and risked being logged in the shell history. For automated processes, store the database connection in a MySQL options file that is readable only by the backup user. Do not include the original secret in articles, repositories, or server logs. You can start by specifying the user and database name in the MySQL database creation guide.

# /root/.mysql-backup.cnf
[client]
user=backup_user
password=GANTI_DENGAN_RAHASIA_PRIVAT
host=127.0.0.1
sudo chmod 600 /root/.mysql-backup.cnf
sudo install -d -m 700 /var/backups/mysql

For remote databases, pay attention to TLS connections and host identity validation. The examples here assume the commands are run on an authorized VPS and the scripts and confidential files are managed by the appropriate administrator.

When restoring, also verify Cloudflare‘s caching settings so that old content doesn’t continue to appear.

The quality of recovery can be evaluated along with an SEO performance audit.

How to Run Mysqldump Manually

Start with manual testing on a test database or a database that has backup permissions. catperku_blog with your database name. The result will be an archive that can be verified with gzip -t, but the short pipeline doesn’t handle mysqldump failure states as thoroughly as the production script. To familiarize yourself with the table structure before backup, read the phpMyAdmin management guide.

mysqldump --defaults-extra-file=/root/.mysql-backup.cnf \
  --single-transaction --quick --routines --events --triggers \
  --databases catperku_blog | gzip -c > /var/backups/mysql/test.sql.gz
gzip -t /var/backups/mysql/test.sql.gz

Remember, empty files or partial dumps may not be detected simply by checking if the archive appears. Use pipeline failure handling and atomic moves in the following script. The official documentation explains that concurrent DDL operations with MySQL snapshots can also cause consistency issues.

Post-restore testing should also check blog loading performance.

Example of a Safer backupsekarang.sh Script

The following script uses the Bash shell, set -Eeuo pipefail, temporary files, and archive checks. If mysqldump or gzip fails, the command does not proceed with moving the files as a final backup. Save as /usr/local/sbin/backupsekarang.sh After adjusting the configuration, database and file requirements can be understood while following the WordPress installation.

#!/usr/bin/env bash
set -Eeuo pipefail
umask 077
DIR="/var/backups/mysql"
DB="catperku_blog"
NOW="$(date +%F_%H-%M-%S)"
TMP="$(mktemp "$DIR/.tmpdump.XXXXXX")"
trap 'rm -f "$TMP"' EXIT

mysqldump --defaults-extra-file=/root/.mysql-backup.cnf \
 --single-transaction --quick --routines --events --triggers \
 --databases "$DB" | gzip -c > "$TMP"

test -s "$TMP"
gzip -t "$TMP"
mv -- "$TMP" "$DIR/$DB-$NOW.sql.gz"
echo "Backup sukses: $DB $NOW"

Grant execution permissions as needed, then manually test before creating an automated job. Use absolute paths so the script doesn’t depend on the working directory when run from cron.

sudo chmod 700 /usr/local/sbin/backupsekarang.sh
sudo /usr/local/sbin/backupsekarang.sh

Migrations that affect HTTP status require a technical SEO audit.

Scheduling MySQL Backups with Crontab

Run sudo crontab -e If root is the owner of the script and the options file used, the example below runs a backup every 2:00 AM according to the host’s timezone, not automatically at 2:00 AM WIB. Ensure the log location is writable and does not store passwords. Differences in backup responsibilities between services can be examined in the hosting context.

0 2 * * * /usr/local/sbin/backupsekarang.sh >> /var/log/mysql-backup.log 2>&1
Expression Frequency Utility
0 2 * * * Daily at 02.00 General website backup
0 3 * * 0 Weekly on Sunday Long interval copy
0 4 1 * * Monthly 1st Historical archives
0 * * * * Every hour Database with fast changes
@reboot After the system boots Not a substitute for daily backups

Alias @yearly, @monthly, @weekly, @daily, @hourly, And @reboot useful but the user must understand the cron implementation. Sign % The crontab command has a special meaning if it is not escaped. Put date +%F in the script file, rather than directly on the cron line, helps avoid such misinterpretations.

If URLs change after migration, review Google indexing again.

Retention Strategy, Offsite Backup, and Restore

If you want to keep a 14-day local backup, check the candidate files first without deleting anything: Why databases are important is also explained in the discussion of dynamic websites.

find /var/backups/mysql -maxdepth 1 -type f \
 -name 'catperku_blog-*.sql.gz' -mtime +14 -print

Order find The above only displays a list. Delete old files using a tested policy after ensuring the target names and folders are correct; do not copy bulk delete commands without review. Copy important backups to separate storage, apply encryption, and ensure recovery keys are stored securely.

Test the restoration using a staging server or test database, not a production instance. Because the dump with --databases may include database selection instructions; check their contents first to avoid overwriting the active database. WordPress plugins can modify data and schemas, which should be taken into account when backing up.

gzip -t /var/backups/mysql/NAMA_FILE.sql.gz
gzip -dc /var/backups/mysql/NAMA_FILE.sql.gz \
 | mysql --defaults-extra-file=/root/.mysql-restore-test.cnf

File .sql.gz A valid, compressed backup may not restore application functionality, for example if media data or server configuration is lost. Document the test, archive size, number of tables, encoding, date, and recovery steps. Do not use VPS snapshots as your sole backup.

Common Mistakes

Problem Common causes Repair
Invalid backup file Dump failure not visible Use pipefail, gzip -t, test restore
Cron not working PATH, permission, timezone Run as user cron, check log
Access denied Incorrect privileges and credentials Audit backup accounts as well as option files
Disk full Retention and capacity are neglected Monitor storage and offsite copies
Restore failed Incompatible schema or version Test on a suitable staging server

Database backups should be part of website management, not a separate task after downtime. Migrating managers should test applications, database connections, images, logins, and HTTP status before changing DNS for visitors. Migrating large backups can require significant network bandwidth.

Frequently Asked Questions (FAQ)

1. What is the function of mysqldump?

Mysqldump produces a logical SQL backup containing database definitions and data.

2. What is the function of crontab?

Crontab schedules commands automatically on Linux systems using cron.

3. Does mysqldump require root access?

Not always. Use a dedicated database user with sufficient privileges for the objects being backed up.

4. What is the use of –single-transaction?

Enables consistent snapshots for InnoDB tables under supported conditions, but does not guarantee the same on MyISAM.

5. Does gzip encrypt backups?

No. Gzip only compresses data; use separate encryption if the backup contains sensitive information.

6. Can passwords be stored in scripts?

It’s best not to. Use a secret manager or an options file with limited permissions.

7. How to make daily backups?

Test the script manually, then add a schedule like 0 2 * * * to the appropriate user’s crontab.

8. Why does the SQL file exist but the backup fails?

Pipelines without failure detection can produce files even if mysqldump is unsuccessful; check the exit code and restore.

9. Where should backup files be stored?

Store outside the webroot with limited access and an encrypted copy in a separate location.

10. How to test MySQL restore?

Restore the archive to an isolated staging, then examine the structure, data samples, and application functionality.

11. Is SQL backup enough for WordPress?

Not yet. Media files, themes, plugins, configurations, and other system requirements may also be required.

12. How long should backup files be kept?

According to business needs, risks, legal obligations, recovery targets, and storage capacity.

External References

Oh hi there 👋
It’s nice to meet you.

Sign up to receive awesome content in your inbox, every month.

We don’t spam! Read our privacy policy for more info.

Rijal Fahmi Mohamadi

I bring an engineer’s analytical mindset to Digital Marketing. Leveraging my background in software development, I specialize in SEO, SEM, social media, and data analytics to help businesses scale and drive profitability in remote setups.

P.S. I haven't lost my coding touch! I'm highly proficient in PHP and currently expanding my data analytics toolkit with Python."

Leave a Reply

Your email address will not be published. Required fields are marked *