Installing MySQL v5

If you are running a website with any ambition of going interactive, the only logical thing to do is putting a database to work. And MySQL is the logical choice. MySQL is a very fast, reliable and light-weight database server - with MySQL you can go a long way with a single server setup. The thing I really like about it, is that it is a transparent easy-to-use system - you don't need a master degree to run it... and by the way, this wonderful database system is free.

• Installing MySQL
• Setting the root password
• How to start and stop the MySQL server
• Security
• Admin interface for MySQL
• MySQL configuration file

Installing MySQL

Install MySQL prior to any other software which depend on it. This ensures that you get your preferred version.

You need two applications ... the MySQL server and the MySQL client. Both are found in /usr/ports/databases/. If you install the server first the client will also be installed automatically.

cd /usr/ports/databases/mysql50-server/
make SKIP_DNS_CHECK=yes install clean distclean

If the system cannot resolve your hostname the installation will fail with the message: Please configure the 'hostname' command to return a correct hostname. The SKIP_DNS_CHECK option is included to prevent this from happening.

After installation you must enable mysql in rc.conf to make it start up automatically:

vi /etc/rc.conf
mysql_enable="YES"

Setting the root password

When MySQL is installed and enabled, you start it and choose a password for root...

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

mysqladmin -u root password mypassword

If it says "command not found" for mysqladmin, type rehash and press [enter].

Change "mypassword" to a password of your own choice. If you use copy/paste and accidently set the password to "mypassword" (as I did - doh!), you can change it again like this:

mysqladmin -u root -p password newpassword
Enter password: oldpassword

Remember to replace newpassword with the password you want, and replace oldpassword with the old one.

How to start and stop the MySQL server

This is how to start and stop the server

cd /usr/local/etc/rc.d/
./mysql-server.sh stop
./mysql-server.sh start

You can check the processlist to see if it's actually running:

ps -ax | grep mysql
65368 p0 S 0:00.38 [mysqld]

Unless you've started mysql with the skip-networking option, you should also see it listening on port 3306:

Use [CTRL]+[C] to quit the connection again telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to localhost.example.tld.
Escape character is '^]'.
8
5.0.18-logR'i\qEDc,H|X#Dmw2/3T0
^C Connection closed by foreign host.

If you are having trouble stopping mysql, use mysqladmin to shut it down:

mysqladmin -u root -p shutdown
Password:

After this, you can start it again with ./mysql-server.sh start as shown earlier.

When you want to login and do stuff with MySQL, this is how to do it

mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.15

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

If you want to type characters with umlauts, accents or special characters like æ, ø and å, you have to enable your shell / console for 8 Bit encoding.. click her to see how.

Security

Quote from http://www.freebsddiary.org/mysql.php

"I only connect to my database server from localhost. So there is no need for network connections. Therefore, I add this flag to mysql-server.sh: --skip-networking. This also eliminates the possibility that someone will connect to your database server over the network/Internet."

That sounds reasonable, so let's open up /etc/rc.conf and insert this line:

mysql_args=" --skip-networking"

Then stop and start the MySQL server again.

Admin interface for MySQL

There is a nice and user friendly interface for MySQL called phpMyAdmin which is described here

MySQL configuration file

The configuration file for MySQL is the place for fine-tuning the database server. MySQL comes with 4 different examples for small, medium, large and huge systems. These files are located in /usr/local/share/mysql. In each of these example configurations (my-small.cnf, my-medium.cnf and so on...) you'll find information on when you should used each of them based on available memory. To use one of them, just overwrite the current configuration, by copying it to /var/db/mysql/my.cnf.

Most of the variables in the configuration file, can also be set at runtime. phpMyAdmin is very helpful when you need an overview of the configuration, and hints on tuning.

When running a busy site, it is of cause important to use the correct type of configuration. But the real memory consumer is badly created indexes on tables. One faulty index on a frequently used table can completely take the breath away from any system... so the very first thing to do on a server with very high load, is to look at the slow-queries log. Your load averages can go from 30 to 0 in a minute, when a bad index is fixed.

You can log all queries, errors and/or slow queries.. the desicion is yours. Add these entries in my.cnf to use one or more of these log options:

log=/var/log/mysql/mysql.log
log-error=/var/log/mysql/mysql-err.log
log-slow-queries=/var/log/mysql/mysql-slow-queries.log
log-queries-not-using-indexes

.. and set the correct permissions on the log files.:

cd /var/log/
mkdir mysql
cd mysql
touch mysql-err.log
touch mysql.log
touch mysql-slow-queries.log
chmod 660 *
chown mysql:mysql *

The 'log' entry causes each and every query to be logged. Use this only if you are debugging and application, since it is a performance killer.

'log-error' will log anything that goes wrong. 'log-slow-queries' will, as the name says, log any query that didn't finish within n seconds (n is determined by the long_query_time configuration value). The presence of 'log-queries-not-using-indexes' ensures that the slow-queries log also contains any queries which does not use indexes.

Be aware that the log-* entries in the config file cannot be set at runtime. You must restart MySQL whenever you make changes.

As usual, make sure that you rotate the logfiles with newsyslog.