Complete Guide: Setting Up Linux Server for Node.js and MySQL

This comprehensive guide will walk you through setting up a production server environment for Node.js applications with MySQL database. We'll cover everything from initial server setup to deploying your application.

Prerequisites

• A Linux server (Ubuntu recommended)
• Root or sudo access
• Basic command line knowledge
• Domain name (optional but recommended)

Step 1: Initial Server Setup

First, update your package lists to ensure you have the latest versions:

sudo apt update
sudo apt upgrade

Step 2: Installing and Configuring Nginx

Nginx will serve as our web server and reverse proxy:

# Install Nginx
sudo apt install nginx

# Check available firewall applications
sudo ufw app list

# Allow Nginx through firewall
sudo ufw allow 'Nginx Full'

# Verify Nginx is running
sudo systemctl status nginx

Step 3: Setting Up MySQL

Install and secure your MySQL installation:

# Install MySQL server
sudo apt install mysql-server

# Install PHP and required modules
sudo apt install php-fpm php-mysql

Step 4: Configuring Nginx

Create a robust Nginx configuration that handles both your frontend and backend:

server {
    listen 80 default_server;
    listen [::]:80 default_server;

    index index.html index.php index.htm index.nginx-debian.html;

    location / {
        root /var/www/html/<your UI project dist>;
        try_files $uri $uri/ /index.html;
    }

    location /phpmyadmin {
        root /usr/share/;
        index index.php;

        location ~ ^/phpmyadmin/(.+\.php)$ {
            try_files $uri =404;
            fastcgi_pass unix:/var/run/php/php8.2-fpm.sock;
            fastcgi_index index.php;
            fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
            include fastcgi_params;
        }

        location ~* ^/phpmyadmin/(.+\.(jpg|jpeg|gif|css|png|js|ico|html|xml|txt))$ {
            # Root directive not needed here
        }
    }

    location ~ \.php$ {
        include fastcgi_params;
        fastcgi_pass unix:/var/run/php/php8.2-fpm.sock;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
    }
}

Save this configuration to: /etc/nginx/sites-available/default

Step 5: Installing phpMyAdmin

Set up phpMyAdmin for easy database management:

# Install phpMyAdmin and dependencies
sudo apt install phpmyadmin php-mbstring php-zip php-gd php-json php-curl

# Configure MySQL root user
sudo mysql
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpassword@232';
SELECT user,authentication_string,plugin,host FROM mysql.user;
exit

# Create symbolic link
sudo ln -s /usr/share/phpmyadmin /var/www/html/phpmyadmin

# Reload Nginx
sudo systemctl reload nginx

Step 6: Configuring MySQL Remote Access

Enable remote access to your MySQL server (use with caution):

1. Edit MySQL configuration:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

2. Update bind-address:

- bind-address = 127.0.0.1
+ bind-address = 0.0.0.0

3. Restart MySQL:

sudo systemctl restart mysql

Step 7: Installing Node.js using NVM

Install Node.js using Node Version Manager (NVM):

# Install NVM
curl -o- https://raw.githubusercontent.com/nvm-sh/nvm/v0.37.2/install.sh | bash
source ~/.bashrc

# Verify NVM installation
nvm --version

# Install Node.js
nvm install 14.15.4
node --version
npm --version

Step 8: Deploying Node.js Application

Deploy your Node.js application using PM2:

# Clone your repository
git clone https://github.com/{ownerName}/{repoName}.git
cd ./directory-name

# Install dependencies
npm install

# Install PM2 globally
npm i -g pm2

# Start your application
pm2 start ./server.js

# Configure PM2 to start on boot
pm2 startup

Security Best Practices

1. Firewall Configuration

sudo ufw enable
sudo ufw allow ssh
sudo ufw allow http
sudo ufw allow https

2. SSL/TLS Setup

sudo apt install certbot python3-certbot-nginx
sudo certbot --nginx

3. MySQL Security

• Use strong passwords
• Limit remote access
• Regularly update user privileges
• Enable binary logging

4. Node.js Security

• Keep dependencies updated
• Use environment variables
• Implement rate limiting
• Enable CORS properly

Troubleshooting Common Issues

1. Nginx Issues

• Check logs: sudo tail -f /var/log/nginx/error.log
• Test configuration: nginx -t
• Verify permissions: ls -la /var/www/html

2. MySQL Issues

• Check status: systemctl status mysql
• Review logs: tail -f /var/log/mysql/error.log
• Test connection: mysql -u root -p

3. Node.js Issues

• Check PM2 logs: pm2 logs
• Monitor processes: pm2 monit
• Verify port availability: netstat -tulpn

Maintenance Tips

1. Regular Updates

sudo apt update
sudo apt upgrade
npm update

2. Backup Strategy

# Database backup
mysqldump -u root -p --all-databases > backup.sql

# Application backup
tar -czf backup.tar.gz /var/www/html

3. Monitoring

• Set up PM2 monitoring
• Configure server monitoring (e.g., Netdata)
• Enable error logging

Conclusion

This setup provides a robust environment for running Node.js applications with MySQL. Remember to:

• Regularly update all components
• Monitor server resources
• Maintain proper backups
• Follow security best practices

For additional support or troubleshooting, refer to the official documentation of each component or reach out to the respective communities.