Difference between revisions of "MySQL remote access"

From Hostek.com Wiki
Jump to: navigation, search
m (Jonc moved page MySQL remote access for root user to MySQL remote access: Make subject more general with specific sub-topics. JC)
m (Restricting All Access to MySQL Root From Remote Addresses)
 
(5 intermediate revisions by 3 users not shown)
Line 1: Line 1:
==How to verify what hosts a user can connect from==
+
__FORCETOC__
 +
===Windows VPS - Unable to connect to mysql remotely===
 +
 
 +
#Verify you have port 3306 open in the VPS firewall. You can open it following the directions in this wiki: [[MySQL_remote_access_for_root_user#Granting_remote_access_to_root_user|Granting remote access to MySQL root user]]
 +
#Verify any [https://wiki.hostek.com/Firewall_Options_-_VPS/Dedicated#Restricting_Access_to_a_Port 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===
 
#For Windows VPSs, log into your VPS via RDP.  For Linux VPSs, log into your VPS via SSH
 
#For Windows VPSs, log into your VPS via RDP.  For Linux VPSs, log into your VPS via SSH
 
#Open a mysql command prompt
 
#Open a mysql command prompt
Line 11: Line 17:
 
#:''select user,host from user where user='foo';
 
#:''select user,host from user where user='foo';
  
==Granting remote access to root user==
+
===Granting remote access to root user===
 
To grant remote  access to the mysql root user on your VPS, follow this procedure:
 
To grant remote  access to the mysql root user on your VPS, follow this procedure:
  
Line 31: Line 37:
 
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.
 
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:
 +
#Connect via RDP to your VPS, then click the Start icon and search '''Windows Defender Firewall with Advanced Security'''. Click the result.
 +
#Click the '''Inbound Rules''' tab on the top-left. Under the list of '''Inbound Rules''', there should be one entitled '''MySQL'''. Double-click it.
 +
#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.
 +
#Click '''OK''' to save your changes.
 +
 +
On Linux/cPanel:
 +
#After logging in to WHM, search for '''ConfigServer Security & Firewall''' at the top-left. Click the result.
 +
#Scroll down until you find a section called '''Firewall Configuration''' and click it.
 +
#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'''.
 +
#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.
 +
#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:
 +
#Connect via RDP to your VPS, then click the '''Start''' icon. Search for '''Services''' and click the result.
 +
#Scroll through this list until you see a service labeled '''MySQL'''. Right-click and choose '''Properties'''.
 +
#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.
 +
#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.
 +
#Under the text '''[mysqld]''', enter the following line: '''skip-networking'''. Save the file, and switch back to your '''Services''' window.
 +
#Restart the MySQL service to save your changes by right-clicking it and choosing '''Restart'''.
 +
 +
On Linux/cPanel:
 +
#Log into your server via SSH.
 +
#Edit the '''/etc/my.cnf''' file by typing ''nano /etc/my.cnf''
 +
#You should see the text '''[mysqld]''' at the top of the file. Right under this text, insert the following line: '''skip-networking'''.
 +
#Save the file by pressing '''CTRL+O'''. Hit enter, then press '''CTRL+X''' to close the file.
 +
#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.
  
 
[[Category:VPS]]
 
[[Category:VPS]]
 
[[category:Databases-MySQL]]
 
[[category:Databases-MySQL]]

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.