Computing & MySQL - How have you got it setup?

[Marshall_IT]

Using MySQL Workbench on my workstation, configured a connection to the mysql instance and setup new users with a schema privilege allowing them to only work on a database that matches their username, granting all privileges on said schema.

 

Absolute MySQL noob here... how exactly did you do that?

Does this allow them to connect to the instance and create a db that matches their username too or are you creating the dbs for each student?

[Marshall_IT]

After launching MySQL Shell, the command to connect to remote instance in sql/classic mode:

 

\sql

\c -c username: password@serverip:serverport

 

From this point it is just normal sql commands, no command prompt access required.

 

I'm assuming students need to be able to launch a command prompt in the first place to complete this step?

[cogrady84]

I'm assuming students need to be able to launch a command prompt in the first place to complete this step?

 

Apologies for the late response, been on my holidays. No command prompt required, MySQL Shell is a separate application

[cogrady84]

Absolute MySQL noob here... how exactly did you do that?

Does this allow them to connect to the instance and create a db that matches their username too or are you creating the dbs for each student?

 

No, they are creating the database. You will need to find a server with some spare resources and install MySQL server on it (MySQL :: Download MySQL Community Server), remembering the root username/password defined. Then install MySQL Workbench on your admin computer and define a connection to the mysql server, default port is 3306. (MySQL :: Download MySQL Workbench). When connected, you will have management capabilities of the SQL instance, go to users and privileges and setup a new user and goto the Scheme Priveleges tab and add entry, set the schema matching pattern to the same string as the username, then grant all priveleges apart from "grant option".

 

MySQL Shell is needed for students to gain access (MySQL :: Download MySQL Shell) install this on a student workstation and use the commands in my previous post to gain access and perform SQL ops.

[FN-GM]

@Blue_Cookeh

 

When I run GRANT ALL PRIVILEGES ON `blue\_%` . * TO 'blue'@'%'; I get the following message. I have already created the users using your commands.

 

Error Code: 1133. Can't find any matching row in the user table

 

Any ideas please?

[FN-GM]

@Blue_Cookeh

 

When I run GRANT ALL PRIVILEGES ON `blue\_%` . * TO 'blue'@'%'; I get the following message. I have already created the users using your commands.

 

Error Code: 1133. Can't find any matching row in the user table

 

Any ideas please?

 

 

This seems to work ok though?

 

GRANT ALL PRIVILEGES ON `blue\_%` . * TO 'blue'@'localhost';

[FN-GM]

\sql

\c -c username: password@serverip:serverport

 

From this point it is just normal sql commands, no command prompt access required.

 

@cogrady84 Im running these commands and when I try to run a MYSQL command it returns no connected. Can you clarity the exact commands used to connect please?

 

Thanks

[Blue_Cookeh]

This seems to work ok though?

 

GRANT ALL PRIVILEGES ON `blue\_%` . * TO 'blue'@'localhost';

@FN-GM;

 

Users can be assigned different source hosts on MySQL. I think what you've done here is created 'blue' on MySQL with access via localhost, but not via another machine.

 

When you create the user change the host from localhost to %, then rerun your privileges grant and it should work.

 

CREATE USER 'blue'@'%' IDENTIFIED BY 'some_pass';

 

This would also explain why you have issues connecting to the database from another machine, if you've tried that.

Edited August 26, 2016 by Blue_Cookeh

[cogrady84]

@cogrady84 Im running these commands and when I try to run a MYSQL command it returns no connected. Can you clarity the exact commands used to connect please?

 

Thanks

@FN-GM Here is a screenshot of my connection process:

 

[gh5000]

Thanks to all contributors for this thread.

 

Have been asking the new ICT teacher if she needed this - no no no...

Head of Business, Computing etc... are you sure she doesn't need this for the new curiculum, it's all over Edugeek - hmm I don't know...

 

One month later - I need mySQL for controlled assessments for Tuesday

 

But been keeping an eye on this thread and it's been all I needed. Slightly more relaxed weekend now....

[davie887]

In the same pickle, will re-read this thread tonight. I tried to packaging up the MySQL Server, Workbench and CLI into a MSI (RM CC4.5 ) and that works to a degree, but it's a pain in the read to have another service running on our ancient PCs, not to mention redirecting the data store to the student home folder.

 

I have an instance of MySQL running on a PC in my office which I plan to use as the main server, another ready to promote to replication server (not sure of the MySQL terminology), thankfully I only need to create/manage 30 controlled assessment accounts for this, my main concern was students seeing and trashing other's databases...

[angrycomputingteacher]

I managed to get a Php dev server thing running on a few of our machines. The files can be used on all pcs through our networked shares.

[FN-GM]

Does anyone know if its possible for the kids to import / export SQL files form the cli please?

 

Thanks

[pcstru]

Does anyone know if its possible for the kids to import / export SQL files form the cli please?

Thanks

For export, they should be able to use mysqldump and access any databases they have permissions to. Just pipe the same file into mysql to restore.

[FN-GM]

For export, they should be able to use mysqldump and access any databases they have permissions to. Just pipe the same file into mysql to restore.

 

How would they physically access the files though?

[pcstru]

How would they physically access the files though?

That depends. If they are running this from a command line, then the dump will be placed where they specify (usually the current working directory). If this is a unix type host, ftp, samba or rsync.

 

[ETA - if you want to make it easy for them and are using windows client machines, grab mysqldump for a windows build and put it somewhere on the path and they can then dump to any file sytem the local machine can see (i.e. their network area).

Edited October 10, 2016 by pcstru

[FN-GM]

That depends. If they are running this from a command line, then the dump will be placed where they specify (usually the current working directory). If this is a unix type host, ftp, samba or rsync.

 

[ETA - if you want to make it easy for them and are using windows client machines, grab mysqldump for a windows build and put it somewhere on the path and they can then dump to any file sytem the local machine can see (i.e. their network area).

 

We are using the MySQLShell to connect to a remote SQL server.

[FN-GM]

Spent some time on this. How does one use the source command on the MySQL Shell. It works ok on the mysql.exe but not the Shell installer.

 

Thanks

[jcubbin]

Spent some time on this. How does one use the source command on the MySQL Shell. It works ok on the mysql.exe but not the Shell installer.

 

Thanks

 

Thanks to everyone who has contributed to this thread as I have been able to follow most of it with never having used SQL products before. I have it working where users can connect from any workstation that has MYSQL shell installed to the MYSQL server and can create their own database without seeing or having access to any other database.

 

I am now in the same position as FN-GM and I'm unsure how to get an sql file imported when using the MYSQL shell set in SQL mode using \sql and at the MySQL-sql> prompt. I can also inport the sql file if I use the MySQL.exe file.

 

Has anyone actually done this as I believe it will be part of the assessment for the pupils and like most other people we don't want to allow pupils access to the command prompt.

@cogrady84 thank you for the instructions and screengrab as this helped tremendously, although it looks as if there is a space after the : in your original posting which threw me to start with.

 

I will post a full guide to show how I got it all working from start to finish once I complete this and the teacher confirms it will do everything he needs for the assessment. Why the exam board can't provide a generic virtual machine with relevant scripts for schools that don't have anyone who is familiar with SQL I don't know, surely it would be a great help for most people.

 

thanks

Edited October 17, 2016 by jcubbin

[Steve21]

Quick question is this still the main one being used? still heard nothing from our Comp guys about needing this and guessing they should be starting it soon?

 

Steve

[FN-GM]

Quick question is this still the main one being used? still heard nothing from our Comp guys about needing this and guessing they should be starting it soon?

 

Steve

 

We have 1 secondary school using it and another isn't. I think they have a choice or something.

[jcubbin]

@ FN-GM

 

I believe I've found the command that you need for importing the sql file into your database.

 

\source n:\yourfile.sql

 

assuming your sql file is on the students n:\drive and that it is called yourfile.sql that is. also note that no semi-colon is required when using the source command in this instance.

 

link to all the commands when using the MYSQL shell is here

 

MySQL :: MySQL 5.7 Reference Manual :: 3.8.4.1 MySQL Shell Commands

 

Hope this helps anyone who is struggling with this.

[foofighterjim]

So, first day of the controlled assessment after no issues at all during testing and....

 

 

Now happening for every user except root

 

Spent the last hour searching for a solution but nothing I have found is using the same type of setup. Anyone else had this or have any advice? I'm a little out of my depth troubleshooting this one.

[mavhc]

Connect as root, run

 

SELECT sql_grants FROM common_schema.sql_show_grants;

 

what output do you get?

[foofighterjim]

what output do you get?

 

 

Not sure how to resolve this though.