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.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s