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

Testing Websites In Both IE 6 and IE 7 On Linux

vim-html-code

As anyone who works with web sites knows, Internet Explorer, is not the most standards compliant browser available. It is, however, for better or for worse, one of the most widely used. The closest competitor is the firefox web browser which we strongly recommend. Firefox is generally more compliant, runs on all common operating systems, and is free.

This article discusses how to run multiple versions of Internet Explorer (IE 6, IE 7, IE 8, etc.) on the same computer – whether that computer is running Windows or Linux. This is vital for production web work as there are vast differences between versions of IE and other browsers such as firefox, opera, etc. in their interpretation of CSS markup, the use of Javascript, and the display of graphics such as png files.

To assist people developing for Microsoft products, Microsoft offers a free download of their Virtual PC software along with images for their various web browsers. This allows you to run different web browser within separate virtual environments, for free.

You will need the following products:

* Microsoft Virtual PC 2007

* Microsoft Virtual PC Images with various Web Browsers

If linux is your primary operating system, then there are a few options. We tend to use an open source virtualization process and then run the software listed above with that program. For this, you will, however, need a copy of windows. We use virtualbox by Sun Microsystems, which is available for free.

If you are testing on your localhost with a xampp installation, for example you will need to load your websites by ip, not by http://localhost – so in linux this would be ifconfig to find your current ip address, or ipconfig in windows.

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

 

Installation of Ubuntu Server on a Linode VPS With Basic Security Setup

vim-html-code

This article will cover the installation of Ubuntu Linux (7.10) on a Linode VPS with startup management tasks, ip tables firewalling, and basic server security techniques. This article assumes you are using a linux machine locally as well, but for the majority you are using a simple ssh client, web browser, and an editor – so adapt these to your local configuration.

Note: While these instructions are for a VPS within Linode, they are essentially the same for any hosting company/distribution with the exception of some differences between debian based linux systems and redhat based ones (apt-get vs. yum, etc.).

If you don’t have a dedicated server, or a VPS, we often recommend Linode as your hosting company. Their support is quite good and their offerings are well provisioned. There are a vast number of hosting companies, but in our opinion Linode is one of the best.

1. Install Ubuntu 7.10 on linode (through the dashboard system, very easy…)

2. While waiting clear your local ssh known_hosts (if necessary, reinstall, etc.)

nano ~/.ssh/known_hosts  (remove all references to vps ip)

3. ssh in as your linode account to lish on your host and then login to your linode as root or for non linode customers (or linode users alternatively)

ssh root@yourip   – then change your pass

passwd

4. Create a new account (so you are not logging in as root)

adduser username

5. Grant new user su privileges

visudo  (at the end of the file add:)

username ALL=(ALL) ALL

6. Login & Set Hostname:

/bin/hostname yourhostname.com

echo yourhostname.com > /etc/hostname

This next section will setup SSH encryption between your local computer(s) and your server. These steps essentially disallow any logins besides those coming from a machine with your SSH key. If you need to travel from computer to computer you will need to perform these steps on all the computers you use. On linode (and other hosts) you can always login through the web interface provided through the dashboard management system.

Set up correctly, these steps stop any number of security attacks from randomly guessing the root password, etc. It enables a fairly high level of initial security.

7. SSH Keygen public/private key (stop logins with just a password)

On Local Machine:

mkdir ~/.ssh

ssh-keygen -t rsa (this makes 2 files – id_rsa.pub (public key) – id_rsa (private key)

Copy public key to Linode:

scp ~/.ssh/id_rsa.pub username@ip:/home/username/

On Your Linode VPS:

mkdir /home/username/.ssh

mv /home/username/id_rsa.pub /home/username/.ssh/authorized_keys

Permissions:

chown -R username:usergroup /home/username/.ssh

chmod 700 /home/username/.ssh

chmod 600 /home/username/.ssh/authorized_keys

8. SSH config

nano /etc/ssh/sshd_config

Change SSH Port to something (i.e. 30100, 30211, anything high really)

Protocol 2

PermitRootLogin no

Unpound AuthorizedKeyFiles…

PasswordAuthentication no

X11Forwarding no

UsePAM no

UseDNS no

AllUsers username

This next section runs through a basic configuration of the IP Tables Firewall system. There are many other helper applications for establishing a firewall on a linux machine, but doing it by hand helps people to understand the concepts involved.

9. Firewall setup (iptables)

iptables-save > /etc/iptables.up.rules

iptables -L

nano /etc/iptables.test.rules config below:

Below is a basic IP Tables Configuration File that locks nearly everything down. As you add services you can open required ports for traffic, but for most people this configuration will meet all their needs.

++++Begin File++++

*filter

#  Allows all loopback (lo0) traffic and drop all traffic to 127/8 that doesn’t use lo0

-A INPUT -i lo -j ACCEPT

-A INPUT -i ! lo -d 127.0.0.0/8 -j REJECT

#  Accepts all established inbound connections

-A INPUT -m state –state ESTABLISHED,RELATED -j ACCEPT

#  Allows all outbound traffic

#  You can modify this to only allow certain traffic

-A OUTPUT -j ACCEPT

# Allows HTTP and HTTPS connections from anywhere (the normal ports for websites)

-A INPUT -p tcp –dport 80 -j ACCEPT

-A INPUT -p tcp –dport 443 -j ACCEPT

#  Allows SSH connections

#

# THE -dport NUMBER IS THE SAME ONE YOU SET UP IN THE SSHD_CONFIG FILE

#

-A INPUT -p tcp -m state –state NEW –dport 30000 -j ACCEPT

# Allow ping

-A INPUT -p icmp -m icmp –icmp-type 8 -j ACCEPT

# log iptables denied calls

-A INPUT -m limit –limit 5/min -j LOG –log-prefix “iptables denied: ” –log-level 7

# Reject all other inbound – default deny unless explicitly allowed policy

-A INPUT -j REJECT

-A FORWARD -j REJECT

COMMIT

++++End File++++

10. Store new IP Tables Information

iptables-restore > /etc/iptables.test.rules

iptables -L

iptables-save > /etc/iptables.up.rules

11. Ensure iptables loads at startup

nano /etc/network/interfaces

after iface…

pre-up iptables-restore > /etc/iptables.up.rules

12. Test connections (don’t logout yet!)

/etc/init.d/ssh reload

From a new local terminal test ssh:

ssh -P portyouset username@yourip

13. Set Locale

sudo locale-gen en_US.UTF-8

sudo /usr/sbin/update-locale LANG=en_US.UTF-8

14. Reboot and login as your newuser via the ssh command:

ssh -P portyouset username@yourip

Now that you have established your ssh keys and a connection and locked down the majority of the open ports on the computer you can setup your local environment. These can be changed according to your needs – just a basic setup that many people will find satisfactory.

15. Configure your local environment

nano ~/.bashrc

export PS1='[�33[0;32m]h[�33[0;36m] w[�33[00m]: ‘

alias dir=”ls -lartF”

alias free=”free -m”

alias update=”sudo aptitude update”

alias install=”sudo aptitude install”

alias upgrade=”sudo aptitude safe-upgrade”

alias remove=”sudo aptitude remove”

16. Get Your Ubuntu Server up to date

sudo nano /etc/apt/sources.list

enable all repositories

sudo aptitude update

sudo aptitude safe-upgrade

sudo aptitude full-upgrade

Finally install the build essentials package which has the tools necessary to install apache, mysql, and other applications you are going to install on your server.

17. Install build essentials

sudo aptitude install build-essential

Setup of Virtualbox on Linux

vim2-html

This article is a collection of helpful tips for using VirtualBox on linux. It discusses basic setup information and common problems that people have including getting USB cards to work, COM errors, permission problems, problems after upgrading kernels, and helpful command line tips. For those not familiar with VirtualBox it is a free virtualization program from Sun Microsystems available for many platforms that, on linux, will allow you to run windows and other linux distributions on one computer. This often helpful for apps like quicken and quickbooks that do not have decent linux counterparts (the last windows app for many people), and for testing websites with windows without having to do dual booting or using a different computer.

* Basic Installation Information

* Getting USB Cards to Work With VirtualBox on Linux

* VirtualBox Fails to Start After Kernel Upgrade

* Failed to Open VirtualBox COM Object Critical Error

* Slow Printing With Windows Guest

* Command Line Tips

Basic Installation Information

Installation is fairly simple on most platforms (linux, osx, windows, etc.). Go to the VirtualBox Downloads page and choose what you need. The install should go smoothly.

Getting USB Cards to Work With VirtualBox on Linux

One problem that many people have on linux is getting the USB cards recognized in VirtualBox. Starting the program as root will often help and if that works for you that is probably a fine solution. To fix it for your user account (or other users) you would do the following (adapt for whichever distribution you have – this is for red hat/fedora, but should apply to all):

1. Add a “USB” Group – Under the System Menu, go under Administration, and then choose Users and Groups. Click to add a new group and call it “usb”, then add yourself and/or any users you want to run VirtualBox to that group – also make note of the group #, you will need it for the next command.

2. Now go to a command line and edit /etc/fstab and add the following: sudo nano /etc/fstab add the following line to the end none /sys/bus/usb/drivers usbfs devgid=502,devmode=664 0 0 save, then do sudo mount -a (or just restart)

3. Insert your usb card and make sure it works within linux

4. Then start VirtualBox (Under Applications/System Tools)

5. Under the Machine Menu, choose settings and then click on the USB item on the left

6. Check the appropriate box to enable the USB controller

7. You should now be able to mount the usb device when you start your image (windows or otherwise).

8. If you want that particular usb card permanent, then within the usb tab (from above) click on the second item on the right “Add Filter From Device” – choose the name of the device and then this will automatically be mounted within your image if it is available each time.

VirtualBox Fails to Start After Kernel Upgrade

After a kernel upgrade VirtualBox will often fail to start. The fix is usually fairly straightforward and consists of typing the following two commands from the command line:
sudo yum install kernel-devel

sudo /etc/init.d/vboxdrv setup

Failed to Open VirtualBox COM Object – Critical Error

This error “Failed to Create the VirtualBox COM Object” can come about for many reasons. When you are running on linux this is usually a permissions issue that often comes out from having run VirtualBox as superuser a time or two. To fix it you can do the following commands from the terminal (also running the VirtualBox from the command line will tell you which file(s) are causing the problem).
cd ~

sudo chown -R myusername:mygroup ~/.Virtualbox

then

cd /tmp sudo -R /tmp/.vbox-yourusername-ipc

Slow Printing With Windows Guest

The default setup for running a windows guest is to use NAT for networking. This generally means that communication from your guest computer to your host is fairly slow. This often effects printing speeds, making them painfully slow. Previously you fixed this by doing a fairly complicated setup of bridges. As of the 2.1.x branches, however, you can simply select HFI for the networking type in the network setup screen for your host (within virtualbox). This will remedy the issue in most cases.

Command Line Tips

VirtualBox has a number of helpful command line tools which are particularly helpful if you are running headless servers. For the general user, however, setting up scripts to start a particular image instead of starting the program and the starting the image may be helpful. To accomplish this you can do the following:
From a terminal:

cd ~ nano startyourimage.sh

In this file add:

#!/bin/sh /usr/bin/VBoxManage startvm "imagename"

Close the file and make it executable:

chmod 755 startyourimage.sh

Then if you want to do a launcher for it you can right click on the top bar in gnome and select “add to panel”, then choose “custom application launcher”, under command tell it /home/yourusername/startyourimage.sh

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