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

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


Published at - Aug 13, 2021

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

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/
Paste the following code and change as your requirement


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/


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





About author

Harendra
Harendra Kanojiya

Hello, I am Harendra Kumar Kanojiya - Owner of this website and a Fullstack web developer. I have expertise in full-stack web development using Angular, PHP, Node JS, Python, Laravel, Codeigniter and, Other web technologies. I also love to write blogs on the latest web technology to keep me and others updated. Thank you for reading the articles.



Follow Us

Follow us on facebook Click Here

Facebook QR
Scan from mobile
Join our telegram channel Click Here
Telegram QR
Scan from mobile