Table of Contents:
[Ref: OpenBSD 4.9 | mysql-server-5.1.54p3.tgz ]
Install the mysql package using pkg_add. The package installation will install the mysql binaries
sudo su export PKG_PATH=ftp://ftp5.usa.openbsd.org/pub/OpenBSD/4.9/packages/i386 pkg_add mysql-server
mysql-server-5.1.54p3:p5-Net-Daemon-0.43p0: ok mysql-server-5.1.54p3:p5-PlRPC-0.2018p1: ok mysql-server-5.1.54p3:p5-DBI-1.609p1: ok mysql-server-5.1.54p3:p5-DBD-mysql-4.014p1: ok mysql-server-5.1.54p3: ok The following new rcscripts were installed: /etc/rc.d/mysqld See rc.d(8) for details. Look in /usr/local/share/doc/pkg-readmes for extra documentation.
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.
Open up the Package Readme file as directed in the install screen output.
For the first time installation, create the default database using
/usr/local/bin/mysql_install_db
Installing MySQL system tables... 110509 21:26:16 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead. OK Filling help tables... 110509 21:26:18 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead. OK PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/local/bin/mysqladmin -u root password 'new-password' /usr/local/bin/mysqladmin -u root -h hostname.example.com password 'new-password' Alternatively you can run: /usr/local/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. Please report any problems with the /usr/local/bin/mysqlbug script!
The server configuration defaults are built in to the compiled binaries, and in the configuration file /etc/my.cnf, which can have a user version at ~/.my.cnf
# MySQL programs look for option files in a set of # locations which depend on the deployment platform. # You can copy this option file to one of those # locations. For information about these locations, see: # http://dev.mysql.com/doc/mysql/en/option-files.html
Periodically review this file to ensure it fits your requirements.
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
If you plan to have a busy MySQL Server, then you can change the login class login.conf(5) of the mysqld (MySQL Daemon) user (_mysql) from the default “daemon” class, to one that you can adjust for the characteristics of your server.
Update the login.conf(5) to something such as:
mysqld:\
:openfiles-cur=1024:\
:openfiles-max=2048:\
:tc=daemon:
If you have to make any of the above changes, then make sure you rebuild the login.conf.db file with cap_mkdb:
[ -f /etc/login.conf.db ] && /usr/bin/cap_mkdb /etc/login.conf
Obviously, if you’ve gone through these steps, you will want to use vipw to update the user configuration to use the above mysqld login class:
Change:
_mysql:*************:502:502:daemon:0:0:MySQL Account:/nonexistent:/sbin/nologin
To something like:
_mysql:*************:502:502:mysqld:0:0:MySQL Account:/nonexistent:/sbin/nologin
Priority 1: Set the root access password for the database, and before we can do that we need to temporarily start mysql.
/etc/rc.d/mysqld start /usr/local/bin/mysqladmin -u root password 'new-password' /usr/local/bin/mysqladmin -u root -h hostname.example.com password 'new-password'
If you already have accounts/users on the system, it is possible that someone may get your admin password from the command-line. Another method for 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/)
/etc/rc.d/mysqld start /usr/local/bin/mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.54-log OpenBSD port: mysql-server-5.1.54p3 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');
Query OK, 0 rows affected (0.05 sec)
mysql> SET PASSWORD FOR root@hostname.example.com=PASSWORD('new_password');
Query OK, 0 rows affected (0.03 sec)
mysql> SET PASSWORD FOR root@127.0.0.1=PASSWORD('new_password');
Query OK, 0 rows affected (0.01 sec)
Always good to get an idea of whether our process is working, so it’s time to review secondary procedures to validate/confirm that the above instructions are working correctly and our installation is progressing.
[Ref: fstat(1) ]
We can always use ps to verify that the mysqld daemon is running, but we want to go a little further than that and ensure that the service/daemon has launched and is actively waiting for connections.
Verify the server is running by using the ‘fstat’ in the following example:
fstat | grep "*:" | grep mysql
_mysql mysqld 19623 13* internet stream tcp 0xd6bf57fc *: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 process is listening.
The displayed line is fstat(1):
USER Command PID FD DOMAIN Socket_Type Socket_Flag Protocol_Numb:Protocol_Address _mysql mysqld 19623 13* internet stream tcp 0xd6bf57fc *:3306
If the server is not listening for connections, then the diagnostics step is to use ps to verify that the binary has been loaded. The next step from there is to review the mysqld error logs for any messages that can help you diagnose the problem.
On my hosts, the error log is stored in the file:
/var/mysql/hostname.domain.tld.err
After we’ve confirmed with the above that the server is running and listening for connections, we access the MySQL database server and look at the default database configuration, server maintenance database ‘mysql.’
Log in to the system through mysql interactive interface to the server.
/usr/local/bin/mysql -u root -p
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.1.54-log OpenBSD port: mysql-server-5.1.54p3 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
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 | +--------------------+ | information_schema | | mysql | | test | +--------------------+ 3 rows in set (0.11 sec)
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;**
Database changed
mysql> **show tables;**
+---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | host | | ndb_binlog_index | | plugin | | proc | | procs_priv | | servers | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 23 rows in set (0.03 sec)
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) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
39 rows in set (0.61 sec)
Grabbing a set of information from the user table lets us see who has been given access to the system.
mysql> **select host, user, select_priv, grant_priv, password from user;**
+----------------------+------+-------------+------------+-------------------------------------------+ | host | user | select_priv | grant_priv | password | +----------------------+------+-------------+------------+-------------------------------------------+ | localhost | root | Y | Y | *AAA8DC73940D53C0532945C34AFAC74A1349A8B8 | | hostname.example.com | root | Y | Y | *AAA8DC73940D53C0532945C34AFAC74A1349A8B8 | | 127.0.0.1 | root | Y | Y | *AAA8DC73940D53C0532945C34AFAC74A1349A8B8 | | localhost | | N | N | | | hostname.example.com | | N | N | | +----------------------+------+-------------+------------+-------------------------------------------+
Anonymous (blank user) and root have accounts on the system.
mysql> **quit**
The documentation recommends using the supplied launcher mysqld_safe for starting the MySQL Daemon/Service. A simplified manner, in OpenBSD, is to use the package supplied rc.d script /etc/rc.d/mysqld
/etc/rc.d/mysqld start
To configure OpenBSD to automatically start mysql with every system start-up then you can edit the rc.conf.local file.
Edit: /etc/rc.conf.local and edit the macro rc_scripts to have something like the below:
mysqld_flags= rc_scripts="mysqld"
Now each restart of the machine will start MySQL using the supplied rc.d script
Once you have the startup script working you can set command-line options by changing the mysqld_flags to be something like the following:
mysqld_flags="--log --open-files-limit=256"
Note: The rc.d(8) script /etc/rc.d/mysqld uses mysqld_safe as the launching tool. Parameters relevant to mysqld_safe can be aplied to mysqld_flags.
To stop the MySQL server, a standard approach is to use the mysqladmin program as shown below:
# /usr/local/bin/mysqladmin shutdown -p
Of course you need mysql administrator privileges for shutting the service down.
As root, you can use /etc/rc.d/mysqld to pkill the daemons.
/etc/rc.d/mysqld stop
The MySQL default install configures anonymous login from localhost with its default installation, you can remove anonymous access by using commands similar to that shown below:
/usr/local/bin/mysql -u root -p**
Enter password: **mypassword**<-- Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.54-log OpenBSD port: mysql-server-5.1.54p3
>mysql > **use mysql**
mysql> select user, password from user;
+------+-------------------------------------------+ | user | password | +------+-------------------------------------------+ | root | *AAA8DC73940D53C0532945C34AFAC74A1349A8B8 | | root | *AAA8DC73940D53C0532945C34AFAC74A1349A8B8 | | root | *AAA8DC73940D53C0532945C34AFAC74A1349A8B8 | | | | | | | +------+-------------------------------------------+
mysql > **delete from user where user = "";**
Query OK, 2 rows affected (0.08 sec)
mysql> select user, password from user;
+------+-------------------------------------------+ | user | password | +------+-------------------------------------------+ | root | *AAA8DC73940D53C0532945C34AFAC74A1349A8B8 | | root | *AAA8DC73940D53C0532945C34AFAC74A1349A8B8 | | root | *AAA8DC73940D53C0532945C34AFAC74A1349A8B8 | +------+-------------------------------------------+
A consistent installation configuration for my MySQL, is as a database backend for a web service, preferably hosted in the default/standard chroot environment for apache.
Key aspects of this chroot environment are:
mkdir -p /var/www/var/run/mysql chown _mysql:_mysql /var/www/var/run/mysql
Adust /etc/my.cnf to put the mysql socket in the chroot.
[client] socket = /var/www/var/run/mysql/mysql.sock [mysqld] socket = /var/www/var/run/mysql/mysql.sock
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.