MySQL: Creating a user account

This entry is part 1 of 26 in the series Learn MySQL

Here in this series, we create a series of topics related to MySQL.

Mostly they are common tasks that we need to do if we have a system that uses MySQL.

In our earlier posts, we have created some series related to MySQL.  We will continue from those series.

If you have downloaded and installed WAMP server, the only MySQL user created is root when you first started it.

Say now we want to create a new user called dbuser.

To create the new user, we will first have to login using root.

mysql_login

 

From the command line,we first login using:

mysql -h localhost -u root -p

Now we set up the user dbuser with privilleges for accessing the database allartist.

The command use is as follow and the password is set by using identified by ‘dbuser’.

create user ‘dbuser’@’localhost’ identified by ‘dbuser’;

grant all on allartist.* to ‘dbuser’@’localhost’;

dbuser

 

 

 

MySQL: Connect using Toad

This entry is part 2 of 26 in the series Learn MySQL

After creating dbuser and granting privilleges to access the the database allartist.  We can use Toad to try to connect to the db using the newly created user.

toad_connect

Check that the Toad connection properties are similar to the screenshot above.  Set both the user and the password as dbuser.

 

MySQL: Show version

This entry is part 3 of 26 in the series Learn MySQL

It is always good to run sql command on a client like Toad instead of running the commands on the command lines.

Here we use Toad to show the version of MySQL we are currently using.

The MySql command is:

SHOW VARIABLES LIKE "%version%";

show_version

 

The version that I am using is 5.6.12.

 

MySQL: Check status and user

This entry is part 4 of 26 in the series Learn MySQL

When using command line, you can check the status and user currently connected to the DB.

Simply type status and a page as below will be shown:

status

The page here shows that you are connected using dbuser.

It also shows the uptime of the MySQL server which has been running for about 14hrs.

For Toad, the user dbuser is shown on the bottom right hand side of the page.

user

 

MySQL: Specifying connection parameters

This entry is part 5 of 26 in the series Learn MySQL

If you would like to fix the connection to only one user, you can update a plain text file my.ini.

If you use WAMP, the file my.ini is located in C:\wamp\bin\mysql\mysql5.6.12.

I have edited mine to:

[client]

host = localhost

user = dbuser

password = dbuser

Now if you type mysql in the command line, you will be login as dbuser.