1

I'm self learning SQL. I've completed the SQLzoo course and wanted to get my hand dirty using a free Microsoft test database and MariaDB as client. I've downloaded the database and saved it on the following path:

/usr/bin/northwind_mysql.sql

To access the database I've tried the following command but 

gianluca@gianluca-Aspire-S3-391 ~ $ mysql -u gianluca -p -h localhost northwind_mysql
Enter password: 
ERROR 1044 (42000): Access denied for user 'gianluca'@'localhost' to database 'northwind_mysql'

What I'm doing wrong? Is there any clear Getting Started guide somewhere for people who don't have any experience with SQL? I'm using it at work (MS SQL Server 2008), but I'm only querying the database with simple reading script. I would like to start learning more, for instance how to start it.

Thank you in advance.

Gianluca
  • 6,307
  • 19
  • 44
  • 65

1 Answers1

3

I ran the following steps and connected successfully.

Verify connect as root

mysql -u root -p

mysql> show databases;
mysql> exit;

Download the Northwind database

Get it from here: http://code.google.com/p/northwindextended/downloads/detail?name=Northwind.MySQL5.sql

Set up the Northwind database as root

mysql -u root -p < Northwind.MySQL5.sql

Add gianluca as a user and grant permission to northwind

CREATE USER 'gianluca'@'localhost' IDENTIFIED BY 'whatevs';
GRANT ALL ON northwind.* TO 'gianluca'@'localhost';
FLUSH PRIVILEGES;
exit;

Connect as gianluca and access northwind tables

mysql -u gianluca -p
show databases;
use northwind;
show tables;

Notice that once you have created a username on localhost you don't have to specify it when connecting.

Stephen O'Flynn
  • 2,309
  • 23
  • 34
  • Hi, I can access the server using this command: $ mysql -u root -p Unfortunately I can't import the northwind database using the first command you wrote. It returns me this error message: MariaDB [(none)]> mysql -u gianluca -p -h localhost northwind < northwind_mysql.sql; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'mysql -u gianluca -p -h localhost northwind < northwind_mysql.sql' at line 1 – Gianluca Aug 18 '13 at 19:00
  • You will have to do some work to find out how to grant access and privileges. But essentially, if you can access via *root*: `mysql -u root -p < northwind_mysql.sql` should do the trick. I misspelt mysql original (sorry) and this new version uses your root access to create the database. – Stephen O'Flynn Aug 18 '13 at 19:25
  • To ensure your user is OK, if you haven't done this before, connect to mysql using root `mysql -u root -p`, then `CREATE USER 'gianluca'@'localhost' IDENTIFIED BY '';` `GRANT ALL ON northwind.* TO 'gianluca'@'localhost';` `FLUSH PRIVILEGES` Then exit, and try the connect part listed in the answer. More help here: http://stackoverflow.com/questions/11300328/mysql-grant-user-permission – Stephen O'Flynn Aug 18 '13 at 19:34
  • I've created the user, exited and restarted mariadb but continue to occur on the same Error Message: MariaDB [(none)]> mysql -u gian -p -h localhost northwind < northwind_mysql.sql; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'mysql -u gian -p -h localhost northwind < northwind_mysql.sql' at line 1. I've used the username 'gian' because 'gianluca@localhost' is too long for user name (should be no longer than 16). – Gianluca Aug 18 '13 at 20:03