Safely Backing Up Live Databases (MySQL and PostgreSQL)

While copying standard website files or code repositories with tools like rsync is straightforward, backing up a live, running database requires a different approach. If you simply copy raw database directories (like /var/lib/mysql or /var/lib/postgresql) while the server is actively processing user transactions, you risk creating corrupted, unusable backups.

The safest, most reliable method is to generate a logical backup—a complete, compressed file containing the exact SQL commands needed to rebuild your database schema and data from scratch.

The Danger of Raw File Copies

Database engines constantly cache data in the server's memory before writing it to the physical disk. A raw file copy might capture half of an ongoing transaction, leaving your database in a fractured state. By using native dump tools, you force the database engine to output a clean, consistent snapshot of your data at that exact moment, without interrupting live traffic.

Automating MySQL and MariaDB Backups

The standard utility for backing up MySQL or MariaDB is mysqldump. To automate this securely, you must avoid typing your database password directly into a script where it could be read by other users on the system.

• Create a hidden configuration file in your user's home directory by running nano ~/.my.cnf.

• Add your database credentials in the following format:

  Plaintext

   

  [mysqldump]
  user=your_backup_user
  password=your_secure_password
  

• Lock down the file's permissions so strictly only your user can read it: chmod 600 ~/.my.cnf.

• Generate the backup and pipe it directly into gzip to heavily compress the output and save disk space:

  mysqldump my_database_name | gzip > /backups/my_database_$(date +%F).sql.gz

Automating PostgreSQL Backups

PostgreSQL relies on the pg_dump utility. Similar to MySQL, you should use a dedicated credentials file rather than hardcoding passwords into your automation scripts.

• Create the hidden PostgreSQL password file: nano ~/.pgpass.

• Add your connection details using the strict format hostname:port:database:username:password:

  localhost:5432:my_database_name:my_backup_user:my_secure_password

• Restrict access to the file immediately: chmod 600 ~/.pgpass.

• Execute the backup using PostgreSQL's custom output format (-F c), which automatically compresses the data and allows for highly targeted restores later:

  pg_dump -h localhost -U my_backup_user -F c -f /backups/my_database_$(date +%F).dump my_database_name

Rotating Old Backups

If you configure cron to run these database dumps every night, your server's hard drive will eventually run out of space. You must pair your backup commands with a cleanup routine that automatically deletes older archives.

• You can use the standard Linux find command to locate and remove files older than a specific timeframe.

• To delete backups in your folder that are older than 7 days, you would run:

  find /backups/ -type f -name "*.gz" -mtime +7 -delete

• Bundle your dump command and your cleanup command into a single .sh bash script, and schedule that script to run nightly via your crontab.