Difference between revisions of "MySQL remote access"

Revision as of 05:49, 16 November 2012

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: MySQL_remote_access_for_root_user#Granting_remote_access_to_root_user 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.