Setting Up MySQL on a Remote Server and Connecting via PHP

Introduction

Deploying MySQL on a remote server unlocks scalable application architecture but introduces security and configuration challenges. This guide walks through:

• Secure MySQL installation

• Remote access configuration

• Database migration strategies

• PHP connectivity

• Production hardening

• Performance tuning

Tested on Ubuntu 22.04 LTS | MySQL 8.0 | PHP 8.1

🔑 Prerequisites

1. Server Specifications:

   • Ubuntu 22.04 LTS (2+ vCPU, 4GB RAM recommended)

   • Public IP address (139.1.1.101 in this example)

2. Local Environment:

   • MySQL Workbench or mysqldump

   • SSH client (OpenSSH)

3. Security Essentials:

   • Firewall (UFW) enabled

   • SSH key authentication

🚀 Step-by-Step Implementation

🔐 1. Secure Server Initialization

# SSH with key authentication (recommended)
ssh -i ~/.ssh/your_key.pem karimtrd@139.1.1.101

# Create sudo user (if not exists)
sudo adduser deploy --gecos "" && sudo usermod -aG sudo deploy

Security Tip: Disable root SSH in /etc/ssh/sshd_config

🛠️ 2. MySQL Installation & Hardening

Install with TLS Support:

sudo apt update && sudo apt install mysql-server -y
sudo systemctl enable --now mysql

Configure Security:

sudo mysql_secure_installation

Follow these choices:

1. VALIDATE PASSWORD COMPONENT → Strong

2. Root password: Hello@Unlock!24Mysql

3. Remove anonymous users? → Yes

4. Disallow root login remotely? → Yes

5. Remove test database? → Yes

6. Reload privileges? → Yes

Verify Installation:

sudo mysql -u root -p
SHOW VARIABLES LIKE '%tls%';  # Confirm TLS active

🌐 3. Remote Access Configuration

Update MySQL Config:

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

[mysqld]
bind-address = 0.0.0.0
# Enable binary logging for point-in-time recovery
log-bin = /var/log/mysql/mysql-bin.log
server-id = 1

Firewall Rules:

sudo ufw allow proto tcp from 192.168.1.0/24 to any port 3306  # Limit to your network
sudo ufw limit 22/tcp  # Protect SSH from brute-force
sudo ufw enable

Restart Service:

sudo systemctl restart mysql

👤 4. Database User & Privileges

CREATE DATABASE postech22_karimtraders
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

CREATE USER 'vm_karimtraders'@'192.168.1.%' 
  IDENTIFIED WITH mysql_native_password BY 'Unlock!24Mysql@Do'
  REQUIRE SSL;  # Enforce TLS

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE 
  ON postech22_karimtraders.* 
  TO 'vm_karimtraders'@'192.168.1.%';

USE postech22_karimtraders;

FLUSH PRIVILEGES;

Note: Avoid GRANT ALL and '%' wildcards in production

🚚 Database Migration Strategies

# Local export with compression
mysqldump -u root -p --single-transaction --routines \
  --triggers postech22_karimtraders | gzip > db_$(date +%F).sql.gz

# Secure transfer
scp -i ~/.ssh/key.pem db_2023-10-05.sql.gz karimtrd@139.1.1.101:~

# Remote import
gunzip < db_2023-10-05.sql.gz | mysql -u vm_karimtraders -p postech22_karimtraders

Method 2: MySQL Replication (Large Databases)

1. Configure master-slave replication

2. Promote the slave to primary after cutover

3. Update application connection strings

🔌 PHP Connectivity & Best Practices

PDO Connection with Error Handling:

<?php
$servername = "139.1.1.101";
$username = "vm_karimtraders";
$password = "Unlock!24Mysql@Do";
$dbname = "postech22_karimtraders";
$ssl = [
    PDO::MYSQL_ATTR_SSL_CA => '/etc/ssl/certs/ca-certificates.crt',
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true
];

try {
    $conn = new PDO(
        "mysql:host=$servername;dbname=$dbname;charset=utf8mb4",
        $username,
        $password,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_PERSISTENT => true
        ] + $ssl
    );

    echo "Connected successfully with TLS encryption";
} catch (PDOException $e) {
    error_log("Connection failed: " . $e->getMessage());
    http_response_code(503);
    exit("Database maintenance in progress");
}
?>

Key Features:

• TLS encryption enforcement

• Prepared statements

• Persistent connections

• Graceful error handling

🔥 Production Hardening Checklist

1. Encryption:

    ALTER DATABASE postech22_karimtraders ENCRYPTION = 'Y';  # InnoDB tablespace encryption
   

2. Auditing:

    sudo apt install mysql-audit-plugin
   

3. Backup Automation:

    # Daily compressed backups with retention
    0 2 * * * /usr/bin/mysqldump -u bkpuser -p[Password] --single-transaction \
      --routines postech22_karimtraders | gzip > /backups/db_$(date +\%F).sql.gz \
      && find /backups -type f -mtime +30 -delete
   

4. Monitoring:

   • Enable Performance Schema: SET GLOBAL performance_schema=ON;

   • Install Percona Monitoring Plugins

⚡ Performance Tuning

my.cnf Optimizations:

[mysqld]
innodb_buffer_pool_size = 2G  # 70-80% of RAM
innodb_log_file_size = 256M
max_connections = 200
thread_cache_size = 50
query_cache_type = 0  # Disable on MySQL 8+

Connection Pooling in PHP:

$conn->setAttribute(PDO::ATTR_PERSISTENT, true);  # Reuse connections

Index Optimization:

EXPLAIN SELECT * FROM orders WHERE status = 'processing';
CREATE INDEX idx_orders_status ON orders(status);

🚨 Troubleshooting Guide

✅ Deployment Validation Checklist

1. Test local → remote connection: mysql -h 139.1.1.101 -u vm_karimtraders -p

2. Verify TLS: \s in MySQL client shows "SSL: Cipher in use"

3. Load test with sysbench

4. Validate backups: Restore to test instance

5. Test failover procedure (if replicated)

🌈 Conclusion

You've now deployed a production-grade MySQL server with:

• Encrypted network connections

• Least-privilege access control

• Automated backups

• Performance monitoring

• PHP connectivity best practices

Next Steps:

1. Implement read replicas for high-traffic apps

2. Set up automated failover with ProxySQL

3. Enable slow query logging for optimization

✨ Pro Tip: Use infrastructure-as-code tools like Ansible to automate this setup!

---

Enjoyed this guide?
✅ Follow me for more DevOps tutorials
🔁 Share with your engineering team
💬 Comment your deployment challenges!