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

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.

We need to create a user to give to the connection side, you can grant MySQL privileges to all databases or specific databases, specific tables.

The way to make the connection is as follows:

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

Create username and password for User:

Assume the newly created username and password are as follows:

// To GRANT ALL privileges to a user, allowing that user full control over all database, use the following syntax:

CREATE USER ‘username’@’%’ IDENTIFIED BY ‘password’;
GRANT ALL PRIVILEGES ON *.* TO ‘username’@’%’ WITH GRANT OPTION;
FLUSH PRIVILEGES;

OR

CREATE USER ‘username’@’localhost’ IDENTIFIED BY ‘password’;
GRANT ALL PRIVILEGES ON *.* TO ‘username’@’localhost’ WITH GRANT OPTION;

 

// To GRANT ALL privileges to a user, allowing that user full control over a specific database:

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

// Or control over a specific table

GRANT ALL PRIVILEGES ON DATABASE_NAME.TABLE_NAME TO ‘username’@’%’;

Get lists User

You can check the list of users with the command:

SELECT user FROM mysql.user;

Drop User Statement

DROP USER user [, user] …

Example:

DROP USER ‘username’@’localhost’;
DROP USER ‘username’@’%’;

The host name part of the account name, if omitted, defaults to ‘%’.

The DROP USER statement removes one or more MySQL accounts and their privileges. It removes privilege rows for the account from all grant tables. An error occurs for accounts that do not exist (https://dev.mysql.com).

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 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.