MySQL Remote Access

Certainly not strictly a Joomla! thing...  You may want to use a remote client for database administration and modifications, or perhaps you have need to integrate a desktop application with the database that runs your website. Whatever the reason, if you need to access a MySQL database remotely, doing so is pretty simple if know how.

First of all, I figured this out by reading instructions on www.cyberciti.bizinstructions on www.cyberciti.biz.  The instructions there are thorough, and I recommend that you try them IF the following doesn't work...

Basically there are 3 things that need to be correct for you to access your MySQL database remotely:

  1. The server firewall, and possibly your local firewall need to be configured to allow access on PORT 3306, which is used by MySQL by default.
  2. The MySQL installation on the server needs to be configured to allow access by users who are not in the 'localhost' domain.
  3. A user needs to be setup with the right permissions for the MySQL database.

It may be the case that the way that your server is setup already accomodates 1 and 2 above.  If not, your means of accomplishing these tasks are going to vary widely.

If you have a dedicated 'root' server hosted with an ISP then chances are that you have control panel access that will allow you to configure your firewall.  To make this as small a firewall hole as possible, add an ALLOW rule for port 3306 for your specific IP address from which you will be accessing the MySQL server.  If you don't have control panel access that allows you to configure your firewall, and you don't already know how to do everything in this article, you need a new host!  I recommend 1and1.

Modifying  the MySQL configuration to allow remote access is pretty simple and is covered well hereModifying  the MySQL configuration to allow remote access is pretty simple and is covered well here.

Lastly - and try this first as it may just work, you need to setup the user and GRANT access to the database in question.  First login to MySQL.  To do this, connect to your server via SSH.  Once connected issue these commands:

[root@yourserver]# mysql -u root -p 

Hit return.  MySQL will prompt you for the root password.  Type it in and hit return.  (BTW...  the [root@yourserver]# bit above is not something that you type - it represents the UNIX prompt - what you type is the bit after the #)

Once your into the mysql app, your prompt will look something like:

mysql>

Now you are going to create the user and GRANT rights.  Don't use an existing username that is configured for local access, as doing this will change their password and domain, resulting in the account no longer working in the local domain.

mysql> GRANT ALL ON database_name.* TO databaseuser@'192.168.1.1' IDENTIFIED BY 'password';

When you do this you will need replace the generic values with real ones.  For instance If the following were true for your setup;

  • Database you want to use = my_joomladb
  • New database user account  = my_joomlaadmin
  • The new user password (you are setting this up now!) = scoobydoo
  • Your IP address =22.66.176.243

Your command would look like:

mysql> GRANT ALL ON my_joomladb.* TO my_joomlaadmin@'22.6.176.243' IDENTIFIED BY 'scoobydoo'; 

that's it really.  Lastly you exit mysql:

mysql> exit 

Like I said, if that doesn't work, refer to this article for a more detailed step-by-step.refer to this article for a more detailed step-by-step. Good luck.  Your milage will almost cerainly vary.