Navigation :

3. Installing and Configuring phpMyAdmin for MySQL Admin

🎯 Goal

Install and configure phpMyAdmin to provide a web-based graphical interface for managing your MySQL database, making it easier to view data, run queries, and perform database administration tasks.

📋 Prerequisites

Before beginning this tutorial, you should:

Have completed Tutorial 1: Manual LEMP Stack Installation
Have a working MySQL database with the contact form application
Understand basic database concepts (tables, records, queries)
Have SSH access to your Ubuntu VM

📚 Learning Objectives

By the end of this tutorial, you will:

Install phpMyAdmin on your LEMP stack
Configure secure access to phpMyAdmin with authentication
Use phpMyAdmin interface to browse and manage database tables
Execute SQL queries through the web interface
Understand phpMyAdmin security considerations and best practices
Learn to backup and restore databases using phpMyAdmin
Configure user privileges through the graphical interface

🔍 Why This Matters

In real-world applications, phpMyAdmin is valuable because:

It provides an intuitive graphical interface for database management
It eliminates the need to memorize complex SQL commands for basic operations
It’s widely used in web hosting environments and development workflows
It offers powerful features for database import/export and backup operations
It helps visualize database structure and relationships
It’s essential for debugging and troubleshooting database issues

📝 Step-by-Step Instructions

Step 1: Install phpMyAdmin

Connect to your VM via SSH:
```
ssh azureuser@<VM_Public_IP>
```
Update package repositories:
```
sudo apt update
```

Install phpMyAdmin and required dependencies:

sudo apt install phpmyadmin php-mbstring php-zip php-gd php-json php-curl -y

During installation, you’ll see configuration prompts:
- Web server selection: Choose apache2 (press Tab, then Space to select, then Enter)
- Configure database: Select Yes
- MySQL application password: Create a strong password for phpMyAdmin (e.g., phpMyAdmin123!)

💡 Information
php-mbstring: Required for handling multi-byte character strings
php-zip: Enables import/export of compressed database files
php-gd: Required for generating graphics and charts
php-json: Handles JSON data operations
Even though we select apache2 during installation, we’ll configure it to work with Nginx

⚠️ Common Mistakes
Not selecting a web server during installation can cause configuration issues
Using a weak phpMyAdmin password creates security vulnerabilities

Step 2: Configure Nginx to Serve phpMyAdmin

Create a symbolic link to make phpMyAdmin accessible via Nginx:
```
sudo ln -s /usr/share/phpmyadmin /var/www/html/phpmyadmin
```
Update Nginx configuration to handle phpMyAdmin properly:
```
sudo nano /etc/nginx/sites-available/default
```

Add a specific location block for phpMyAdmin within the existing server block:

server {
    listen 80;
    root /var/www/html;
    index index.php index.html index.nginx-debian.html;

    # Existing configuration...

    # phpMyAdmin configuration
    location /phpmyadmin {
        root /var/www/html;
        index index.php index.html index.htm;

        location ~ ^/phpmyadmin/(.+\.php)$ {
            try_files $uri =404;
            root /var/www/html;
            include snippets/fastcgi-php.conf;
            fastcgi_pass unix:/var/run/php/php8.1-fpm.sock;
            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 /var/www/html;
        }
    }

    # Restrict access to phpMyAdmin from specific IPs (optional security measure)
    location /phpmyadmin {
        # allow YOUR_IP_ADDRESS;
        # deny all;
        try_files $uri $uri/ =404;
    }

    # General PHP processing (existing)
    location ~ \.php$ {
        include snippets/fastcgi-php.conf;
        fastcgi_pass unix:/var/run/php/php8.1-fpm.sock;
    }

    # Basic file serving (existing)
    location / {
        try_files $uri $uri/ =404;
    }
}

Test and reload Nginx configuration:

sudo nginx -t
sudo systemctl reload nginx

💡 Information
Symbolic link: Creates a reference to phpMyAdmin files without copying them
Location blocks: Nginx directives that define how to handle specific URL paths
IP restrictions: Optional security measure to limit access to specific IP addresses
The configuration ensures PHP files in phpMyAdmin are processed correctly

Step 3: Configure phpMyAdmin Security

Configure phpMyAdmin with enhanced security settings:
```
sudo nano /etc/phpmyadmin/config.inc.php
```

Add or modify the following security configurations:

<?php
/* Existing configuration... */

/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['AllowNoPassword'] = false;

/* Security settings */
$cfg['blowfish_secret'] = 'CHANGE_THIS_TO_32_RANDOM_CHARACTERS'; /* You must fill in this for cookie auth! */

/* Disable some potentially dangerous features */
$cfg['ShowPhpInfo'] = false;
$cfg['ShowServerInfo'] = false;
$cfg['ShowDbStructureCreation'] = false;
$cfg['ShowDbStructureLastUpdate'] = false;
$cfg['ShowDbStructureLastCheck'] = false;

/* Hide databases that user shouldn't see */
$cfg['Servers'][$i]['hide_db'] = '^(information_schema|performance_schema|mysql|sys)$';

/* Set session timeout */
$cfg['LoginCookieValidity'] = 3600; // 1 hour

/* Disable warning about configuration file */
$cfg['CheckConfigurationPermissions'] = false;
?>

Generate a secure blowfish secret:
```
openssl rand -base64 32
```
Replace CHANGE_THIS_TO_32_RANDOM_CHARACTERS with the generated string.

Set proper permissions for the configuration file:

sudo chmod 644 /etc/phpmyadmin/config.inc.php
sudo chown root:www-data /etc/phpmyadmin/config.inc.php

💡 Information
Blowfish secret: Used to encrypt cookies for session management
hide_db: Hides system databases that regular users shouldn’t modify
LoginCookieValidity: Automatically logs out users after specified time
AllowNoPassword: Prevents login without a password

Step 4: Create a Dedicated phpMyAdmin User

Connect to MySQL as root:
```
sudo mysql -u root -p
```

Create a dedicated user for phpMyAdmin administration:

-- Create phpMyAdmin admin user
CREATE USER IF NOT EXISTS 'phpmyadmin_user'@'localhost' IDENTIFIED BY 'SecureAdminPass123!';

-- Grant necessary privileges for database administration
GRANT ALL PRIVILEGES ON contact_db.* TO 'phpmyadmin_user'@'localhost';
GRANT SELECT ON mysql.user TO 'phpmyadmin_user'@'localhost';
GRANT SELECT ON mysql.db TO 'phpmyadmin_user'@'localhost';
GRANT SELECT ON mysql.host TO 'phpmyadmin_user'@'localhost';
GRANT SELECT ON mysql.tables_priv TO 'phpmyadmin_user'@'localhost';

FLUSH PRIVILEGES;
EXIT;

💡 Information
Dedicated user: Separate user for phpMyAdmin reduces security risks
Limited privileges: Only necessary permissions for database administration
mysql. tables*: Required for phpMyAdmin to display user and privilege information
This user can manage the contact_db but has limited system access

Step 5: Test phpMyAdmin Installation

Open your web browser and navigate to:
```
http://<VM_Public_IP>/phpmyadmin
```
You should see the phpMyAdmin login screen.
Log in using the credentials you just created:
- Username: phpmyadmin_user
- Password: SecureAdminPass123!
After successful login, you should see the phpMyAdmin dashboard with:
- Database list on the left sidebar
- Main content area showing server information
- Navigation tabs for different functions

✅ Verification Steps
phpMyAdmin login page loads without errors
Successful authentication with the dedicated user
Can see the contact_db database in the sidebar
No error messages about missing extensions

Step 6: Using phpMyAdmin - Basic Database Operations

6.1: Browse Database Structure

In the left sidebar, click on contact_db to expand it
Click on the contacts table to view its structure
Use the Structure tab to see column definitions:
- Column names (id, name, email, message, created_at)
- Data types (INT, VARCHAR, TEXT, TIMESTAMP)
- Key information (PRIMARY KEY on id)

6.2: View and Browse Data

Click the Browse tab to see all records in the contacts table
You should see any messages submitted through your contact form
Use the navigation controls to:
- Browse through pages if you have many records
- Sort data by clicking column headers
- Search for specific records

6.3: Insert New Data

Click the Insert tab to add a new contact manually
Fill in the form fields:
- name: “Test Admin User”
- email: “admin@example.com”
- message: “This is a test message from phpMyAdmin”
- Leave id and created_at empty (auto-generated)
Click Go to insert the record
Navigate back to Browse to see your new record

6.4: Execute SQL Queries

Click the SQL tab at the top of the interface

Try some basic SQL queries:

-- Count total messages
SELECT COUNT(*) as total_messages FROM contacts;

-- Find messages from a specific email domain
SELECT * FROM contacts WHERE email LIKE '%@gmail.com';

-- Get messages from the last 7 days
SELECT * FROM contacts WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);

-- Update a message (be careful with this!)
UPDATE contacts SET message = 'Updated message' WHERE id = 1;

Click Go to execute each query and see the results

💡 Information
Browse tab: Shows actual data stored in tables
Structure tab: Displays table schema and indexes
Insert tab: Provides a form interface for adding new records
SQL tab: Allows execution of custom SQL commands
Always be careful with UPDATE and DELETE operations

Step 7: Database Export and Backup

Select the contact_db database from the sidebar
Click the Export tab at the top
Choose export options:
- Export method: Quick (for simple backup) or Custom (for advanced options)
- Format: SQL (most common) or other formats like CSV
- Tables: Select all or specific tables to export
Click Go to download the backup file

Creating Automated Backups

Create a backup directory:

sudo mkdir -p /var/backups/mysql
sudo chown mysql:mysql /var/backups/mysql

Create a backup script:

sudo nano /usr/local/bin/backup_contact_db.sh

Add the backup script content:

#!/bin/bash

# Database backup script
DB_NAME="contact_db"
DB_USER="contact_app"
DB_PASS="StrongAppPassword123!"
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)

# Create backup
mysqldump -u $DB_USER -p$DB_PASS $DB_NAME > $BACKUP_DIR/contact_db_backup_$DATE.sql

# Keep only last 7 days of backups
find $BACKUP_DIR -name "contact_db_backup_*.sql" -mtime +7 -delete

echo "Backup completed: contact_db_backup_$DATE.sql"

Make the script executable:

sudo chmod +x /usr/local/bin/backup_contact_db.sh

Test the backup script:

sudo /usr/local/bin/backup_contact_db.sh

Setting Up Automated Backups with Cron

Create a cron job to run backups automatically:
```
sudo crontab -e
```
If prompted to choose an editor, select nano (option 1).

Add the following line to schedule daily backups at 2:00 AM:

# Daily database backup at 2:00 AM
0 2 * * * /usr/local/bin/backup_contact_db.sh >> /var/log/mysql_backup.log 2>&1

Save and exit the editor (Ctrl+X, then Y, then Enter).
Verify the cron job was added:
```
sudo crontab -l
```

Create the log file with proper permissions:

sudo touch /var/log/mysql_backup.log
sudo chown root:adm /var/log/mysql_backup.log
sudo chmod 640 /var/log/mysql_backup.log

Test the cron job by temporarily setting it to run every minute:
```
sudo crontab -e
```

Change the line to:

# Test backup every minute (remove after testing)
* * * * * /usr/local/bin/backup_contact_db.sh >> /var/log/mysql_backup.log 2>&1

Wait a few minutes, then check if backups are being created:
```
ls -la /var/backups/mysql/
cat /var/log/mysql_backup.log
```

Once confirmed working, change it back to daily at 2:00 AM:

sudo crontab -e

# Daily database backup at 2:00 AM
0 2 * * * /usr/local/bin/backup_contact_db.sh >> /var/log/mysql_backup.log 2>&1

💡 Information
Cron format: minute hour day month day-of-week command
**0 2 * * ***: Runs at 2:00 AM every day
» /var/log/mysql_backup.log: Appends output to log file
2>&1: Redirects error messages to the same log file
sudo crontab: Runs cron jobs with root privileges (needed for MySQL access)

For more advanced backup configurations, you can:

Set different schedules (every 6 hours: 0 */6 * * *, weekly: 0 3 * * 0)
Add error handling and status monitoring
Implement backup verification and testing

Step 8: Database Import and Restore

To restore from a backup using phpMyAdmin:
- Select the database (contact_db)
- Click the Import tab
- Click Choose File and select your SQL backup file
- Click Go to import

To restore from command line:

# Restore from backup file
mysql -u contact_app -pStrongAppPassword123! contact_db < /var/backups/mysql/contact_db_backup_YYYYMMDD_HHMMSS.sql

⚠️ Important Security Notes
Always backup before making significant changes
Test restores on a copy, not production data
Import operations will overwrite existing data
Keep backups in a secure location with appropriate permissions

Step 9: Security Hardening for phpMyAdmin

Create an additional security layer with HTTP authentication:
```
sudo nano /etc/nginx/sites-available/default
```

Add HTTP basic authentication to the phpMyAdmin location:

location /phpmyadmin {
    auth_basic "Admin Area";
    auth_basic_user_file /etc/nginx/.htpasswd;

    root /var/www/html;
    try_files $uri $uri/ =404;
}

Install apache2-utils to create password file:
```
sudo apt install apache2-utils -y
```

Create HTTP authentication credentials:

sudo htpasswd -c /etc/nginx/.htpasswd admin

Enter a strong password when prompted (e.g., WebAdmin123!)

Test and reload Nginx:

sudo nginx -t
sudo systemctl reload nginx

Now accessing phpMyAdmin will require both HTTP authentication and MySQL login.

💡 Information
HTTP Basic Auth: Adds an extra layer of protection before reaching phpMyAdmin
Double authentication: Users must pass HTTP auth, then MySQL auth
htpasswd: Creates encrypted password files for web server authentication
This significantly reduces automated attacks on phpMyAdmin

🧪 Core Installation Tests

Test 1: Verify Basic phpMyAdmin Access

Open browser and navigate to http://<VM_Public_IP>/phpmyadmin
Should prompt for HTTP authentication first (username: admin, password: WebAdmin123!)
Then show phpMyAdmin login screen
Login should work with phpmyadmin_user credentials (password: SecureAdminPass123!)

Test 2: Test Basic Database Operations

Browse the contacts table and verify data displays correctly
Insert a test record through phpMyAdmin interface
Execute a simple SELECT query in the SQL tab
Verify the new record appears in your contact form application

✅ Expected Results

phpMyAdmin loads without PHP errors or missing extensions
Database structure and data display correctly
SQL queries execute successfully
Double authentication (HTTP + MySQL) functions correctly

🎉 Basic phpMyAdmin Installation Complete!

Congratulations! You now have a fully functional phpMyAdmin installation for managing your MySQL database. You can:

Browse and edit database tables through a web interface
Execute SQL queries without command line access
View database structure and relationships
Perform basic database administration tasks

📦 Optional Advanced Features

The sections below cover additional features for production environments and automation. These are optional and not required for basic phpMyAdmin functionality.

Database Export and Backup (Optional)

To export databases for backup using phpMyAdmin:
- Select the contact_db database from the sidebar
- Click the Export tab at the top
- Choose export options:
  - Export method: Quick (for simple backup) or Custom (for advanced options)
  - Format: SQL (most common) or other formats like CSV
  - Tables: Select all or specific tables to export
- Click Go to download the backup file

Automated Backups with Scripts (Optional)

Creating Manual Backup Scripts

Create a backup directory:

sudo mkdir -p /var/backups/mysql
sudo chown mysql:mysql /var/backups/mysql

Create a backup script:

sudo nano /usr/local/bin/backup_contact_db.sh

Add the backup script content:

#!/bin/bash

# Database backup script
DB_NAME="contact_db"
DB_USER="contact_app"
DB_PASS="StrongAppPassword123!"
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)

# Create backup
mysqldump -u $DB_USER -p$DB_PASS $DB_NAME > $BACKUP_DIR/contact_db_backup_$DATE.sql

# Keep only last 7 days of backups
find $BACKUP_DIR -name "contact_db_backup_*.sql" -mtime +7 -delete

echo "Backup completed: contact_db_backup_$DATE.sql"

Make the script executable:

sudo chmod +x /usr/local/bin/backup_contact_db.sh

Test the backup script:

sudo /usr/local/bin/backup_contact_db.sh

Setting Up Automated Backups with Cron (Optional)

Create a cron job to run backups automatically:
```
sudo crontab -e
```
If prompted to choose an editor, select nano (option 1).

Add the following line to schedule daily backups at 2:00 AM:

# Daily database backup at 2:00 AM
0 2 * * * /usr/local/bin/backup_contact_db.sh >> /var/log/mysql_backup.log 2>&1

Save and exit the editor (Ctrl+X, then Y, then Enter).
Verify the cron job was added:
```
sudo crontab -l
```

Create the log file with proper permissions:

sudo touch /var/log/mysql_backup.log
sudo chown root:adm /var/log/mysql_backup.log
sudo chmod 640 /var/log/mysql_backup.log

Database Import and Restore (Optional)

To restore from a backup using phpMyAdmin:
- Select the database (contact_db)
- Click the Import tab
- Click Choose File and select your SQL backup file
- Click Go to import

To restore from command line:

# Restore from backup file
mysql -u contact_app -pStrongAppPassword123! contact_db < /var/backups/mysql/contact_db_backup_YYYYMMDD_HHMMSS.sql

🔧 Troubleshooting

If phpMyAdmin shows “The mbstring extension is missing”:

Restart PHP-FPM: sudo systemctl restart php8.1-fpm
Verify extension: php -m | grep mbstring

If you can’t access phpMyAdmin:

Check Nginx error logs: sudo tail -f /var/log/nginx/error.log
Verify symbolic link: ls -la /var/www/html/phpmyadmin
Test Nginx configuration: sudo nginx -t

If MySQL connection fails in phpMyAdmin:

Verify user exists: sudo mysql -u root -p -e "SELECT User FROM mysql.user;"
Check user privileges: Login as root and run SHOW GRANTS FOR 'phpmyadmin_user'@'localhost';

If backup script fails:

Check file permissions: ls -la /usr/local/bin/backup_contact_db.sh
Verify backup directory: ls -la /var/backups/mysql
Test database connectivity: mysql -u contact_app -pStrongAppPassword123! -e "SELECT 1;"

🚀 Optional Challenge

Want to enhance your phpMyAdmin setup further? Try:

Setting up SSL/HTTPS for encrypted phpMyAdmin access
Configuring phpMyAdmin to work with multiple databases
Creating custom themes for the phpMyAdmin interface
Setting up automated email notifications for backup completion
Implementing IP whitelisting for additional security
Creating read-only database users for safer data browsing

📚 Further Reading

phpMyAdmin Official Documentation - Comprehensive configuration and usage guide
phpMyAdmin Security Guide - Security best practices
MySQL Administration Guide - Understanding MySQL administration
W3Schools SQL Tutorial - Learn SQL for database operations
Nginx HTTP Auth Module - HTTP authentication configuration

Done! 🎉

Excellent work! You’ve successfully installed and configured phpMyAdmin for your LEMP stack and learned how to use it for database administration, backup operations, and security hardening! You now have a powerful web-based tool for managing your MySQL databases efficiently. 🚀