In this short article I will demonstrate how you can set-up a user for remote access to your MySQL server. This will allow you to use the array of awesome GUI tools offered by MySQL AB to administer your remote MySQL databases.
In this article I will be using phpMyAdmin to apply the rights therefore; in order to follow along with this article you will need the following software, all of which are available as free open source downloads. UPDATE: If you have access to SSH or some form of command line utility and are comfortable using this you can also grant the needed privileges running the following command as root:
GRANT ALL PRIVILEGES ON *.* to 'root'@'%' IDENTIFIED BY 'password'
On your server you should have installed and running versions of Apache Web Server and MySQL server. If you need help in setting up the software applications, either ask your system administrator or read my article on getting a WAMP environment set-up.
So, once you have everything installed and running the next step is to login to your phpMyAdmin application. Go to the URL where this is installed and log in using your root username and password. Once logged in you will be presented by the phpMyAdmin home interface. On this page you will find a link called ‘Privileges’. Click on this link which will take you to the following page where you can change the privileges to allow for remote administration.
On the right hand side of the list of users is a link to edit the user, click on the edit link for the user you want to give remote access. In my case I have chosen to give my root user this privilege as I want to be able to manage all my databases with one login. You can of course provide limited access by giving a user remote access that has only been given access right to a predefined database or databases.
On the next screen look for a ‘fieldset’ with the title ‘Change Login Information / Copy User’. This is where you are going to change the access rights. On the Host drop down form field change the value from ‘local’ to ‘Any Host’ and hit the ‘Go’ button. Your query will execute and you should receive a message that states ‘Your SQL query has been executed successfully’.
Right, now we have a user that has remote access right, the only steps left over is to add these credentials to the MySQL GUI tools. So without further a due, launch your MySQL Administrator. BTW, you can safely logout of phpMyAdmin at this stage. When the administrator has launched you will be presented with the following screen.
Now, go ahead and click on the button top right labeled ‘…’. This will launch the Options dialog where we will be creating our connection.
First stop is to create a new connection, so go ahead and click on the button labeled ‘New Connection’. First thing is to give your connection a name. Next, type in the username and password for the user we previously granted remote access rights too. In the following field add your host name. Leave the port number as is unless you know that your MySQL server is running on a different port. If you want to limit this connection to only a specific database you can enter the name of this database in the ‘Schema’ field. After having entered all of the above you should click on ‘Apply’ and then ‘Close’. You now have a new connection that you can select from the ‘Stored Connection’ drop down. For security reasons the password field will be blank, so go ahead and re-enter your password and hit Ok.
If you have followed along and entered everything correctly you will be presented with the MySQL Administrator window. From here you can administer your remote databases in a myriad of ways from doing back-ups and restores to setting up a replication schedule. Your first step though would more then likely be the ‘Catalogs’ view, which will list all of the databases hosted by this MySQL server or just the schema you entered earlier during the set-up process.
That is all there is to it. I hope you find this tutorial useful and that it will go some way in making your MySQL database administration easier.