Host Asp.Net Core Web API and MySql with Nginx on a DigitalOcean 5$ Ubuntu 18.04 Droplet
This involved a bit of tedious manual work compared to setting up an application on IIS. Moreover, you need to refer to a couple of documentations to get everything together, so I thought I will document all the steps in one place.
Create a Droplet in DigitalOcean
DigitalOcean calls their VPS(Virtual Private Server) Droplets, and this is how you create one. First, sign up for DigitalOcean and then create a project.
Then click on the Create button and select Droplets.
Select Ubuntu 18.04 in Chose image section.
Select the $5/mo plan. Leave all other options default, and you can select the closest datacenter region based on your geographical location.
You can provide a meaningful name for the hostname. I name this as dev-droplet.
Finally, click on Create Droplet button
You will receive an email with the public IP of the Droplet and the root credentials.
Then you will have to create a firewall in order for you to access the new droplet over the Internet. Click on Create and select Cloud Firewall. Give it a name, and then add below inbound rules. Creating these rules is intuitive. You just select the type of rule and the system will pick the port and protocol automatically.
Now you can use Putty utility to connect to the server. At the first login, you will have to change the password.
After that, create a new user, and then you can use this new user for all your regular activities, so you don’t need to use the root user. Using root user for regular activities is risky; thus the new user.
root@dev-droplet:~# adduser manjula
Set a password and provide the required information for the new user as per the prompt.
Then provide new user admin privileges; run this command to add your new user to the sudo group.
root@dev-droplet:~# usermod -aG sudo manjula
Now, when logged in as your regular user, you can type sudo before commands to perform actions with superuser privileges.
Open a new session using Putty, and log in with the regular user. Notice the difference in the prompt.
manjula@dev-droplet:~$
Installing MySQL
First, update the package index.
manjula@dev-droplet:~$ sudo apt update
Then install the MySql package
manjula@dev-droplet:~$ sudo apt install mysql-server
Run below command to make the installation secure. This will disable some of the less secure default options
manjula@dev-droplet:~$ sudo mysql_secure_installation
You will need to switch the root account authentication method from auth_socket to mysql_native_password. To do this, open up the MySQL prompt from your terminal:
$ sudo mysql
Next, check which authentication method each of your MySQL user accounts uses with the following command:
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
Run below command to change the authentication model.
mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;
Then run the previous query command, and you can see the plugin has been updated.
Reload the privileges by running below command.
mysql> FLUSH PRIVILEGES;
Exit mysql prompt
mysql> exit;
Try to change to mysql prompt again with the command “sudo mysql” and you will get below error. This is because we have changed the authentication model of the root user.
So you need to use a different command for this:
manjula@dev-droplet:~$ mysql -u root -p
Now you will get to enter the password you have created for the root (‘password’ as above) and this will take you to the msql prompt.
We will not be using the root user in our application, so we will create a new user for our application. Run below command to create a new user.
mysql> CREATE USER ‘appuser’@’localhost’ IDENTIFIED BY ‘sqlpassword’;
Then, grant your new user the appropriate privileges.
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘appuser’@’localhost’ WITH GRANT OPTION;
Connect to MySQL remotely
Run this command to edit the mysql config file
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Find and set the bind-address to the public IP of the droplet
bind-address = 127.0.0.1
I changed this to my droplets IP
bind-address = 137.68.99.187
Save and exit the editor.
And then run below command to restart mysql
sudo systemctl restart mysql
Check the connectivity by telnet to the droplet IP on port 3306 (mysql default port)
Telnet is not working, and it says host 2.49.0.213 is not allowed.
Looks like I am not able to connect to the server. For this to work, we need to allow my computers IP address on MySql user table.
Go back to Putty and go to mysql prompt.
Run below query again see the users.
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
You can see our new user ‘appuser’ is on the bottom and that has the hostname as ‘localhost’ We need this to be updated to my computers IP. Find your computers IP by typing into google ‘what's my ip’
Now update users table with this IP instead of ‘localhost’
mysql> update mysql.user set Host=’2.49.0.213' where user=’appuser’;
Query again:
One more time run below command to restart mysql
sudo systemctl restart mysql
Now try telnet again:
It works.
After that, you can access your mysql database hosted on the cloud from your machine either from your program or from a client software like MySQL Workbench. You have to update the host IP address in mysql.users when ever your IP address got changed.
Download and install MySQL Workbench
Click on the new connection and fill out the information as below. I am trying to use Standard TCP option here because I want to connect this DB through my .net code and debug.
Click on test connection, and you should get successful message as below.
Now you can use MySQL Workbench to manage your database.
Developing .Net Core web API with MySQL
Follow this youtube tutorial to build your app. You just need to change the connection string as per your DB hosted on Droplet.
https://www.youtube.com/watch?v=TcovfE8IsHs
Connection string:
“DevConnection”: “server=137.68.99.187;port=3306;database=TestDB;uid=appuser;password=sqlpassword”
After changing the connection string, run the EF migration and then you can see the DB is created with the PayementDetail table in it.
You can refresh the MySQL workbench and see the new database TestDB and the table created.
Install Nginx
sudo apt update
sudo apt install nginx
After installing the package, check the status of the Nginx web server by running below command.
systemctl status nginx
I am not using ufw, but I am using the cloud firewall; therefore, I will not explain adding ufw rules here.
Now you should be able to access the Droplets public IP over http.
Creating a server block
Server blocks are required when you need to host more than one application on the same server.
Here we will create two directories to represent two applications and create two html pages inside those directories and will access them over the Internet.
Go to behind two levels up and find the var directory. This is the default webroot folder.
We will create two directories as appone and apptwo inside /var/www/html/
sudo mkdir -p appone.com
sudo mkdir -p apptwo.com
Change the ownership of the directory to $USER environment valiable
sudo chown -R $USER:$USER appone.com
sudo chown -R $USER:$USER apptwo.com
Provide permission to the directories
sudo chmod -R 775 appone.com
sudo chmod -R 775 apptwo.com
Create an html page in appone.com directory
sudo nano appone.com/index.html
Copy below content into the editor, save and exit.
<html>
<head>
<title>Welcome to Appone.com!</title>
</head>
<body>
<h1>Success! The appone.com server block is working!</h1>
</body>
</html>
Create a html page in apptwo.com directory
sudo nano apptwo.com/index.html
Copy below content to the editor, save and exit.
<html>
<head>
<title>Welcome to Apptwo.com!</title>
</head>
<body>
<h1>Success! The apptwo.com server block is working!</h1>
</body>
</html>
Nginx first server block for the appone.com
Run below command
sudo nano /etc/nginx/sites-available/appone.com
Copy below configuration, save and exit.
server {
listen 80;
listen [::]:80;
root /var/www/html/appone.com;
index index.html index.htm;
server_name appone.com www.appone.com;
location / {
try_files $uri $uri/ =404;
}
}
Nginx second server block for the apptwo.com
Run below command
sudo nano /etc/nginx/sites-available/apptwo.com
Copy below configuration, save and exit.
server {
listen 80;
listen [::]:80;
root /var/www/html/apptwo.com;
index index.html index.htm;
server_name apptwo.com www.apptwo.com;
location / {
try_files $uri $uri/ =404;
}
}
Create a link to the nginx/sites-enabled directory because this is the location Nginx will look for the configuration.
For appone.com
sudo ln -s /etc/nginx/sites-available/appone.com /etc/nginx/sites-enabled/
For apptwo.com
sudo ln -s /etc/nginx/sites-available/apptwo.com /etc/nginx/sites-enabled/
It is recommended to uncomment one line to avoid hash bucket memory problem that can arise from adding additional server names. Run below command:
sudo nano /etc/nginx/nginx.conf
Find and uncomment the below line, save and exit.
server_names_hash_bucket_size 64;
To validate the text in Nginx config files, run below command.
sudo nginx -t
Expected response:
nginx: the configuration file /etc/nginx/nginx.conf syntax is ok
nginx: configuration file /etc/nginx/nginx.conf test is successful
Restart Nginx
sudo systemctl restart nginx
Test appone.com:
Put an entry in hosts file at C:\Windows\System32\drivers\etc as below:
137.68.99.187 appone.com
This is to simulate the DNS appone.com because there is no public DNS entry for this.
Now check http://appone.com on browser
Test apptwo.com:
Put an entry in hosts file at C:\Windows\System32\drivers\etc as below:
137.68.99.187 apptwo.com
This is to simulate the DNS apptwo.com because there is no public DNS entry for this.
Now check http://apptwo.com on browser
Now the real stuff: build and deploy the .Net core web app
Install .Net core 2.2 on the Droplet. Follow instruction on this:
https://dotnet.microsoft.com/download/linux-package-manager/ubuntu18-04/sdk-current
Publish the API project and copy the content of the published folder to the /var/www/html/apptwo.com using a tool like WinScp or FileZilla
Run below command again to modify apptwo.com routing. Now we don’t need the static content routing, and we just need to rout any traffic comes to port 80 on the server with the host header apptow.com to localhost:5000
sudo nano /etc/nginx/sites-available/apptwo.com
Copy below configuration, save and exit.
server {
listen 80;
server_name apptwo.com *.apptwo.com;
location / {
proxy_pass http://localhost:5000;
proxy_http_version 1.1;
proxy_set_header Upgrade $http_upgrade;
proxy_set_header Connection keep-alive;
proxy_set_header Host $host;
proxy_cache_bypass $http_upgrade;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_set_header X-Forwarded-Proto $scheme;
}
}
Validate and reset Nginx
sudo nginx -t
sudo systemctl restart nginx
Run the .Net core app by executing below command
dotnet <DLL name of the published project>
In my case:
dotnet NetCoreCrudApp.dll
This will run the app on localhost:5000
Now go to the browser and check the default API: http://apptwo.com/api/values
Now check the api/PaymentDetail at http://apptwo.com/api/PaymentDetail
You will get an error on the server terminal as below:
fail: Microsoft.AspNetCore.Server.Kestrel[13]
Connection id “0HLOESG0CKQ07”, Request id “0HLOESG0CKQ07:00000001”: An unhandled exception was thrown by the application.
MySql.Data.MySqlClient.MySqlException (0x80004005): Host ‘137.68.99.187’ is not allowed to connect to this MySQL server
For this, we need to change the mysql.user to allow connectivity within the droplet. Therefore, you need to update host IP to droplet’s IP for the appuser
mysql> update mysql.user set Host=’137.68.99.187' where user=’appuser’;
and then restart mysql. After we do this, appuser will not be able to access from your local code, but it will work from the server. So it’s easy to have two different users for local code and for the deployed app.
Try again, and you will get an empty JSON object.
Let’s create some data using Postman
Then, query again:
Now we get the newly created record.
References:
https://docs.microsoft.com/en-us/aspnet/core/host-and-deploy/linux-nginx?view=aspnetcore-2.2
https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-18-04
https://www.digitalocean.com/community/tutorials/initial-server-setup-with-ubuntu-18-04
https://www.youtube.com/watch?v=fom80TujpYQ&t=989s
https://www.digitalocean.com/community/tutorials/how-to-install-nginx-on-ubuntu-18-04