Search Our Database

Introduction

While it is a common practice to have both website and database resides in the same server, it is not considered as the best practice due to multiple reasons such as performance, security or scalability. In this guide, we will provide the steps on how to connect to your MySQL database remotely via SSH

Prerequisites

• SSH access for the web and database servers
• MYSQL installed in both web and database servers
• Ensure both servers able to connect via Firewall (Such as iptables or CSF)

1.  Login into MySQL server as root

mysql -u root -p

2. Create new user that will access the database. In this case, user – ips1 that will access the server from – 192.168.1.1 using password – ips1DbUserPassword

CREATE USER 'ips1'@'192.168.1.1' IDENTIFIED BY 'ips1DbUserPassword';

3. If you wish to allow database user – ips1 to access database – myDatabase from IP address – 192.168.1.1, execute the following command.

GRANT ALL PRIVILEGES ON myDatabase.* TO ips1@'192.168.1.1' IDENTIFIED BY 'ips1DbUserPassword' with grant option; flush privileges;

4. If you wish to revoke database user ips1 from accessing myDatabase at IP address – 192.168.1.1, execute the following command.

DELETE FROM mysql.user WHERE User = 'ips1' AND Host = '192.168.1.1'; flush privileges;

Conclusion

Separating the website and database in different servers allows each servers to have their own individual maintenance and minimize downtime period. Additionally, isolating the database improve security and helps meet compliance standards for sensitive data protection

Article posted by Louis on 23 February, 2020