All about MySQL Remote Access Under Plesk – Full Guide

Plesk is available in Cloud Server Plesk.Contact us to find out our latest offers!

MySQL is in the view of many people one of the most common database management systems in use on a day to day basis. You can run MySQL on just about any platform including Microsoft Windows and server UNIX varieties, like Linux, with many platforms also supporting MySQL remote access.

What is MySQL?

It’s often the engine that holds the data for  programs, including online publishing. Backed by Oracle, MySQL is an open source (everyone has access to the code) database that fits into the RDBMS (Relational database management system) paradigm.

MySQL has shifted hands a few times. It was first designed and developed by a company located in Sweden called MySQL AB, but was then bought out by Sun Microsystems in 2018. Though MySQL is owned by a commercial enterprise, developers may use it under the GNU GPL (or General Public License). However, enterprises must get a specific license from Oracle if they want to use MySQL in their applications.

How MySQL fits into Plesk Onyx

Plesk Onyx is database-driven and needs a database server to facilitate the various database services that Plesk components use. For example, there are databases involved in hosting Plesk Webmail. Each customer website will also use a database to store content while APS application databases are also required.

Every Plesk installation ships with a number of database management tools and Plesk supports a range of databases including MySQL. Plesk can interact with database servers located on the machine running Plesk. But Plesk can also link to a database server on a remote machine. You can also facilitate remote MySQL access via Plesk, should you have an external application that requires access to your MySQL databases.

Plesk MySQL Remote Access

In many instances, a user needs to facilitate a connection to a database from a third-party software tool that isn’t running on the same server as the database. You can enable Plesk MySQL remote access so that remote servers and hosts can access the MySQL databases in your Plesk account.

Activating MySQL Remote Access Under Plesk

For security reasons, Plesk doesn’t automatically make it possible to get remote MySQL access. We recommend that you create a new, separate user to use for remote MySQL connections. Nonetheless, if you prefer to do so, you can use the admin user account to facilitate remote MySQL access.

To enable MySQL remote access you must connect to your server with SSH/RDP and carefully follow the steps we list below:

    1. Login to Plesk and make sure the option Allow remote connections from any host is enabled at Tools & Settings > Database Servers > Settings.
    2. Connect to a Plesk server via SSH/RDP and open the MySQL configuration file in any text editor. Location of this file is:
      • For Linux:
        • for CentOS/RHEL-based distributions:
          /etc/my.cnf
        • for Debian/Ubuntu-based distributions:

/etc/mysql/my.cnf

      • For Windows:
        %plesk_dir%DatabasesMySQLmy.ini

3. Change bind-address parameter to:

bind-address = 0.0.0.0

Note: 0.0.0.0 means every IP address on the server. If it is required to bind to specific IP address, specify this particular IP instead of 0.0.0.0

4. Also, make sure that skip-networking parameter is not defined.

5. After that, restart MySQL server:

For Linux:

      • for CentOS/RHEL-based distributions:

# service mariadb restart

      • for Debian/Ubuntu-based distributions:

# service mysql restart

    1. Login to MySQL server
    2. Grant the access to remote IP address and login to MySQL. For example, if you want to allow access to database called database for user user with password password and remote IP address 203.0.113.2:

GRANT ALL ON database.* TO user@’203.0.113.2′ IDENTIFIED BY ‘password’;
FLUSH PRIVILEGES;

To create a user that has access to all databases, execute the following query:

GRANT ALL PRIVILEGES ON *.* TO ‘user’@’203.0.113.2’ IDENTIFIED BY ‘password’ REQUIRE NONE WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
FLUSH PRIVILEGES;

Note: to allow connections from any IP address use ‘ % ‘ character.

  • Make sure that MySQL server is listening on the correct IP address.

For Windows:

netstat -anp tcp | findstr 8306
TCP 0.0.0.0:8306 0.0.0.0:0 LISTENING

netstat -anp tcp | findstr 3306
TCP 0.0.0.0:3306 0.0.0.0:0 LISTENING

For Linux:

# netstat -anp | grep :3306
tcp 0 0 203.0.113.10:3306 0.0.0.0:* LISTEN 13151/mysqld

    1. Login from the remote server:

mysql -u someuser -p “password” -h example.com

The post All about MySQL Remote Access Under Plesk – Full Guide appeared first on Plesk.

Plesk is available in Cloud Server Plesk.Contact us to find out our latest offers!

Comments are closed.

>
WhatsApp chat WhatsApp us