dragffy.com

MySQL is a very popular relational database. It can be used by many different applications and thankfully the installation procedure is a snap. For this tutorial we will be compiling MySQL 5.0.45 from ports with some custom options. I did this on FreeBSD 6.2 release but it should work on FreeBSD 6.3 and 6.x.Change to the MySQL ports directory

cd /usr/ports/databases/mysql50-server

Compile and install with custom options

The following should be typed all on one line, the double backslash (\\) indicates that it has been broken up for better viewing on the internet.

make WITH_CSV=yes WITH_FEDERATED=yes WITH_ARCHVIVE=yes \\
WITH_CHARSET=utf8 WITH_COLLATION=utf8__general_ci install clean

This sets it up to use UTF8 as the default character sets and collations, of course you can change these. It also gives support to alternative database formats such as CSV, I don’t actually use them at the moment but thought I’d add them for good measure. For a production system it is good practice to install only what is absolutely necessary, so if you don’t need them for a production environment you might want to leave them out.

Starting MySQL on boot

To start the server you will need to make sure it is enabled in rc.conf, this will also make it start on boot-up:

echo 'mysql_enable="YES"' >> /etc/rc.conf

Now you will be able to start the server:

/usr/local/etc/rc.d/mysql-server start

Securing MySQL

By default MySQL is installed in an insecure way. The root user (different to the system root) can authenticate without a password. It is best to set a good password for the MySQL root user…

mysql -u root -p

It will now prompt you for the current password. After you enter the current password, you will enter the mysql client where you should use something like the following query to change your password:

SET PASSWORD FOR root = PASSWORD('secret');

In this case root’s password is changed to the word “secret”. There is only one problem with this. It will only change root’s password for connections from wherever you are currently connected from, probably localhost. mysql usually has another entry for root for myhost.www.example.org. To change this you can use the following query:

SET PASSWORD FOR root@"myhost.www.example.org" = PASSWORD('secret');

Of course you could also use a different password if you would like, although I’m not sure what purpose this would serve. If you want to try out this second password you can connect in the following way:

mysql -h myhost.www.example.org -u root -p

If you want to check for all root entries you can do the following:

mysql -u root -p mysql

Then enter the following query:

SELECT Host, User, Password FROM user WHERE User = 'root';

MySQL Config File

If you want to use a config file for MySQL you can copy either my-small.cnf, my-medium.cnf, my-large.cnf, or my-huge.cnf to /etc:

cp /usr/local/share/mysql/my-small.cnf /etc/my.cnf

Edit at will!

Tags: FreeBSD, guide, HowTo, Server

This entry was posted on Friday, September 28th, 2007 at 15:35 GMT and is filed under FreeBSD, HowTo, Server. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.