Table of Contents:
Troubleshooting
[Ref: OpenBSD 3.5Beta | mysql-server-4.0.18.tgz | Paul Dubois, MySQL, (Indiana, New Riders, 2000) ]
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:
sudo su export PKG_PATH=ftp://ftp5.usa.openbsd.org/pub/OpenBSD/4.7/packages/i386 pkg_add mysql-server
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.
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
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= # directories for ldconfig
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 mypassword
If you already have accounts/users on the system then a more secure way of changing the password is to log into the mysql client program and create the password from in there.
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)
[Ref: 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
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**
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:
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
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
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 &
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.
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)
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=
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.
[Ref: misc@openbsd.org October 2001 archives | /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 by Derek Sivers