Transcript (Auto-generated)
Specify Software Project Staff & G. Nelson, iDigBio
Revised: 24 March 2014
Setting Up Specify to Use a Networked MySQL Server
This installation documentation is intended for campus or institutional configurations where MySQL is already
running and managed on a server computer as a shared organizational resource. Setting up a Specify database
and MySQL together on the same Windows, Linux or Mac OS X Workstation uses a simpler procedure which is
described in installation documents available from the Specify Software web site (www.specifysoftware.org).
Specify 6 is a ‘thick-client’ application which is installed on (Windows, Linux or Mac OS) desktop workstations.
Specify uses Oracle Corp. database server software, MySQL, to manage collection data records. Before creating
a Specify collections database, MySQL must be installed and running, either on the same workstation on which
Specify will be installed, or on a separate server computer. A MySQL database server computer makes a
collection database accessible over the network to any number of Specify workstations. When setting up
Specify to work with MySQL server computer, the Specify application itself does not need to be installed on the
server.
Specify databases have user accounts and passwords which can be created with several levels of permission
and access. The highest level user account in Specify is a ‘manager’ account which has full access to all of
Specify’s capabilities including the ability to create other Specify user accounts. A manager account is created
by the Specify Wizard application which is run after MySQL software is installed and operating.
MySQL also has user accounts, login names and passwords; they are not the same as Specify user accounts.
Configuring Specify requires the creation of two MySQL user accounts. The first is what we refer to as the
“Specify IT User” (although the actual user name in MySQL will likely be something simpler). The Specify IT User
has powerful permissions to modify and update a Specify database in MySQL. It is used for the initial creation
of a Specify database structure (schema) and for periodic software updates from the Specify Project which
update Specify’s database schema. The Specify IT User MySQL account is similar to a root-level user in MySQL
but its permissions are strictly limited to a Specify database and do not apply to other databases hosted by a
MySQL installation.
The second MySQL user account that a Specify installation requires is what we refer to as the “Master User”
account. The Master User is the MySQL account used to actually create, edit and delete data records in a
Specify database. The Master account is used ‘behind the scenes’ in Specify client software to provide database
access to all of the Specify end-user (scientist) accounts created within the Specify client itself. A MySQL
account is not needed for each Specify user as the Master account in MySQL handles all of the ‘backend’
database operations for every Specify user.
The IT User and Master User accounts in MySQL require strong passwords and careful use. Typically, your
database will be reachable by anyone on your campus or building network, and if the server computer is not
behind a network firewall for protection, your MySQL server and Specify database will be reachable by anyone
on the Internet. Once strong Specify IT User and Master User credentials are created, they should be stored in a
safe location where they can be easily retrieved. Loss of the IT User and Master User credentials will create
significant, serious problems for updating Specify software and for database recovery from backups.
In a standalone workstation installation of Specify and MySQL on the same machine, the Master User account
in MySQL can be created by a scientist end-user. For creation of a Specify database on a separate MySQL server
computer, the steps later in this document are needed for creating Specify “IT User” and “Master User”
accounts in MySQL. Those tasks assume that your institution: (1) has setup and configured a running MySQL
server computer, (2) has configured MySQL’s and the host machine’s network IP range filters to allow access
from your workstation, and (3) employs a database or systems administrator who is familiar with issuing MySQL
commands_. If these conditions are not met, locate a source of MySQL database administration expertise to first
set up an institutional MySQL server computer._
The two key points for configuring the two MySQL user accounts which Specify requires are: (1) to grant the
two accounts database operation privileges on a newly created Specify database and (2) to configure MySQL to
permit those two user accounts access to the Specify database based on IP address range values of the Specify
workstations. Institutions may want to modify the MySQL scripts below to accommodate local security policy
for MySQL server accessibility. However, the permissions granted should be specified as shown. Making sure
that the Specify “IT User” and “Master User” MySQL accounts can access the MySQL server is a critical step of
the setup process. If not done correctly, Specify will not be able to access the database and the Specify login
window will display - unable to connect to database- messages.
The commands shown in bold below are issued through a terminal window on the server computer running
MySQL.exe. When entering the commands at the MySQL prompt, replace the Courier font text
below with the appropriate values for your MySQL setup.
Note: MySQL command syntax is precise and critical! Extra spaces or incorrect characters will cause the
commands to fail. You may wish to copy and paste the commands, then edit the appropriate values.
[Italicized text in square brackets is comment.]
------------------- Start MySQL Command Session ------------------
[Database administrator opens a terminal window on the database host machine and logs into
MySQL, …/bin/mysql must be in the path or be the current directory:]
[Now create a Specify “IT User” and grant it privileges. First, choose a Specify “ITUserName” other than ‘root’ or
‘specify’. Then specify an IP range for workstations which will access the MySQL server software. MySQL command
syntax also allows IP ranges such as ‘ITUserName’@‘192.58.197.0/255.255.255.0’; where IP addresses range from
192.58.197.0 to 192.58.197.255. Designate a name for the DatabaseName that will be created during the Setup
Wizard process and communicate the name to scientist who will run the Specify Setup Wizard. The database name
is only used internally for user logins to the collection database, it can be anything reasonable. The
ITUserPassword should be strong, but Oracle recommends using ASCII characters only. Note carefully the spaces
or lack of spaces in the command below.]
MYSQL> GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES, ALTER, CREATE, DROP, INDEX, GRANT
OPTION ON DatabaseName.* TO ‘ITUserName’@‘ClientIpAddressRange’ IDENTIFIED BY
“ITUserPassword” ;
MYSQL> GRANT RELOAD ON . TO ‘ITUserName’ @’ ClientIpAddressRange ’ IDENTIFIED BY “ITUserPassword” ;
[This command allows the privileges specified above to be applied by the Specify IT user.]
[Next create the Specify Master User and grant it privileges. The user name for the account “MasterUsername”
should not be an easily guessed word, avoid ‘master’, ‘specify’ , ‘masteruser’, etc. Use the same designated
database name as above. MySQL command syntax also allows IP ranges such as
‘ITUserName’@‘192.58.197.0/255.255.255.0’; where IP addresses range from 192.58.197.0 to 192.58.197.255.
MySQL documentation states the ‘%’ wild card character can be used in specifying range addresses, but we have
found them to cause errors and be interpreted incorrectly. Note: All Specify workstations which will access a
Specify database in MySQL must be included in this IP range statement. The Master user is used by every
Specify client workstation for database access. The MasterUserPassword should be strong, Oracle recommends
using ASCII characters only. MySQL Syntax must be exact! Do not add spaces where none are shown. You may
wish to copy and paste the command lines below. ]
MYSQL> GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON DatabaseName.* TO
‘MasterUsername’ @ ‘ClientIpAddressRange’ IDENTIFIED BY “MasterUserPassword” ;
MYSQL> FLUSH PRIVILEGES;
MYSQL> EXIT;
------------------- End MySQL Command Session ------------------
Once a name has been designated for the database and the MySQL accounts are created, that information and the network name of the database server should be transmitted to the collections scientist creating a Specify collections database with specific instructions about the importance of keeping and safeguarding these
credentials for easy retrieval when needed. The collections researcher can then populate the Specify database with data tables and add Specify user accounts. A new Specify database is populated and customized with tables, indexes and relationships by using the Specify Setup Wizard (SpWizard.exe) – an application distributed
in the downloadable Specify installation package. The person running the Specify Wizard application will require the new database name, the host address of the MySQL server, the database port on the server (if changed from the MySQL default), and the Specify IT User and Master User accounts’ names and passwords.
The Specify Software Project periodically issues software updates; some of which update the Specify database
schema and require collections scientist to use the MySQL “IT User” account credentials to authorize changes
to the Specify schema in MySQL. The Specify user designated at the manager level within the Specify database
requires the MySQL “Specify Master User” account information for creating new researcher user accounts and
passwords within Specify.
Restoring data from backup files made through the Specify menu Backup function and which are usually stored
on local disks requires the “Specify IT User” account credentials. Creating local Specify database backup files
using the Specify Backup menu function does not. Also in MySQL server configurations, in order for the Specify
Backup and Restore menu commands to work, they require access to two MySQL programs in order to make
local backup copies of a database and to restore them. The programs ‘mysqldump.exe’ and ‘mysql.exe’ are
required on the workstation executing those commands for backup and restore operations, respectively. Their
location on the workstation is indicated in a Specify preference window. They can be obtained from the
appropriate installation package from Oracle or by contacting the Specify Software Project. Most sites with
MySQL server installations centrally organize regular backups for all databases on a shared MySQL server. We
Specify Software Project Staff & G. Nelson, iDigBio
Revised: 24 March 2014
recommend daily backups of Specify databases. ‘Mysqldump.exe’ allows a Specify manager user to make a local
backup in addition to, or instead of, a centrally organized backup process.
Using the Specify Setup Wizard to Populate and Customize the Specify Database
After designating a name for the Specify database and creating the IT and Master User accounts, the next step
involves populating the empty database with new data tables. This is done with the Specify Setup Wizard,
which is an application included in the Specify installation package, and resides in the Specify bin directory on
each workstation on which Specify has been downloaded. The Wizard has extensive embedded help and the
help file for the Wizard is also available in its entirety on the Specify Project web site. It is useful to review the
Specify Wizard documentation before running it as there are several configuration questions about your
institution and collection that require choices during the process of completing the setup of your Specify
database. Your answers to those questions change the way your Specify collection database behaves and
handles your data.
When ready to run the Specify Wizard, find the file “SpWizard.exe” in the Specify installation /bin directory and
run it. In the first Wizard window enter the information for the new database: IT User name, IT User Password,
designated Database name, and Hostname (network name of the computer running MySQL), then click on
Create Database. This will initiate the process of creating Specify’s database tables and may take several
minutes to complete.
Note: if you click on the “Test IT Login” button, and if the login is successful, the Setup Wizard will proceed to
the next screen. Before proceeding though, you must immediately use the “Back” button to return to the
Database Information window, and click on the ‘Create Specify Database’ button, otherwise your Specify
database tables will not be created and the Specify Wizard will eventually fail.
In the subsequent Wizard window, which asks you for the Master account information, enter the master
username and password, and then click “Test Master Login”. This should return a message that your Master
Login has worked and then you can proceed with the Wizard screens. If your Master account credentials do not
allow you to login to the MySQL server, check your credentials for a misspelling.
Do NOT click on the “Create Master User” button on this window, as that will re-create your Specify master user
account with MySQL access permissions restricted only to the workstation on which you are running the Specify
Wizard. This will prevent other workstations from accessing your Specify database.
See the embedded help within the Specify Wizard or the Specify Wizard help documentation (PDF) on the
Specify Project web site for advice on how to complete the creation of your collection database installation.