MySQL Database Deployment

Staging and deployment of your MySQL database depends on meticulous planning and testing. A staging environment provides a space where developers can test their database before it goes live. Your staging environment should always remain separate from your production environment however a good rule of thumb is to ensure that the hardware and software in your staging environment matches what is being used in your production environment.

At UNHM there is a server dedicated for staging named STEM. The STEM machine is a virtual Windows 2008 RT server operating system. It’s DNS name is stem.unh.edu and its IP address is 132.177.189.56. The STEM virtual machine is hosted by a server computer in the lab. The main purpose of STEM is to stage web applications and MySQL databases. It has XAMPP installed on the C: drive and the apache server configuration (C:\xampp\apache\conf\httpd.conf) has a document root directory set up to point to the V:\web folder.

Connect to the UNHM VPN
Connect to the UNHM VPN by following the steps below.


 * Connect to the UNHM vpn through the following URL https://vpn.unh.edu/dana/home/index.cgi
 * Go to student/faculty
 * Enter your UNHM credentials
 * Install all necessary software

Map the Web Folder on STEM
After you have successfully connected via VPN, you must map STEM's "web" folder to a networked drive on your computer.


 * In a Window's 7 environment go to Start then to Computer. Once Computer is launched click "Map Network Drive" on the top control pane.
 * In the Map Network Drive Window, choose the drive letter that you desire and copy \\stem.unh.edu\web as the folder destination path.
 * Click the connect using different credentials check box and click the finish button
 * Once finished you will be prompted to enter your credentials for authentication. You need to enter your UNHM username preceded by the wildcat domain (wildcat\xxx) then enter your password.
 * You now have access to the web folder on STEM. This folder can be used to save your XXXschema.sql and XXXload.sql files.
 * If you are starting a new project, please create a new folder for your files.

Launch the MySQL client
Users are able to launch the mysql client remotely due to a change on the binding-address on STEM's my.ini file. See the MySQL Security page for more details on allowing or restricting network access. Now that you have an active VPN session and have access to the web folder on STEM, you can utilize the mysql client on STEM. Launching the mysql client is similar to launching it on your local machine. Follow the steps below.


 * Kill the mysql process if you have it running on through xampp on your local machine. By running it locally you may not be able use the mysql service that is already running on STEM.
 * Launch a command prompt window on your computer and change the directory to "xampp/mysql/bin".
 * The username and password for students to use for staging is "CISadmin1".
 * You must specify the host (stem.unh.edu or 132.177.189.56) by using the -h argument
 * Type "mysql -h stem.unh.edu -u CISadmin1 -pCISadmin1" to launch the client

Example

C: cd xampp/mysql/bin

C:\xampp\mysql\bin>mysql -h stem.unh.edu -u CISadmin1 -pCISadmin1

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.5.25a MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Source Your Scripts
You're now using the mysql client on STEM remotely through your VPN connection. You can source your XXXschema.sql and XXXload.sql scripts from either your computer or from the mapped web directory on STEM if you saved your files there. Remember that if you're sourcing from the web directory that you copy the new path for your files. An easy way to do this is to open the files saved if your folder on the web directory into notepad++. Then right click the tab for your file and hit copy full path to clipboard.

User Issues
We discovered a problem when first creating the CISadmin1 user. The script that was used to create the user was flawed in some way. After the user was created, it was available for authentication but we couldn't use it to run any administrative commands such as create database or drop database. After investigation, we found that the CISadmin1 user had no privileges assigned despite "granting all" in the create user script. We eventually ended up looking at the user in phpmyadmin which showed that none of the privileges for the user were selected. We then assigned a few required grants to the user to allow for configuration changes. We also copied the command given by phpmyadmin when assigning privileges to the user.

Example

GRANT ALL PRIVILEGES ON *. * TO 'CISadmin1'@'%' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

Lesson's learned


 * Copy the syntax above when assigning privileges
 * If there is a problem with a user, check phpmyadmin on STEM
 * Accessing phpmyadmin on STEM requires remote desktop access which only the COMPTECH Admins have.
 * To contact the COMPTECH Admin group ???