MySQL

From freebsd.xn--wesstrm-f1a.se

Jump to: navigation, search

Contents

Overview

MySQL is a free open source relational database with millions of installations all over the world. At the time of this writing version 5.1 is the latest stable version. It has been noted during extensive testing of MySQL on FreeBSD and Linux that 5.0 actually performs better on FreeBSD than 5.1 so you're free of course to choose 5.0 if you wish but this guide will describe how to install 5.1.

Instructions

Compiling

MySQL Server 5.1 is in the Ports Collection like almost anything else you need. It's dependent on databases/mysql51-client and will automatically install that too.

# cd /usr/ports/databases/mysql51-server
# make
# make install clean
# rehash

Configuration

In /usr/local/share/mysql you can find five example configuration files.

# ls -1 /usr/local/share/mysql/*.cnf
/usr/local/share/mysql/my-huge.cnf
/usr/local/share/mysql/my-innodb-heavy-4G.cnf
/usr/local/share/mysql/my-large.cnf
/usr/local/share/mysql/my-medium.cnf
/usr/local/share/mysql/my-small.cnf

They represent reasonable defaults for systems of various capacity and the details can be found in the comments first in each file. It turns out that system RAM is a major factor deciding which config file to choose. When you have made your choice, copy the file to /usr/local/etc/my.cnf, which is the default location and name for this file in FreeBSD.

# cp /usr/local/share/mysql/my-large.cnf /usr/local/etc/my.cnf

MySQL also creates a new user called mysql so add it to /etc/mail/aliases as usual so you get any mail for it forwarded to your own account instead.

# cd /etc/mail
# echo "mysql: pp" >> aliases
# make

Starting the server

The following command will add a line to rc.conf that will start MySQL automatically the next time you reboot.

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

Now start the server manually.

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

Securing the server

Your MySQL installation is unfortunately wide open at this point. There's a passwordless root account created and also an anonymous account and you have to fix this. Let's have some MySQL fun! First start the client so you can interact with the server. All SQL commands used below are written in bold just like all other commands in this guide that you're supposed to type.

# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.26-rc-log FreeBSD port: mysql-server-5.1.26

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

mysql>

You need to switch to the user database and look at its contents.

mysql> USE mysql;
Database changed
mysql> SELECT Host,User,Password FROM user;
+--------------------------+------+----------+
| Host                     | User | Password |
+--------------------------+------+----------+
| localhost                | root |          | 
| gatekeeper.pp.dyndns.biz | root |          | 
| 127.0.0.1                | root |          | 
| localhost                |      |          | 
| gatekeeper.pp.dyndns.biz |      |          | 
+--------------------------+------+----------+
5 rows in set (0.00 sec)

Your output will look similar to this but the hostname and domain name should match your own of course. When accessing a database you'll always need a userid and a password but you can only use them if they're present in this table and only from the host shown in the corresponding record. Using the root account, you are by default only allowed to connect to the MySQL server from the physical router itself. localhost, gatekeeper.pp.dyndns.biz and 127.0.0.1 are all different names for the router and since you're executing the MySQL client on the router right now, you're allowed to connect. If you had been running the MySQL client on one of your computers on your LAN instead, you wouldn't have been granted access. You want to keep those three entries but you need to set a password for them since it's empty by default. There are also two anonymous entries at the bottom which should be deleted immediately. Let's correct them one by one and remember to replace newpassword with a password of your own and also make sure it's your hostname and domain used in the commands.

mysql> SET PASSWORD FOR "root"@"localhost" = PASSWORD("newpassword");
Query OK, 0 rows affected (0.01 sec)

mysql> SET PASSWORD FOR "root"@"gatekeeper.pp.dyndns.biz" = PASSWORD("newpassword");
Query OK, 0 rows affected (0.01 sec)

mysql> SET PASSWORD FOR "root"@"127.0.0.1" = PASSWORD("newpassword");
Query OK, 0 rows affected (0.00 sec)

mysql> DROP USER ""@"localhost";
Query OK, 0 rows affected (0.00 sec)

mysql> DROP USER ""@"gatekeeper.pp.dyndns.biz";
Query OK, 0 rows affected (0.00 sec)

Now run the query again.

mysql> SELECT Host,User,Password FROM user;
+--------------------------+------+-------------------------------------------+
| Host                     | User | Password                                  |
+--------------------------+------+-------------------------------------------+
| localhost                | root | *DC4662F10F574972EBDA222D34259ED17D6013BE | 
| gatekeeper.pp.dyndns.biz | root | *DC4662F10F574972EBDA222D34259ED17D6013BE | 
| 127.0.0.1                | root | *DC4662F10F574972EBDA222D34259ED17D6013BE | 
+--------------------------+------+-------------------------------------------+
3 rows in set (0.00 sec)

Everything looks much better now, the root entries have a password set (encrypted) and the anonymous entries are gone. You should continue to only allow connections to MySQL from the router itself for security reasons so no more entries have to be added to the list at this point. This also means you don't have to add any firewall rules to allow access to MySQL since no one will be accessing it from the Internet. Also remember that the user accounts listed in this table are virtual users, internal to MySQL only and not related to the user accounts in FreeBSD. You'll be adding more accounts here when you start adding applications that use MySQL.

You must now clear any cached permissions and then exit your MySQL client.

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> EXIT
Bye

If you need to connect to the server again you now have to add the -p argument to be prompted for the password you just set - like this:

# mysql -u root -p
Enter password:

Log files

MySQL's regular logfile is by default stored in the database directory which you changed to /usr/local/var/db/mysql. The filename is composed by the hostname of the router so in this case it's called gatekeeper.pp.dyndns.biz.err

MySQL also keeps track of every transaction that is made to every database in the system. These transactions are stored in binary logs, also in the database directory, and they're called mysql-bin.xxxxx where the x:es are replaced by numbers. The purpose of these logs are to recover a database from a crash or to rollback a database to a certain date. By default these logs are never emptied and they grow large pretty fast so a small configuration change is necessary if this behaviour is unwanted. Open /usr/local/etc/my.cnf in your editor and find the following line:

log-bin=mysql-bin

Add this line below it:

expire_logs_days=10

Save the file and restart MySQL.

# /usr/local/etc/rc.d/mysql-server restart
Stopping mysql.
Waiting for PIDS: 7913.
Starting mysql.

MySQL will now only keep transaction logs for the past 10 days.

Upgrading MySQL

As with all applications on your router, you should regularly upgrade MySQL to fix bugs and security issues. After an upgrade it's absolutely necessary to run mysql_upgrade afterwards to apply changes to MySQL's system tables. This is mentioned in the output after an upgrade but might be easy to miss. Failure to run mysql_upgrade would probably corrupt your databases quickly. A typical run of mysql_upgrade would look like this:

# mysql_upgrade -u root -p

The program will ask for your MySQL root password and will then print a list of the changes it makes to your databases and tables.

Summary

  • /usr/local/etc/my.cnf is the configuration file for the MySQL server.
  • /usr/local/etc/rc.d/mysql-server is the start/stop script for the MySQL server.
  • MySQL has its own access control and user accounts created in it are separate from the accounts in FreeBSD.
  • rehash can be used after installing a port to be able to use the new binaries in the current shell without having to add the full path to them.
  • The logfile is /usr/local/var/db/mysql/hostname.err where hostname is the name of your router.

References


Next guide: PHP
Personal tools