MySQL in schools - How do you make this happen?

[JRA]

Go Ryan! Let us know how you get on? Post up any juicy/not-so-juicy bits you find?

[mjk]

So on to my first Linux server build! Learn by doing eh?

You'll be replacing your Active Directory with linux/Samba in no time :-)

 

I use vi every day. It's not exactly fun, but once you get used to it it's a really fast and efficient editor.

vi can be fun!!

https://vim-adventures.com/

[RJohnson91]

Katy you're a legend thanks SO MUCH!

 

Think there's any merit in knocking up a click-by-click guide for non-Linux users? If so I'll get the ball rolling. Please amend as an ongoing concern if it'll be any use to anyone. Pink text means an incomplete area and I'll get back to this when I can (meant to put it up whole but work :/ )

 

[color=#000000][font=Arial]Here’s how to (or how I do.) Oi oi.[/font][/color]

[color=#ee82ee][font=Arial]Install ubuntu server to a VM or a mouldy old HP pizza box in a toilet somewhere. You’ll be prompted to set up an account just to log in as, so do. Note the IP addy. It’ll want to be connected to the internet too.[/font][/color]
[color=#ee82ee]
[/color][color=#ee82ee][font=Arial]If you wanna go sit in your office to administer this box now, download and run putty from your Windows machine. Log in with that account you set up whilst doing the above.[/font][/color]

[color=#000000][font=Arial]Once up, you’ll want to install mysql and phpmyadmin, so we’ll get on. If you’ve just logged in, we’ll change to the root account first: [/font][/color]

[color=#000000][font=Arial][i]$ sudo su[/i][/font][/color]
[color=#000000][font=Arial](Prompt then appears wanting this box’s root password:)[/font][/color]

[color=#000000][font=Arial][i]# apt-get update[/i][/font][/color][color=#000000][font=Arial] [/font][/color]
[color=#000000][font=Arial](Takes a little bit.)[/font][/color]

[color=#000000][font=Arial][i]# apt-get install mysql-server[/i][/font][/color][color=#000000][font=Arial] [/font][/color]
[color=#000000][font=Arial](Takes another little while. You’ll be prompted twice for a root account password for mysql. Note this is going to be a different root account to THE MACHINE’S root account, this one’ll be just for the mysql root account.)[/font][/color]
[color=#000000][font=Arial][img=https://lh5.googleusercontent.com/jvt267qf3_Bu5QHhPOryVUhwAYNlyH1b-ONtdNDuUkkRpmYE9iYJuVvdQzY3vR7_rch20VkEWo3pZH8duq5kK_j1xePaGhHnIF1gAMx9UTYk7cGMG5RiIWLDX33qCiPV3-Ig5Wsv][/font][/color]


[color=#000000][font=Arial][i]# mysql -u root -p[/i][/font][/color]
[color=#000000][font=Arial](Then you’ll be prompted to enter the mysql root account password. This is just to test that it works and the prompt will change to “mysql>” - if it does, top notch! Type “exit”.)[/font][/color]

[color=#000000][font=Arial][i]# apt-get install phpmyadmin[/i][/font][/color][color=#000000][font=Arial] [/font][/color]
[color=#000000][font=Arial](Now, it’s VERY important you read the following when you see this box:[/font][/color]
[color=#000000][font=Arial][img=https://lh3.googleusercontent.com/WoxMRlUM9ldyrPs8uW5BLvh7Zyg2wSXyJ3ntllaqH3p3-NbWTepF_XzexwT_JqL1yV_g4Npe6ttfRiVkYISkE4KHyvgVYObaRQ6OVW3fUSbwu9d-YEni41Wt0O06Fmxrg3kc5Jln][/font][/color]
[color=#000000][font=Arial]The RED square here means that the option is HIGHLIGHTED but NOT SELECTED! Press SPACE to insert a little start into/select the top/apache2 option, then TAB to ok as below:[/font][/color]
[color=#000000][font=Arial][img=https://lh6.googleusercontent.com/g6bWa1h5xRTrP7--zAtUbI_adWgFjMP-wzqunSwS10cOLE2mai_-oDPZ4QuBTVPSPUhIPhMBON0ABsOfdZYgqaEFoFSaCrkEVl9M1FLiJVWEtXSWogY8FGFZ1gCLfLPi0B3Z99gv][/font][/color]

[color=#000000][font=Arial]At this screen, opt for a quiet life and select yes:[/font][/color]
[color=#000000][font=Arial][img=https://lh3.googleusercontent.com/7b2UUrFvzHfkacjS46JKUNnBsOHnEZkDVKCA6bVZIf6PCwDWRiI1Ek3FXsl_jJJ6Qasc437TRw8U3c-oQO5UGZXfgUlVkrmf2Z74MojQqGdp3HdudFrkinLQrwGknkZ9xo6rXSkp][/font][/color]

[color=#000000][font=Arial]Now you’re prompted for ANOTHER password![/font][/color]
[color=#000000][font=Arial][img=https://lh6.googleusercontent.com/_abDaa2mhE4hoOieGGCJY_4d6fDwUzEOHkWHxpIEaUtlxXacBuWbl9Puj2hX23Rx7Z4Zt7kbNwDBaYkR2FIyqPIqI-6FkKuypStP1f0njH92_jt9snrTn0G3PWz2EDbnJ9mEqOED][/font][/color]
[color=#000000][font=Arial]	[/font][/color]
[color=#000000][font=Arial]Now once that’s all done, go to a normal PC for normal people that runs oh I dunno Windows, and type into a browser: [/font][/color][url="http://myservernameoripaddress/phpmyadmin"][color=#1155CC][font=Arial][u]http://myservernameoripaddress/phpmyadmin[/u][/font][/color][/url]

[color=#ee82ee][font=Arial]That’s it BUT if you want to get PROPER CLEVER just for a bit of polish, you can edit that stupid sailing ship thingy to your school logo:[/font][/color]
[color=#ee82ee][font=Arial][img=https://lh3.googleusercontent.com/UMDTGTmEYuufPlA9P_Pqs7-RLJdiyTiOJLA9XN2tLgHRN7r4XabkwQXFUd4alqJmWsQhAHlWRrBpMYoPr8Z5QduBxj-7DJu5BypFkQxnMFHcOt6G0G_YiW8fmv6ObwkaAlEUDc96][/font][/color]
[color=#ee82ee]
[/color]
[list]
[*][color=#ee82ee]Make up a .png of roughly the same size[/color]

[*][color=#ee82ee]Put it in /usr/share/phpmyadmin/themes/ and call it “logo_right.png” without the “”.[/color]

[*][color=#ee82ee]# service apache2 restart[/color]
[/list]

 

If there's not really any merit in it then no worries.

 

 

This!

 

Following this guide, I now have a Ubuntu virtual server (We run XenDesktop throughout the school ) , running MySQL and PHPMyAdmin accessible via our windows environment through apache2 !

 

One slight addition for all linux noobs like me, when you have installed the server, make your life easier type sudo apt-get install update and then sudo apt-get install ubuntu-desktop

 

Probably obvious to all your Linux gurus, but it allows access through the GUI rather than just command line.

 

Now onto actually configuring it!

 

Ryan

 

 

 

 

EDIT - User's have been added with what Katy kindly suggested.

 

Students

Standard username and password with the host left at %

Tick Grant all privileges on wildcard name (username\_%) with nothing else ticked.

 

Teachers

Standard username and password with the host left at %

Tick SELECT for privileges so the teachers can read the data only.

 

 

 

The only pitfalls I can see so far is that the exam board ask the students to create a database titled 'exams' , they will have to change that name to username_exams due to the permissions set above...

 

 

Now to download a command line interface console and link it to the SQL database ...

 

All you MySQL masters out there, can you see any fundamental flaws in any of the above, that will shoot me in the foot later on ?

 

Ryan

Edited February 2, 2017 by RJohnson91

[RJohnson91]

We have progress !

 

So what we have we achieved so far ?

 

I have built a Ubuntu virtual server, running PHPMyAdmin and MySQL, and set up a test user via PHPMyAdmin (with help from all the great guides on here).

 

Once that was completed I downloaded the Windows version of MySQL to obtain a Windows SQL shell so we can access the SQL server via a command line, which is needed for the exams, (I downloaded MYSQL.5.7.17-Winx64.zip).

 

Once that was downloaded I took the MySQL.exe from the bin folder and copied it locally, now we have an accessible command line But it wont connect to our nice and new SQL server...

 

I found that you need to change the Bind-address in the Ubuntu server to allow access from other devices other than the local host, so how do you do that?

 

For mysql version 5.7 and above, use terminal to navigate to /etc/mysql/mysql.conf.d/ and Vi to mysqld.cnf, replace 'bind-address = xxx.xxx.xxx.xxx' with 'bind-address = 0.0.0.0' if you want all IPs to be able to access your server. Remember to ZZ and save, then reboot the server after this change

 

To check if it works type netstat -nlt | grep 3306 when the server is back up you should get:

 

tcp 0 0 xxx.xxx.xxx.xxx:3306 0.0.0.0:* LISTEN

 

Which is perfect!

 

Finally, go back to your MySQL.exe on your windows machine and use the command mysql.exe -h *SQLServerIP* -u *TestUser* -p

 

And viola, connected with locked permissions for that one user, they can only create and view their own tables and databases !

 

Last concern is giving CMD access to students, but I may separate the VMs and the SQL server onto their own VLAN away from our main network to eliminate hacking worries!

 

 

Hope the above will help somebody

 

Ryan

[RJohnson91]

Final post.

 

All done!

 

We have a batch file that calls the SQL.exe so kids get forced into the console with no access to CMD (Much easier and quicker than vlan separation), this is hard coded with the -h IP etc.

 

So we now have a fully working solution, students can login to a console with their usernames created via MyPHPAdmin, create databases and tables. The teacher can login and view their progress. Everything we need

 

If anyone is still reading this post I am happy to help with anything.

 

Ryan

Edited February 3, 2017 by RJohnson91

[MYK-IT]

Final post.

 

All done!

 

We have a batch file that calls the SQL.exe so kids get forced into the console with no access to CMD (Much easier and quicker than vlan separation), this is hard coded with the -h IP etc.

 

So we now have a filly working solution, students can login to a console with their usernames created via MyPHPAdmin, create databases and tables. The teacher can login and view their progress. Everything we need

 

If anyone is still reading this post I am happy to help with anything.

 

Ryan

Hi Ryan,

 

Yes, I would be very much interested in the setup and this extra configuration bit you mentioned too.

 

Very much appreciated.

 

Thanks.

[JRA]

Hi Ryan,

 

Yes, I would be very much interested in the setup and this extra configuration bit you mentioned too.

 

Very much appreciated.

 

Thanks.

 

Ooh yes please Ryan, me also.

 

Folks, some awesome teamwork went on in this thread!

[mavhc]

BTW, nano is a more normal command line text editor, rather than vi.

[JRA]

Final post.

 

All done!

 

We have a batch file that calls the SQL.exe so kids get forced into the console with no access to CMD (Much easier and quicker than vlan separation), this is hard coded with the -h IP etc.

 

So we now have a fully working solution, students can login to a console with their usernames created via MyPHPAdmin, create databases and tables. The teacher can login and view their progress. Everything we need

 

If anyone is still reading this post I am happy to help with anything.

 

Ryan

I must admit I can't for the LIFE of me get this bit to work!

 

Linux-side, set the option in /etc/mysql/mysql.conf.d/mysqld.cnf thus:

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address    = 0.0.0.0

 

Then

$ netstat -nlt | grep 3306

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN

 

Seems aight. So, create a test kid, or even with the mysql root account. Get myself on a Windows PC with mysql on it. Pass the following to C:\Program Files\MySQL\MySQL Shell 1.0\bin\mysqlsh.exe in a batch file:

"C:\Program Files\MySQL\MySQL Shell 1.0\bin\mysqlsh.exe" -h 10.10.10.10 -u testkid -p

(Where 10.10.10.10 would be the address of my server.)

 

Where I get the following back:

MySQL Error (2002): No connection could be made because the target machine actively refused it connecting to 10.10.10.10:33060

 

Hmm, so I pass it the port that 10.10.10.10 is listening on with:

"C:\Program Files\MySQL\MySQL Shell 1.0\bin\mysqlsh.exe" -h 10.10.10.10 --port=3306 -u testkid -p

 

This time I get:

MySQL Error (2027): Unknown message received from server 10.

 

Awesome! Thanks MySQL.

 

Is anyone getting the same doing what I'm doing? Have I missed anything obvious? I mean, at this stage students *can* log into the php site and do the work there, but I'd certainly rather they have the shell.

 

Guhh?

[pcstru]

I must admit I can't for the LIFE of me get this bit to work!

 

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address    = 0.0.0.0

Err, is 0.0.0.0 correct or is that just placeholder for the server address? If it is actually 0.0.0.0 then it needs to be the server address if you want to make network connection from a different IP.

[RJohnson91]

I must admit I can't for the LIFE of me get this bit to work!

 

Linux-side, set the option in /etc/mysql/mysql.conf.d/mysqld.cnf thus:

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address    = 0.0.0.0

 

Then

$ netstat -nlt | grep 3306

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN

 

Seems aight. So, create a test kid, or even with the mysql root account. Get myself on a Windows PC with mysql on it. Pass the following to C:\Program Files\MySQL\MySQL Shell 1.0\bin\mysqlsh.exe in a batch file:

"C:\Program Files\MySQL\MySQL Shell 1.0\bin\mysqlsh.exe" -h 10.10.10.10 -u testkid -p

(Where 10.10.10.10 would be the address of my server.)

 

Where I get the following back:

MySQL Error (2002): No connection could be made because the target machine actively refused it connecting to 10.10.10.10:33060

 

Hmm, so I pass it the port that 10.10.10.10 is listening on with:

"C:\Program Files\MySQL\MySQL Shell 1.0\bin\mysqlsh.exe" -h 10.10.10.10 --port=3306 -u testkid -p

 

This time I get:

MySQL Error (2027): Unknown message received from server 10.

 

Awesome! Thanks MySQL.

 

Is anyone getting the same doing what I'm doing? Have I missed anything obvious? I mean, at this stage students *can* log into the php site and do the work there, but I'd certainly rather they have the shell.

 

Guhh?

 

That is odd!

 

The batch file I used was:

 

@ECHO OFF
cls
echo.
echo.
echo Welcome to GCSE Controlled Assessment Console
echo.
SET /P uname=Please enter your MySQL Username: 
IF "%uname%"=="" GOTO Error
ECHO Hello %uname%, Welcome
[b]
mysql.exe -h 10.10.10.10 -u %uname% -p[/b]
GOTO End
:Error
ECHO You did not enter your name! Bye bye!!
:End
exit

 

That essentially just created a more 'polished' feel to the batch file with prompts e.t.c but the important part is in Bold, which you have done.

 

It is to my knowledge though that you wont be able to log in as Root through your MySQL shell, I don't think Ubuntu allows you too... I'd need someone to confirm this but I vaguely remember reading this somewhere.

 

At this point, I think your set up is good, the things I would check are:

 

* Have you checked the permissions on your testkid via MyPHPAdmin

* Do you run an onsite firewall such as Smoothwall (we have this internally and had to add port 3306 to our access list)

* Is your Ubuntu server on the same network / vlan e.t.c

 

 

Sorry I know this is 'teaching how to suck eggs' but sometimes it's the obvious we all miss!

 

Ryan

[RJohnson91]

Err, is 0.0.0.0 correct or is that just placeholder for the server address? If it is actually 0.0.0.0 then it needs to be the server address if you want to make network connection from a different IP.

 

The default is 0.0.0.0 which is all interfaces. This setting does not restrict which IPs can access the server, unless you specified 127.0.0.1 for localhost only

[JRA]

I understood that changing it to (specifically that) in /etc/mysql/mysql.conf.d/mysqld.cnf allowed access from any ip. That right? Can't remember where I googled that up from but it checked out somewhere...

[JRA]

Thanks both!

 

Ryan, what are you using/did you download that gave you a mysql.exe? I've got a mysqlsh.exe specifically, which I think I got from the mysql-shell-1.0.5 download for Windows. If you recall do let me know, I'll try that out too.

 

Thanks again all.

[RJohnson91]

Thanks both!

 

Ryan, what are you using/did you download that gave you a mysql.exe? I've got a mysqlsh.exe specifically, which I think I got from the mysql-shell-1.0.5 download for Windows. If you recall do let me know, I'll try that out too.

 

Thanks again all.

 

I downloaded my package from here https://dev.mysql.com/downloads/mysql/

 

Make sure to download the right 32/64 for the end point where you'll be running the .exe

 

Once downloaded extract MySQL.exe from the bin folder

 

Ryan

[JRA]

I downloaded my package from here https://dev.mysql.com/downloads/mysql/

 

Make sure to download the right 32/64 for the end point where you'll be running the .exe

 

Once downloaded extract MySQL.exe from the bin folder

 

Ryan

Well I'll be funked, used that download and my batch file and it all worked!

 

You're the daddy Ryan thanks for that step! Got mysql-5.7.17-winx64 zip from that link.

 

Okay, looks like we're cooking! That's the whole dealey then from start to finish! :)

[RJohnson91]

Well I'll be funked, used that download and my batch file and it all worked!

 

You're the daddy Ryan thanks for that step! Got mysql-5.7.17-winx64 zip from that link.

 

Okay, looks like we're cooking! That's the whole dealey then from start to finish! :)

 

good man!

 

We have also given the ICT teacher a login to the MyPHPAdmin so she can view all of the students tables etc and mark as she goes along!

[DarkenRahl]

Everything seems to be working except for the MySQL.exe bit for me. I get the following error when running the batch script from a share where it sits next to the executable.

 

ERROR 2003 (HY000): Can't connect to MySQL server on 'XX.XX.XX.XX' (10061)

 

I believe i have added the port correctly on Smoothwall and set the commands for the server to listen to all hosts with the 0.0.0.0 stuff mentioned earlier. I get the same message on my own machine so I don't think it is a VLAN issue. I have also even tried allowing the correct port in and out on the ubuntu server itself incase a firewall was stopping it. No joy.

 

Any ideas?

[mavhc]

Everything seems to be working except for the MySQL.exe bit for me. I get the following error when running the batch script from a share where it sits next to the executable.

 

ERROR 2003 (HY000): Can't connect to MySQL server on 'XX.XX.XX.XX' (10061)

 

I believe i have added the port correctly on Smoothwall and set the commands for the server to listen to all hosts with the 0.0.0.0 stuff mentioned earlier. I get the same message on my own machine so I don't think it is a VLAN issue. I have also even tried allowing the correct port in and out on the ubuntu server itself incase a firewall was stopping it. No joy.

 

Any ideas?

 

First steps when something can't connect: run netstat on the server to see if it's listening on the right ports, and use telnet to try to connect to that port from the client, if it's tcp, to make sure the basic tcp level stuff is working.

 

On my debian server:

ps aux | grep mysql

mysql 12036 0.0 0.0 615700 1636 ? Sl Feb10 24:29 /usr/sbin/mysqld

 

the first mysql is the user that mysql runs as, the 12036 is the Program ID, PID

 

netstat -a -p -n | grep 12036

tcp 0 0 10.1.2.3:3306 0.0.0.0:* LISTEN 12036/mysqld

 

a = all, ie also includes servers listening without a connection, p = include PIDs, n = numbers, don't bother to resolve every IP

 

so it's listening on IP 10.1.2.3 on port 3306

 

In Windows that's something like: tasklist | find "mysql", then netstat -a -o -n | find "12036"

 

So on the client do: telnet 10.1.2.3 3306

 

That's a space, not a colon between the ip and the port, also from Win7 telnet client isn't a default install, you can add it in Turn Windows Features on or off

[HereIGoAgain2601]

Hi guys,

 

Been following this thread and thanks to your help managed to get MYSQL installed and running. I am trying to find a way to bulk create user accounts.. I have generated a list of users and passwords for them but need a way of getting into my SQL.

 

I have been using this script below but doesn't seem to want to run from a .SQL file.... any ideas sorry if this is a daft question never done mySQL before!

 

CREATE USER 'jthornton'@'%' IDENTIFIED BY 'password';GRANT ALL PRIVILEGES ON `jthornton\_%`.* TO 'jthornton'@'%';

[JRA]

Hmmm - that worked for me when (on the linux box) I ran:

 

mysql> source /home/usererlolomg/testmysql.sql;

 

 

[HereIGoAgain2601]

I get an error saying that the file "is not a compiled character seta nd is not specified in the file index.xml" Charset is not found ....

 

Thanks for the reply

[JRA]

I get an error saying that the file "is not a compiled character seta nd is not specified in the file index.xml" Charset is not found ....

 

Thanks for the reply

Sorry, been a couple of days. You up and running now? If not, post the exact command you're running?

 

We only had a few kids, so I delegated that one and we made the accounts up manually in phpmyadmin, though the script I tried out above did work ok.

[HereIGoAgain2601]

Hi

 

Nope not sorted it yet.. no major rush as they aren't using it till after Easter. We are running Windows to host MySQL if that makes a difference.. but these are the commands:

 

source C:\CreateUsers.SQL

 

The contents of which is

 

CREATE USER 'Test1'@'%' IDENTIFIED BY 'Test123';GRANT ALL PRIVILEGES ON `Test1\_%`.* TO 'Test1'@'%';

 

Still getting the same error as above.. The commands work if I run them manually, just don't want to do this for 50 users!

 

Cheers