Three survival tools for an inhospitable (production) environment

As a web developer, you may need to deal with your client’s production environment. Sometimes it’s not the easiest environment to work with, like in a shared hosting or a limited

I already worked with some clients that I wasn’t able to remote connect to the database. Others didn’t allow me to connect to a regular FTP service, all files had to be transferred one by one via a Web FTP.

Over the years I’ve put together some tools to help me with those situations.Other than small snippets and scripts, here are three of the tools that I must used in the last eight years.

Adminer (formerly phpMinAdmin)

Adminer is a single file database management tool written in PHP that can handle MySQL, PostgreSQL, SQLite, MS SQL, Oracle, Firebird, SimpleDB, Elasticsearch and MongoDB. Useful since most of the free database GUI tools doesn’t offer Tunneling connections.

MySQL BigDump

BigDump allows you to import massive and large MySQL dumps. It works well very limited servers.

phpFileManager

phpFileManager can help you when you don’t have FTP access to the hosting. It’s multi-uploader and compress/extract are very convenient.

Conclusion

I know that the use of such hostings are decreasingly over the years. But we, as developers, must be prepared to such sittuations. I hope my tips here can help you someway.

Running MariaDB/MySQL on small VPS

I had my databases down twice in a month with the following error message:

[ERROR] mysqld: Out of memory

Compared to shared hosting services that we used to have, the tiny and cheap cloud VPSs are amazing. I am a user and big fan of the service as I don’t have any personal projects that require AWS by now.

These servers are convenient and comfortable as you can customize, install and configure as you wish. When everything is to your liking you can take snapshots as backup or to create duplicates of your server.

The only problem is that some of the softwares needed to run a fully web server comes with predefined configuration files. They run fine on their own but you need to take in mind that your VPS have a limited RAM to work with. MySQL and MariaDB by default will allocate around 400MB, which is reasonable to a normal server, but not to a limited virtual instance running 24/7.

I will comment on the measures it took to get around this problem. As usual the following steps are for Ubuntu 14.04, your steps may vary depending on your distro and version. You may find a my.cnf over the internet that fits your server’s specifications.

Disable Performance Schema

In my opinion, performance schema only has a use if you or a DBA wants to monitor the database activity. To disable it edit the default MySQL configuration file, in this case /etc/mysql/my.cnf, and edit the following line under the [mysqld] block (if it doesn’t exist, create the line).

performance_schema = off

MyISAM tables

InnoDB tables have a huge impact in performance and memory usage, if you don’t use its resources you may consider to use MyISAM. To do so you need edit the following variables under the [mysqld] block of the /etc/mysql/my.cnf file:

default-storage-engine=MyISAM
default-tmp-storage-engine=MyISAM

If you don’t need InnoDB resources at all you can disable it adding the following line to your my.cnf:

skip-innodb

You may need to change your existing tables to MyISAM. The following ShellScript can do that for you.

#!/bin/bash

MYSQLCMD=mysql

for db in `echo show databases | $MYSQLCMD | grep -v Database`; do
        for table in `echo show tables | $MYSQLCMD $db | grep -v Tables_in_`; do
                TABLE_TYPE=`echo show create table $table | $MYSQLCMD $db | sed -e's/.*ENGINE=\([[:alnum:]\]\+\)[[:space:]].*/\1/'|grep -v 'Create Table'`
                if [ $TABLE_TYPE = "InnoDB" ] ; then
                        mysqldump $db $table > $db.$table.sql
                        echo "ALTER TABLE $table ENGINE = MyISAM" | $MYSQLCMD $db
                fi
        done
done

Swap

Swap isn’t recommended, at least by DigitalOcean that uses SSDs, but I use it as a fail-safe measure. The following commands will create a swapfile and use it as swap space in your machine, run as root:

fallocate -l 256M /swapfile
chmod 600 /swapfile
mkswap /swapfile
swapon /swapfile

To keep your changes permanent don’t forget to add the following line to your /etc/fstab file:

/swapfile   none    swap    sw    0   0

Conclusion

Cloud VPSs are amazing considering their cost, reliability and convenience, keep in mind that you will need to tweak one thing or other. If you need a more robust solution I recommend Amazon’s RDS or upgrading your instance plan.

Don’t forget to restart your MySQL/MariaDB service after any changes to the my.cnf file.

service mysql restart