Difference between revisions of "MySQL remote access"

Revision as of 19:19, 23 October 2012

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.