RJohnson91 Posted January 11, 2017 Posted January 11, 2017 Hi all, I have been given the task of getting MySQL available for all students that are studying it. We run a windows environment so the Windows installation is what we are looking to use. My question to you guys is, what do you do? Have you installed a brand new SQL server and installed stuff like SQL Workbench? Or do you have an online or packaged version that you use? Any direction would be amazing. Thanks Ryan
jamesreedersmith Posted January 11, 2017 Posted January 11, 2017 Do you run Hyper-V - spin up a linux bos (centos or ubuntu) and then install a webmin module and a MySQL module and away you go. Set up 1 DB per pupil with seperate logins. Alternatively Spin up a windows VM and install WAMP and use that? Several ways to skin a fish.......
RJohnson91 Posted January 11, 2017 Author Posted January 11, 2017 Do you run Hyper-V - spin up a linux bos (centos or ubuntu) and then install a webmin module and a MySQL module and away you go. Set up 1 DB per pupil with seperate logins. Alternatively Spin up a windows VM and install WAMP and use that? Several ways to skin a fish....... We run a Citrix virtual environment, we can easily spin up Linux machines but I really want to keep it to the current windows systems, so there isn't xtra work getting mapped network drives etc to work through Linux. You mention WAMP, i'll have a look at that. Ryan
Meldrew Posted January 11, 2017 Posted January 11, 2017 There was another thread about this. Do you students need to be able to create their own MySQL database? I think that was talked about as a requirement in other schools which puts a completely different emphasis on things. From memory students had to be able to create their own databases but all their work had to be strictly kept inaccessible from other students which is tricky from a permissions perspective. Meldrew
jinnantonnixx Posted January 11, 2017 Posted January 11, 2017 (edited) Potential curve ball: Is the requirement for MySQL set in stone? A far easier SQL system to use in almost any environment would be SQLite. https://www.sqlite.org/ It's serverless, which means that you just take away the file as a self-contained database for unparalleled portability. By the way, it's the most commonly used SQL system in the world. It's in everything from smart-watches to supercomputers. I'm a big fan. Tutorial: SQLite tutorial If MySQL is a definite requirement, then I would plump for a pre-rolled applicance. e.g. https://www.turnkeylinux.org/mysql You can get a VirtualBox image, or if you're feeling creative, a Docker image. Edited January 11, 2017 by jinnantonnixx
jinnantonnixx Posted January 11, 2017 Posted January 11, 2017 (edited) This might be worth investigating. https://www.tutorialspoint.com/mysql_terminal_online.php Update: I had a quick bash at this and I think this might be all you need. It's on-line and it allows you to save your work. I'm really impressed with this. MariaDB [(none)]> MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | CODINGGROUND | | information_schema | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) MariaDB [(none)]> create database mydb; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> use mydb Database changed MariaDB [mydb]> create table edugeektest (afield varchar(10)); Query OK, 0 rows affected (0.00 sec) MariaDB [mydb]> insert into edugeektest (afield) values ("Hello"); Query OK, 1 row affected (0.01 sec) MariaDB [mydb]> insert into edugeektest (afield) values ("World"); Query OK, 1 row affected (0.00 sec) MariaDB [mydb]> select * from edugeektest; +--------+ | afield | +--------+ | Hello | | World | +--------+ 2 rows in set (0.00 sec) MariaDB [mydb]> insert into edugeektest (afield) values ("World");Ctrl-C -- exit! Aborted sh-4.3$ Edited January 11, 2017 by jinnantonnixx 1
Katy Posted January 12, 2017 Posted January 12, 2017 If you have to go MySQL rather than SQLite: We set it up a while ago, armed with the sample Computing exam paper to discover they have to be able to create databases themselves. I've not got the code to hand for setting the permissions but can look it up tomorrow if needed, basically: - Install a linux (debian) VM, plus mysql and phpmyadmin. - Give the teacher a mysql login with permission to create databases and users etc. - Create user accounts but don't give them any permissions - Grant the new user full permissions on \_% (this allows them to create as many databases as they want so long as they begin with their username and an underscore). You don't have to create a database before granting privileges to it, so this is perfect for leaving them to create their own. Pupils then connect to the phpmyadmin page to log in and run their code, and can't view or alter any other pupils databases. 4
MYK-IT Posted January 13, 2017 Posted January 13, 2017 We've just literally been asked for this to be installed too, by our Computing Department; so I am interested to see how other schools are implementing this especially as my knowledge, is next to nothing. Still not 100% happy with Python being installed; especially from a security point of view.
mavhc Posted January 13, 2017 Posted January 13, 2017 If your computers can be hacked with python they can be hacked without python. Ask specifically which parts of MySQL they need installing, server, client, gui, cli? etc
jinnantonnixx Posted January 13, 2017 Posted January 13, 2017 (edited) Is this any good? It's a pre-rolled VirtualBox VM containing: Oracle Linux 5.7 Oracle Enterprise Manager 12.1.0.4 (for Linux x86-64) Oracle Database 11g (for Linux x86-64) MySQL Database 5.6.21 (for Linux x86-64) Hands-On-Labs materials MySQL and Oracle Enterprise Manager Developer VM I haven't tried it, but it might be possible to run this VM on a bridged network and allow remote access to your students instead them running their own VM if that makes things easier. Alternatively, https://www.turnkeylinux.org/lampstack Edited January 13, 2017 by jinnantonnixx
JRA Posted January 31, 2017 Posted January 31, 2017 If you have to go MySQL rather than SQLite: We set it up a while ago, armed with the sample Computing exam paper to discover they have to be able to create databases themselves. I've not got the code to hand for setting the permissions but can look it up tomorrow if needed, basically: - Install a linux (debian) VM, plus mysql and phpmyadmin. - Give the teacher a mysql login with permission to create databases and users etc. - Create user accounts but don't give them any permissions - Grant the new user full permissions on \_% (this allows them to create as many databases as they want so long as they begin with their username and an underscore). You don't have to create a database before granting privileges to it, so this is perfect for leaving them to create their own. Pupils then connect to the phpmyadmin page to log in and run their code, and can't view or alter any other pupils databases. Ooh, yes please, and if you can knock up anything resembling a walkthrough you'll pretty much be a new deity around here!
Sheridan Posted January 31, 2017 Posted January 31, 2017 We've just put Virtualbox VM on each IT suite computer with the lot on, MySQL,PHP,Python,VB.net, Netbeans, and probably a dozen other things I've forgotten. They're set to immutable so can't be broken, but they can copy files to and from their work area so they can save projects etc. Seems to work well, and the VM's only take about seconds to start up (W7 running under W7) on SSD based PCs. As they effectively have admin rights on the Vm everything works ok.
Katy Posted February 1, 2017 Posted February 1, 2017 Ooh, yes please, and if you can knock up anything resembling a walkthrough you'll pretty much be a new deity around here! Here is how we do it: [h=1]Pre-requisites[/h]You will need to have installed MySQL and phpmyadmin on a fresh server. I usually spin up a Debian VM for this. Make sure you change the root password for mysql. [h=1]Adding Pupil Users[/h]Log in to http://server/phpmyadmin/ as root, and click on Users, then Add User. Fill out the username and password (leave host set to %). Tick “Grant all privileges on wildcard name (username\_%)”. Don’t tick anything else. Now click on Add User. Repeat this for each of the pupils. If you have lots and want to script it, these are the two commands you need to run for each user: CREATE USER 'katytest2'@'%' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON `katytest2\_%`.* TO 'katytest2'@'%'; [h=1]Adding Teacher User[/h]As with the pupil user, click on the User tab and fill out the form, however this time tick whichever global privileges you wish the teacher to have (such as just SELECT if you only want them to be able to read the data, we give ours pretty much full access as he's technical enough to cope with adding pupils). [h=1]Testing It[/h]Log in to phpmyadmin as one of the pupils – you should not be able to see any databases besides information_schema. You should now be only able to create databases named _, e.g. katytest2_1 or katytest2_exam. The pupils can create databases/tables either using the built in forms or just by clicking on the SQL tab and typing in the MySQL commands directly, which I think OCR expects them to do. When logged in as the teacher account it will nicely list all pupil databases in the tree view under their username. 3
JRA Posted February 1, 2017 Posted February 1, 2017 (edited) Here is how we do it: Pre-requisites You will need to have installed MySQL and phpmyadmin on a fresh server. I usually spin up a Debian VM for this. Make sure you change the root password for mysql. Adding Pupil Users Log in to http://server/phpmyadmin/ as root, and click on Users, then Add User. Fill out the username and password (leave host set to %). Tick “Grant all privileges on wildcard name (username\_%)”. Don’t tick anything else. Now click on Add User. Repeat this for each of the pupils. If you have lots and want to script it, these are the two commands you need to run for each user: CREATE USER 'katytest2'@'%' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON `katytest2\_%`.* TO 'katytest2'@'%'; Adding Teacher User As with the pupil user, click on the User tab and fill out the form, however this time tick whichever global privileges you wish the teacher to have (such as just SELECT if you only want them to be able to read the data, we give ours pretty much full access as he's technical enough to cope with adding pupils). Testing It Log in to phpmyadmin as one of the pupils – you should not be able to see any databases besides information_schema. You should now be only able to create databases named _, e.g. katytest2_1 or katytest2_exam. The pupils can create databases/tables either using the built in forms or just by clicking on the SQL tab and typing in the MySQL commands directly, which I think OCR expects them to do. When logged in as the teacher account it will nicely list all pupil databases in the tree view under their username. 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. Edited February 1, 2017 by JRA 2
MYK-IT Posted February 1, 2017 Posted February 1, 2017 Thanks @Katy & @JRA I will follow both of your instructions and test etc. when I get the opportunity (Linux Noob too) Thanks.
Katy Posted February 1, 2017 Posted February 1, 2017 Thanks @Katy & @JRA I will follow both of your instructions and test etc. when I get the opportunity (Linux Noob too) Thanks. You should also be able to do it all on a Windows PC/server either by installing mysql along with php for IIS or installing apache/php directly, but I've never done it this way so don't have any instructions.
ZeroHour Posted February 1, 2017 Posted February 1, 2017 (edited) Here is a nice Windows client thats free you could use for them: HeidiSQL - MySQL, MSSQL and PostgreSQL made easy I think it should just run from a UNC as well. EDIT: Actually it keeps the connection configs in the same directory as the exe so it may/may not be a good option running it from UNC. Edited February 1, 2017 by ZeroHour 1
JRA Posted February 1, 2017 Posted February 1, 2017 (edited) A decent server setup guide follows at: system installation - How do I install Ubuntu Server (step-by-step) - Ask Ubuntu Only changes I myself would make would be to install OpenSSH only at stage 26 (and it's VERY IMPORTANT to remember that the RED mark DOESN'T mean it's actually selected! Press SPACE and a little star appears, then it's selected, then arrows up/down and tab to ok) and pay some attention to the order of some options in these stages as I'm SURE a couple of screens have changed. Anyway, the above will get you to a point where, once you know the IP address of it (either get it to take a reservation, or once logged in type ifconfig to display it) that you can download putty on a regular Windows PC like what normal, sensible people use and connect to it that way. If you want to set a static IP address for your server on the server itself now you'll *sigh* need to edit a text file. Summary follows: networking - Assigning a static IP to Ubuntu Server 14.04 LTS - Ask Ubuntu To actually DO that though, do this: $ sudo su (This'll change you to the root acct. Note the prompt will change as follows.) # cd /etc/network/ (This changes the working directory to the /etc folder, and then the /network folder inside of that.) # cp ./interfaces ./interfaces.BAK (This backs up what's there currently so that if we really screw up we can un-screw it up. Note full-stops there.) # vi ./interfaces (This opens up the file in the world's worst text editor. Srs. If at any time you do something you didn't want whilst editing this or you get scared and want to start again, press the ESC key four or five times, then type ":q!" and enter, without the "". Press the down arrow and right until you're hovering under the 'd' of 'dhcp', where it differs in that link above (as in, where we'd want that word to say 'static' now, usually under the "# The primary network interface" bit.) Press the 'x' key four times. The letters 'dhcp' will disappear. Press the 'a' key - you're now in "insert mode." Type the word "static" then press ESC key. Press the 'o' key. Now press the tab key, type out each line (with regards to the settings you'll need for your network) pressing enter at the end of each line, then tab once at the start of each line. When you've finished the last bit of the last IP address on the last line, press the ESC key about four or five times, then once you're definitely sure it's all reading as it should, type ":x" without the "" and press enter. Acid test will be when you restart the box and try to putty back in/ping it! Oh, and restarting is usually only done by the root acct out-of-the-box, and is # shutdown -r now Edited February 1, 2017 by JRA ANOTHER typo
Katy Posted February 1, 2017 Posted February 1, 2017 (This opens up the file world's worst text editor. Srs.) I usually find it's worth installing nano as it's much easier to edit in. 2
ZeroHour Posted February 1, 2017 Posted February 1, 2017 Haha the joy of vi, I still know people who swear its the only way they want to edit text files.
JRA Posted February 1, 2017 Posted February 1, 2017 Yes probably not a bad call. I just got on with vi/vim from the start as pretty much everything's got it out of the box. My old boss hated it and always used joe, but yes there are 'friendlier' text editors out there in the world. And you found another bit I typo-ed and re-wrote!
pcstru Posted February 1, 2017 Posted February 1, 2017 Haha the joy of vi, I still know people who swear its the only way they want to edit text files. Not sure I'd be in that category but there is a good reason to know vi - it is part of the standard toolset on unix/unix systems (SUS), so you know if you log into any unix system these days (and for the last 30 years), vi will be available. 1
jinnantonnixx Posted February 1, 2017 Posted February 1, 2017 Haha the joy of vi, I still know people who swear its the only way they want to edit text files. 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. ed, on the other hand.... 1
2ilent8cho Posted February 1, 2017 Posted February 1, 2017 I always use VI on my Linux boxes, i find it kind of therapeutic with the key presses. 1
RJohnson91 Posted February 2, 2017 Author Posted February 2, 2017 (edited) Thank you all for the responses I think the way that this will be achieved is with our own Linux server, so I'm going to get my teeth into that today. Tutorialspoint.com which was mentioned earlier in this post is absolutely fantastic in general, I would strongly suggest you guys checking it out, it has emulators such as Python on there providing alternative access rather than having it on your network, however by definition, using a web based method to access these utilities means you rely on your internet connection, site traffic and site availability. Unfortunately because of those considerations I have deemed it not usable in a controlled assessment environment. So on to my first Linux server build! Learn by doing eh? Ryan Edited February 2, 2017 by RJohnson91 1
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now