Setting up a LAMP server on Ubuntu 17.04, and enabling remote access to MySQL

Introduction

This gist records some steps I took to set up a LAMP server on Ubuntu, and how I configured MySQL to allow remote access with MySQL workbench.

Please note that this is not meant to be an exhaustive guide, and absolutely no security measures are in place.

I used a Google Cloud VM, but I'm pretty sure this could be applied to a home server as well. Just remember to open to required ports on your router and forward them appropriately.

Where possible, I will credit the original source of information, and paste code snippets to facilitate replication of this. I have left out most of the text from the guides I referenced. If you'd like to understand in depth what each line of code does, please refer to the original links.

Machine Configuration

Google Cloud Platform

f1-micro (1 vCPU, 0.6 GB memory)
10GB HDD
Ubuntu 17.04

Allows:

Http / Https traffic
Open port 3306 (for MySQL remote access)

LAMP Server Setup

Reference: https://www.digitalocean.com/community/tutorials/how-to-install-linux-apache-mysql-php-lamp-stack-on-ubuntu-16-04

Installing Apache and making some changes to configuration

Install Apache

sudo apt-get update
sudo apt-get install apache2

Update Apache config file

sudo nano /etc/apache2/apache2.conf

Update ServerName server_domain_or_IP

sudo systemctl restart apache2

Check if UFW has an application profile for Apache like so:

sudo ufw app list

Apache Full profile should show that it enables traffic to ports 80 and 443:

sudo ufw app info "Apache Full"

Allow incoming traffic for this profile:

sudo ufw allow in "Apache Full"

Accessing http://your_server_IP_address should yield a generic index.html page

Install MySQL

Install MySQL server

sudo apt-get install mysql-server

Update security settings

mysql_secure_installation //Configure security settings your way

Install PHP

sudo apt-get install php libapache2-mod-php php-mcrypt php-mysql

Test PHP

sudo nano /var/www/html/info.php

<?php
phpinfo();
?>

Access via localhost/info.php. You should see your current php configuration displayed.

Update: Follow this link if doing on Raspberry Pi https://www.stewright.me/2016/03/turn-raspberry-pi-3-php-7-powered-web-server/

Configuring MySQL for Remote Access via MySQL Workbench

This assumes you already have installed MySQL Workbench on the remote computer which is not on the same local network. Also, port 3306 should be open for this to work. Check your port with netstat -tulnp | grep <port no>.

Reference: https://stackoverflow.com/questions/37916941/cant-connect-to-remote-mysql-server-10061, https://stackoverflow.com/questions/1673530/error-2003-hy000-cant-connect-to-mysql-server-on-127-0-0-1-111

To allow remote access to MySQL, you have to comment out bind-address and skip-networking in the configuration file.

This file is either at /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf.

If you cannot find bind-address in the my.cnf file, look for it in /etc/mysql/mysql.conf.d/mysqld.cnf file.

Next, you have to make sure the user is allowed remote access. Check your user with this:

SELECT User, Host FROM mysql.user;

If your user here has '127.0.0.1' or 'localhost' listed as host, you don't have remote access.

Change this with:

UPDATE mysql.user SET HOST='%' WHERE User='__here_your_username';

Flush privileges:

FLUSH PRIVILEGES;

The '%' is a wildcard for 'all hosts'.

You can then test your connection with MySQL Workbench, with your external IP address.

Setting up subdomains

Update Host DNS to route subdomain to correct IP address.

Reference: https://serverfault.com/questions/195611/how-do-i-redirect-subdomains-to-a-different-port-on-the-same-server

Run the following line on terminal (specify your domain and sub domain name correctly)

sudo nano /etc/apache2/sites-available/<subdomain.domain.com.conf>

Paste the following code and change as your requirement

<VirtualHost *:80>
    ServerAdmin [email protected]
    ServerName dev.mydomain.com
    ProxyPreserveHost On

    # setup the proxy
    <Proxy *>
        Order allow,deny
        Allow from all
    </Proxy>
    ProxyPass / http://localhost:8888/
    ProxyPassReverse / http://localhost:8888/
</VirtualHost>

Run the following lines on terminal (specify your domain and sub domain name correctly)

sudo a2enmod proxy
sudo a2enmod proxy_http
sudo a2ensite subdomain.domain.com.conf
sudo service apache2 restart

If sudo a2enmod subdomain.domain.com.conf doesn't work, and you get a module not found error, run the following code:

sudo a2dissite 000-default.conf //disables the default web site, allowing you to promote another one in its place.

Source: https://ubuntuforums.org/showthread.php?t=2100563

Further work

I intend to try this with a Raspberry Pi 3 in the near future.

EdwinChua/lamp-mysql.md