Computing & MySQL - How have you got it setup?

[mavhc]

Sorry, that relies on install: common_schema documentation being installed.

[foofighterjim]

Sorry, that relies on install: common_schema documentation being installed.

 

I think I might just start again with it all as I can't even import common_schema 2.2

 

 

If I start tomorrow morning I should have everything set before the end of the day.

[jcubbin]

Hi foofighterjim, I'm sure that the problem is very likely because of the name of the database you are trying to create.

 

When i initially set it up i could only get users to create databases that were the same as their username. i.e user1 could only create a database also called user1. I modified my file so that users could create a database called user1_exams but i don't have of that information t hand at the moment.

 

When you are trying to modify or grant permissions you need to be logged on to the server hosting the MySQL database as root and i would use MySQL workbench to view what users and settings you have set.

 

I will try and post some more info for you tomorrow once i'm back at work.

Edited November 9, 2016 by jcubbin

[jcubbin]

Hi again foofighterjim, I have attached an excel spreadsheet that you can use to gather the information you need to create, set password and grant permissions to your sql users.

 

Create and set passwords by copying the data under the create user heading and pasting in the sql command line once you are logged in as root.

 

Grant permissions by doing the same but using the grantpermissions header.

 

You can grant permissions to a database called user1_exams by changing the grant permissions information like shown below

 

grant select,insert,update,delete,create,drop on user1_exams.* to user1@'%';

 

this is how I created my users and use MySQL workbench to check they created correctly.

 

https://www.dropbox.com/s/tmx3um9lem9xltr/CreateSQLUsers.xlsx?dl=0

 

 

 

 

 

 

[TABLE=width: 452]

[TR]

 

[TD=width: 603, bgcolor: transparent][/TD]

 

[/TR]

[/TABLE]

[foofighterjim]

Thanks @jcubbin and @mavhc , because this has happened at the start and no student data had been lost (just my time and effort), I rebuilt the VM last night and reconfigured SQL this morning. Everything now working as expected and I now have a schedule in place in vCenter to create snapshots after each session.

[cogrady84]

Apologies for not getting back to this thread sooner, I noticed some asking for the SQL import:

 

\source filepath\filename.sql

 

I use MySQL workbench to create users, so not totally sure about the command line sequence, although here's a grab of my users/schema privileges:

 

[JamesGHBray]

I'm having a nightmare getting this working how ICT need it to. I've created all of the accounts in Workbench as suggested in this thread, and Workbench is working perfectly for the students. However because they need a CLI, I installed the Shell on the machines as well, and neither myself or the ICT teachers can work out how to use the shell to connect to the MySQL server.

 

Can anyone help? We're running out of time on this now and it's causing everyone involved lots of stress!

[jcubbin]

Hi James,

 

Cogrady84 has this part covered early on in the thread.

 

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

 

\sql (This switches you to SQL classic mode)

\c -c username:password@serverip:serverport (example would be \c -c user1:[email protected]:3306 )

 

you will probably need the command for importing a sql file into your database aswell which would be

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

 

also have a look at the manual for sql shell commands

 

 

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

Edited November 23, 2016 by jcubbin

[JamesGHBray]

Hi jcubbin, thanks for your help (Cogrady84 too).

 

I get an error "No default schema selected" when doing the above commands. As advised, I set up 30 accounts, sqluser1 - 30 with passwords sqluser1 - 30 and each one is limited to a schema of the same name.

 

Is there a way to set the default in workbench, or do I need to do that as part of the command?

[jcubbin]

Hi James that is correct, once you connect you should get "creating a classic session to sqluser1@testserver:3306 (with "testserver" being the computer that is hosting the MySQL instance) and underneath that will say "no default schema selected"

 

so you need to do

 

\sql

 

\c –c sqluser1:sqluser1@testserver:3306

 

show databases; (this will show just information schema)

 

create database sqluser1; (create database)

 

show databases; (you will now see sqluser in the list)

 

use sqluser1; (use the sqluser1 database)

 

show tables; (shows as an empty set)

 

 

etc etc

Edited November 24, 2016 by jcubbin

[JamesGHBray]

You're an absolute legend. Looking back at support tickets it looks like they were typing in slightly incorrect commands, given to them by someone on the TES forums...

 

Thanks again.

[jcubbin]

There are some slight differences from regular SQL commands I believe James, ha ha that's probably why it's so awkward to get it all working properly and why information on the internet doesn't seem to quite tally up when trying to run commands.

 

I've included the sourcefile import command in some of my posts as it is one that is definitely different and your users will need it early on in their database creation.

 

It is a steep learning curve to get this all setup and configured, why the exam board or course providers don't provide complete installation and configuration instructions I don't know as I'm sure most schools don't have SQL gurus working in them.

[Mark182]

This thread is very helpful. We have just had this land on our desks for after Christmas. My colleague has suggested https://db4free.net/ any thoughts or downfalls or will we have to install MySQL on a server like everyone else?

[mavhc]

Wouldn't trust it for exams. Use it as an excuse to learn new skills

[Mark182]

Wouldn't trust it for exams. Use it as an excuse to learn new skills

 

Yeah, the line 'There can be outages, data loss and security features do not meet the standards which you expect from a professional data hosting provider' does not fill us with much confidence.

[JRA]

Might just be worth mentioning THIS THREAD too, just in case people are looking.

[davie887]

Has anyone got MySQL Shell working over a network? I have MySQL server on a separate machine, user accounts with privilege to just their own database (and any database they create with _/%, eg joeblogs_mydb etc) from any remote IP and that works great with Workbench connecting to the server with IP address and user credentials but the faculty want to use command line...

 

Using the switches I have found I am able to connect to the server in Shell but it only seems to want to pass the Windows [email protected] details, which are obviously rejected as not the same as the MySQL user details.

 

The -u and -p switches don't seem to work.

Have I missed something? Anyone else using Shell?

Edited February 24, 2017 by davie887

[mavhc]

Has anyone got MySQL Shell working over a network? I have MySQL server on a separate machine, user accounts with privilege to just their own database (and any database they create with _/%, eg joeblogs_mydb etc) from any remote IP and that works great with Workbench connecting to the server with IP address and user credentials but the faculty want to use command line...

 

Using the switches I have found I am able to connect to the server in Shell but it only seems to want to pass the Windows [email protected] details, which are obviously rejected as not the same as the MySQL user details.

 

The -u and -p switches don't seem to work.

Have I missed something? Anyone else using Shell?

 

isn't it --user and --password?

[jcubbin]

Hi davie887,

 

are you using sql mode? and connecting using the format shown below

 

start the shell command line

\sql (This switches you to SQL classic mode)

\c -c username@assword@serverip:serverport (example would be \c -c user1:[email protected]:3306 )

Edited March 2, 2017 by jcubbin