How to connect PHP to MySQL database on another server using PDO and MySQLi

by in Database


If normally the source code and database are on the same hosting server, you only need to set the server name to localhost. However, in many cases where we need to use an additional database or have an external server IP range, a little setup is required on this additional server.

When connecting via IP address on other server, you may get the following error: Host ‘xxx.xxx.xxx.xxx’ is not allowed to connect to this MySQL server

The way to make the connection is as follows:

 

I. Create a user and grant it permission before using to connect to.

Assume the newly created username and password are as follows:

CREATE USER ‘username’@’localhost’ IDENTIFIED BY ‘password’;

GRANT ALL PRIVILEGES ON *.* TO ‘username’@’localhost’ WITH GRANT OPTION;

CREATE USER ‘username’@’%’ IDENTIFIED BY ‘password’;

GRANT ALL PRIVILEGES ON *.* TO ‘username’@’%’ WITH GRANT OPTION;

FLUSH PRIVILEGES;

You can check the list of users with the command:

SELECT user FROM mysql.user;

 

II. Use PDO and MySQLi connect to MySQL database

On the connected server, the information is as follows:

$serverIP = “xxx.xxx.xxx.xxx”; // server ip or server name
$username = “username”;
$password = “password”;
$databaseName = “your_database_name”;
$port = 3306; // default_port or your_server_port

1. Connect PHP to MySQL via MySQLi

// create connection
$conn = new mysqli($serverIP, $username, $password, $databaseName, $port);

// check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}
echo “Connected successfully”;

2. Connect PHP to MySQL via PDO

$conn = new PDO(‘mysql:host=’ . $serverIP . ‘; port=’ . $port . ‘; dbname=’ . $databaseName, $username, $password);
try {
$conn->exec(“SET NAMES ‘utf8′”);
} catch (PDOException $ex) {
die($ex->getMessage());
}
return $conn;

3. When querying the database on another server

If after connecting you execute a query and get an error like: mysql illegal mix of collations for operation ‘=’

How to check: show variables like “collation_database”;

 

how-to-connect-php-to-mysql-database-on-another-server-using-pdo-and-mysqli

 

How to handle the error: You need to convert the database and the connected table to the same format as the other database, I called CurrentDatabase and NewDatabase, NewTable is in NewDatabase

ALTER DATABASE NewDatabase DEFAULT COLLATE utf8_unicode_ci;
ALTER TABLE NewDatabase CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

 

Thanks for reading the article.

Tags: , , , , ,