1. Linux Server Management Commands

These are common commands for managing Linux-based hosting environments:

System Monitoring & Management

htop                # Interactive process viewer
top                 # Show running processes
vmstat 1            # Show system performance (updates every second)
df -h               # Show disk space usage
du -sh *            # Show size of all files/folders in current directory
free -m             # Check memory usage
uptime              # Show system uptime
whoami              # Check logged-in user
uname -a            # Show OS details

User & Permissions

adduser       # Create a new user
usermod -aG sudo  # Grant sudo privileges
passwd        # Change user password
chmod 755         # Set executable permissions
chown user:group  # Change file ownership

Networking

ping google.com        # Test connectivity
curl -I google.com     # Check HTTP headers
netstat -tulnp         # Show open ports
ss -tulnp              # Show listening ports (alternative to netstat)
iptables -L            # List firewall rules
traceroute google.com  # Show route to destination

2. Web Server Management (Apache, Nginx)

Apache

systemctl restart apache2     # Restart Apache (Ubuntu/Debian)
systemctl restart httpd       # Restart Apache (CentOS/RHEL)
apachectl configtest          # Check Apache configuration
tail -f /var/log/apache2/error.log  # Check Apache error logs

Nginx

systemctl restart nginx       # Restart Nginx
nginx -t                      # Test Nginx configuration
tail -f /var/log/nginx/error.log  # Check Nginx error logs

3. Database Commands (MySQL, PostgreSQL)

MySQL / MariaDB

mysql -u root -p                     # Login to MySQL
SHOW DATABASES;                        # List databases
USE database_name;                      # Select database
SHOW TABLES;                            # Show tables
mysqldump -u root -p database > backup.sql  # Backup database
mysql -u root -p database < backup.sql     # Restore database

PostgreSQL

psql -U postgres                        # Login to PostgreSQL
\l                                       # List databases
\c database_name                         # Connect to a database
\dt                                      # List tables
pg_dump -U postgres dbname > backup.sql  # Backup database
psql -U postgres dbname < backup.sql     # Restore database

4. Application Deployment & Process Management

Git Deployment

git clone repo.git                   # Clone a repository
git pull origin main                  # Update project
git log --oneline --graph --decorate  # Show commit history

PM2 (Process Manager for Node.js)

pm2 start app.js           # Start a Node.js app
pm2 restart app            # Restart app
pm2 logs                   # View logs
pm2 save                   # Save process list for auto-start
pm2 startup                # Generate startup script

Docker

docker ps                         # List running containers
docker images                      # List all images
docker pull image_name              # Pull an image
docker run -d -p 80:80 image_name   # Run a container
docker logs -f container_id         # View logs
docker-compose up -d                # Start services with Docker Compose
docker-compose down                 # Stop all services

5. Cron Jobs for Scheduling Tasks

List Existing Cron Jobs

crontab -l

Edit Crontab

crontab -e

Example Cron Jobs

# Run script every minute
* * * * * /path/to/script.sh 

# Run backup at midnight
0 0 * * * /path/to/backup.sh 

# Restart a service daily at 2 AM
0 2 * * * systemctl restart nginx

6. Logs & Debugging

tail -f /var/log/syslog      # View system logs in real-time
tail -f /var/log/auth.log    # View authentication logs
journalctl -u nginx --no-pager  # View Nginx logs
journalctl -xe               # View system errors

7. Cloud & Serverless (AWS, DigitalOcean, Google Cloud)

AWS CLI

aws s3 ls                        # List S3 buckets
aws s3 cp file.txt s3://mybucket # Upload file to S3
aws ec2 describe-instances       # Show EC2 instances
aws lambda invoke --function-name myFunction output.txt  # Invoke Lambda function

Google Cloud CLI

gcloud compute instances list    # List VM instances
gcloud storage buckets list      # List storage buckets
gcloud functions deploy myFunc --trigger-http  # Deploy Cloud Function

DigitalOcean

doctl compute droplet list       # List droplets
doctl compute droplet delete ID  # Delete a droplet

8. Security & Hardening

ufw enable                       # Enable firewall (Ubuntu)
ufw allow 22                     # Allow SSH
ufw allow 80/tcp                  # Allow HTTP traffic
fail2ban-client status sshd       # Check SSH protection
ssh-keygen -t rsa                 # Generate SSH key
ssh-copy-id user@server           # Copy SSH key to remote server

9. Load Balancing & Reverse Proxy

Nginx Reverse Proxy

server {
    listen 80;
    server_name example.com;
    
    location / {
        proxy_pass http://127.0.0.1:3000;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
    }
}

systemctl restart nginx  # Restart Nginx

10. Backup & Restore

tar -czvf backup.tar.gz /var/www/html  # Backup website
tar -xzvf backup.tar.gz                # Restore backup
rsync -avz /source/dir/ user@server:/destination/dir/  # Sync files

11. Server Configuration & Optimization

General Configuration

hostnamectl set-hostname new-hostname  # Set server hostname
timedatectl set-timezone Asia/Kolkata  # Set timezone
sysctl -p                              # Reload system configurations
cat /etc/os-release                    # Show OS version

Managing Services

systemctl status service_name       # Check service status
systemctl start service_name        # Start a service
systemctl stop service_name         # Stop a service
systemctl enable service_name       # Enable service on boot
systemctl disable service_name      # Disable service from starting on boot

12. SSH & Remote Access

Managing SSH Access

vi /etc/ssh/sshd_config                # Edit SSH config
systemctl restart ssh                   # Restart SSH service
ssh-keygen -t rsa                       # Generate SSH key
ssh-copy-id user@remote_host             # Copy SSH key to remote server

Secure SSH

sed -i 's/#Port 22/Port 2222/' /etc/ssh/sshd_config  # Change SSH port
systemctl restart sshd                               # Restart SSH service

SSH Port Forwarding

ssh -L 8080:localhost:80 user@server   # Forward local port to remote server

13. Firewall & Network Security

Using UFW (Ubuntu)

ufw allow 443/tcp                    # Allow HTTPS traffic
ufw deny 22                          # Block SSH (Use with caution)
ufw limit ssh                        # Limit SSH connections
ufw status verbose                    # Show firewall rules

Using iptables (CentOS)

iptables -A INPUT -p tcp --dport 22 -j ACCEPT  # Allow SSH
iptables -A INPUT -p tcp --dport 80 -j ACCEPT  # Allow HTTP
iptables-save > /etc/iptables.rules            # Save rules

14. Web Hosting with Apache/Nginx

Apache Virtual Host Configuration

vi /etc/apache2/sites-available/example.com.conf

    ServerName example.com
    DocumentRoot /var/www/example.com

a2ensite example.com.conf  # Enable site
systemctl restart apache2  # Restart Apache

Nginx Virtual Host

server {
    listen 80;
    server_name example.com;
    root /var/www/example.com;
    index index.html;
}

ln -s /etc/nginx/sites-available/example.com /etc/nginx/sites-enabled/
nginx -t
systemctl restart nginx

15. SSL & HTTPS (Let's Encrypt)

apt install certbot python3-certbot-nginx  # Install Certbot for Nginx
certbot --nginx -d example.com -d www.example.com  # Issue SSL certificate
certbot renew --dry-run  # Test renewal process

16. Database Performance Optimization

MySQL Performance Tuning

mysqltuner

PostgreSQL Optimization

psql -U postgres -c "SHOW config_file;"  # Locate configuration file

shared_buffers = 256MB
work_mem = 64MB
maintenance_work_mem = 128MB
effective_cache_size = 512MB

systemctl restart postgresql  # Apply changes

17. Load Balancing & High Availability

Nginx as Load Balancer

upstream backend {
    server app1.example.com;
    server app2.example.com;
}
server {
    listen 80;
    location / {
        proxy_pass http://backend;
    }
}

systemctl restart nginx

18. File Management & Transfers

scp file.txt user@server:/path/   # Copy file to remote server
scp -r folder user@server:/path/  # Copy folder to remote server
rsync -avz /source/ user@server:/destination/  # Sync files

19. Automated Backups

tar -czf backup.tar.gz /var/www/html  # Backup website files
crontab -e                            # Schedule backup job

0 3 * * * tar -czf /backups/site_$(date +\%F).tar.gz /var/www/html

20. CI/CD with GitHub Actions

name: Deploy Website
on: push
jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v2
      - name: Deploy
        run: |
          ssh user@server 'cd /var/www/html && git pull'

21. Kubernetes Commands

kubectl get pods                     # List running pods
kubectl describe pod pod_name         # Describe a pod
kubectl delete pod pod_name           # Delete a pod
kubectl apply -f deployment.yaml      # Apply changes

22. AWS EC2 Management

aws ec2 describe-instances             # List EC2 instances
aws ec2 start-instances --instance-ids i-xxxx  # Start instance
aws ec2 stop-instances --instance-ids i-xxxx   # Stop instance

23. AWS S3 Bucket Management

aws s3 ls                          # List buckets
aws s3 cp file.txt s3://mybucket/  # Upload file
aws s3 sync /local/dir s3://mybucket/  # Sync directory

24. Automating Deployments with Ansible

Install Ansible

apt install ansible

Sample Playbook

- name: Deploy Web Server
  hosts: web_servers
  tasks:
    - name: Install Nginx
      apt: name=nginx state=present
    - name: Start Nginx
      service: name=nginx state=started

25. Logs & Debugging

journalctl -u nginx --no-pager    # View Nginx logs
tail -f /var/log/mysql/error.log  # Monitor MySQL logs
dmesg | tail                      # View recent kernel logs

26. Performance Monitoring

iostat -x 1                 # Show disk performance
sar -u 1                    # CPU usage statistics
iotop                       # Monitor disk I/O

27. Advanced Troubleshooting

Find Large Files

du -ah / | sort -rh | head -n 10

Find Which Process is Using a Port

lsof -i :80  # Check which process is using port 80

Restart Server If Load is High

uptime | awk '{ if ($10 > 5.0) system("reboot") }'

1. MySQL Connection & Access

Login to MySQL

mysql -u root -p  # Login with root user
mysql -u user -p  # Login with specific user

Check MySQL Version

mysql --version

2. Database Management

List Databases

SHOW DATABASES;

Create Database

CREATE DATABASE mydb;

Use Database

USE mydb;

Drop Database

DROP DATABASE mydb;

3. Table Management

Show Tables in Database

SHOW TABLES;

Create Table

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Describe Table Structure

DESC users;

Drop Table

DROP TABLE users;

Truncate Table (Remove All Data)

TRUNCATE TABLE users;

4. Insert, Update & Delete Data

Insert Data

INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');

Update Data

UPDATE users SET name = 'Jane Doe' WHERE id = 1;

Delete Data

DELETE FROM users WHERE id = 1;

5. Select Queries

Select All Records

SELECT * FROM users;

Select Specific Columns

SELECT name, email FROM users;

Select with Conditions

SELECT * FROM users WHERE name = 'John Doe';

Order Results

SELECT * FROM users ORDER BY name ASC;

Limit & Offset

SELECT * FROM users LIMIT 5 OFFSET 10;

Count Rows

SELECT COUNT(*) FROM users;

6. MySQL User Management

Show Users

SELECT user, host FROM mysql.user;

Create User

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Grant Privileges

GRANT ALL PRIVILEGES ON mydb.* TO 'newuser'@'localhost';

Revoke Privileges

REVOKE ALL PRIVILEGES ON mydb.* FROM 'newuser'@'localhost';

Delete User

DROP USER 'newuser'@'localhost';

Apply Changes

FLUSH PRIVILEGES;

7. MySQL Indexing for Performance

Show Indexes

SHOW INDEX FROM users;

Create Index

CREATE INDEX idx_name ON users(name);

Drop Index

DROP INDEX idx_name ON users;

8. Joins in MySQL

Inner Join

SELECT users.name, orders.amount 
FROM users 
INNER JOIN orders ON users.id = orders.user_id;

Left Join

SELECT users.name, orders.amount 
FROM users 
LEFT JOIN orders ON users.id = orders.user_id;

Right Join

SELECT users.name, orders.amount 
FROM users 
RIGHT JOIN orders ON users.id = orders.user_id;

Full Outer Join (Not Available in MySQL)

SELECT users.name, orders.amount 
FROM users 
LEFT JOIN orders ON users.id = orders.user_id
UNION
SELECT users.name, orders.amount 
FROM users 
RIGHT JOIN orders ON users.id = orders.user_id;

9. Backup & Restore

Backup Database

mysqldump -u root -p mydb > mydb_backup.sql

Backup All Databases

mysqldump -u root -p --all-databases > all_backup.sql

Restore Database

mysql -u root -p mydb < mydb_backup.sql

Backup Table

mysqldump -u root -p mydb users > users_backup.sql

10. MySQL Logs & Debugging

Show MySQL Process List

SHOW PROCESSLIST;

Check Slow Queries

tail -f /var/log/mysql/slow-query.log

Enable Slow Query Log

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;

Kill Query

KILL 1234;  # Use Process ID from SHOW PROCESSLIST;

11. MySQL Configuration & Optimization

Check Configuration Variables

SHOW VARIABLES;

Optimize Table

OPTIMIZE TABLE users;

Check & Repair Table

CHECK TABLE users;
REPAIR TABLE users;

Increase MySQL Connection Limit

SET GLOBAL max_connections = 500;

Flush Cache

RESET QUERY CACHE;
FLUSH TABLES;

12. MySQL Security

Change Root Password

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

Disable Remote Root Login (Edit my.cnf)

[mysqld]
bind-address = 127.0.0.1

systemctl restart mysql

Remove Anonymous Users

DELETE FROM mysql.user WHERE user = '';
FLUSH PRIVILEGES;

Check Open Connections

SHOW STATUS LIKE 'Threads_connected';

13. MySQL Stored Procedures & Functions

Create Stored Procedure

DELIMITER //
CREATE PROCEDURE GetUsers()
BEGIN
    SELECT * FROM users;
END //
DELIMITER ;

Call Stored Procedure

CALL GetUsers();

Drop Stored Procedure

DROP PROCEDURE GetUsers;

Create Function

DELIMITER //
CREATE FUNCTION GetUserCount() RETURNS INT
BEGIN
    DECLARE total INT;
    SELECT COUNT(*) INTO total FROM users;
    RETURN total;
END //
DELIMITER ;

Call Function

SELECT GetUserCount();

14. MySQL Events & Scheduling

Enable Event Scheduler

SET GLOBAL event_scheduler = ON;

Create Event

CREATE EVENT DeleteOldUsers
ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM users WHERE created_at < NOW() - INTERVAL 30 DAY;

Show Events

SHOW EVENTS;

Drop Event

DROP EVENT DeleteOldUsers;

15. MySQL Advanced Administration

Check MySQL Uptime

SHOW STATUS LIKE 'Uptime';

Show Running Transactions

SELECT * FROM information_schema.INNODB_TRX;

Check Current Connections

SHOW STATUS WHERE variable_name = 'Threads_connected';

Show User Privileges

SHOW GRANTS FOR 'user'@'localhost';

Enable Binary Logging (For Replication & Point-in-Time Recovery)

[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
server-id = 1

systemctl restart mysql

16. MySQL Replication

Master Server Configuration (my.cnf)

[mysqld]
log-bin=mysql-bin
server-id=1
binlog-do-db=mydb

systemctl restart mysql

Show Master Status

SHOW MASTER STATUS;

Slave Server Configuration (my.cnf)

[mysqld]
server-id=2
replicate-do-db=mydb

Start Replication on Slave

CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='replication_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
START SLAVE;

Check Replication Status

SHOW SLAVE STATUS\G;

Stop Replication

STOP SLAVE;

17. MySQL Partitioning for Large Datasets

Create Partitioned Table

CREATE TABLE sales (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, sale_date)
)
PARTITION BY RANGE(YEAR(sale_date)) (
    PARTITION p1 VALUES LESS THAN (2023),
    PARTITION p2 VALUES LESS THAN (2024),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

View Partitions

SELECT * FROM information_schema.partitions WHERE table_name = 'sales';

Drop Partition

ALTER TABLE sales DROP PARTITION p1;

18. MySQL JSON Data Handling

Create Table with JSON Column

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    attributes JSON
);

Insert JSON Data

INSERT INTO products (name, attributes) 
VALUES ('Laptop', '{"brand": "Dell", "price": 1000, "stock": 50}');

Query JSON Data

SELECT name, attributes->'$.brand' AS brand FROM products;

Update JSON Field

UPDATE products SET attributes = JSON_SET(attributes, '$.price', 1200) WHERE id = 1;

Remove JSON Key

UPDATE products SET attributes = JSON_REMOVE(attributes, '$.stock') WHERE id = 1;

19. Full-Text Search in MySQL

Create Full-Text Index

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    FULLTEXT(title, content)
);

Search Using Full-Text Index

SELECT * FROM articles WHERE MATCH(title, content) AGAINST('database optimization');

20. MySQL Performance Tuning

Check Query Execution Plan

EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

Enable Query Cache

SET GLOBAL query_cache_size = 1048576;  # 1MB

Check Open Tables

SHOW GLOBAL STATUS LIKE 'Open_tables';

Increase Buffer Pool Size (For InnoDB)

SET GLOBAL innodb_buffer_pool_size = 512M;

21. MySQL Stored Procedures & Triggers

Create Trigger Before Insert

CREATE TRIGGER before_insert_users
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.created_at = NOW();

Show Triggers

SHOW TRIGGERS;

Drop Trigger

DROP TRIGGER before_insert_users;

22. MySQL Views

Create View

CREATE VIEW active_users AS 
SELECT id, name, email FROM users WHERE active = 1;

Query View

SELECT * FROM active_users;

Drop View

DROP VIEW active_users;

23. MySQL Dynamic Queries (Prepared Statements)

Prepare & Execute Statement

PREPARE stmt FROM 'SELECT * FROM users WHERE email = ?';
SET @email = 'john@example.com';
EXECUTE stmt USING @email;

Deallocate Prepared Statement

DEALLOCATE PREPARE stmt;

24. MySQL Backup & Restore Advanced

Backup Only Schema

mysqldump -u root -p --no-data mydb > mydb_schema.sql

Backup Only Data

mysqldump -u root -p --no-create-info mydb > mydb_data.sql

Restore Specific Table

mysql -u root -p mydb < table_backup.sql

25. MySQL User Role Management

Create Role

CREATE ROLE 'developer';

Grant Privileges to Role

GRANT SELECT, INSERT ON mydb.* TO 'developer';

Assign Role to User

GRANT 'developer' TO 'user'@'localhost';

Show Role Assignments

SELECT * FROM information_schema.applicable_roles;

26. MySQL Security Enhancements

Check for Insecure Users

SELECT user, host, authentication_string FROM mysql.user WHERE authentication_string = '';

Enable Strict Mode

sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

Change Default MySQL Port

[mysqld]
port=3307

systemctl restart mysql

27. MySQL Connection Pooling

Check Max Allowed Connections

SHOW VARIABLES LIKE 'max_connections';

Increase Max Connections

SET GLOBAL max_connections = 1000;

Monitor Active Connections

SHOW STATUS WHERE variable_name = 'Threads_connected';

28. MySQL Federated Tables (Remote Data Access)

Enable Federated Engine

[mysqld]
federated

systemctl restart mysql

Create Federated Table

CREATE TABLE remote_users (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (id)
) ENGINE=FEDERATED CONNECTION='mysql://user:password@remote_host/mydb/users';

29. MySQL Query Optimization & Indexing

Find Queries Using Full Table Scans

SELECT * FROM information_schema.PROFILING WHERE state LIKE 'Sending data%';

Check Index Usage in Query

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';

Force Index Usage

SELECT * FROM users FORCE INDEX (idx_email) WHERE email = 'john@example.com';

Drop an Index

ALTER TABLE users DROP INDEX idx_email;

30. MySQL Parallel Execution & Performance

Enable Parallel Query Execution

SET GLOBAL innodb_parallel_read_threads = 4;

Monitor MySQL Thread Performance

SHOW ENGINE INNODB STATUS;

Check Active MySQL Connections

SHOW PROCESSLIST;

31. Advanced Data Analysis with MySQL

Find Duplicate Records

SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

Retrieve Nth Highest/Lowest Value

SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1; -- 2nd highest salary

Rolling Average (Moving Window Functions)

SELECT name, salary, 
       AVG(salary) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees;

32. MySQL Event Scheduler for Automation

Enable Event Scheduler

SET GLOBAL event_scheduler = ON;

Create Auto-Delete Event

CREATE EVENT cleanup_old_users
ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM users WHERE last_login < NOW() - INTERVAL 180 DAY;

Show All Scheduled Events

SHOW EVENTS;

Disable Event

ALTER EVENT cleanup_old_users DISABLE;

33. MySQL Backup & Restore Optimization

Backup Large Database in Compressed Format

mysqldump -u root -p mydb | gzip > mydb_backup.sql.gz

Restore from Compressed Backup

gunzip < mydb_backup.sql.gz | mysql -u root -p mydb

Enable Incremental Backups (Binary Logs)

[mysqld]
log_bin = mysql-bin
expire_logs_days = 7

Purge Old Binary Logs

PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;

34. MySQL Time-Based Queries

Get Data from Last 7 Days

SELECT * FROM orders WHERE order_date >= NOW() - INTERVAL 7 DAY;

Get Data Between Two Dates

SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-02-01';

Convert Timestamp to Readable Date

SELECT id, FROM_UNIXTIME(created_at) FROM logs;

35. MySQL JSON & Dynamic Data Handling

Check If JSON Key Exists

SELECT * FROM products WHERE JSON_CONTAINS_PATH(attributes, 'one', '$.brand');

Extract JSON Array Values

SELECT JSON_EXTRACT(attributes, '$.colors[0]') FROM products;

1. Preparing the Server

1.1 Install Required Components on Windows Server

Enable-WindowsOptionalFeature -Online -FeatureName IIS-WebServerRole
Install-WindowsFeature Web-Server -IncludeManagementTools

Install-WindowsFeature NET-Framework-4.8
Install-WindowsFeature NET-Framework-Core

1.2 Install IIS and Required Modules

Install-WindowsFeature -Name Web-Asp-Net45
Install-WindowsFeature -Name Web-Asp-Net

iisreset

1.3 Install .NET Core Hosting Bundle (For .NET Core)

iisreset

2. Deploying .NET Application on Windows Server

2.1 Publish the Application

dotnet publish -c Release -o "C:\inetpub\wwwroot\myapp"

2.2 Configure IIS Website

New-WebSite -Name "MyApp" -Port 80 -PhysicalPath "C:\inetpub\wwwroot\myapp" -ApplicationPool ".NET v4.5"

2.3 Set Application Pool Settings

Set-ItemProperty IIS:\AppPools\MyApp -Name managedPipelineMode -Value Integrated
Set-ItemProperty IIS:\AppPools\MyApp -Name enable32BitAppOnWin64 -Value True

2.4 Set File Permissions

icacls "C:\inetpub\wwwroot\myapp" /grant IIS_IUSRS:F /T

2.5 Restart IIS

iisreset

3. Configuring .NET Application for Linux Deployment

3.1 Install .NET Runtime

wget https://packages.microsoft.com/config/ubuntu/20.04/packages-microsoft-prod.deb -O packages-microsoft-prod.deb
sudo dpkg -i packages-microsoft-prod.deb
sudo apt-get update
sudo apt-get install -y aspnetcore-runtime-7.0

3.2 Publish the Application for Linux

dotnet publish -c Release -r linux-x64 --self-contained false -o /var/www/myapp

3.3 Run the Application on Linux

cd /var/www/myapp
dotnet MyApp.dll

3.4 Set Up a Systemd Service for Auto-Restart

sudo nano /etc/systemd/system/myapp.service

Add the following content:

[Unit]
Description=My .NET App
After=network.target

[Service]
ExecStart=/usr/bin/dotnet /var/www/myapp/MyApp.dll
Restart=always
User=www-data
Group=www-data
Environment=ASPNETCORE_ENVIRONMENT=Production
Environment=DOTNET_PRINT_TELEMETRY_MESSAGE=false
WorkingDirectory=/var/www/myapp

[Install]
WantedBy=multi-user.target

3.5 Enable and Start the Service

sudo systemctl enable myapp
sudo systemctl start myapp

4. Database Configuration

4.1 Configure SQL Server Connection String

Update appsettings.json:

"ConnectionStrings": {
  "DefaultConnection": "Server=myserver;Database=mydb;User Id=myuser;Password=mypassword;"
}

4.2 Apply Database Migrations

dotnet ef database update

4.3 Backup & Restore SQL Database

BACKUP DATABASE MyDB TO DISK = 'C:\Backups\MyDB.bak';

RESTORE DATABASE MyDB FROM DISK = 'C:\Backups\MyDB.bak';

5. Security Hardening

5.1 Enable HTTPS in IIS

New-SelfSignedCertificate -DnsName "mydomain.com" -CertStoreLocation Cert:\LocalMachine\My

5.2 Bind Certificate to IIS

New-WebBinding -Name "MyApp" -IP "*" -Port 443 -Protocol https

5.3 Enable Firewall Rules

New-NetFirewallRule -DisplayName "Allow HTTP" -Direction Inbound -Protocol TCP -LocalPort 80 -Action Allow
New-NetFirewallRule -DisplayName "Allow HTTPS" -Direction Inbound -Protocol TCP -LocalPort 443 -Action Allow

5.4 Secure SQL Server

ALTER LOGIN sa DISABLE;
REVOKE CONNECT TO public FROM MyDB;

6. CI/CD Deployment with GitHub Actions

6.1 Create GitHub Actions Workflow

name: .NET CI/CD

on:
  push:
    branches:
      - main

jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout Code
        uses: actions/checkout@v2

      - name: Setup .NET
        uses: actions/setup-dotnet@v2
        with:
          dotnet-version: '7.0.x'

      - name: Restore Dependencies
        run: dotnet restore

      - name: Build
        run: dotnet build --configuration Release

      - name: Publish
        run: dotnet publish -c Release -o out

      - name: Deploy to Server
        run: scp -r out/* user@server:/var/www/myapp

7. Monitoring and Logging

7.1 Enable Application Logging

Update appsettings.json:

"Logging": {
  "LogLevel": {
    "Default": "Information",
    "Microsoft": "Warning",
    "System": "Warning"
  }
}

7.2 View Application Logs

journalctl -u myapp --no-pager -n 50

7.3 Monitor Server Performance

htop

8. Load Balancing & Scaling

8.1 Configure Reverse Proxy with Nginx

sudo nano /etc/nginx/sites-available/default

server {
    listen 80;
    server_name myapp.com;

    location / {
        proxy_pass http://localhost:5000;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
    }
}

sudo systemctl restart nginx

8.2 Set Up Load Balancer (AWS/GCP)

• AWS: Use Application Load Balancer
• GCP: Use HTTP Load Balancer
• Azure: Configure Azure Application Gateway

1.1 Database Operations

CREATE DATABASE mydb;
SHOW DATABASES;
USE mydb;
DROP DATABASE mydb;

1.2 Table Management

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50), age INT);
SHOW TABLES;
DESCRIBE users;
DROP TABLE users;

1.3 Insert, Update, Delete Data

INSERT INTO users (id, name, age) VALUES (1, 'John', 25);
UPDATE users SET age = 26 WHERE id = 1;
DELETE FROM users WHERE id = 1;

1.4 Querying Data

SELECT * FROM users;
SELECT name, age FROM users WHERE age > 20;
SELECT COUNT(*) FROM users;

🟡 2. Intermediate Level (Joins, Functions, Indexing)

2.1 Advanced SELECT Queries

SELECT DISTINCT age FROM users;
SELECT * FROM users ORDER BY age DESC;
SELECT name, age FROM users WHERE name LIKE 'J%';

2.2 Using Joins

SELECT orders.id, users.name FROM orders 
JOIN users ON orders.user_id = users.id;

SELECT u.name, o.amount FROM users u 
LEFT JOIN orders o ON u.id = o.user_id;

SELECT u.name, o.amount FROM users u 
RIGHT JOIN orders o ON u.id = o.user_id;

SELECT u.name, o.amount FROM users u 
FULL JOIN orders o ON u.id = o.user_id;

2.3 Grouping & Aggregation

SELECT age, COUNT(*) FROM users GROUP BY age;
SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 2;

2.4 Indexing for Performance

CREATE INDEX idx_name ON users(name);
DROP INDEX idx_name ON users;

2.5 Subqueries & EXISTS

3.1 Transactions for Data Integrity

START TRANSACTION;
UPDATE users SET age = 30 WHERE id = 1;
COMMIT;

START TRANSACTION;
DELETE FROM users WHERE id = 1;
ROLLBACK;

3.2 Stored Procedures

DELIMITER //
CREATE PROCEDURE GetUsers()
BEGIN
  SELECT * FROM users;
END //
DELIMITER ;
CALL GetUsers();

DELIMITER //
CREATE PROCEDURE UpdateAge(IN userId INT, IN newAge INT)
BEGIN
  UPDATE users SET age = newAge WHERE id = userId;
END //
DELIMITER ;
CALL UpdateAge(1, 35);

3.3 Triggers for Automation

CREATE TRIGGER before_insert_users 
BEFORE INSERT ON users 
FOR EACH ROW 
SET NEW.name = UPPER(NEW.name);

3.4 Views for Simplifying Queries

CREATE VIEW user_orders AS 
SELECT users.name, orders.amount 
FROM users JOIN orders ON users.id = orders.user_id;

3.5 JSON Data Handling

SELECT JSON_EXTRACT(data, '$.name') FROM json_table;