如何通过PHP连接跨服务器MySQL数据库？附服务器信息求代码

Hey there! Connecting to a MySQL database hosted on server B (alrand.ly) from server A (rec.misuratau.edu.ly) is straightforward with PHP, but first let's cover the critical prerequisites you need to sort out:

Prerequisites #

• MySQL User Permissions: On server B, you need to create (or update) a MySQL user that allows connections from server A's domain/IP. Run this query on server B's MySQL console:

  GRANT ALL PRIVILEGES ON your_target_database.* TO 'your_mysql_user'@'rec.misuratau.edu.ly' IDENTIFIED BY 'your_secure_password';
  FLUSH PRIVILEGES;
  

• Firewall Access: Ensure server B's firewall (like UFW or iptables) allows incoming traffic on MySQL's default port 3306 (or your custom port) from server A's IP.
• PHP Extensions: Server A's PHP environment must have either the mysqli or PDO extension enabled (most hosting environments have these pre-installed).

Implementation Code #

Below are two reliable approaches to connect:

1. Using MySQLi (Object-Oriented Style)

This is a MySQL-specific extension, easy to use for simple use cases:

<?php
// Remote database credentials (server B details)
$remoteHost = 'alrand.ly';
$dbName = 'your_target_database';
$dbUser = 'your_mysql_user';
$dbPass = 'your_secure_password';
$dbPort = 3306; // Adjust if you're using a non-default port

try {
    // Initialize connection
    $conn = new mysqli($remoteHost, $dbUser, $dbPass, $dbName, $dbPort);

    // Check for connection errors
    if ($conn->connect_error) {
        throw new Exception("Connection failed: " . $conn->connect_error);
    }

    echo "Successfully connected to the remote MySQL database on alrand.ly!\n";

    // Example: Run a sample query
    $query = "SELECT id, name FROM your_table LIMIT 5";
    $result = $conn->query($query);

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            echo "ID: {$row['id']} | Name: {$row['name']}\n";
        }
    } else {
        echo "No records found.\n";
    }

    // Clean up
    $result->free();
    $conn->close();
} catch (Exception $e) {
    die($e->getMessage());
}
?>

2. Using PDO (PHP Data Objects)

PDO is a more flexible, database-agnostic extension that supports parameterized queries (great for preventing SQL injection):

<?php
// Remote database credentials
$remoteHost = 'alrand.ly';
$dbName = 'your_target_database';
$dbUser = 'your_mysql_user';
$dbPass = 'your_secure_password';
$dbPort = 3306;

try {
    // Initialize PDO connection
    $conn = new PDO(
        "mysql:host=$remoteHost;port=$dbPort;dbname=$dbName;charset=utf8mb4",
        $dbUser,
        $dbPass,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]
    );

    echo "Successfully connected to the remote MySQL database on alrand.ly!\n";

    // Example: Run a prepared query (safer for dynamic data)
    $query = "SELECT id, name FROM your_table LIMIT 5";
    $stmt = $conn->prepare($query);
    $stmt->execute();

    $records = $stmt->fetchAll();
    foreach ($records as $row) {
        echo "ID: {$row['id']} | Name: {$row['name']}\n";
    }

} catch (PDOException $e) {
    die("Connection/Query Error: " . $e->getMessage());
}

// PDO closes the connection automatically when the script ends, but you can explicitly do:
$conn = null;
?>

Important Notes #

• Never hardcode credentials: Store them in a separate config file with restricted permissions (e.g., chmod 600 config.php) or use environment variables.
• Connection Timeouts: If you get timeout errors, double-check server B's firewall rules and MySQL user permissions.
• Security: Always use parameterized queries (especially with user input) to avoid SQL injection attacks—PDO makes this seamless.

内容的提问来源于stack exchange，提问作者Mustafa Aburwais