Setting Up Automated Backups for WordPress and Others to S3 with Serverpilot

vim-html-code

Serverpilot is an automated server building solution, similar to forge from the creators of laravel.  These are great solutions for those of us who want to focus on building applications and programming and not on server maintenance.  While the server setup is not difficult, particularly for one or a few sites, if you have many sites on your server and many development environments with multiple ssl certificates, etc. these tools can save a lot of time and headaches.

While this is written for serverpilot, the majority of it is applicable regardless of what solution you use to manage your server, even if you set them up yourself.  Serverpilot will also run on vultr, digital ocean, or other vps providers, so there might be some other tailoring as well. Additionally, while serverpilot is often used with WordPress sites, this script will backup all databases and all files so it will work for drupal, processwire, anything you have on your server.

While serverpilot handles many aspects of managing your web sites, it doesn’t provide any backup services.  And while, vultr and digital ocean, for example, offer server wide backups these are impractical for many usages, particularly on servers with multiple sites for different clients.

On to the steps:

  1. ssh into your server
    ssh username@servername.com
  2. su to root on your server and cd /tmp
    su -root
  3. Now download and setup the command line tools for amazon web services (AWS)
    curl "https://s3.amazonaws.com/aws-cli/awscli-bundle.zip" -o "awscli-bundle.zip"
    unzip awscli-bundle.zip ** note that on serverpilot administered servers unzip is not available by default so you will need to apt-get install unzip
    ./awscli-bundle/install -b ~/bin/aws
  4. Now you need to configure with your keys
    aws configure ** if you don’t know how to get your keys, read this.
  5. Now you should be able to do a command like aws s3 ls (dir list of s3 buckets), if you cannot, you need to give the user associated with the keys you entered in the configuration appropriate s3 permissions.

Now we are going to setup the database backup script.  In serverpilot by default you have .my.cnf file which contains your root mysql password.  This means that commands like mysqldump do not require a password if run by this user.  You can create this file manually, or better, use mysql_config_editor (5.6+) to create mylogin.cnf which hashes the password.

  1. cd ~
  2. touch sitesbackup.sh && chmod 700 sitesbackup.sh && nano sitesbackup.sh
  3. Enter the following into this file (modify as needed).  For the most part this would simple be changing the “ExcludeDatabases” line to contain any other databases you don’t want to backup.  If you only have one database you can drop all of that and just use the mysqldump command for that one database.  This script assumes you have multiple sites setup, otherwise you probably wouldn’t be using serverpilot anyhow…
    #!/bin/bash
    ExcludeDatabases="Database|information_schema|performance_schema|mysql"
    databases=`mysql -u mysqladminuser -e "SHOW DATABASES;" | tr -d "| " | egrep -v $ExcludeDatabases`
    for db in $databases; do
    echo "Dumping database: $db"
    mysqldump --add-drop-table -u mysqladminuser $db | bzip2 -c > /whereyouwantthem/backups/`date +%Y%m%d`.$db.sql.bz2
    done

Now we will open the script and clean it up a bit and add our tar backups and then copy everything off to s3.  So nano sitesbackup.sh and the entire script is below:


  1. #!/bin/bash
    ExcludeDatabases="Database|information_schema|performance_schema|mysql"
    databases=`/usr/bin/mysql -u mysqladminuser -e "SHOW DATABASES;" | tr -d "| " | /bin/egrep -v $ExcludeDatabases`
    BackUpDir="/whereyouwantthem/backups"
    TodaysDate=`date +%Y%m%d`#Setup or Clear Backups Directory
    if [ -d "$BackUpDir" ]; then
    rm $BackUpDir/*.bz2
    fiif [ ! -d "$BackUpDir" ]; then
    mkdir $BackUpDir
    fi#Dump all the Databases into Backup Directory
    for db in $databases; do
    echo "Dumping database: $db"
    /usr/bin/mysqldump --add-drop-table -u mysqladminuser $db | bzip2 -c > $BackUpDir/$TodaysDate.$db.sql.bz2
    doneExcludeDirectories="--exclude=/directory/to/exclude/*.jpg --exclude=/directory/to/exclude/logs"

    # This will copy all serverpilot user accounts and all of their files for a complete backup

    tar -cvpjf $BackUpDir/$TodaysDate.allsites.tar.bz2 $ExcludeDirectories /srv/users/

    #Copy Everything in the Directory to S3 for offsite backup
    #Occasionally you should delete some of these on aws to save space
    /usr/local/bin/aws s3 cp $BackUpDir/ s3://bucketname/$TodaysDate --recursive --include "*.bz2" --region your-region-1

  2. Then add this to the root (or admin user) crontab, crontab -e, to run, perhaps weekly on saturdays at 2am – 0 2 * * sun /root/sitesbackup.sh

That’s about it, obviously you should tailor this for you own needs and also clean up your aws buckets from time to time so that you are not paying for storage you don’t need.  I hope this is helpful for others.

Helpful SQL Queries Commands For Drupal

vim2-html

Simplifying mass updates and changes to websites is one of the main reasons to use a database driven content management system like drupal.  While drupal has a variety of helpful tools for automating tasks there are many times where it is easier and some where it is your only option to make changes to the database directly.  Below is a list of mysql commands that we use occasionally to create changes and make updates to our sites.  They are here largely for reference, please do not use them without knowing what they mean and without a backup of your database.

  • General Search and Replace Function
  • UPDATE tablename SET tablefield = replace(tablefield,”findstring”,”replacestring”) WHERE type = “x”
  • Example:  Create mass changes in drupal node body fields

    UPDATE `node_revisions` SET `body` = replace(body,’texttofind’,’texttoreplacewith’)

 

  • Create a spreadsheet of your drupal users usernames, emails and uid (we use this at times to create email newsletter subscription lists as an example)
  • This should be done in phpmyadmin so you can easily export the results in a CSV file for import, say into Mailchimp…
  • Example:  Select the desired user information from users with a specific role
  • SELECT users.name, users.uid, users.mail FROM (users LEFT JOIN users_roles ON users.uid = users_roles.uid) WHERE users_roles.rid = “3” ORDER BY users.uid

Install Or Move Drupal To A Linux VPS

vim2-html

This article covers some basic steps to move an existing installation of Drupal or to create a new installation of Drupal on a linux VPS – in this case one running Ubuntu hosted with Linode. These instructions assume some configurations that we have performed in previous articles, so your individual configuration may vary.

The related articles are our Ubuntu Setup on a Linode VPS, and our PHP, Mysql, Apache Setup on Ubuntu articles.

Your particular situation may vary, but these general steps are common to most situations. These steps are related more to creating databases, moving data around, etc. and do not cover the installation of Drupal in any depth. The Drupal guidebooks have detailed information on this, and the procedure is fairly straightforward (i.e. create the database, copy the files to your location, and edit the settings file with your db username and password).

1. Setup The MySQL Database For Your Site
sudo mysql -u root -p
CREATE DATABASE name;
GRANT ALL PRIVILEGES ON dbname.* TO "dbuser" IDENTIFIED BY "dbpass";
FLUSH PRIVILEGES;

2. Copy Your Database Files (if you are moving an existing site, otherwise the empty database is fine) and your Drupal files to your site:
scp -r -P yoursshport /home/myaccount/mysites/site1 username@yourip:~/webfiles

scp -r -P 30101 /home/myaccount/mysites/site1.sql username@yourip:~/webfiles

3. In case you don’t already have a database dump from a previous host and just for reference. Here is how you peform and database backup and restore for Drupal (and any other database)
mysqldump -u username -p database > file.sql
mysql -u username -p database < file.sql

4. Connect to your server and upload the database file you just copied over:

ssh -P yoursshport yourusername@yourip
mysql -u username -p databasename < /home/websites/site1.sql

5. Copy your web files to the appropriate directory:
cp -r /webfiles* /srv/hostname.com/public

6. Final Apache Changes for Drupal .htaccess to work
In /etc/apache2/domainname.com.conf make sure these sections match:

  <Directory /srv/domainname/public/>
                Options Indexes FollowSymLinks +Includes
                AllowOverride All
                Order allow,deny
                allow from all
  </Directory>

Drupal and other CMS’s require cron jobs to perform a range of maintenance tasks. The following sets up the crontab to run drupal cron application to perform the necessary system updated. The following lines will run it every 6 hours at 3 minutes past the hour. You can modify this according to your needs (hourly, daily, etc.).

7. Drupal Cron Setup for domain1

  sudo crontab -e
  3 */6 * * * /usr/bin/wget -O – -q http://domain1.com/cron.php
  sudo crontab -l

 

Install PHPMyAdmin On A Linux VPS

vim2-html

This article covers the basics of setting of PHPMyAdmin to manage your MySQL databases on your Ubuntu Linux machine. These instructions will also work for a variety of other linux distributions as well. This is part of our series on setting up a Linode VPS with a fully working PHP, Apache, MySQL setup with multiple domains.

The following articles may be of interest to you:
Basic Linode VPS Setup
PHP, Apache, MySQL Setup on Ubuntu
Drupal Setup on Ubuntu

1. PhpMyAdmin Setup
sudo aptitude install phpmyadmin
This sets up phpmyadmin at your default server /phpmyadmin
PhpMyAdmin uses the following components/locations by default:

/usr/share/phpmyadmin
    /etc/apache2/conf.d/phpmyadmin.conf
    /etc/phpmyadmin

Below is a minor security change to the location of phpmyadmin to offset hackers who try to break in via the interface by going to yoursite.com/phpmyadmin . This isn’t all that strong, but every little thing helps.

2. Minor PhpMyAdmin Security Change
sudo nano /etc/apache2/conf.d/phpmyadmin.conf
Change Alias /myphpalias to /usr/share/phpmyadmin
Then available at http://domainname/myphpalias instead of phpmyadmin