View RSS Feed

sonofsanta

Installing a PHP/MySQL Development Server for students on Windows Server 2012 - Part2

Rate this Entry
by , 29th April 2013 at 12:31 PM (11958 Views)
So, continuing on from Part 1, where we set up a PHP server on Server 2012, lets get into MySQL.

You don't need to do this bit if you don't need the database functionality. There's still plenty you can teach with PHP alone, and that's what we're planning to do with Key Stage 4 anyway. Our Key Stage 5 do want to make use of databases though, so here we go...

Installing MySQL
Open up the Web Platform Installer that we downloaded last time round and search for MySQL. Add MySQL Windows 5.1 and click install. You'll be asked what password you want to use for root. Accept any licences and away you go.

Download MySQL Workbench (you'll need to sign up for an Oracle account, unfortunately) & install it.
Keep this installer handy; you'll install this on client machines that want SQL access as well. I didn't need the prerequisites listed for my Server 2012 install, you may need them for your client machines (I run the vcredist on my Win7 machines before install).

Configuring MySQL
Run C:\Program Files\MySQL\MySQL Server 5.1\bin\MySQLInstanceConfig.exe.
In order, the responses I used were:
  • Reconfigure
  • Detailed
  • Server Machine
  • Multifunctional Database
  • Next (leave as)
  • Manual setting to however many connections works for you
  • enable TCP/IP on port 3306 & strict mode
  • standard character set
  • type your root password out again three times
  • execute
  • finish

Open up MySQL Workbench. Go to the Database menu and choose Manage Connections.
Click New.
Name it localhost at the top
Then just click Test Connection - the values are prefilled for the default settings of a local DB, which we've followed above, so you should just get an authentican box pop up asking for your root password and then a success confirmation. Click Close when it's done.

Back on the home screen, click New Server Instance under Server Administration (right hand pane). Select Take Parameters from Existing Database Connection; your localhost settings should already be selected.
Click Next and provide your root password. You should get a successful connection. Click Next.
Do not use remote management. Next.
Leave Server Instance Name as localhost, click Finish.

Now for a bug - you need to click on Manage Server Instances, make sure localhost is selected (it should be if it's your only instance so far) then close the window that opens up. Until you jump through this hoop, you won't be able to access the server instance you just set up - it will whinge about the current profile having no WMI enabled. Once it's done once, it's fine forever after.

You can now manage the database through this GUI by double-clicking on localhost in this Server Administration pane. Give it your root password and you can check status, logs, change options, set up users, and import & export databases.

Personally I set up a new user called student, and under Admin roles ticked only Create, Delete, Insert, Select, Update - sufficient for them to access a schema. That's far from perfect, but with this being KS5 only I'm not so concerned about security; we like to put a bit more trust in them by that age.

You can, if you want to spend the time on it, set up multiple users and assign them schema-specific permissions, so each user has their own credentials and database and they only have control over that. It's certainly the better way to do it, but you'll probably only want to go for it if you can leave your IT teachers to manage it, as they'll have to handle it in lessons.

Allowing SSH Access to MySQL
As hinted at earlier, MySQL Workbench can be used to administer the server from client machines. The easiest way to do this is to set up SSH access.

Download & install FreeSSHd. You'll be asked a couple of questions during install:
  • Generate private keys when asked
  • Do not run as system service

FreeSSHd seemed to work better for me when launched directly; once it's installed, just copy its Start Menu shortcut into C:\Users\All Users\Microsoft\Windows\Start Menu\Programs\Startup so it runs at login.

Open FreeSSHd from the Start Menu (if it's not already running) then find it in the systray and click to open it. There's a couple of bits of config to do:
On the Users tab, click to Add a user, call it what you will (e.g. user)
Choose Password Stored as SHA1 Hash for the Authorization and give it a password. Tick the box for Tunnelling.
On the Tunnelling tab, Allow Local Port Forwarding, but Only Forward to localhost

Then in Windows Firewall > Advanced Settings, create a New Inbound Rule
Port-based, TCP 22, allow domain connections, name it SSH and type a description out

Connecting to MySQL over SSH
These are the instrucitons you'll give out to users.

With a client install of MySQL Workbench, go to Database > Manage Connections, as we did earlier.
Create a New connection, name it for your server.
Under Connection Method, choose Standard TCP/IP over SSH
The SSH Hostname is [servername]:22 e.g. php:22
The SSH username is what we just set up in FreeSSHd
The MySQL Hostname should be 127.0.0.1 and the port 3306
The Username is whatever MySQL user is appropriate - root, student, etc.

Click Test Connection; it'll ask for the SSH password first (as set up in FreeSSHd) and then the MySQL password. If you get a warning about it being unable to connect to MySQL, make sure you enabled tunnelling in FreeSSHd.

Once the connection is set up, you can manage the instance (as we did earlier) and Open Connections to start querying. Sorted!

Connecting to MySQL through PHP
To connect to a database with PHP code, then, you need the following chunk:
Code:
<?php
	// database variables to use to connect
		$schema = "databaseName";
		$dbusername = "user";
		$dbpassword = "password";	// passwords are case sensitive

	// connect to DB server using the variables or close connection and display error message
		mysql_connect(localhost,$dbusername,$dbpassword) or die("Could not connect to server: ".mysql_error());

	// select the right database or display error message
		@mysql_select_db($schema) or die( "Unable to select database: ".mysql_error());

	// example of running a query:
	// $sql = "SELECT * FROM table";
	// $result=mysql_query($sql);
?>
You can then play with your data as normal with PHP code, for which there are terabytes of reference materials available, all of which goes well beyond the scope of this guide

End Result
Further to our end result last time, users can now work with a database in PHP code, querying it directly through Workbench and even generating databases from models they've set up in Workbench (it does loads of good stuff, not just what we use it for in this guide). Your IT teacher doesn't need to pay for hosting to allow web application development. Your users don't have to wait for uploads from Dreamweaver and can work much faster.

And all that in less than a day's work

Updated 9th May 2013 at 12:29 PM by sonofsanta

Categories
Uncategorized

Comments

  1. NikChillin's Avatar
    Superb, just what I need!

Trackbacks

Total Trackbacks 0
Trackback URL: