From DSD Wiki

Here's some notes on running MySQL.

Note that commands starting with # are run as root (or using sudo) while $ is as your own user.

Also note that on OS X most of the installed programs end with a '5' (the version #) so that you could run multiple versions of MySQL on the same system (yikes).

Install

FreeBSD:

# portinstall -pL /var/tmp/portupgrade/%s::%s.log mysql-server

Mac:

$ sudo port install mysql5

Initial Setup

This only needs to be done once, to create /var/db/mysql (or /opt/local/var/db/mysql5 on the OS X) where all the DB files live. Make sure that dir is owned and writable only by user mysql (or _mysql on OS X).

# mysql_install_db --user=mysql

$ sudo mkdir /opt/local/var/run/

Start up the MySQL server daemon

# mysqld_safe &

At this point run the script mysql_secure_installation or do the following (since that script doesn't seem to get installed by the FreeBSD port).

Change the root user's password (that's root of the DB not Unix root)

# mysqladmin -u root password sesame

Remove the anonymous user:

$ mysql -u root -p mysql
mysql> delete from user where user='';

Keep dropping users until

mysql> select host, user, password from user;

looks reasonable.

Various admin commands

Server startup:

# mysqld_safe &

Ping server to see if it is running:

$ mysqladmin5 ping

return of 0 is success (regardless if you don't have user privileges to log in) and 1 is failure - meaning server is most likely not running.

To shutdown the server:

# mysqladmin shutdown -u root -p

Users, Passwords, access, etc.

If you need to change the mysql root user's password again use:

# mysqladmin -u root -p password sesayou

and type in the old password.

Though this will only change one of the root user passwords, the one connecting from 'localhost' not all the ones for other hosts. To change all passwords for a given user get into the interpreter as root and do:

$ mysql -u root -p
mysql> use mysql
mysql> update user set password=PASSWORD("sesayou") where user='root';
mysql> flush privileges;

Create a new user:

$ mysql -u root -p
mysql> create user 'newbie'@'localhost' identified by 'secret';

To remove a user:

$ mysql -u root -p
mysql> show grants for 'newbie'@'localhost'; -- just to see that it is there
mysql> revoke all privileges, grant option from 'newbie'@'localhost';
mysql> drop user 'newbie'@'localhost';
mysql> show grants for 'newbie'@'localhost'; -- just to see that it is gone

Dump & Import

To dump a database:

$ mysqldump -B mydata > mydata.sql

To import that database:

$ mysql -u root -p
mysql> source mydata.sql;

mysql> create user 'newbie'@'localhost' identified by 'secret';
mysql> grant all on mydata.* to 'newbie'@'localhost';

Reference

Installing Web Server in FreeBSD 6.0 with Apache 2.2, MySQL 5.0 and PHP 5 - Part 3

Mysql install on FreeBSD

MySQL 5.0 Reference Manual :: 2.17.3 Securing the Initial MySQL Accounts