Difference between revisions of "MySQL remote access"

Latest revision as of 21:27, 22 April 2019

Windows VPS - Unable to connect to mysql remotely

1. Verify you have port 3306 open in the VPS firewall. You can open it following the directions in this wiki: Granting remote access to MySQL root user
2. Verify any restrictions for access based on IP in the Windows Firewall, this may be locked down to hostek.com management servers only. You will need to add your IP as an allowed IP. To find your IP address, visit http://hostek.com/ip, and add to the "scope".

How to verify what hosts a user can connect from

1. For Windows VPSs, log into your VPS via RDP. For Linux VPSs, log into your VPS via SSH
2. Open a mysql command prompt
   • On Linux, just run the command mysql -u root -p. You will be prompted for the mysql root password.
   • On Windows, open a command prompt and run the command mysql -u root -p. You will be prompted for the mysql root password.
3. You should now be at a mysql command prompt. Run the following commands in order:

   use mysql;

   select user,host from user where user='username';

   • So for example, you want to verify what hosts the user foo can connect from. The command sequence would be:

   use mysql;

   select user,host from user where user='foo';

Granting remote access to root user

To grant remote access to the mysql root user on your VPS, follow this procedure:

1. For Windows VPSs, log into your VPS via RDP. For Linux VPSs, log into your VPS via SSH
2. Open a mysql command prompt
   • On Linux, just run the command mysql -u root -p. You will be prompted for the mysql root password.
   • On Windows, open a command prompt and run the command mysql -u root -p. You will be prompted for the mysql root password.
3. You should now be at a mysql command prompt. Run the following commands in order:

   use mysql;

   GRANT ALL PRIVILEGES ON *.* TO 'root'@'REMOTE_IP' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

   flush privileges

   • So for example, the remote public IP address you are connecting from is 1.2.3.4 and the mysql password is "WEON#@fasdpnq23?$$@". The command sequence you would run would be:

   use mysql;

   GRANT ALL PRIVILEGES ON *.* TO 'root'@'1.2.3.4' IDENTIFIED BY 'WEON#@fasdpnq23?$$@' WITH GRANT OPTION;

   flush privileges

   • [Click here] to determine what your public IP address is.

Please not that it is not advisable to use the root username for admin purposes, as this is the default and easily guessed by potential attackers. For improved security, it is advisable to use a different username for administrative purposes (you should also avoid using admin or administrator). There is no technical reason to use root as the admin username for mysql.

Restricting ALL Remote Access to MySQL

There are two simple ways to disable access to the 'root' MySQL user from all external connections. In this section, we'll go over both methods and how to implement them.

Method #1: Restricting via Firewall

The first method is simple for both Windows and Linux VPSs. It effectively blocks off the port used for MySQL transactions to the outside world so that MySQL will only work for requests inside the server.

On Windows:

1. Connect via RDP to your VPS, then click the Start icon and search Windows Defender Firewall with Advanced Security. Click the result.
2. Click the Inbound Rules tab on the top-left. Under the list of Inbound Rules, there should be one entitled MySQL. Double-click it.
3. On the new pop-up, click the checkmark next to Enabled to disable the rule and close off external access to your server's MySQL port.
4. Click OK to save your changes.

On Linux/cPanel:

1. After logging in to WHM, search for ConfigServer Security & Firewall at the top-left. Click the result.
2. Scroll down until you find a section called Firewall Configuration and click it.
3. Scroll down on the new page until you see a section called IPv4 Port Settings. In this section, you should see a box next to the letters TCP_IN.
4. Search through the numbers/ports in that box until you find 3306. Delete it and the comma after it to close off external access to your server's MySQL port.
5. Scroll all the way down to the bottom of the page and click Change. On the new page, click Restart csf+lfd to apply your changes.

Method #2: Restricting via MySQL Configuration

This method leaves the firewall alone and instead uses the 'skip-networking' command in MySQL that tells the service to not listen for incoming connections at all.

On Windows:

1. Connect via RDP to your VPS, then click the Start icon. Search for Services and click the result.
2. Scroll through this list until you see a service labeled MySQL. Right-click and choose Properties.
3. You should see a line that says Path to executable:. Under this path, look for the text --defaults-file=. This is the path to your MySQL configuration file. Keep the Services window open.
4. Open a new Windows File Explorer window and navigate to the path of the MySQL configuration file you found in step #3. For example, C:\MySQL\my.ini. The file should always be named my.ini. Edit this file.
5. Under the text [mysqld], enter the following line: skip-networking. Save the file, and switch back to your Services window.
6. Restart the MySQL service to save your changes by right-clicking it and choosing Restart.

On Linux/cPanel:

1. Log into your server via SSH.
2. Edit the /etc/my.cnf file by typing nano /etc/my.cnf
3. You should see the text [mysqld] at the top of the file. Right under this text, insert the following line: skip-networking.
4. Save the file by pressing CTRL+O. Hit enter, then press CTRL+X to close the file.
5. Restart the MySQL service to apply your changes with the command service mysql restart. Note: If you get an error, try using the service mysqld restart or service mariadb restart commands.