How to install mysql-server 5.1 on FreeBSD

When I install MySQL on FreeBSD I usually choose to set it up with UTF-8 support by default.
This is what I’m also going to do in this how to.

Install MySQL with UTF-8 support

Configure MySQL with my.cnf

Copy default huge config.

You now need to edit /etc/my.cnf to your needs, I would add the following lines.

If you want MySQLd to listen on one IP instead of ALL, I choose localhost :-):

Higher the max_connections number (default: 100):

Set MySQL to use UTF-8 as default (you NEED these for UTF-8 in default):

To prevent MySQLd to be reachable via tcp/ip, uncomment:

If you don’t want to use replication, comment the below line:

For some more performance change this line (CPU’s * 2, I use a quadcore so I set it to 8):

In case you have big queries you might want to increase max_allowed_packet. Default is 1MB… I would start with setting it to 8MB.

Fire up MySQL

Edit /etc/rc.conf and add the following two lines:

Start MySQL

Set a password for the root user

Run the folowing mysql queries:

In case you use the local FTP daemon of FreeBSD you might want to add the mysql user to the ftpusers file to prevent any FTP login from this user.

Portupgrade

When you are a user of portupgrade you might want to add the following to /usr/local/etc/pkgtools.conf

 

 

Default database

Sometimes the installation fails to install the default database, if this happens run the following command and reset your root password

Add new user other than root