Instructions for Creating a Remote MySQL Database on a VPS with AlmaLinux 8 using Hestia and phpMyAdmin

by | Jun 14, 2024 | Tutorials, VPS | 0 comments

Prerequisites

  • Access to the VPS: If you don’t have it, you can get your VPS here. (you can request this installation directly when requesting your VPS)
  • Hestia Control Panel: Hestia must be installed and configured with phpMyAdmin and MySQL/MariaDB.

Detailed Steps

1. Access Hestia Control Panel

Open your web browser and navigate to your Hestia control panel URL. Generally, it will be something like

https://your-domain.com:8083

Log in with your username and password.

2. Create the Database

In the Hestia control panel, go to the “Database” section. Click on “Add Database” and fill in the required fields:

  • Database: Name of the database.
  • User: Username for the database.
  • Password: Password for the database user.
  • Host: Leave this as % if you want to allow access from any host, or specify a specific IP.

3. Configure Remote Access in MySQL

Access your VPS via SSH and edit the MySQL configuration file to allow remote connections. Generally, this is done in the my.cnf or mysqld.cnf file, usually located in /etc/my.cnf or /etc/mysql/mysql.conf.d/: Find the line bind-address and change its value to 0.0.0.0 to allow connections from any IP:

sudo nano /etc/my.cnf

bind-address = 0.0.0.0

Save the file and restart the MySQL service:

sudo systemctl restart mysqld

4. Configure the Firewall

Make sure port 3306 (the default MySQL port) is open on the VPS firewall:

sudo firewall-cmd --permanent --add-port=3306/tcp
sudo firewall-cmd --reload
5. Verify Remote Access
From your local machine or any other machine, try to connect to the MySQL database on the VPS using a MySQL client or command line:
mysql -u username -p -h vps_ip
Enter the password when prompted.
6. Access and Manage the Database with phpMyAdmin
Open your web browser and navigate to the URL where phpMyAdmin is installed. Generally, it will be something like
http://your-domain.com/phpmyadmin
Log in with the username and password you created in Hestia for the database. From phpMyAdmin, you can manage the database remotely.

Additional Notes
  • Security: Allowing remote access to MySQL can be a security risk. Ensure you use strong passwords and, if possible, restrict the IPs that can connect to your MySQL server.
  • Backups: Regularly back up your databases to prevent data loss.