Difference between revisions of "MySQL remote access"
From Hostek.com Wiki
m (Jonc moved page MySQL remote access for root user to MySQL remote access: Make subject more general with specific sub-topics. JC) |
|||
| Line 1: | Line 1: | ||
| − | ==How to verify what hosts a user can connect from== | + | ===Windows VPS - Unable to connect to mysql remotely=== |
| + | |||
| + | #Verify you have port 3306 open in the VPS firewall | ||
| + | #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 16: | ||
#:''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: | ||
Revision as of 19:21, 23 October 2012
Windows VPS - Unable to connect to mysql remotely
- Verify you have port 3306 open in the VPS firewall
- 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
- For Windows VPSs, log into your VPS via RDP. For Linux VPSs, log into your VPS via SSH
- 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.
- 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:
- For Windows VPSs, log into your VPS via RDP. For Linux VPSs, log into your VPS via SSH
- 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.
- 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.