CentOS

After setting up my automated upload to Dropbox cronjob in my last post I realized the timezone on my server was set to MSD or ‘Moscow Daylight Time’. That was causing some issues with my job since it was running at 5PM CDT instead of 2AM CDT.

[root@alderaan ~]# date
 Sat Jul 20 19:05:45 MSD 2013

No biggie. On a CentOS based system your timezone is determined by a symlink from /etc/localtime to the timezone data file appropriate for your region. Since my DotVPS server is hosted out of Chicago (CDT), you can change your timezone by creating a new symlink:

[root@alderaan ~]# ln -sf /usr/share/zoneinfo/America/Chicago /etc/localtime

Then check to make sure it worked:

[root@alderaan ~]# date
 Sat Jul 20 10:09:25 CDT 2013

Now that I have invested some time and content into my blog I decided to get serious about making sure I have a good backup system. One of the primary reasons I use Dropbox is because of its awesome cross-platform support for Windows, Mac, and Linux so I figured some genius must have already figured out how to make backups work from Linux.

I found a script called Dropbox Uploader hosted on GitHub that appeared to be exactly what I needed. Follow the instructions on the project’s site to either git clone the repo or simply (as I did) use curl to pull it down to your webserver. Once you execute the script for the first time you will be prompted to access a couple of URL’s, setup a new Dropbox app and provide authorization.

Once you have that working you can upload and download anything from the command line. You should test to make sure its working at this point since from here on we’re focusing on automation.

I created a script to prepare my backup before invoking the Dropbox uploader then clean it all up when its done. Its really a hack, there’s no error checking or backup file rotation. It will continue to backup until you run out of space on your Dropbox so keep that in mind.

Change the 3 bolded values to match your site:

create_site_backup.sh

#!/bin/bash

#Setup Environment

BACKUP_SRC=”/var/www/SITENAME
BACKUP_DST=”/tmp”
MYSQL_SERVER=”127.0.0.1″
MYSQL_USER=”root
MYSQL_PASS=”PASSWORD
NOW=$(date +”%Y.%m.%d”)
DESTFILE=”$BACKUP_DST/$NOW.tgz”

# Dump MySQL DB + Compress
mysqldump -u $MYSQL_USER -h $MYSQL_SERVER -p$MYSQL_PASS –all-databases > “$NOW-Databases.sql”
tar cfz “$DESTFILE” $BACKUP_SRC “$NOW-Databases.sql”
rm -f “$NOW-Databases.sql”

/root/bin/dropbox_uploader.sh upload “$DESTFILE”

rm -f “$DESTFILE”

Kick it off once and you should see Dropbox sync a tarball with your entire site inside:

Fullscreen capture 7192013 111405 AM

On CentOS any script in root’s /bin directory is automatically in the PATH. I put both my dropbox_upload.sh and create_site_backup.sh scripts in /root/bin.

Edit root’s crontab to kickoff the script every night at 2AM:

crontab -e

# Minute Hour Day of Month Month Day of Week Command
# (0-59) (0-23) (1-31) (1-12 or Jan-Dec) (0-6 or Sun-Sat)
0 2 * * * /root/bin/create_site_backup.sh

That’s it. Completely hands-off Dropbox backups of your self-hosted WordPress site. Keep an eye on it and stay tuned for an update on how to add some enhancements to the script.

If you followed my last post you wound up with a killer VPS running WordPress. One that crashes a little too often. Whoops. So here’s how to correct that issue.

MySQL uses InnoDB as its default Storage Engine. Well as it turns out InnoDB consumes a lot of RAM which makes it not very VPS friendly. So just disable it, right? Yeah of course not quite so easy. Since its been on from when you started setting up your wordpress DB, everything is already in InnoDB format. By switching it off, WordPress won’t be able to see the tables and will want to start over from scratch. Luckily a little Google search turned up a quick and painless way to convert between the 2 storage engine formats. Easy for you that is, I spent a good hour trying to sort this all out.

First, take this handy little php script and save it on your server:

<?php
#This script will change all the table engine types for a given database!
#All the DB tools I have (GNU/freeware) will not change a list of database
# types, so this script saves time when a CMS or other populates a database
# with tables we cannot use! This can be migrated to InnoDB by changing line
# 23, col 46 from MyISAM to InnoDB (double check the capitals there!).
# Change these variables relative: serverName, userName, password, databaseName

# 20051410 JLynch
# myisamFixer.php

ini_set(‘display_errors’, ‘On’);
error_reporting(E_ALL);

$link = mysql_connect(“localhost“,”root“,”myrootpass“)
or die(“unable to connect to msql server: ” . msql_error());

mysql_select_db(“wordpress“, $link)
or die(“unable to select database ‘db’: ” . msql_error());

$result = mysql_query(“show tables”);
if (!$result) {
die(‘query failed: ‘);
}

while ($row = mysql_fetch_array($result)){
mysql_query(“ALTER TABLE “.$row[0].” ENGINE=MyISAM; “);
#Command Reference: ALTER TABLE tableName ENGINE=MyISAM
}

?>

Ovbiously you need to change the bolded values to match your own config.

Next, stop mysql:

service mysqld stop

Execute the .php script from the command line. It might throw a couple of errors about such and such command is now deprecated, whatever bitch, it works:

php myisamFixer.php

Next, edit /etc/my.cnf. Add these 2 lines somewhere in the [mysqld] section:

ignore_builtin_innodb
default_storage_engine=MyISAM

Start mysql back up:

service mysqld start

Thats it. Back in business with all your content and settings preserved running MyISAM instead of the memory-hungry DB crashing VPS hatin nightmare InnoDB. Had I done this from the start I could have avoided the conversion.

lowendbox.com has some pretty amazing deals on VPS servers. One in particular that jumped out at me was a 128MB  / 10GB disk / 500GB bandwidth per month OpenVS server from DotVPS for only $15.00 per year.

 [OpenVZ] 128MB RAM
 128MB RAM
 128MB Burst/vSWAP
 1 vCPU core
 10GB Diskspace
 500GB Bandwidth
 OpenVZ/SolusVM
 1 IPv4 Address
 5 IPv6 Address (UK)
 1Gbit port
 $15/Year

I was skeptical about a server this small being capable of hosting a WordPress powered site, but with a few tweaks I got it working perfectly. Here is how you can do the same, save yourself a ton of cash, and learn some awesome open-source goodness in the process.

1) Register a domain name. Registering your own domain name costs almost as much as the VPS server itself but I highly recommend getting one. You can do some neat things with a virtual hosts on your VPS server, but if your just getting started and want to play around with a VPS without committing to a name yet, you can skip this step for now and come back to it later. I use and highly recommend namecheap.com for all your domain registration needs.

2) Order your VPS server. If you haven’t done so yet use this link to order your VPS from DotVPS, or a similar VPS provider. This article is based on what I was provided – a 32 bit CentOS 5.8 powered Linux server. I’ll be using yum for installing all the packages. Debian based VPS’s are going to use apt-get and probably have different package naming schemes so ymmv.

3) Login as root and get to work installing packages. This is where the fun begins. If at any point you screw up so badly and you need to start over, you can login to your control panel and hit ‘reinstall’ to start from scratch. Fire up your favorite ssh client and login to your new server using the IP address, username and password established during setup.

First off, my server had apache2 pre-installed and already running. Apache is great, but I’m working with 128MB of RAM and need to conserve as much of it as possible. For my blog, I decided to use nginx along with an alternative php processor, php-fpm. Both of these projects are focused on 2 things that are very important – high performance and low memory consumption. A winning combination for a micro VPS server. I could have used lighthttpd instead but nginx is something I had no experience with and I’m always up for learning something new.

Kill off apache with:

service httpd stop

chkconfig httpd off

Next, enable a couple of extra repositories from Remi and EPEL to get access to nginx and php-fpm:

rpm -Uvh http://dl.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpm
rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-5.rpm

Now install nginx and php-fpm with:

yum –enablerepo=remi,remi-test install php-pecl-apc php-cli php-pear php-pdo php-mysql php-pgsql php-pecl-mongo php-sqlite php-pecl-memcache php-pecl-memcached php-gd php-mbstring php-mcrypt php-xml

When its all done, fire up nginx, php-fpm, and set them to both start when the system comes up:

service nginx start
service php-fpm start
chkconfig nginx on
chkconfig php-fpm on

Yeah. Its that simple. You can fire up a web browser at this point and take a look at the default nginx welcome page using your server’s public IP address.

Now its time to install MySQL, start MySQL, and set it to start on boot:

yum –enablerepo=remi,remi-test install mysql mysql-server

service mysqld start
chkconfig mysqld on

Before you continue lets drop in a replacement mysql.conf file optimized for small memory systems:

cp /etc/my.cnf /etc/my.cnf.original

cp /usr/share/doc/mysql-server-5.5.32/my-small.cnf /etc/my.cnf

Then start the secure setup of MySQL. The default password for root is blank, so just hit enter when prompted and set a new one. You can say Y to everything else:

/usr/bin/mysql_secure_installation

Now we have nginx, php-fpm, and MySQL all setup and ready to go. Only 2 more steps remain. Setup nginx virtual-hosts and install WordPress.

Lets start with getting your virtual host setup. There are a couple of values you should define before going much further – the name of your site (most likely your domain name if you registered one), and the name of your database and user account that will connect to that database. You will want to have a new one for each virtual host you are hosting. You will also want to update the DNS A record for your domain to point to your VPS server’s IP address. When I did this with my provider, namecheap.com, the update was almost instant. When a http request is sent to your VPS, nginx looks at the host-header, looks up what virtual host is responsible for the content, and sends over the request. This makes is possible to host dozens of different websites using a single VPS server.

For my example, I’m using:

jasonoconnell.com as my domain

jasonoconnell as my web root

wordpress as my database name

wpuser as my wordpress DB username

wppass as my wordpress DB user’s password

Setup public_html and log directories for your new virtual host:

cd /var/www
mkdir -p jasonoconnell.com/{public_html,logs,stats}

Create a new config file for your virtual host:

vi /etc/nginx/conf.d/jasonoconnell.conf

Mine looks like this:

server {
listen 80;
server_name jasonoconnell.com www.jasonoconnell.com;

access_log /var/www/jasonoconnell.com/logs/access.log ;
error_log /var/www/jasonoconnell.com/logs/error.log ;

location / {
root /var/www/jasonoconnell.com/public_html;
index index.php index.html index.htm;
if (!-e $request_filename) {
rewrite ^(.*)$ /index.php?q=$1 last;
break;
}

}

error_page 500 502 503 504 /50x.html;
location = /50x.html {
root /var/www/jasonoconnell.com/public_html;
}

# pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
location ~ \.php$ {
fastcgi_pass 127.0.0.1:9000;
fastcgi_index index.php;
root /var/www/jasonoconnell.com/public_html;
fastcgi_param SCRIPT_FILENAME /var/www/jasonoconnell.com/public_html$fastcgi_script_name;
include fastcgi_params;
}

location ~ /\.ht {
deny all;
}
}

You can test if its working by dropping in an index.html file under your virtual hosts public_html directory, restarting nginx, and looking for a response. You can take that a step further and also check to make sure php is working by creating a file called info.php with the following:

<?php
phpinfo();
?>

Point your browser at domain.com/info.php and you should see a bunch of info on your php install.

Last step, install and configure WordPress.

cd /tmp

wget http://wordpress.org/latest.zip

unzip latest.zip

mv wordpress/* /var/www/example.com/public_html/

rm -rf wordpress latest.zip

Connect to MySQL and setup the DB for WordPress:

mysql -u root -p
mysql> CREATE DATABASE wordpress;

mysql> GRANT ALL PRIVILEGES ON wordpress.* TO “wpuser”@”localhost” IDENTIFIED BY “wppass”;

mysql> FLUSH PRIVILEGES;

mysql> EXIT

Now tell WordPress how to connect to your DB. You only need to modify 3 values in the config file: DB_NAME, DB_USER, and DB_PASSWORD:

cd /var/www/jasonoconnell.com/public_html

cp wp-config-sample.php wp-config.php vi wp-config.php

// ** MySQL settings – You can get this info from your web host ** // /** The name of the database for WordPress */ define(‘DB_NAME’, ‘wordpress’);
/** MySQL database username */

define(‘DB_USER’, ‘wpuser’);

/** MySQL database password */ define(‘DB_PASSWORD’, ‘wppass’);

That’s it, your done! If all went well you should be able to type in your domain name and get to your new self-hosted WordPress site. Walk through the simple setup and you can start blogging away. In my next article, I’ll walk you through setting up Google AdSense to try and make back the $30 you just spent 🙂