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.)

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!

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! 😀

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

Updated Comprehensive VPS Setup Documentation

Building a VPS requires lots of complex steps, and these steps change over time.

The time has come to create comprehensive, consolidated documentation for how I setup these machines. Many of these steps are optional.

  1. Deploy A New VPS With Digital Ocean
  2. FQDN DNS Setup With Godaddy
  3. Recommended Initial Installations
  4. Setup Email Server
  5. Create a VirtualHost
  6. Setup Free SSL With LetsEncrypt
  7. Automate Database Backups
  8. Install Webmin

And then you are golden!

VPS Setup: Automated Database Backups

This is a subpost of the larger post Updated Comprehensive VPS Setup Documentation.

 

Create a new directory for the backups;

mkdir /var/backups/mysql

 

I added the following line to /etc/crontab in order to facilitate automatic database backups;
0 22 * * * root /usr/bin/mysqldump -uroot -i[MySQL Root Password] [MySQL Database Name] | gzip > /var/backups/mysql/mysql-backup-$( date +'\%Y-\%m-\%d_\%H-\%M-\%S' ).sql.gz

VPS Setup: Install Free SSL From LetsEncrypt

This is a subpost of the larger post Updated Comprehensive VPS Setup Documentation.

 

LetsEncypt allows us to setup free SSL certificates for our virtualhosts.

First, make sure you are in your root home directory “/~” and then clone the LetsEncrypt git repository;

git clone https://github.com/letsencrypt/letsencrypt

Enter the directory cd letsencrypt

And run the automatic script ./letsencrypt-auto --apache

It will ask which virtual hosts you want to install certificates for, and then it does all the work for you!

 

When you need to renew these, check out my tutorial Renewing Free LetsEncrypt SSL Certificates.

VPS Setup: Create A Virtual Host

This is a subpost of the larger post Updated Comprehensive VPS Setup Documentation.

 

Once you have your FQDN forwarded to the VPS, create a directory for it with;

mkdir /var/www/[fqdn]/

Now we make a new virtualhost conf file with this command. Again, substitute your fqdn;

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

Then edit the file with nano /etc/apache2/sites-available/[fqdn].conf

It needs to contain the following;

	ServerName [fqdn]

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

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

Activate the new virtualhost with a2ensite [fqdn] and if you haven’t already done this, deactivate the default virtualhost with a2dissite 000-default.conf

Restart apache with service apache2 restart so the changes take effect.

 

Automated Backups

If you want to setup automated backups, create a new directory for the backups;

mkdir /var/backups/[fqdn]

 

Add the following line to /etc/crontab in order to facilitate automatic daily backups;
0 22 * * * root tar -cf /var/backups/[fqdn]/www-backup-$( date +'\%Y-\%m-\%d_\%H-\%M-\%S' ).gz /var/www/[fqdn]

 

Or if you would prefer weekly updates every Sunday night, use this instead;

0 0 * * 0 root tar -cf /var/backups/[fqdn]/www-backup-$( date +'\%Y-\%m-\%d_\%H-\%M-\%S' ).gz /var/www/[fqdn]

VPS Setup: Email Server

This is a subpost of the larger post Updated Comprehensive VPS Setup Documentation.

 

Many of my apps send lots of emails, so I usually need to setup a local outbound email server.

Secure the port with iptables -A INPUT -i eth0 -j REJECT -p tcp --dport 25

Install postfix for the server apt-get -y install postfix && apt-get -y install mailutils

Now edit the config files and change the interface to loopback-only like so;

nano /etc/postfix/main.cf

Find this line;

inet_interfaces =

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;

postfix stop
postfix start