Never put a plaintext password directly in the script or on the mysqldump command line, since it becomes visible to anyone who reads the file or runs ps aux during the dump. Instead, create a ~/.my.cnf file with a [client] section containing user and password, then run chmod 600 ~/.my.cnf so only that account can read it — mysqldump picks up these credentials automatically. An alternative is a dedicated backup MySQL user with SELECT, LOCK TABLES and SHOW VIEW privileges only, further limiting the damage if credentials ever leak.

For a gzip-compressed dump, run gunzip -c backup_file.sql.gz | mysql -u root -p database_name to decompress and pipe the SQL directly into the target database. For an uncompressed .sql file, use mysql -u root -p database_name < backup_file.sql instead. Always restore into a freshly created or empty database first when testing, and confirm the dump completed successfully (no truncated file) before relying on it — periodically test-restoring backups is the only way to know they actually work.

A backup stored only on the same VPS as the live database is not a real disaster-recovery plan — if the server is compromised, the disk fails, or the provider account is suspended, the backups disappear along with the data they were meant to protect. Copying dumps off-box with rsync (or to object storage) to a separate host, provider, or region means a single point of failure cannot take out both your database and its backups at the same time. This is often called the 3-2-1 rule: at least 3 copies, on 2 different types of storage, with 1 copy off-site.

A common baseline is 7 daily backups for quick recent-history recovery, but the right number depends on your recovery point objective and how quickly you would notice data corruption or accidental deletion. Sites with slow-to-detect issues (e.g. a bad migration discovered weeks later) benefit from a tiered approach: keep daily backups for 7-14 days, plus a few weekly or monthly snapshots retained longer. Balance this against available disk space — the retention_days setting in this generator only controls local cleanup via find -mtime, so remote or object-storage copies can follow a separate, longer retention policy.

mysqldump is single-threaded and reads every row through SQL, so on databases in the tens or hundreds of gigabytes it can take hours and place heavy load on the server. mydumper is a drop-in alternative that dumps tables in parallel across multiple threads, cutting backup time significantly. For very large or high-write databases, physical/hot backup tools like Percona XtraBackup (MySQL/MariaDB) copy the underlying data files directly with minimal locking, and cloud-VPS snapshot features (LVM or provider-level disk snapshots) can capture a consistent point-in-time image almost instantly. mysqldump remains the simplest and most portable option for small-to-medium databases where a few minutes of extra load is acceptable.