I originally wrote this on my site, https://blog.crazy.technology, which I will be taking down in a few days.
This post 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.
Google Cloud Platform
- f1-micro (1 vCPU, 0.6 GB memory)
- 10GB HDD
- Ubuntu 17.04
- Http / Https traffic
- Open port 3306 (for MySQL remote access)
LAMP Server Setup
Installing Apache and making some changes to configuration
sudo apt-get update sudo apt-get install apache2
Update Apache config file
sudo nano /etc/apache2/apache2.conf
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 server
sudo apt-get install mysql-server
Update security settings
mysql_secure_installation //Configure security settings your way
sudo apt-get install php libapache2-mod-php php-mcrypt php-mysql
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
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
If you cannot find bind-address in the
my.cnf file, look for it in
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';
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.
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@example.com 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
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.
I intend to try this with a Raspberry Pi 3 in the near future.