Setting up mySQL & iODBC

Note: The following is part of a series of steps to setup an email server using Exim 4.x, with imap and webmail access. It will use winbind to get user information from an NT server. If you found this page via a search engine it may not cover what you need or you may need to start at the beginning to understand everything I have done.

(Note: These instructions reference software that is now possibly much newer with many new or different configuration options. This page is being left up for reference.)

Quick Steps:

For mySQL:

  1. Install mySQL
  2. Edit /etc/my.cnf
  3. Start mySQL
  4. Set the root user's password for mySQL
  5. Set up the User(s)
  6. Set up the Database(s)

For iODBC:

  1. Get iODBC Driver Manager
  2. Get MyODBC
  3. Configure iODBC on *nix
  4. Test ODBC on *nix
  5. Troubleshooting on *nix

For MyODBC on Windows

  1. Get MyODBC and Install
  2. Testing the connection with telnet
  3. Configuring a User DSN
  4. Using a DSNless Connection

For mySQL

I'm using version 3.23.55a.

Note: Databases are kept in /var/lib/mysql

Documentation can be found here: http://www.mysql.com/documentation/index.html

A how to make a backup copy of a database: http://www.linuxhaiku.com/modules.php?op=modload&name=News&file=article&sid=23

Some basic SQL: http://www.eh10.pwp.blueyonder.co.uk/gisq/howto/xfunaccess.htm

MySQL security: http://www.mysql.com/doc/en/Privilege_system.html

1) Install mySQL

I used the RPM.

2) Edit /etc/my.cnf

This is the global config file. Here is mine:

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
 
[mysqld]
port = 3306
datadir = /var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = key_buffer_size=16M
set-variable = max_allowed_packet=1M
# If you do not want to use innodb set this
# skip-innodb
# And don't set the following
# From http://www.innodb.com/ibman.html
#  Data file(s) must be able to
#  hold your data and indexes.
#  Make sure you have enough
#  free disk space.
innodb_data_file_path = ibdata1:10M:autoextend
#  Set buffer pool size to
#  50 - 80 % of your computer's
#  memory
set-variable = innodb_buffer_pool_size=30M
set-variable = innodb_additional_mem_pool_size=10M
#  Set the log file size to about
#  25 % of the buffer pool size
set-variable = innodb_log_file_size=8M
set-variable = innodb_log_buffer_size=8M
#  Set ..flush_log_at_trx_commit
#  to 0 if you can afford losing
#  some last transactions
innodb_flush_log_at_trx_commit=1
 
[mysql.server]
user = mysql
basedir = /var/lib
 
[safe_mysqld]
err-log = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid

3) Start mySQL

service mysqld start

Here is the init.d script:

#!/bin/bash
#
# mysqld        This shell script takes care of starting and stopping
#               the MySQL subsystem (mysqld).
#
# chkconfig: - 78 12
# description:  MySQL database server.
# processname: mysqld
# config: /etc/my.cnf
# pidfile: /var/run/mysqld/mysqld.pid
 
# Source function library.
. /etc/rc.d/init.d/functions
 
# Source networking configuration.
. /etc/sysconfig/network
 
prog="MySQL"
 
datadir="/var/lib/mysql"
 
start(){
       touch /var/log/mysqld.log
        chown mysql.mysql /var/log/mysqld.log
        chmod 0640 /var/log/mysqld.log
        if [ ! -d $datadir/mysql ] ; then
            action $"Initializing MySQL database: " /usr/bin/mysql_install_db
            ret=$?
            chown -R mysql.mysql $datadir
            if [ $ret -ne 0 ] ; then
                return $ret
            fi
        fi
        chown -R mysql.mysql $datadir
        chmod 0755 $datadir
        /usr/bin/safe_mysqld  --defaults-file=/etc/my.cnf >/dev/null 2>&1 &
        ret=$?
        if [ $ret -eq 0 ]; then
            action $"Starting $prog: " /bin/true
        else
            action $"Starting $prog: " /bin/false
        fi
        [ $ret -eq 0 ] && touch /var/lock/subsys/mysqld
        return $ret
}
 
stop(){
        /bin/kill `cat /var/run/mysqld/mysqld.pid  2> /dev/null ` > /dev/null 2>
        ret=$?
        if [ $ret -eq 0 ]; then
            action $"Stopping $prog: " /bin/true
        else
            action $"Stopping $prog: " /bin/false
        fi
        [ $ret -eq 0 ] && rm -f /var/lock/subsys/mysqld
        [ $ret -eq 0 ] && rm -f $datadir/mysql.sock
        return $ret
}
 
restart(){
    stop
    start
}
 
condrestart(){
    [ -e /var/lock/subsys/mysqld ] && restart || :
}
 
# See how we were called.
case "$1" in
  start)
    start
    ;;
  stop)
    stop
    ;;
  status)
    status mysqld
    ;;
  restart)
    restart
    ;;
  condrestart)
    condrestart
    ;;
  *)
    echo $"Usage: $0 {start|stop|status|condrestart|restart}"
    exit 1
esac
 
exit $?

4) Set the root user's password for mySQL

Which is different from the root (*nix) user:
/usr/bin/mysqladmin -u root password new-password

If you get "Access denied" for root@localhost try this method:
Stop mysqld:
service mysqld stop
Start it with:
/usr/libexec/mysqld --skip-grant-tables --user=mysql
Log onto another terminal or SSH session (you're not using telnet I hope) and run:
mysql -u root mysql

mysql> UPDATE user SET Password=PASSWORD('mynewpassword')
-> WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit

Then test with:
mysql -u root -p
and enter the new password. If you are able to login, exit and do a:
kill `cat /var/run/mysqld/mysqld.pid'
to stop it.
Log out of the 2nd session and restart the mysql server as normal from the first. Retesting the login is a good idea.

5) Set up the User(s)

To create a user with a password that can access all the databases from anywhere and do anything:
GRANT ALL PRIVILEGES ON *.* TO user@"%" IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

See the manual for more.

From the manual, section 4.3.3: "If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should execute a FLUSH PRIVILEGES statement or run mysqladmin flush-privileges or mysqladmin reload to tell the server to reload the grant tables. Otherwise, your changes will have no effect until you restart the server. If you change the grant tables manually but forget to reload the privileges, you will be wondering why your changes don't seem to make any difference!"

Okay, I cheated and used Webmin to configure most of my other users. User dbedit can access from all hosts and has the privileges of Select, Insert, Update, Delete, Create, Drop, File, and Alter. This is the user I will use when making an ODBC connection from MS Access. User ldapac can access from localhost and has the privilege of Select.

6) Set up the Database(s)

For ours, basically everything is in one database table - name, phone number, email, snail-mail, etc. You need to figure out what mySQL column types you need for your data. See the manual, section "6.2 Column Types". MS Access has a field type "Yes/No", which translates as -1/0 respectively. For this I decided to use TINYINT(1). For the rest VARCHAR(length) will work nicely.

Note that the O'Reilly "mySQL & mSQL" book recommends CHAR(13) for phone numbers. However, we have a good dose of international numbers and I think that VARCHAR(20) will work nicely. Main difference between CHAR and VARCHAR is CHAR will always take up specified (length) bytes, where as VARCHAR is the number of characters + 1 bytes. Max field length for both is 255.

Since we are migrating from an Access database, what follows are some key notes of what I did for exporting.

What I did was open the MS Access database, created a query that got everything, after cleaning up the autonumber primary key, and exported it as a CSV file with quotes around the text fields. The reason for dropping the primary key is I want it created via mySQL. I also made sure no column names had spaces or were over 64 characters long. See the mySQL manual, section "6.1.2 Database, Table, Index, Column, and Alias Names".

How to rename fields in an Access query:
Generate your query via the wizard or however you want. Insert all the fields needed. Now switch to design view and find the field you want to change. Example:
Field: Name with Spaces
Change to:
Field: NoMoreSpaces: Name with Spaces
Remember that in the *nix world things are typically case sensitive.

To 'clean up' your autonumber field in an existing table (make a backup copy first!):

  • Open the existing table in design view.
  • Delete the primary key (assuming autonumber).
  • Create a new field, same or different name, and make it an autonumber field and the primary key.

To create a query for the initial import into ldap_entries, for the ldap back-sql database using mySQL which can't use VIEW, I used these field definitions:

  • id: [contacts].[id]+1
    (This is because field 1 will be manually input before import as:
    id = "1", dn = "ou=contacts,dc=example,dc=com", oc_map_id = "1", parent = "0", keyval = "1")
  • dn: "uid=" & [contacts].[id] & ",ou=contacts,dc=example,dc=com"
    The reason for using the primary key from the contacts table for the uid is that the dn must be unique for every entry.
  • oc_map_id: "2"
    The number is based on the corresponding entry in ldap_oc_mappings.
  • parent: "1"
    (See explanation for id field.)
  • keyval: [contacts].[id]
    is equal to the id field of the query on the contacts table

2008-02-14 note: MySQL does now support VIEWs. If you need a bit more updated information check out this site:
http://www.docunext.com/blog/2006/10/31/openldap-mysql-documentation/

If you need more information about the back-sql setup, go here and read "Simple Example Setup"


For iODBC

1) Get iODBC Driver Manager

http://www.iodbc.org/

Available in an RPM. iODBC 2.50.3 or later needed & the devel-*.rpm as well. Install with:
rpm -ivh name_of.rpm

You may also want the SDK which has the odbctest program. The link is at the bottom of the page for downloading the binaries.
Note: Its extension is .taz. Just gunzip then tar -xvf to unpack.

2) Get MyODBC

Available in an RPM. I'm using 2.50.x:
http://www.mysql.com/downloads/api-myodbc.html

You'll also need the Windows one if you plan on using the database with something like MS Access.

3) Configure iODBC on *nix

Edit the /etc/odbc.ini file.

Here is a sample one using MyODBC 2.50.39:

; begin odbc.ini
[ODBC Data Sources]
test = MySQL ODBC 2.50 Driver DSN
 
[test]
Driver = /usr/local/lib/libmyodbc.so
Description = MySQL ODBC 2.50 Driver DSN
DSN = test
Server = localhost
PORT = 3306
SOCKET = /var/lib/mysql/mysql.sock
User = valid_user
Password = valid_passwd
Database = test
ReadOnly = no
ServerType = MySQL
FetchBufferSize = 99
ServerOptions =
ConnectOptions =
OPTION = 3
TraceFile = /var/log/mysql_test_trace.log
Trace = 0
 
[Default]
Driver = /usr/local/lib/libmyodbc.so
Description = MySQL ODBC 2.50 Driver DSN
Server = localhost
PORT = 3306
USER = valid_user
Password = valid_passwd
SOCKET = /var/lib/mysql/mysql.sock
;end odbc.ini

Example using MyODBC 3.51 (not tested, but should work):

; begin odbc.ini
[ODBC Data Sources]
test = MySQL ODBC 3.51 Driver DSN 
 
[test]
Driver = /usr/local/lib/libmyodbc3.so
Description = MySQL ODBC 3.51 Driver DSN
DSN = test
Server = localhost
PORT = 3306
SOCKET = /var/lib/mysql/mysql.sock
USER = valid_user
Password = valid_passwd
Database = test
ReadOnly = no
ServerType = MySQL
FetchBufferSize = 99
ServerOptions =
ConnectOptions =
OPTION = 3
TraceFile = /var/log/mysql_test_trace.log
Trace = 0
 
[Default]
Driver = /usr/local/lib/libmyodbc3.so
Description = MySQL ODBC 3.51 Driver DSN
SERVER = localhost
PORT =
USER = valid_user
Password = valid_passwd
OPTION = 3
SOCKET = /var/lib/mysql/mysql.sock
; end odbc.ini

4) Test ODBC

This is where you need the odbctest from the SDK. It is not required, but can be helpful for verifying ODBC is working.

cd to where you unpacked the SDK. In the odbcsdk/examples directory you will find odbctest. To run:
./odbctest

Now enter the name of the database to connect to that was defined in your odbc.ini file. In the above example it is test, so enter:
DSN=test

To see your tables:
show tables;

To exit:
exit

5) Troubleshooting

If you get "Access denied for user: '@localhost' to database 'xxxx'" (note the lack of username before the @), but you can connect using:
mysql -u [username] -p [database]
then check to see that the environment variable ODBCINI is pointing to the odbc.ini file.
To see all variables:
printenv
To set:
ODBCINI="/path/odbc.ini"; export ODBCINI


For MyODBC on Windows

1) Get MyODBC and Install

Download from here: http://www.mysql.com/downloads/api-myodbc.html

Read the MyODBC FAQ.

2) Testing the Connection with Telnet

Open your telnet client and try to connect to port 3306. Or use the Start->Run: telnet (server name or ip):3306. If you see gibberish everything is fine. If it immediately disconnects without showing anything and /var/log/mysqld.log shows mysql was killed and restarted, you need to add the client computer(s) to the /etc/hosts file if you do not have a DNS server that can resolve the machine's name. The other alternative is to start mysqld with '--skip-name-resolve', but then you must specify database privileges by IP instead of host name.

3) Configuring a User DSN

Read the MyODBC FAQ, Section 3.3 "How do I configure DSN on Windows".

How to check and, if not exist, auto-create the DSN via Access 97:

Modified from http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q159691&LN=EN-US

Create an Access table called tblODBCDataSources:
Database: Text, 50
UID: Text, 50
PWD: Text, 50
Server: Text, 50
ODBCTableName: Text, 50
LocalTableName: Text, 50, Primary Key
DSN: Text, 50
Driver: Text, 50
Option: Text, 50
Port: Text, 50

Sample data for the above table:
Database: mysql_database_name
UID: valid_user
PWD: valid_password
Server: name_or_ip
ODBCTableName: mysql_table_name
LocalTableName: as_it_will_appear_in_access
DSN: name_for_the_dsn
Driver: MySQL (for 2.50 or MySQL ODBC 3.51 Driver for 3.51)
Option: 3
Port: 3306

Create a Module called CreateDSN:

'***************************************************************
'The DoesTblExist function validates the existence of a TableDef
'object in the current database. The result determines if an
'object should be appended or its Connect property refreshed.
'***************************************************************
 
Function DoesTblExist(strTblName As String) As Boolean
   On Error Resume Next
   Dim db As Database, tbl As TableDef
   Set db = CurrentDb
   Set tbl = db.TableDefs(strTblName)
   If Err.Number = 3265 Then   ' Item not found.
      DoesTblExist = False
      Exit Function
   End If
   DoesTblExist = True
End Function
 
Function CreateODBCLinkedTables() As Boolean
   On Error GoTo CreateODBCLinkedTables_Err
   Dim strTblName As String, strConn As String
   Dim db As Database, rs As Recordset, tbl As TableDef
   ' ---------------------------------------------
   ' Register ODBC database(s)
   ' ---------------------------------------------
   Set db = CurrentDb
   Set rs = db.OpenRecordset("tblODBCDataSources")
   With rs
      While Not .EOF
         DBEngine.RegisterDatabase rs("DSN"), _
                  rs("Driver"), _
                  True, _
                  "Description=VSS - " & rs("DataBase") & _
                  Chr(13) & "Server=" & rs("Server") & _
                  Chr(13) & "Database=" & rs("DataBase")
         ' ---------------------------------------------
         ' Link table
         ' ---------------------------------------------
         strTblName = rs("LocalTableName")
         strConn = "ODBC;"
         strConn = strConn & "DSN=" & rs("DSN") & ";"
         strConn = strConn & "DATABASE=" & rs("DataBase") & ";"
         strConn = strConn & "USER=" & rs("UID") & ";"
         strConn = strConn & "PASSWORD=" & rs("PWD") & ";"
         strConn = strConn & "OPTION=" & rs("Option") & ";"
         strConn = strConn & "TABLE=" & rs("ODBCTableName")
         If (DoesTblExist(strTblName) = False) Then
            Set tbl = db.CreateTableDef(strTblName, _
                          dbAttachSavePWD, rs("ODBCTableName"), _
                          strConn)
            db.TableDefs.Append tbl
         Else
            Set tbl = db.TableDefs(strTblName)
            tbl.Connect = strConn
            tbl.RefreshLink
         End If
 
          rs.MoveNext
      Wend
   End With
   CreateODBCLinkedTables = True
   MsgBox "Refreshed ODBC Data Sources", vbInformation
CreateODBCLinkedTables_End:
   Exit Function
CreateODBCLinkedTables_Err:
   MsgBox Err.Description, vbCritical, "MyApp"
   Resume CreateODBCLinkedTables_End
End Function
'**** End Code ***

Now just call CreateODBCLinkedTables() in the database's AutoExec macro (RunCode). You may want to rem out the message dialog "Refreshed ODBC Data Sources" in the code above if you do not want your users bothered by it each time it loads. Note that if you check the User DSN it will seem that the OPTION value didn't pass. However, if you open the linked table in design view and then look at it's properties you will see OPTION there.

4) Using a DSNless Connection

Read the MyODBC FAQ, Section 3.6 "Can I connect without a predefined DSN"

See http://members.rogers.com/douglas.j.steele/DSNLessLinks.html I never could get this to work in Access 97. If anyone knows how, please let me know.