Mysql Setup (on FreeBSD)

On ZFS it's best to align the record size if using inodb. First create a zfs mount point. Then: zfs set recordsize=16k zroot/database

Set ZFS caching to metadata only for this directory. ZFS caches things in memory however mysql is already doing this. (Also true for Posgresql) zfs set primarycache=metadata zroot/database

Also skip the innodb double write buffer (zfs makes this unneccesary): in my.cnf innodb_doublewrite = no

Determine your database directory, then change ownership to mysql user before trying to start the server.

Also set a password for the root user:
$ mysqladmin -u root password SeCrEt

Mysql Information

SHOW PROCESSLIST

Display what's currently running on a mysql server. This is pretty handy if you're running myisam tables and want to see what's blocking transactions.

Users and Permissions

Users are added via CREATE USER and deleted via DROP USER.

Users are stored in the mysql.users table which shows authentication information for an account, but does not show permissions.

CREATE USER 'bob'@'localhost' IDENTIFIED BY 'pass';

SET PASSWORD FOR {user} = PASSWORD('something');

mysql commands are stored in a hisory which will also expose this password to anyone who can access that history.

GRANT USAGE ON *.* TO 'u'@'host' REQUIRE SSL