how to grant MySQL privileges to client machines to access databases (command-line)

I will be showing you on how to grant MySQL privileges to client machines to access databases.

So, let’s get started.

Dependencies:

  • You must have a MySQL Server installed in your computer. If you don’t have, go to MySQL website and download the MySQL Server.
  • We also need the PhpMyAdmin for MySQL user interface or you can choose your own like SQLyog, etc. (This is included in the WAMPServer)
  • To save your time installing and configuring the MySQL Server, simply download and install the WAMP Server. It is free of charge and can be downloaded at their website WampServer. Windows Apache MySQL Server Php
In this stage, I assume you already installed the WAMPServer or MySQL Server. In my case, I installed the MySQL Server 5.5 and PhpMyAdmin and the Apache Server(separate install). But if you installed WAMP, it’s ok because there’s no big difference.

1. Open your MySQL Server. If you installed this manually, hit start orb->type in ‘services‘ in the searchbox and select the Services in the search results. Now, scroll down until you find the MySQL and right-click ‘Start‘. If you’re using WAMPServer, just fire it up and it will run the services.

2. Now, let’s open the MySQL Command Line/Console.

Using WampServer
3. A command prompt will appear and type in your password. If your root(default) has no password, just hit enter.
NOTE: In case you have another user account created and has password, you can type in its password or use the root password. MySQL will detect the user and privilege so nothing to worry.
In my case, I set a password to the root so I’m going to type in the root password.
4. Now, type in this code:
GRANT ALL PRIVILEGES ON databasename.* TO username @ ‘%oripaddress_of_computerorlocalhost‘ IDENTIFIED BY ‘password’;

Example: GRANT ALL PRVILEGES ON mydatabase.* TO yanyan @ ‘%’ IDENTIFIED BY ‘yanyanpassword’;

Simple explanation on code :

GRANT ALL PRIVILEGES – add,update,delete etc.

mydatabase.* – the database that will be using.

* (ALL) – means all access to the tables in the database. In this part of the code, only the database specified will be accessed. If you want all the database be accessed by other machines, then change it to *.*

username – you can create a new user, just type in a username (ex.yanyan) or you can use root (which makes another account with root username) . You can set your username to root but I will not recommend you to use the root because root is the default user (it will be confusing in your part because you’ll be having a new user with the same username).

———————————-

% – makes the database, username, password available to all host/computer in the network.

192.168.0.110 (using IP Address) – using this line/code, you are limiting which computer can connect to the specified mysql database. In this part, I put 192.168.0.110 so only the computer with this ip address can connect to specified mysql database. (I’m expecting that computers in your network have a fixed ip address). I do not recommend you to do this but I just have to add in this tutorial for you to know that IP Addresses work.

localhost – as we all know, localhost is your local machine.

———————————-

Now, you can choose either of the three.

password – of course, your account password (ex.yanyanpassword).

Now, let’s try.
Here’s my code : GRANT ALL PRIVILEGES ON itrmcdb.* TO yanyan @’%’  IDENTIFIED BY ‘yanyanpassword’;

After that, we need to refresh the privileges. Type these line : FLUSH PRIVILEGES;

And finally, type exit  to end.

Now we’ve done on granting privileges. What we’re going to do is to check.
Open your browser and type in http://localhost/phpmyadmin and login as root to check if the user has been created. Go to Privileges tab and you’ll see the user account.
If you find this post helpful, please do share and like this. Also, feel free to post comments and suggestions.

website resources

Here are some websites you might want:

 website url: http://www.google.com

–  Google Inc. is an American multinational public corporation invested in Internet search, cloud computing, and advertising technologies. Google hosts and develops a number of Internet-based services and products, and generates profit primarily from advertising through its AdWords program. The company was founded by Larry Page and Sergey Brin, often dubbed the “Google Guys”, while the two were attending Stanford University as PhD candidates. Maybe Google is your default homepage of your favorite Internet Browser.

 website url: http://www.youtube.com

– YouTube is a video-sharing website, created by three former PayPal employees in February 2005, on which users can upload, share and view videos. The company is based in San Bruno, California, and uses Adobe Flash Video and HTML5 technology to display a wide variety of user-generated video content, including movie clips, TV clips, and music videos, as well as amateur content such as video blogging and short original videos. Most of the content on YouTube has been uploaded by individuals, although media corporations including CBSBBCVEVOHulu, and other organizations offer some of their material via the site, as part of the YouTube partnership program.

 website url: http://www.fullandfree.info

Full and Free was made especially for all of you who seek on a daily basis the latest software,music,movies,movie trailers e.t.c.

 website url: http://www.makeuseof.com/

MakeUseOf launched in July 2006 and now has approximately 450,000 active subscribers and serves about 15 million pageviews a month. It also ranks among the most popular blogs on the web according to Technorati and in top 1000 websites worldwide according to Alexa (an AOL company).

 website url : http://thepiratebay.org/

– The Pirate Bay is the worlds largest bittorrent tracker. Bittorrent is a filesharing protocol that in a reliable way enables big and fast file transfers. This is an open tracker, where anyone can download torrent files. To be able to upload torrent files, write comments and personal messages one must register at the site. This is of course free.

 website url : http://www.videocopilot.net/

– Video Copilot is a collaborative resource for training, design tools and artists. Our goal is to show you what is possible and how effects can be created so that you can apply these techniques on your own creative adventures. We strive to develop distinctive and innovative tools that improve the quality and speed of your production while offering freedom and flexibility.