I was wondering if it is possible to have a CNAME record for say:
'mysite.domain.com' point to 'www.domain.com/mysite'?
Or if CNAME's have to point to actual domains (not directories)?

nope! You can't do that!

You will need to do the following
1.) setup CNAME record
2.) setup WWW server (apache) to do the forwarding

enjoy!

Well, what I am doing is creating subdomains that need to point to a directory on a domain (like CPanel does). What is the best way to do this?

Or would it be easier just to create virtual accounts for the subdomains? I am making a system where users can sign up (sort of like hypermart) and get their own subdomain

The only reason I might not want to create virtual accounts for them is because it might make a heavier server load when there are hundreds of virtual accounts on a server.

well, you do not need to create a virtual account for each user.

You can configure/set-up redirect in httpd.conf file, thus, you can create mysql backend and scripts that generate httpd.conf file with VirtualHost's and redirect rules :) And there you go, a professional redirection service.

I suggest you also read some info on different redirect codes (eg. 302....) you will probably find it in HTTP protocol RFC

Any tutorials on how I could go about doing this?

I have found a few urls that will assist you, check out the links below.

http://httpd.apache.org/docs/vhosts/index.html
http://httpd.apache.org/docs/mod/mod_alias.html#redirect
http://httpd.apache.org/docs/mod/mod_alias.html#redirectmatch
http://httpd.apache.org/docs/mod/mod_alias.html#redirectperm
http://httpd.apache.org/docs/mod/mod_alias.html#redirecttemp

1) go to everydns.net
2) point an NS for your sub.domain.com to ns1-4.everydns.net
3) add a webhop for "sub.domain.com" --> http://where.every/poo.html

done

-davidu