SQL Zone is brought to you in partnership with:

For the past eight(8) years Schalk Neethling has been working as a freelance developer under the pseudo of Volume4 and is now the president of Overt Strategy Consulting. During this period he has completed over 300 projects ranging from full web application development to complete branding. As president and lead developer of Overt Strategy Consulting, Schalk Neethling and his team has released a 100% Java standards based content management system called AlliedBridge and business document exchange and review system, called Doc-Central. Schalk Neethling is also actively involved on a daily basis in the open source, web standards and accessibility areas and is a current active member of the Web Standards Group. Schalk is also the co-founder and president of the non-profit The South Web Standards and Accessibility Group, which aims to actively educate and raise awareness of web standards and accessibility to both the developer society as well as business large and small. Schalk also has a long relationship with DZone and is currently zone leader for both the web builder, css.dzone.com, as well as the .NET zone, dotnet.dzone.com, and you can find a lot of his writing there as well as on his blog located at schalkneethling.alliedbridge.com. Schalk is constantly expanding on his knowledge of various aspects of technology and loves to stay in touch with the latest happenings. For Schalk web development and the internet is not just a job, it is a love, a passion and a life style. Schalk has posted 173 posts at DZone. View Full User Profile

How-To Remote MySQL Database Administration With MySQL GUI tools

06.18.2008
| 46322 views |
  • submit to reddit

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.

References
Published at DZone with permission of its author, Schalk Neethling. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)