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:
- Install mySQL
- Edit /etc/my.cnf
- Start mySQL
- Set the root user's password for mySQL
- Set up the User(s)
- Set up the Database(s)
- Get iODBC Driver Manager
- Get MyODBC
- Configure iODBC on *nix
- Test ODBC on *nix
- Troubleshooting on *nix
- Get MyODBC and Install
- Testing the connection with telnet
- Configuring a User DSN
- 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
I used the RPM.
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
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.
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.
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
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.
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.
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
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
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
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.
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.
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.
