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

Issue	Diagnosis Command	Solution
Connection timeout	telnet 139.1.1.101 3306	Check UFW rules and bind-address
"Access Denied" errors	SELECT host FROM mysql.user WHERE user='vm_karimtraders';	Verify user privileges
High CPU usage	SHOW PROCESSLIST;	Optimize slow queries
Replication lag	SHOW SLAVE STATUS\G	Increase innodb_buffer_pool_size
Can't connect via PHP	`php -m	grep mysql`

✅ 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

CONTACT:

I’m Kumar Bishojit Paul, the Founder and CEO of BIKIRAN. If you need further assistance, please leave a comment. I’m interested in helping you.

✨ 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!

---

🏢 About Bikiran

Bikiran is a software development and cloud infrastructure company founded in 2012, headquartered in Khulna, Bangladesh. With 15,000+ clients and over a decade of experience, Bikiran builds and operates a suite of products spanning domain services, cloud hosting, app deployment, workflow automation, and developer tools.

SL	Topic	Product	Description
1	Website	Bikiran	Main platform — Domain, hosting & cloud services
2	Website	Edusoft	Education management software for institutions
3	Website	n8n Clouds	Managed n8n workflow automation hosting
4	Website	Timestamp Zone	Unix timestamp converter & timezone tool
5	Website	PDFpi	Online PDF processing & manipulation tool
6	Website	Blog	Technical articles, guides & tutorials
7	Website	Support	24/7 customer support portal
8	Website	Probackup	Automated database backup for SQL, PostgreSQL & MongoDB
9	Service	Domain	Domain registration, transfer & DNS management
10	Service	Hosting	Web, app & email hosting on NVMe SSD
11	Service	Email & SMS	Bulk email & SMS notification service
12	npm	Chronopick	Date & time picker React component
13	npm	Rich Editor	WYSIWYG rich text editor for React
14	npm	Button	Reusable React button component library
15	npm	Electron Boilerplate	CLI to scaffold Electron.js project templates
16	NuGet	Bkash	bKash payment gateway integration for .NET
17	NuGet	Bikiran Engine	Core .NET engine library for Bikiran services
18	Open Source	PDFpi	PDF processing tool — open source
19	Open Source	Bikiran Engine	Core .NET engine — open source
20	Open Source	Drive CLI	CLI tool to manage Google Drive from terminal
21	Docker	Pgsql	Docker setup for PostgreSQL
22	Docker	n8n	Docker setup for n8n automation
23	Docker	Pgadmin	Docker setup for pgAdmin
24	Social Media	LinkedIn	Bikiran on LinkedIn
25	Social Media	Facebook	Bikiran on Facebook
26	Social Media	YouTube	Bikiran on YouTube
27	Social Media	FB n8nClouds	n8n Clouds on Facebook