# 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**

```bash
# 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**:

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

**Configure Security**:

```bash
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**:

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

#### **🌐 3. Remote Access Configuration**

**Update MySQL Config**:

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

```plaintext
[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**:

```bash
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**:

```bash
sudo systemctl restart mysql
```

#### **👤 4. Database User & Privileges**

```sql
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**

```bash
# 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
<?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**:
    
    ```sql
    ALTER DATABASE postech22_karimtraders ENCRYPTION = 'Y';  # InnoDB tablespace encryption
    ```
    
2.  **Auditing**:
    
    ```bash
    sudo apt install mysql-audit-plugin
    ```
    
3.  **Backup Automation**:
    
    ```bash
    # 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**:

```plaintext
[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**:

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

**Index Optimization**:

```sql
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](https://www.bikiran.com/). 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**](https://yourprofile/) for more DevOps tutorials  
🔁 **Share** with your engineering team  
💬 **Comment** your deployment challenges!


---

## 🏢 About Bikiran

**[Bikiran](https://bikiran.com/)** 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](https://bikiran.com/)                                      | Main platform — Domain, hosting & cloud services        |
| 2   | Website      | [Edusoft](https://www.edusoft.com.bd/)                               | Education management software for institutions          |
| 3   | Website      | [n8n Clouds](https://n8nclouds.com/)                                 | Managed n8n workflow automation hosting                 |
| 4   | Website      | [Timestamp Zone](https://www.timestamp.zone/)                        | Unix timestamp converter & timezone tool                |
| 5   | Website      | [PDFpi](https://pdfpi.bikiran.com/)                                  | Online PDF processing & manipulation tool               |
| 6   | Website      | [Blog](https://blog.bikiran.com/)                                    | Technical articles, guides & tutorials                  |
| 7   | Website      | [Support](https://support.bikiran.com/)                              | 24/7 customer support portal                            |
| 8   | Website      | [Probackup](https://probackup.bikiran.com/)                          | Automated database backup for SQL, PostgreSQL & MongoDB |
| 9   | Service      | [Domain](https://www.bikiran.com/domain)                             | Domain registration, transfer & DNS management          |
| 10  | Service      | [Hosting](https://www.bikiran.com/services/hosting/web)              | Web, app & email hosting on NVMe SSD                    |
| 11  | Service      | Email & SMS                                                          | Bulk email & SMS notification service                   |
| 12  | npm          | [Chronopick](https://www.npmjs.com/package/@bikiran/chronopick)      | Date & time picker React component                      |
| 13  | npm          | [Rich Editor](https://www.npmjs.com/package/@bikiran/editor)         | WYSIWYG rich text editor for React                      |
| 14  | npm          | [Button](https://www.npmjs.com/package/@bikiran/button)              | Reusable React button component library                 |
| 15  | npm          | [Electron Boilerplate](https://www.npmjs.com/package/create-edx-app) | CLI to scaffold Electron.js project templates           |
| 16  | NuGet        | [Bkash](https://www.nuget.org/packages/Bikiran.Payment.Bkash)        | bKash payment gateway integration for .NET              |
| 17  | NuGet        | [Bikiran Engine](https://www.nuget.org/packages/Bikiran.Engine)      | Core .NET engine library for Bikiran services           |
| 18  | Open Source  | [PDFpi](https://github.com/bikirandev/pdfpi)                         | PDF processing tool — open source                       |
| 19  | Open Source  | [Bikiran Engine](https://github.com/bikirandev/Bikiran.Engine)       | Core .NET engine — open source                          |
| 20  | Open Source  | [Drive CLI](https://github.com/bikirandev/DriveCLI)                  | CLI tool to manage Google Drive from terminal           |
| 21  | Docker       | [Pgsql](https://github.com/bikirandev/docker-pgsql)                  | Docker setup for PostgreSQL                             |
| 22  | Docker       | [n8n](https://github.com/bikirandev/docker-n8n)                      | Docker setup for n8n automation                         |
| 23  | Docker       | [Pgadmin](https://github.com/bikirandev/docker-pgadmin)              | Docker setup for pgAdmin                                |
| 24  | Social Media | [LinkedIn](https://www.linkedin.com/company/bikiran12)               | Bikiran on LinkedIn                                     |
| 25  | Social Media | [Facebook](https://www.facebook.com/bikiran12)                       | Bikiran on Facebook                                     |
| 26  | Social Media | [YouTube](https://www.youtube.com/@bikiranofficial)                  | Bikiran on YouTube                                      |
| 27  | Social Media | [FB n8nClouds](https://www.facebook.com/n8nclouds)                   | n8n Clouds on Facebook                                  |