It took me a couple of days to work out a game plan for this owner. My goals were to have a system in place that would allow automatic account updates to PetFinder. The shelter needed an easy to learn GUI for employees at each branch to use (uniformity is a good thing with data management). The shelter needed a reliable database backend that allowed simultaneous database access for both the primary shelter and its branch offices. This project obviously needed redundancy with an automated backup scheme. Because animal shelters run on donations (money and equipment) they tend to rely on poor machines. By poor quailty I mean the workstations and server would be low-end systems with faulty hardware that the origninal owner replaced with a new unit.
Linux was the operating system of choice for cost reasons. Since I would be using Linux I figured MySQL was the database to use because it met the requirement of simultaneous client access.
I must confess that I already had a front-end in mind when I started planning the project. At my former job I researched Animal Control software for a police department. That research turned me onto Animal Shelter Manager which I knew would fit the GUI requirement.
The last requirement was a redundancy scheme. Again I was in luck with my choices of Linux for the operating system and MySQL for the database. With a couple of scripts I could easily perform a nightly backup of the database and store extra copies off site for further protection.
All that was left was to work out how to tie all of this together into a seamless package. I read most of the entries on the ASM forum and found that quite a lot people were having issues with setting their systems up for remote client access - so much so that on practically every thread there was one or more postings offering to host sites for a fee. My thought was if these money-hungry buggers were able to setup remote client access then by golly I would figure it out too!
Three days later I had a Linux server running MySQL and a fully functioning Animal Shelter Manager database with three remote users accessing the data.
Using the Linux System I created from my last posting (ProFTPd server), here's how I completed this project:
Log into your Linux system as root and Type urpmi mysql to install Mysql on your system
Type vi /etc/my.cnf at the command prompt to open my.cnf in vi for editing
Type /skip to search for the string skip networking. If it isn't on the first result then type n for the next instance of the word
Press the [Shift+I] keys or the Insert key on your keyboard, and with the cursor in front of the words skip networking, place a # sign (Shift+3 keys) in front of it. The # comments out the instruction
Press the [Shift+:] key to leave Editing mode, and then type wq then hit Enter to write your changes and exit out of vi
Type service mysqld restart to restart mysql with the new change you made to my.cnf (you are now allowing network access to MySQL)
Type mysql at command prompt to enter into MySQL> so you can enter SQL commands
Type SET PASSWORD FOR root@localhost=PASSWORD('rubberchicken'); to set a MySQL password for the root user - place your prefered password in place of rubberchicken (DON'T LOSE THIS PASSWORD! Resetting it is an option, but it's a darn lenghty process to do)
Type create database asm; to create a database for the Animal Shelter Manager data
Type quit; to exit the mysql command prompt
At this point you could type mysql -u root -p and type at the prompt the pssword you replaced rubberchicken with in the command above to test it out. You can type quit; to leave MySQL
Type mkdir /var/src to create a folder to store your tar files in.
Type cd /var/src to move into the new directory
Type urpmi lynx to install a text-only web browser. We will use this to download the ASM package to your system
Type lynx http://downloads.sourceforge.net/sheltermanager/sheltermanager-2.3.5_i386_linux.tar.gz
Hit D for download, press the down arrow to highlight Save to Disk, hit enter to save the file to disk, and hit enter again to save the file with its default file name.
Type tar -xzf shetlermanager-2.3.5_i386_linux.tar.gz to untar and uncompress the downloaded file in the current folder (/var/src)
Type cd /var/src/asm/data/sql and press Enter to move into the sql folder
Type at the (NORMAL) command prompt mysql -u root -p asm '<'mysql.sql and press the Enter key. You must type in your MySQL password when prompted. This command will populate your previously created asm database with the necessary tables and fields from the mysql.sql file
Type mysql -u root -p and password when prompted, and then type grant all privileges on asm.* to 'ferdenand'@'%' identified by 'royal1'; and hit Enter to allow remote access to the asm database from this particular user. Be sure you replace ferdenand with whatever username you like and also chance royal1 to an appropriate password for that user. Repeat the grant statement with other users and passwords if you wish for branch offices. Also note that this username/password combo is only for remotely connecting to the asm database. the asm software that will be installed on the client machines will require unique username/password combos for access for each user at each shelter site.
Next step is to create the redundancy segment of the project. We are going to rely again on sourceforge for the answer:
Type cd /var/src to move into the new directory
Type lynx http://downloads.sourceforge.net/automysqlbackup/automysqlbackup.sh.2.5
Hit D for download, press the down arrow to highlight Save to Disk, hit Enter to save the file to disk, and hit Enter again to save the file with its default file name.
Type chmod +x automysqlbackup.sh.2.5 to make the file executable
Type mv automysqlbackup.sh.2.5 /etc/cron.daily where it will be ran automatically each day
Type vi /etc/cron.daily/automysqlbackup.sh.2.5 to edit the file
Edit (at least) the following lines:
USERNAME=root (The user must have at least select privileges to the databases)
PASSWORD=yourrootpassword
DBNAMES="asm"
BACKUPDIR="/mnt/automysqlbackup"
Save the changes and exit the editor
Type mkdir /mnt/automysqlbackup to create the archive folder for your daily, weekly, and monthly database backups
You are done, at least as far as the server installation portion goes. The next part of this project is to install the ShelterManager software on a remote machine. Here's what you need to do:
Download the ShelteManager software for your operating system (I will assume this will be on a Windows OS machine)Install the ShelterManager software
Double-click the Animal Shelter Icon on the desktop to start the program
Choose English at the first window (if that is your preference)
Choose MySQL as the database
Type the IP address of your Linux system in the host field
Type asm for the database name
Type ferdenand for the user and royal1 for the password
OK, at this point a lot of people have issues with not being able to connect to the remote MySQL server. Here is the sollution I found through trial & error:
Click Cancel on the database window. This will bypass the remote MySQL access and instead set you up to use localhost.
When the second window pops up asking you to log into Shelter Manager, simply close the window to get out of the software.
Do a search for a jdbc.properties file and edit it in notepad
Delete the entire JDBCURL line and replace it with the following:
JDBCURL=jdbc:mysql://yourLinuxSystemIPaddress/asm?user=ferdenand&password=royal1&characterEncoding=UTF8
Save the file and restart the ShelterManager software. Hopefully all will go well and you will receive a login screen.
Type user for the usernameType letmein for the password
As a final verification you can hover your mouse pointer over the middle icon in the bottom right corner of the window - the icon looks like two stacks of gold coins. A bubble should pop up showing mailto:MySQL@yourLinuxsystem.
Deion "Mule" Christopher
Posts
No comments:
Post a Comment