Backing up WordPress Database from Command Line

As a developer who is very sensitive about performance, I never (mostly) used any backup plugin those depends mostly upon WordPress cron and tries to backup and upload to the same server or a remote server without offloading from the main PHP process.

To me, it feels like I’m slowing down the server by relying on backing up a site on PHP FPM. It could be done more efficiently from command line using tools like mysqldump, gzipping it and copying to any location you would like. Because it’s supposed to work like that, I never liked any backup plugin (I can’t speak for every backup solutions out there, but most plugin does). Although VaultPress bridges the gap between both of two worlds by backing up normally and using SSH, but it’s costly. Well, not that much, but still you require to opt-in to their monthly payment plan.

Till now I’ve been using bash scripts to backup my sites, but those also require you to manually edit and put the server locations where to store the backup and they aren’t all purpose scripts. So I built a new one – wp-db-backup. It’s just like another command line utility that lets you do whatever you want without touching any configuration file or editing something, and it comes with a installer script so you won’t have to remember all those things to configure which you would forget after few days anyway. For me, that works!

WP DB Backup (link)

This is simply shell/bash script, but it’s kind-of serves all my purpose. It supports backing up your database in a directory on the current machine, to a remote server or in Amazon S3. As it supports command line arguments, you don’t need to hardcode anything in code level, so you tell it what to do and where to put things, it’ll do the rest.

How it works

This script uses the combination of WP-CLI to backup your WordPress database, Linux SCP (Secure Copy) to move the backup to a remote destination of SSH, and s3cmd – a CLI tool to copy files to Amazon S3. The backup file will be gzipped automatically.

Installation

The installation is pretty straight forward and automatic. Just run the command:

wget -O - https://raw.github.com/tareq1988/wp-db-backup/master/setup.sh | bash

It’ll download and install the necessary dependencies if you don’t have them already (wp-cli and s3cmd). The main script will be installed in /usr/local/bin/wp-db-backup path. So you can invoke the script anywhere using just wp-db-backup command.

Options:

  -h 	--help

  [--path=]
	Path to the WordPress installation. Required if the current directory is not a WordPress installation.

  [--type=]
	Type of backup. Default: dir. Possible values include 'dir', 'scp', 's3'.

  [--backup-dir=]
	Path to your backup directory if --type=dir.

  [--ssh=]
	Path to your remote directory if --type=scp. e.g. --ssh=user@host:/path/to/dir

  [--s3path=]
	Path to your s3 directory if --type=s3. e.g. --s3path=path/to/dir. s3:// will be prepended automatically.

  [--filename=]
	The file name for the backup file. The date name will be automatically appended.
	.e.g -.sql.gz. Default is 'database'

For detailed usage instructions, please visit the GitHub repository.

Amazon S3

If you are backing up to Amazon S3, here’s two sample IAM policies you can attach to the backup IAM user. Those are configured to be secure enough to use as a backup user policy protected from hackers. What that means is – it’s possible that your site might get hacked and if the site is compromised, even though you’re storing the backup in S3, the attacker could delete all the backups that IAM user has access to. Those policies limits every possibility of doing that. That user will only be able upload backup files, but will never be able to delete those files and access the files and folders outside of its scope. 

Let me know if you have any suggestions, and happy backing up your site! ?