I don't know your experience with Unix and with configuring and managing servers. Accordingly, some of the following may or may not apply to you.
Is there an advantage to Leapord Server vs. just using Leapord for the small user?
It really depends on your skills and your experience and your needs, and what you want to use the box for. Leopard Server provides significantly more capabilities than does Leopard, though those would be of interest as you grow past the current MySQL database requirements.
Server boxes are inherently more work to configure and to operate, and require more knowledge of network technologies and related tools and environments. But then Leopard server also gets you a single sign-on for all your servers, and piles of other features listed in the [Leopard Server Product Overview|http://www.apple.com/server/macosx/resources>.
Recurring theme: you'll have to understand your requirements and your own experiences and where you're likely to change or grow into. Before the box is due to be retired. You'll have to answer those and the other questions yourself.
If I go with the xserve do I need a RAID card to do mirroring?
Software RAID is integrated with Mac OS X Server. Hardware RAID is optional. Hardware RAID is available for internal storage and for external storage.
You would think a server with 3 hot-swappable drive bays would include some RAID functionality standard.
There are a number of folks that don't want the costs or the complexity or the configuration requirements of hardware RAID. Why would you need RAID for a grid engine, for instance? And some folks need disks with faster access times, and some need disks with larger capacity. You're clearly cost-sensitive with your disk purchases, but you're clearly also interested in RAID. There's no single target market here.
How about with the MacPro?
Similar software (with Mac OS X Server installed) or hardware options are available.
Equal or arguably more interest here is your archival storage, as RAID is very far from an archival strategy and does not protect you from volume corruptions or incidental deletion or intentional damage or hardware theft.
Do I need to get expensive Apple hard drives or can I get something from Office Depot for less than half the price? For Xserve? For Mac Pro?
There are third-party drives around, yes. The expense comes from the integration and testing and warranty provided by Apple. If you don't want to deal with the drives again save to swap them and if you generally want to not think about the drives, then get the Apple drives. If you're willing to provide integration and testing (or work around differences in drive diagnostics and reporting), then third-party or even your own selected-as-compatible drives are a potential option here. But then how much is your time worth? Disks aren't as compatible as everybody involved would like, though they do continue to get better over the years.
Is there a big advantage of going with the Xserve over the MacPro for the small user?
Remote management capabilities. Hot-swap bays. Available second power supply. Etc. Poke around the forums here. The Xserve is rack-mount. If you have a computer room or acoustical cabinet or need its features or if you're likely going to be connecting piles of external disks to the box via SAS, SATA, SCSI or Fibre Channel, then Xserve is your box.
Could there be an advantage of going with the MacPro?
The Mac Pro is quieter. If you're working in the same office and near the box (without an acoustical rack), you very likely want the Mac Pro. The Mac Pro is available as a tower mount, and not a rack-mount.
I have no use for another copy of iLife etc on the MacPro and the Xserve includes Leopard Server for unlimited users for about the same price. But of course I'd have to buy a rack and keyboard. And then there's the $600 hard drive issue.
FWIW, used and surplus racks usually end up on local surplus-equipment lists here and there and most used-equipment vendors know of some of these around; whether you can find a used rack locally is another matter, but they're often around. (Acoustical racks are scarce, though.)
And I do know of Xserve boxes that are [mounted on closet shelves|http://discussions.apple.com/thread.jspa?forumID=854&threadID=1815186], and (presuming sufficient cooling and low dust) they work just fine.
Some additional reading: [threadID=1164015|http://discussions.apple.com/thread.jspa?threadID=1164015] and [threadID=1968568|http://discussions.apple.com/thread.jspa?threadID=1968568]. And Xserve can get more-better noisy when you crank up the NICs or crank up the cores.
As part of the purchase, I'd presume you're also looking for appropriate archival storage and for the mysqldump procedures and related; for the requisite archival processing. Ultrium and LTO tapes are potential options here. Or maybe disk-based archives, though tapes are more rugged than disks for near-line and offsite storage. (Or cloud storage, depending on your preferences.) All this depending on the amount of data and the rate of change, and the desired depth of archives.
Given what you're probably dealing with based on your forum id, there are also potential requirements for auditing and data encryption, for your disk data and for the archives and for the near-line data. (I deal with this sort of stuff, too.)
You're going to have to decide those (and other) matters.
