Should I have a different MySQL user for each database so that if someone gets access to a mysql user they cannot effect the others?
If so how would i go about doing this?
Its a principle called "separation of privileges", which reduces the impact of an intrusion.
you can do something like
GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'127.0.0.1' IDENTIFIED BY 'somepassword';
FLUSH PRIVILEGES;
or you can provide some subset of privileges using a command similar to this;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON mydatabase.*
TO 'myuser'@'localhost';
The privileges supported by MySQL (5.1) are documented here;
http://dev.mysql.com/doc/refman/5.1/en/grant.html#grant-privileges
root
system user, or that each user connects to the database as root i.e. mysql -uroot -psomepassword somedatabase
?
root
user should always exist with full privileges, but its that normal day-to-day access should not being using it!