MySQL Database Server


Table of Contents:


Introduction

To be written: but there are seriously a lot of written material on the 'NET about mysql, this guide is for how to install the package in OpenBSD and some basic testing tools to verify everything is running.

Another great location for aids are:

Installing MySQL

[ref: Paul Dubois, MySQL, (Indiana, New Riders, 2000)]
Reference: mysql-server-4.0.18.tgz (port-package) OpenBSD 3.5Beta

Install the mysql package using pkg_add. The package installation will install the mysql binaries and create the default database by executing mysql_install_db. This includes initialising the data directory (--datadir=/var/mysql,) and Grant Tables for the 'root' user. The datadir is where the system-wide databases will be located. The Grant Tables is specify access privileges available. Together with creating the database directories/files the package will also chown/chmod the directories. Install the package with the standard method shown below:

# pkg_add /[path-to-package]/mysql-server-4.0.18.tgz

The package automatically creates the user '_mysql' (on my system with uid 502) and group '_mysql' (gid 502) which are used for running the sql server.

Setting the Library Configuration

Somewhere in the life of mysql development, the libraries were moved from /usr/local/lib to their own directory /usr/local/lib/mysql. Because of this, we need to specify its location for the machine startup routines. We make these changes in rc.conf.local by modifying the reference to shlib_dirs:

Edit rc.conf.local and add the following line in Section 3:

shlib_dirs="$shlib_dirs /usr/local/lib/mysql" # extra directories for ldconfig

This will include the library directory to the original settings in rc.conf which is usually:

shlib_dirs=             # extra directories for ldconfig

Setting the Password

Priority 1: Set the root access password for the database, and before we can do that we need to temporarily start mysql.

# /usr/local/bin/mysqld_safe &
# /usr/local/bin/mysqladmin -u root password new_password

If you are on a live system a more secure way of changing the password is to log into the mysql interactive client program 'mysql' and create the password from within the client.

For example, after 'mysqld_safe &' do something like the below. (courtesy of http://www.revunix.tk/)

# /usr/local/bin/mysqld_safe &
# /usr/local/bin/mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.20-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');
Query OK, 0 rows affected (0.07 sec)

 

Testing the installation.

Is it Running?

Reference: fstat(1)

Verify the server is running by using the 'fstat' in the following example:

# fstat | grep "*:" | grep mysql
_mysql mysqld 22190 5* internet stream tcp 0xd0bc25a4 *:3306

Now we know through fstat that the mysql daemon (mysqld) is running with user privileges of _mysql and listening on port 3306. The [ | grep "*:" ] filters for processes that have an "internet stream" open. The [ | grep mysql ] further filters down to processes with the word mysql on the line.

One neat feature of the 'fstat' program is that the "*.nnnnn" indicates the port on which the processes is listening.

The displayed line is fstat(1):

USER   Command PID    FD DOMAIN   Socket_Type Socket_Flag  Protocol_Numb:Protocol_Address
_mysql mysqld  22190  5* internet stream tcp  0xd0bc25a4   *:3306

 

Can we access the server?

Our first test for validating the installation is to access the MySQL database server and look at the server maintenance database 'mysql.' We log in to the system through mysql interactive interface to the server.

# /usr/local/bin/mysql -u root -p
Enter password: mypassword <-- this will show as **********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.0.18

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


mysql>

The mysql> prompt allows sql statements and MySQL commands to be entered. Most commands are completed by using the ";" semi-colon delimiter.

We check whether the initial database creation was successful (mysql, and test.) Use the show databases; command. The MySQL package should have successful created the system database 'mysql' and a sample database 'test'.

mysql> show databases;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+

We can check whether the mysql database has been installed by looking at the installed tables which should look like the below.

mysql> use mysql;
mysql> show tables;
+-----------------+
| Tables in mysql |
+-----------------+
| columns_priv    |
| db              |
| func            |
| host            |
| tables_priv     |
| user            |
+-----------------+

The user table is the system wide table to record what users are allowed onto the system and with what privileges. By using the 'describe' command we can see a list of the table fields and data-types. In this table it shows us the different levels of privileges available on the MySQL server.

mysql> describe user;
+-----------------+---------------+------+-----+---------+-------+
| Field           | Type          | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| Host            | char(60)      |      | PRI |         |       |
| User            | char(16)      |      | PRI |         |       |
| Password        | char(16)      |      |     |         |       |
| Select_priv     | enum('N','Y') |      |     | N       |       |
| Insert_priv     | enum('N','Y') |      |     | N       |       |
| Update_priv     | enum('N','Y') |      |     | N       |       |
| Delete_priv     | enum('N','Y') |      |     | N       |       |
| Create_priv     | enum('N','Y') |      |     | N       |       |
| Drop_priv       | enum('N','Y') |      |     | N       |       |
| Reload_priv     | enum('N','Y') |      |     | N       |       |
| Shutdown_priv   | enum('N','Y') |      |     | N       |       |
| Process_priv    | enum('N','Y') |      |     | N       |       |
| File_priv       | enum('N','Y') |      |     | N       |       |
| Grant_priv      | enum('N','Y') |      |     | N       |       |
| References_priv | enum('N','Y') |      |     | N       |       |
| Index_priv      | enum('N','Y') |      |     | N       |       |
| Alter_priv      | enum('N','Y') |      |     | N       |       |
+-----------------+---------------+------+-----+---------+-------+

Grabbing a set of information from the user table lets us see who has been given access to the system. The machine I have mysql installed on is called iwill, and you should see a similar result to the select query on your machine. Note the "blank" users is used by mysql for 'anonymous' and at the beginning only --user=root has privileges to do anything on the system. Note that the password field is encrypted with a one-way encryption system similar to but not the unix crypt() function.

mysql> select host, user, select_priv, grant_priv, password from user;
+-----------+------+-------------+------------+------------------+
| host      | user | select_priv | grant_priv | password         |
+-----------+------+-------------+------------+------------------+
| localhost | root | Y           | Y          | 162eebfb6477e5d3 |
| iwill     | root | Y           | Y          |                  |
| localhost |      | N           | N          |                  |
| iwill     |      | N           | N          |                  |
+-----------+------+-------------+------------+------------------+
mysql> quit

Starting MySQL with each start-up.

To configure OpenBSD to automatically start mysql with every system start-up then you can edit the rc.conf.local file to modfiy the configuration and rc.local to take action when the configurations are set.

Edit: /etc/rc.conf.local file to include in Section 2 the following lines:

mysql=YES

Edit: /etc/rc.local

After the 'starting local daemons' and before the following echo '.', Insert the following instructions to the /etc/rc.local file:

echo -n 'starting local daemons:'   
# [ ... stuff left out ... ]
 
if [ X"${mysql}" == X"YES" -a -x /usr/local/bin/mysqld_safe ]; then

    echo -n " mysqld"; /usr/local/bin/mysqld_safe --user=_mysql --log --open-files-limit=256 &

    for i in 1 2 3 4 5 6; do
        if [ -S /var/run/mysql/mysql.sock ]; then
            break
        else
            sleep 1
            echo -n "."
        fi
    done
    #
    # Apache chroot Settings

    mkdir -p /var/www/var/run/mysql
    sleep 2
    ln -f /var/run/mysql/mysql.sock /var/www/var/run/mysql/mysql.sock

    #
    # Postfix chroot Settings
    if [ "X${postfix_flags}" != X"NO" ]; then
        mkdir -p /var/spool/postfix/var/run/mysql
        sleep 2
        ln -f /var/run/mysql/mysql.sock /var/spool/postfix/var/run/mysql/mysql.sock
    fi

fi

# [ ... stuff left out ... ]
echo '.'

Now each restart of the machine will automatically check to see whether we have enabled mysql in the configuration file (rc.conf) and then start the mysql daemon. If we wish to disable mysql we can simply change mysql=YES to mysql=NO

Once you have the startup script working you can get rid of all the extraneous messages that mysql startup daemons makes by changing the mysqld_safe line to be something like the following:

/usr/local/bin/mysqld_safe --user=_mysql --log --open-files-limit=256 > /dev/null 3>&1 2>&1 &

Since the above configuration is what I use on my test server, you may find that your needs require more open-files so in your situation (as I've been informed for others) you may need to increase the number of open files like: http://marc.theaimsgroup.com/?l=openbsd-misc&m=109842287618174&w=2

/usr/local/bin/mysqld_safe --user=_mysql --log --open-files-limit=1000 > /dev/null 3>&1 2>&1 &

Note: /usr/local/share/mysql/mysql.server is a script for starting/stopping mysql daemon. The files are there with mysql-server-4.0.18 on OpenBSD 3.5 so your mileage may vary.

WARNING!!! Hard Link (ln) won't work across devices

[ref: http://mailman.theapt.org/pipermail/openbsd-newbies/2005-February/002341.html]

The above rc.local updates work fine when you are running your chroot environments within the /var filesystem (ie. /var/www and /var/spool/postfix are not separate filesystems 'mounted' on /var/...) 

From the manual ln(1)

By default, ln makes "hard" links.  A hard link to a file is indistinguishable from the original directory entry; any changes to a file are effectively independent of the name used to reference the file.  Hard links may not normally refer to directories and may not span file systems.

If you are using separate file systems for your chroot environment, instead of hard links to sockets there are a few alternatives.

  1. Use 127.0.0.1 as your host for connecting to the mysql server instead of "localhost"
  2. Create the socket file inside the chroot environment.
    /usr/local/bin/mysqld_safe --user=_mysql --log --open-files-limit=1000 --socket=/var/www/var/run/mysql/mysql.sock > /dev/null 3>&1 2>&1 &

    Alternatively you can add something like the following to your /etc/my.cnf file:

Stopping MySQL

To stop the MySQL server, a standard approach is to use the mysqladmin program as shown below:

# /usr/local/bin/mysqladmin shutdown

Of course you have to be logged in as a user with privileges to shutdown the server (as noted above in the the user table, field Shutdown_priv. Otherwise if you had root access you could shutdown the server through a 'kill'.

Security Notice: MySQL installs anonymous login access from the localhost with its default installation, you may or may not consider this a security issue. If you do consider it a problem then you can remove the anonymous access by using commands similar to that shown below:

# /usr/local/bin/mysql -u root -p
Enter password: mypassword <-- this will show as **********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 3.22.32

Type 'help' for help.
mysql > use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
mysql > delete from user where user = "";
Query OK, 2 rows affected (0.08 sec)

Usability Assistance

User Configuration Files

mysql will search for user configurations in a file called ~/.my.cnf which has a simple format to let you specify settings we have been forced to manually type with each invocation (start) of msyql.

~/.my.cnf have the below settings which are user configurable.

[client]
host=
user=
password=
socket=

[mysqld]
socket=

Screen Editor in mysql

In Unix, the mysql command line supports using a screen editor for modifying and creating queries. The 'edit' command in the MySQL command line calss the text editor of your choice (typically set with the EDITOR environment variable, probably vi on your OpenBSD.)

mysql> edit

If you make a mistake in keying in a command, then you can use the up-arrow to review your command, or you can type in edit to re-enter. edit remembers the previous command string you typed, so it is especially useful getting those large queries working.

Troubleshooting

[ref: misc@openbsd.org October 2001 archives]
[ref: /usr/ports/databases/mysql/patches/]

One problem that seems to be reported a lot on the mailing lists (and subsequently responded to) is that mysql at a large level of use begins to consume all available resources and locks up the system.

The answer for this problem OpenBSD 2.x and OpenBSD 3.0 have been well documented at http://www.hostbaby.com/misc/mysql-openbsd.html by Derek Sivers.

Related References

MySQL Home http://www.tcx.se
phpAdmin http://www.phpwizard.net/phpMyAdmin/

MySQL 5.x and OpenBSD 4.0: http://www.openbsdsupport.org/mysql.htm

Page Descriptions Verified: 2003-01-09;

OpenBSD 3.2-current (GENERIC) #39: Wed Dec 11 21:00:29 MST 2002
deraadt@i386.openbsd.org:/usr/src/sys/arch/i386/compile/GENERIC
mysql-server-3.23.49.tgz

Author and Copyright

Copyright (c) 2000/1/2 Samiuela LV Taufa. All Rights Reserved.

I reserve the right to be totally incorrect even at the best advice of betters. In other words, I'm probably wrong in enough places for you to call me an idiot, but don't 'cause you'll hurt my sensibilities, just tell me where I went wrong and I'll try again.

You are permitted and encouraged to use this guide for fun or for profit as you see fit. If you republish this work in what-ever form, it would be nice (though not enforceable) to be credited.

MySQL - Serving up SQL

Copyright  © 2000/1/2 NoMoa Publishers All rights reserved. Caveat Emptor