Difference between revisions of "Odbc"

From ArchWiki
Jump to: navigation, search
(Verify Port 3360 is Listening)
(Verify Port 3360 is Listening)
Line 86: Line 86:
on the server
on the server
  netstat -ant
  netstat -ant
If port 3306 is open, you'll see the following
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address          Foreign Address        State
tcp        0      0*              LISTEN
==Permissions on MySQL for Remote Connection==
==Permissions on MySQL for Remote Connection==

Revision as of 23:37, 9 July 2006

MySQL, ODBC with OpenOffice Setup on LocalHost

This document shows how to set up ODBC in Arch, first to access you database on your localhost and then extends the steps to configure MySQL to allow remote access through ODBC.

Packages To Install

Start by installing the unixodbc and myodbc

# pacman -Sy unixodbc
# pacman -Sy myodbc

Configure Your ini Files

Configure your "odbc.ini", ".odbc.ini", and "odbcinst.ini" file. Startinig with odbcinst.ini, Su to root and set up your /etc/odbcinst.ini file as follows

Description     = ODBC Driver for MySQL
Driver          = /usr/lib/libmyodbc.so
Setup           = /usr/lib/libodbcmyS.so
FileUsage       = 1

Next set up your "odbc.ini" and ".odbc.ini" file. It should be pointed out here that the ".odbc.ini" file is just like the "odbc.ini" file except that it is a file that gets saved in the user's home dir (i.e a hidden file).

Description     = MySQL database test
Driver          = MySQL
Server          = localhost
atabase         = test
Port            = 3306
Socket          = /tmp/mysql.sock
Option          =
Stmt            =

Create A Symbolic Link

Next we need to create a symlink for libmyodbc.so. To do this we need to go to /usr/lib/myodbc3-3.51.12.so or whatever the version of the module is and set up a symlink to libmyodbc.so

ln -s /usr/lib/libmyodbc3-3.51.12.so ./libmyodbc.so

Renamed Modules

OK, for this step I cheap-skated it a little and simply copy these files to agree with version of ODBC that Arch was looking for.

cp libssl.so.0.9.8 libssl.so.0.9.7
cp libcrypto.so.0.9.8 libcrypto.so.0.9.7

Test Datbase

On my machine I run phpmyadmin so I already had a test database to use. If you don't have a test database then create one in MySQL.

Testing the ODBC

To test the ODBC connection

isql MySQL-test

If the connection is established, you will see

| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |

If you have a problem connecting then check the error message by running

isql MySQL-test -v

A Couple Useful Websites


This website got me going on ODBC with MySQL but left out some things that were necessary for me to get isql up and running. However this might be a good reference for the OpenOffice part.


To work around error messages this URL proved helpful so here it is as well.

MySQL, ODBC with OpenOffice Setup on the Remote Server

Edit my.cnf

Edit /etc/my.cnf so that port 3306 will be running to do this comment out skip-networking and also add bind-address = your IP

bind-address =

Edit hosts.allow

Next add a line in "hosts.allow"


It is recommended to do something like mysql: or whatever your client machine would be.

Verify Port 3360 is Listening

restart the mysql server with the command "/etc/rc.d/mysqld restart" and then check to see that port 3306 is listening on the server

netstat -ant

If port 3306 is open, you'll see the following

Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 *               LISTEN

Permissions on MySQL for Remote Connection

Next it is neccesary to give permissions on the server so that MySQL will allow a remote connection.

mysql -u "username" -p
mysql> GRANT ALL ON foo.* TO username@'' IDENTIFIED BY 'PASSWORD';

3) grant permission to individual IP in mysql http://www.cyberciti.biz/nixcraft/vivek/blogger/2006/03/how-do-i-enable-remote-access-to-mysql.php Grant access to new database If you want to add new database called foo for user bar and remote IP then you need to type following commands at mysql> prompt:

mysql> CREATE DATABASE foo; mysql> GRANT ALL ON foo.* TO bar@'' IDENTIFIED BY 'PASSWORD'; Grant access to existing database Let us assume that you are always making connection from remote IP called for database called webdb for user webadmin then you need to grant access to this IP address. At mysql> prompt type following command for existing database:

mysql> update db set Host='' where Db='webdb'; mysql> update user set Host='' where user='webadmin';

From remote system type command:

$ mysql -u webadmin –h –p You can also use telnet to connect to port 3306 for testing purpose:

$ telnet 3306

____________________________________________________ [MySQL-test] Description = MySQL database test Driver = MySQL Server = Trace = Off TraceFile = stderr Database =foo Port = 3306 USER = adowns Password = boston Socket = /tmp/mysql.sock Option = Stmt =