Backing up and restoring the WordPress database

Backing up your database:

Backing up your WordPress database is simple. The following command will make sure WordShell’s internal copy of your database is up to date (which means, that it has been sync-ed within the last 3 hours):

wordshell mysite --database --refreshcache

Add –sync to that command line  if you wish to enforce a new sync, even if the present copy was up to date.

When a new copy is saved, then the previous copy will be stored as a “rollback”. Thus you can keep versions of your database going back as far and at whatever intervals you like. You can list them like so:

wordshell mysite --database --listrollbacks

or for all sites:

wordshell all --database --listrollbacks

The version number listed when you list your “rollbacks” is the WordPress version (if known) that the database came from.

If you have a need, then database backups must be restored to a site by hand; WordShell does not yet have an option to restore to a live site. That is not hard – just take one of the rollbacks (from ~/.wordshell/old.database) or the most recent version (from ~/.wordshell/current//database) and import it using PHPMyAdmin, Adminer or the mysql command-line.

If you wish to write out your database to a file, then use this:

wordshell mysite --remotecli=dbdump >mysite-sql-28Sep2012.sql

You can use standard shell piping to immediately clone your database into a new database as follows:

echo "CREATE DATABASE mynewdatabase" | mysql

wordshell mysite --remotecli=dbdump | mysql mynewdatabase

You can easily use a cron job to schedule your backups, e.g.

wordshell all --database --refreshcache

Or an alternative approach: this command will output a quasi-SQL file, where each valid SQL file (one for each site) is separated by a “Site: (name)” header:

wordshell all --remotecli=dbdump > alldatabases-sql-28Sep2012.sql

Restoring your database

Presently the easiest way to restore your database is to import the SQL file using mysql (if you have access to the command line), or by installing something like Adminer or PHPMyAdmin if not. e.g.

mysql -h mysql.example.com -u myuser -p mydatabase < mysite-sql-28Sep2012.sql

Your backed up databases which WordShell has stored internally, if you have ever made any (using –database –refreshcache) are found in ~/.wordshell/old.database and ~/.wordshell/current//database.

Next manual section: Searching and replacing on the WordPress database
Previous manual chapter: Working with WordPress users
Back to manual index