How To Migrate Production MySQL Database Servers

Migrating database servers in production, whether they are large or small, is a surprisingly simple process but a delicate one. There are fewer methods available as the size goes up. This is something I have been asked about by several colleagues so I decided to document my processes and best practices.

The File Method (BEST)

This assumes you can afford a few minutes of downtime, and that you are using Debian/Ubuntu or something like it, and MySQL on both servers.

Take your database application offline, and then on the origin server run this command;

/usr/bin/mysqldump -u[Username] -p[Password] [Database] > backup.sql

This will take a while and give you the sql file you need. I just moved the file to a directory on the origin server where I could wget it from the destination server. But you could also email it to yourself or use a flash drive or network share.

Now on the destination server, create a new blank database with the same name and run this command in the same directory as that file;

mysql -u[Username] -p [Database]< backup.sql

You will be prompted for the new server’s password and then it will put all the content into the new database.

This is not a complex process, but it is a powerful one because it works no matter how large and complex the database is. I recently used this for a database containing millions of rows in dozens of tables.

PHPMyAdmin Method (Easiest)

If your database is just a few megabytes or less, you can use PHPMyAdmin to transfer everything to a new server.

On the origin server, navigate to the database you want to migrate, and click the “Export” tab at the top.

Select “Custom” for the export option, and then “View Output as Text” and submit.

This will take a while and give you a text box full of SQL code. This is the same code that would be in the file we generate in the “File Method” mentioned above. If the database is too large, this step could freeze or crash the browser, but for smaller databases, this should work fine.

Now copy that text and navigate to the destination server’s PHPMyAdmin installation. Create a database with the same name, and navigate to the SQL tab within that database. Paste the code there and click “Go.”

You have successfully migrated your database!

The Replication Method (Hardest)

Digital Ocean has a great tutorial on this alternative option, but it is a lot more technically complex. I would not advise this unless 100% uptime is critical. Using the file method will only give you a few minutes of downtime, and if you can’t afford that you probably shouldn’t need this tutorial ;P

If 100% uptime is critical, use this tutorial to set up slave replication to the new server, then switch the application load over to that server, and disable slave replication.

You have now migrated production servers with zero downtime! (But really, you should use the File method. It is much less complex and far easier to implement.)

Startup 2: RSI Alert

This is part of a series on Building 12 Startups in 12 Months.

This is product number two: RSIAlert.com!

What Inspired This Project?

I follow a few dozen stocks and do some day trading in my spare time. Working on a previous project Securities.Science, I did some research into strategies using RSI to decide when to buy and sell stocks. I got some feedback from the first users of that project about how they would like to be able to receive email alerts at certain indicator points.

For example, one simulation on Securities.Science explored trading based on the RSI-14, or the RSI for the previous 14 trading days. Whenever the RSI-14 of a stock is below 30, the simulation buys, and then sells at close on the same day. Run against the previous year’s data, this simulation indicated a 136% return. This number could easily be improved upon by selling at a better point than close, but that’s another story.

Initially, I explored trying to add email alerts to queries inside Securities.Science, but it really isn’t set up to work that way. Users construct arbitrary datasets which would be difficult to integrate into a mail trigger system, and there is obviously potential risk of abuse with automated outbound emails. I decided to build a new product which focuses on only this one type of automated email.

This product is very simple compared to the other ones I am considering for this challenge. It just shows a list of a few high-return securities and their RSI-14 as of the previous close. Users can sign up to receive email alerts each day letting them know when the RSI-14 of any of the securities is below 30.

What Exactly is RSI?

From Investopedia:

“The relative strength index (RSI) is a momentum indicator developed by noted technical analyst Welles Wilder, that compares the magnitude of recent gains and losses over a specified time period to measure speed and change of price movements of a security. It is primarily used to attempt to identify overbought or oversold conditions in the trading of an asset…

The RSI provides a relative evaluation of the strength of a security’s recent price performance, thus making it a momentum indicator. RSI values range from 0 to 100. The default time frame for comparing up periods to down periods is 14, as in 14 trading days…

Traditional interpretation and usage of the RSI is that RSI values of 70 or above indicate that a security is becoming overbought or overvalued, and therefore may be primed for a trend reversal or corrective pullback in price. On the other side of RSI values, an RSI reading of 30 or below is commonly interpreted as indicating an oversold or undervalued condition that may signal a trend change or corrective price reversal to the upside.”

Straightforward Monetization

Monetization will be very straightforward; ads on the site and maybe in emails. This project also has the potential to expand into other verticals for various other things people may want automated emails about.

Lack of Similar Products
Means Competitive Advantage

As far as I am aware, there is no other product which does what this does, or I would be using that myself rather than building this.

A few companies have put together similar models for other topics, like Medium which offers a daily email containing some stories they have picked for you to read.

I was also partially inspired by IFTTT which allows you to set up automated emails for lots of different things, but it all requires domain expertise and setting it up involves some degree of technical complexity. This product and any future expansion is designed to be idiot-proof, with a broad market in mind.

Future Features

One obvious next step would be to integrate this with my upcoming current-events project; automatically including relevant news content related to these data would be valuable data for users to analyze along with the RSI-14 data.

Setting Up PHP Apache2 MySQL MSSQL Server on Azure

First, update the packages;

sudo apt-get update && sudo apt-get upgrade

Set up initial applications;

sudo apt-get -y install unzip fail2ban apache2 mysql-server php5 php5-curl php-pear php5-mysql php5-mcrypt screenfetch htop nload curl git ntp freetds-common freetds-bin unixodbc php5-sybase && sudo php5enmod mcrypt && sudo a2enmod rewrite && sudo service apache2 restart && sudo mysql_secure_installation

Set Up SMTP Email with Postfix

Create firewall rule to block smtp access (This is redundant because we will configure SMTP for loopback access only);

sudo iptables -A INPUT -i eth0 -j REJECT -p tcp –dport 25

Install Postfix;

sudo apt-get -y install postfix && sudo apt-get -y install mailutils

Edit postfix config file;

sudo nano /etc/postfix/main.cf

Change “inet_interfaces = all” to “inet_interfaces = 127.0.0.1” allowing only loopback requests. This is in addition to the firewall which prevents outside access.

Edit aliases list;

sudo nano /etc/aliases

Append this to the end and save;

root email@domain.com

Run this to apply the changes;

sudo newaliases && sudo postfix stop && sudo postfix start

Edit the default virtualhost

sudo nano /etc/apache2/sites-available/000-default.conf

Set the ServerName to the fqdn. Save and restart apache2;

sudo service apache2 restart

Edit aptitude’s sources list;

sudo nano /etc/apt/sources.list

 

Set Up LetsEncrypt for SSL/HTTPS

Install LetsEncrypt Certbot;

sudo apt-get install python-certbot-apache -t jessie-backports

(This may require extra steps. The Debian default aptitude sources list does not contain backports, but the default Azure list does.)

Run Certbot to install HTTPS;

sudo apt-get install python-certbot-apache -t jessie-backports

Create a credential set with some high-entropy username and password combination. I like to use a 32-bit random key for both. This will only be transmitted through an SSL 1.2 connection with LetsEncrypt, so it’s very safe;

sudo htpasswd -c /etc/apache2/.htpasswd [Username]

Edit the ssl-virtualhost and add this within the virtualhost tag;

<Directory “/var/www/”>
AuthType Basic
AuthName “Restricted Content”
AuthUserFile /etc/apache2/.htpasswd
Require valid-user
</Directory>

Add this to the end of the end of the file, outside the virtualhost tag in order to enable htaccess if you’re going to need that;

<Directory /var/www/>
Options Indexes FollowSymLinks
AllowOverride All
Require all granted
</Directory>

Restart Apache;

sudo service apache2 restart

Set Up PHPMyAdmin

PHPMyAdmin can also be installed via aptitude, but that exposes it publically and there is some potential for exploits down the road. This way you can;t access it until you get past the virtualhost password we set up earlier.

Head over to PHPMyAdmin and clone the current version into the /var/www directory;

sudo wget https://files.phpmyadmin.net/phpMyAdmin/[Version]/phpMyAdmin-[Version]-all-languages.zip

Unzip it;

sudo unzip phpMyAdmin-[Version]-all-languages.zip

PHPMyAdmin It will prompt you for a blowfish secret. Navigate to its directory and copy the sample config file;

sudo cp config.sample.inc.php config.inc.php

Open the new file and look for this line…

$cfg[‘blowfish_secret’] = ”;

I wrote a tool which comes up with a perfectly sized high-entropy string to put here. Check it out.

Once that is entered, navigate to the sql/ directory within the PHPMyAdmin folder and run this to set the tables up. It will prompt you for the password you set up earlier;

sudo mysql -u root -p < create_tables.sql

I Made This Simple Stats Tool

VPS-Home is a simple tool I made some time ago which shows a few important things. It shows the free space on the disk, the disk utilization for each directory within /var/www and the top running processes at the moment, along with the runtime and motd.

Install it by simply downloading it into the virtualhost we made;

sudo wget https://raw.githubusercontent.com/cjtrowbridge/vps-home/master/index.php

Connect to MSSQL

We installed FreeTDS which allows for tabular data-stream connections to Microsoft SQL Server from PHP5.

Test it with this command;

tsql -H [host/ip] -p [port] -U [username] -P [password] -D [database]

You should see something like this;

locale is “en_US.UTF-8”
locale charset is “UTF-8”
using default charset “UTF-8”
Default database being set to [database]
1>

If you see something about “Unable to connect: Adaptive Server is unavailable or does not exist” that is ok too. Edit /etc/freetds/freetds.conf and add this to the end;

[nickname]
host = [host/ip]
port = [port]
tds version = 7.0

Your version may vary. For MS SQL Server 2008, this was the version I used.

 

Now you can use mssql_query() in PHP5

to build server applications with Microsoft SQL Server!

Startup 1: Securities Science

This is part of a series on Building 12 Startups in 12 Months.

This is number one: Securities.Science!

What Inspired This Project?

My first startup in the series is Securities.Science. It lets users run queries against historic stock trading data in order to test theories and strategies. All data is public and everyone can see the work that others are doing.

This started with my coworker Luke Leggio and I trying to collaborate on developing strategies for trading leveraged commodity ETFs on RobinHood. I was very frustrated with the few tools and communities that exist for this purpose.

I had tried Openfolio which has since pivoted to a totally different kind of product. At the time, they let you share your trading activity and results with others and compare to how their strategies worked out for them. The problem was that it was terribly buggy and often reported things incorrectly. I wrote to their support people several times, even offering to do the work of fixing their products for them because the problems were so obvious. (Numbers being negative instead of positive when pulled from certain APIs, etc.) Some features like search and viewing the top performers didn’t work at all. They had no interest in making their product work, so I decided to make my own as an alternative.

Securities.Science automatically pulls data from various public APIs and allow users to write SQL queries that implement securities trading strategies. Their queries will pair with simple visualization tools in order to show how each strategy works over time.

First Steps

The site is now live, and the source code is all available on Github. Anyone can sign up for free and start running queries against historic datasets.

I have included lots of different tickers including all of the leveraged commodity ETFs which I follow, along with all the top stocks millennials like according to Business Insider. Adding more is trivially easy, but I didn’t want to just add thousands of tickers because of the maintenance overhead. And because most of them are not particularly interesting.

I wrote this as a plugin for Astria, a simple web application framework I have been developing for almost a decade. The code is very simple and hopefully distilled to the minimum necessary to explain the content. Check it out!

Next Steps

There are a few next steps that jump out at me if this finds adoption.

Expanded Datasets

The page describing available data encourages the user to reach out to me if they want to see any additional data sources. Eventually, users should be able to add data sources for whatever they want with simple tools.

Content Development

Scraping and collating data is one thing, but presenting it in a format which brings in organic traffic is a separate art. Other news and data sources relating to each stock could be integrated so that users can focus on particular industries, commodities, or ETFs and get more information than just trading data.

Execution Integration

There are lots of great APIs which would allow integration with stock brokerages so that users can set up triggers for buying and selling based on their models in the app. It would be fun to add that later.

Machine Learning and Other Advanced Analytics

The first version of the product only features SQL queries for strategy development. This enables lots of interesting and basic strategies to be implemented and tested, but adding machine learning and other advanced analytics features would be another order of magnitude in capability for users.

 

Getting Started With Golang

I went to a Software Engineering Daily meetup a couple days ago and spoke with several CEOs whose companies are focused on data science and machine learning. I asked about what languages they are looking for in new hires. These conversations cemented my desire to learn Golang in conjunction with TensorFlow as my next major engineering paradigm.

“Hello, World!”

I started with a new Debian droplet at Digital Ocean (Referal Link) and followed this tutorial from Digital Ocean to set up the server with the latest version of Golang and print out my first “Hello, World.”

Next Steps

Golang’s site has a tutorial called Writing Web Applications which seems fairly comprehensive. I am going to work through this tutorial and then get started on the TensorFlow tutorials.

Implementing the Webroot SecureAnywhere Business Endpoint Protection API in PHP

At Tech 2U, we sell Webroot SecureAnywhere Business Endpoint Protection as our antivirus product. This is typically used for managed enterprise endpoints.

Webroot SecureAnywhere Business Endpoint Protection is managed through a web console which must be accessed each time a new endpoint is created; dozens of times every day for us. This web console is intended to be used for companies managing dozens or maybe a hundred endpoints. We use it to manage many thousands of endpoints. This quickly led to the web console being so slow and unresponsive that it became unusable, taking minutes to load each page. I decided to implement their API in order to avoid using the web console and automate the process of creating keys.

I had already built a comprehensive custom PHP/MySQL CRM to manage all operations at Tech 2U, so this new API integration would need to simply create keys whenever they are sold and show them to the person selling them.

I came up with this: PHP-WebrootAPI.

It gives you one main function: MakeWebrootKey(); This is pretty straightforward and allows you to create keys by passing in the customer’s information. The keys are then stored in a local table and accessible from the customer’s profile page, or by searching.

Getting this API implemented was very tricky because their documentation is terrible and they don’t respond to tickets. I ended up combing through their web console’s html to find many of the missing pieces. This is the only way I could find to get the GSM key IDs, Policy IDs and some of the other credentials. Once I had all of those though, implementing the rest of the API fell into place.

Check it out and email me if you need help or have any questions!

 

There is a tremendous amount of customer information in this API and I can’t wait to integrate it into my marketing automation platform! 😀

Raspberry Pi 3 NAS: Debian, Apache, Samba, BitTorrent Sync, BitTorrent [In-Progress]

NAS stands for Network Accessible Storage, but in this post we are taking it to the next level!

I will show you how to build a Raspberry Pi 3 NAS with a few extra features:

  • Using Debian as the OS will be easy to secure and light on resources, but also Debian runs on anything, so almost all of these steps will work on other hardware as well. If you do not want to use a Pi, skip to the “Setting up Networking” step.
  • Apache will share files over the web, and secure them with a password.
  • Samba shares files over the local network.
  • BitTorrent Sync automatically backs up of all my devices in real time.
  • BitTorrent will allow me to remotely download files to my NAS and then access them. This means I can download large or slow files from my phone, or while my laptop is off.

The Hardware

Debian famously runs on anything, so probably any hardware will work, but I wanted to do this with a Raspberry Pi 3 so I picked one up along with a few accessories;

  • Raspberry Pi 3 Model B ($39 on Amazon)
  • Clear acrylic case This is probably unnecessary, but it seemed like a good precaution to protect the Pi and the surfaces it sits on. ($6.95 on Amazon)
  • Touchscreen for the Pi ($21.99 on Amazon) This was kind of a nightmare to set up. I would skip this unless you really want it.

I already had these other three items, but here they are if you want the same ones!

Installing the OS

Debian runs on anything, but it needs to know how. The Raspberry Pi uses a lot of unusual hardware which is not supported by mainstream Debian, so Raspberry Pi have come out with several customized versions which have support for all their hardware.

I decided to use the most lightweight version they offer, Raspbian Jessie Lite. Ideally, I would like to star with just pure Debian, but that would involve building a kernel and that doesn’t sound like fun.

Head over to the official site or click here to get the torrent for Raspbian Jessie.

Now head over to Sourceforge and download Win32DiskImager.

Use that to write the image from the torrent onto the SD card.

If You Decided To Get That Touchscreen…

Like I said, setting it up is a nightmare. I got the “Kuman 3.5 Inch 480×320 TFT Touch Screen” and eventually figured out how to set it up by reading through dozens of angry Amazon comments until I pieced this together.

Open the sd card you wrote the image to. Edit the file “/boot/cmdline.txt” and change its contents to… (All one line)

dwc_otg.lpm_enable=0 console=tty1 console=ttyAMA0,115200 root=/dev/mmcblk0p2 rootfstype=ext4 elevator=deadline rootwait fbcon=map:10 fbcon=font:ProFont6x11 logo.nologo

Now, edit “/boot/config.txt” and add onto the end…

dtparam=audio=on
dtparam=spi=on
dtoverlay=ads7846,penirq=25,penirq_pull=2,xohms=150,swapxy=1,xmin=300,ymin=700,xmax=3800,ymax=3400,pmax=255
dtoverlay=waveshare35a

Go to this GitHub and download the file “waveshare35a.dtb.”

Rename it to “waveshare35a.dtbo” and put it in the directory “/boot/overlays”

That’s all it takes! ;P

Setting Up Networking

Now you will need to eject the sd card and boot up the pi with the screen attached, as well as a usb keyboard.

The default username is pi, and the default password is raspberry.

In order to connect the Pi to Wifi, type “sudo nano /etc/wpa_supplicant/wpa_supplicant.conf” and add the following…

network={
  ssid="YOUR SSID"
  psk="YOUR PSK"
}

Save that and exit.

Now lets set a static IP.

Select an IP outside the range of your router’s DHCP and then type “sudo nano /etc/network/interfaces”. Find the line that says “iface wlan0 inet manual” and replace it like this…

iface wlan0 inet static
        address 192.168.1.200
        netmask 255.255.255.0
        gateway 192.168.1.1

Leave everything before and after that line we replaced, but all all this stuff in place of that line.

Last but not least, run “sudo raspi-config”.

Under boot options, set Desktop/CLI to “console autologin”

Under advanced options, enable ssh server.

Now exit raspi-config and reboot with “sudo reboot.” You can disconnect the keyboard. We will no longer need it. This is a good time to move the Pi to its eventual destination if you would like.

Remote Access

Install Putty or another SSH client and use it to connect to the static IP you just set up.

In order to use SSL, we will need to add another repository to the package manager. Type “sudo nano /etc/apt/sources.list” and add…

deb http://ftp.debian.org/debian jessie-backports main

Now we need to make sure everything is up to date with “sudo apt-get update && sudo apt-get upgrade”

Now let’s install all the things we will need:

sudo apt-get install fail2ban apache2 screenfetch htop nload python-certbot-apache -t jessie-backports && sudo a2enmod rewrite && sudo service apache2 restart

This will install the following:

  • Fail2Ban: Prevents bruteforce ssh attacks.
  • Apache2: Shares files over the web, securely.
  • Screenfetch: Let’s us share sweet screenshots.
  • Htop: Shows resource usage in case there is a problem.
  • Nload: Shows a graphical representation of real-time network usage. Good ambient display.
  • Certbot: Gives us free SSL through LetsEncrypt.

Set Up The Web Server

Change directory to the new webroot with “cd /var/www” and remove the default files with “rmdir html –ignore-fail-on-non-empty”

Edit the default VirtualHost configuration file, “sudo nano /etc/apache2/sites-available/000-default.conf”.

Change the line “DocumentRoot /var/www/html” to “DocumentRoot /var/www” and save the file.

 

Wifi Pentesting on a Raspberry Pi [In-Progress]

I want to expand on these resources to create a good post about wifi pentestint on Raspberry Pi. I will also include Wifite which has made the process really easy for me.

 

http://www.catonrug.net/2014/05/prepare-reaver-for-raspberry-pi.html

http://lifehacker.com/5873407/how-to-crack-a-wi-fi-networks-wpa-password-with-reaver

http://askubuntu.com/questions/362146/how-to-install-cowpatty

Building a Debian Application Server with PHP 7 + MySQL 5.7 + MS SQL

One of the applications I am building integrates with a legacy software platform including MS SQL Server. In past versions of PHP, there was a simple and free tool called FreeTDS which enabled PHP to connect to MS SQL Server, but this has been deprecated as of PHP 7.

In order to get all the new features, performance increases, and security improvements that come with PHP 7, we need to find an alternative to FreeTDS. Oddly enough, Microsoft has released an official replacement! 😀
First, I created a droplet with Digital Ocean (Referral Link) and give it 1GB RAM.

Now, the default apt list needs to be expanded;

nano /etc/apt/sources.list

Add these sources…

deb http://httpredir.debian.org/debian jessie main contrib non-free
deb-src http://httpredir.debian.org/debian jessie main contrib non-free

deb http://httpredir.debian.org/debian jessie-updates main contrib non-free
deb-src http://httpredir.debian.org/debian jessie-updates main contrib non-free

deb http://ftp.debian.org/debian jessie-backports main

deb http://repo.mysql.com/apt/debian/ jessie mysql-apt-config
deb http://repo.mysql.com/apt/debian/ jessie mysql-5.7
deb http://repo.mysql.com/apt/debian/ jessie mysql-tools
deb-src http://repo.mysql.com/apt/debian/ jessie mysql-5.7

deb http://packages.dotdeb.org jessie all
deb-src http://packages.dotdeb.org jessie all

Now lets install the GPG key for DotDeb and MySQL so we can install their packages…

wget https://www.dotdeb.org/dotdeb.gpg && apt-key add dotdeb.gpg &&
gpg --keyserver pgpkeys.mit.edu --recv-key  8C718D3B5072E1F5 &&
gpg -a --export 8C718D3B5072E1F5 | apt-key add -

Update our sources and run any available upgrades;

apt-get update && apt-get upgrade

Add a firewall rule to prevent unwanted sending of outbound mail.

iptables -A INPUT -i eth0 -j REJECT -p tcp --dport 25

Now run this command to install PHP7 and MySQL 5.7;

apt-get -y install fail2ban apache2 && apt-get install php7.0 php7.0-fpm php-pear php7.0-dev php7.0-mysql mcrypt php7.0-mcrypt php-mbstring php7.0-mbstring libapache2-mod-php7.0 php7.0-curl php7.0-xml screenfetch htop nload curl git unzip ntp mcrypt postfix mailutils php7.0-memcached mysql-server apt-transport-https && apt-get install python-certbot-apache -t jessie-backports && a2enmod rewrite && service apache2 restart && mysql_secure_installation

You will be prompted to create a new root password for the mysql installation, and then give that password to mysql_secure_installation so it can run.

Setup Postfix Mail Server

Now edit the config files and change the interface to loopback-only like so. We already set up a firewall rule to block connections to port 25, but those rules can be changed by mistake, so this will be a good second line of defense to prevent public access to sending mail through our server, while allowing us to still use it locally.

nano /etc/postfix/main.cf

Find this line;

inet_interfaces = all

And change to;

inet_interfaces = 127.0.0.1

Now edit the email aliases;

nano /etc/aliases

At the end of the file, make sure there is a line that starts with root and ends with your email, like so;

root: email@domain.com

Save the file and exit. Then run newaliases to let Postfix apply the changes. Restarting Postfix is not enough because we changed the interfaces line in the config file. We need to stop and start it like so;

newaliases && postfix stop && postfix start

 

Creating Two VirtualHosts

First, we need to forward an A-Record from our DNS provider over to the public IP of our new server.

We will need to do the following steps twice: once for the fqdn of the machine, and once for the fqdn of the application we are serving. I like to set the machine’s virtualhost to use /var/www and then put the other virtualhosts in directories inside there, to make them easy to access.

First disable the default VirtualHost.

a2dissite 000-default.conf

Create a directory for our new FQDN.

mkdir /var/www/[fqdn]/

Create a new VirtualHost for our new FQDN.

cp /etc/apache2/sites-available/000-default.conf /etc/apache2/sites-available/[fqdn].conf

Edit the virtual host and make sure it has all of this;

nano /etc/apache2/sites-available/[fqdn].conf
ServerName [fqdn]

ServerAdmin your_email@website.com
DocumentRoot /var/www/[fqdn]/

ErrorLog ${APACHE_LOG_DIR}/error.log
CustomLog ${APACHE_LOG_DIR}/access.log combined

Now enable the virtualhost and restart apache.

a2ensite [fqdn] && service apache2 restart

Securing the Machine’s FQDN VirtualHost

Create a password file for the VirtualHost. Pick a high entropy username and password.

htpasswd -c /etc/apache2/.htpasswd [username]

Now add this to the VirtualHost for the machine’s FQDN.

<Directory "/var/www/">
      AuthType Basic
      AuthName "Restricted Content"
      AuthUserFile /etc/apache2/.htpasswd
      Require valid-user
  </Directory>

Restart Apache to make the changes take effect.

service apache2 restart

PHPMyAdmin

Navigate to the machine’s webroot.

cd /var/www/[FQDN]

Download PHPMyAdmimn.

wget https://files.phpmyadmin.net/phpMyAdmin/[version]/phpMyAdmin-[version]-all-languages.zip

Unzip it into a new directory in the current directory.

unzip phpMyAdmin-[version]-all-languages.zip -d .

Troubleshooting

For basic troubleshooting and performance monitoring, I wrote a simple tool to see the output of a few simple cli tools. It also includes a directory listing. So it’s essentially just a better index file for the vps. Try it out if you like.

wget https://raw.githubusercontent.com/cjtrowbridge/vps-home/master/index.php

Using LetsEncrypt for Free SSL

We already added the repository we need, and we installed the Certbot to take care of our certificates, so now let’s run Certbot to setup SSL for our VirtualHosts.

certbot --apache

Installing MS SQL For PHP

First install the tools.

curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
apt-get update
ACCEPT_EULA=Y apt-get install msodbcsql mssql-tools unixodbc-dev

Then install the drivers

pecl install sqlsrv
pecl install pdo_sqlsrv

Add the newly installed tools into the PHP configuration file.

echo "extension=/usr/lib/php/20151012/sqlsrv.so" >> /etc/php/7.0/apache2/php.ini
echo "extension=/usr/lib/php/20151012/pdo_sqlsrv.so" >> /etc/php/7.0/apache2/php.ini
echo "extension=/usr/lib/php/20151012/sqlsrv.so" >> /etc/php/7.0/cli/php.ini
echo "extension=/usr/lib/php/20151012/pdo_sqlsrv.so" >> /etc/php/7.0/cli/php.ini

Restart Apache and you’re ready to go!

service apache2 restart

Setting Up a Debian Server with PHP 7 and MySQL 5.7

First, I created a droplet with Digital Ocean (Referral Link) and give it 1GB RAM.

Now, the default apt list needs to be expanded;

nano /etc/apt/sources.list

Add these sources…

deb http://httpredir.debian.org/debian jessie main contrib non-free
deb-src http://httpredir.debian.org/debian jessie main contrib non-free

deb http://httpredir.debian.org/debian jessie-updates main contrib non-free
deb-src http://httpredir.debian.org/debian jessie-updates main contrib non-free

deb http://ftp.debian.org/debian jessie-backports main

deb http://repo.mysql.com/apt/debian/ jessie mysql-apt-config
deb http://repo.mysql.com/apt/debian/ jessie mysql-5.7
deb http://repo.mysql.com/apt/debian/ jessie mysql-tools
deb-src http://repo.mysql.com/apt/debian/ jessie mysql-5.7

deb http://packages.dotdeb.org jessie all

Now lets install the GPG key for dotdeb and MySQL so we can install their packages…

wget https://www.dotdeb.org/dotdeb.gpg && apt-key add dotdeb.gpg &&
gpg --keyserver pgpkeys.mit.edu --recv-key  8C718D3B5072E1F5 &&
gpg -a --export 8C718D3B5072E1F5 | sudo apt-key add -

Update our sources and run any available upgrades;

apt-get update && apt-get upgrade

Add a firewall rule to prevent unwanted sending of outbound mail.

iptables -A INPUT -i eth0 -j REJECT -p tcp --dport 25

Now run this command to install PHP7 and MySQL 5.7;

apt-get -y install fail2ban apache2 && apt-get install php7.0 php-pear php7.0-mysql php7.0-mcrypt php7.0-mbstring libapache2-mod-php7.0 php7.0-curl screenfetch htop nload curl git unzip ntp mcrypt postfix mailutils php7.0-memcached mysql-server && apt-get install python-certbot-apache -t jessie-backports && a2enmod rewrite && service apache2 restart && mysql_secure_installation

You will be prompted to create a new root password for the mysql installation, and then give that password to mysql_secure_installation so it can run.

Setup Postfix Mail Server

Now edit the config files and change the interface to loopback-only like so. We already set up a firewall rule to block connections to port 25, but those rules can be changed by mistake, so this will be a good second line of defense to prevent public access to sending mail through our server, while allowing us to still use it locally.

nano /etc/postfix/main.cf

Find this line;

inet_interfaces = all

And change to;

inet_interfaces = 127.0.0.1

Now edit the email aliases;

nano /etc/aliases

At the end of the file, make sure there is a line that starts with root and ends with your email, like so;

root: email@domain.com

Save the file and exit. Then run newaliases to let Postfix apply the changes.

newaliases

Restarting Postfix is not enough because we changed the interfaces line in the config file. We need to stop and start it like so;

postfix stop
postfix start

Creating Two VirtualHosts

First, we need to forward an A-Record from our DNS provider over to the public IP of our new server.

We will need to do the following steps twice: once for the fqdn of the machine, and once for the fqdn of the application we are serving. I like to set the machine’s virtualhost to use /var/www and then put the other virtualhosts in directories inside there, to make them easy to access.

First disable the default VirtualHost.

a2dissite 000-default.conf

Create a directory for our new FQDN.

mkdir /var/www/[fqdn]/

Create a new VirtualHost for our new FQDN.

cp /etc/apache2/sites-available/000-default.conf /etc/apache2/sites-available/[fqdn].conf

Edit the virtual host and make sure it has all of this;

nano /etc/apache2/sites-available/[fqdn].conf
ServerName [fqdn]

ServerAdmin your_email@website.com
DocumentRoot /var/www/[fqdn]/

ErrorLog ${APACHE_LOG_DIR}/error.log
CustomLog ${APACHE_LOG_DIR}/access.log combined

Now enable the virtualhost and restart apache.

a2ensite [fqdn] && service apache2 restart

Securing the Machine’s FQDN VirtualHost

Create a password file for the VirtualHost. Pick a high entropy username and password.

htpasswd -c /etc/apache2/.htpasswd [username]

Now add this to the VirtualHost for the machine’s FQDN.

<Directory "/var/www/">
      AuthType Basic
      AuthName "Restricted Content"
      AuthUserFile /etc/apache2/.htpasswd
      Require valid-user
  </Directory>

Restart Apache to make the changes take effect.

service apache2 restart

PHPMyAdmin

Navigate to the machine’s webroot.

cd /var/www/[FQDN]

Download PHPMyAdmimn.

wget https://files.phpmyadmin.net/phpMyAdmin/[version]/phpMyAdmin-[version]-all-languages.zip

Unzip it into a new directory in the current directory.

unzip phpMyAdmin-[version]-all-languages.zip -d .

Troubleshooting

For basic troubleshooting and performance monitoring, I wrote a simple tool to see the output of a few simple cli tools. It also includes a directory listing. So it’s essentially just a better index file for the vps. Try it out if you like.

wget https://raw.githubusercontent.com/cjtrowbridge/vps-home/master/index.php

Using LetsEncrypt for Free SSL

We already added the repository we need, and we installed the Certbot to take care of our certificates, so now let’s run Certbot to setup SSL for our VirtualHosts.

certbot --apache