Difference between revisions of "Odbc"

From ArchWiki
Jump to: navigation, search
(Edit hosts.allow)
m (Verify Port 3306 is Listening: use iproute2 command ss.)
(48 intermediate revisions by 13 users not shown)
Line 1: Line 1:
=MySQL, ODBC with OpenOffice Setup on LocalHost=
+
[[Category:Networking]]
 +
[[Category:Database management systems]]
 +
{{moveto|Open Database Connectivity}}
  
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.  
+
Open Database Connectivity, commonly ODBC, is an open specification for providing application developers with a predictable API with which to access Data Sources
==Packages To Install==
+
 
 +
This document shows how to set up [http://www.unixodbc.org/ unixODBC] in Arch, first to access your database on your localhost and then extends the steps to configure MySQL to allow remote access through ODBC.  
 +
==MySQL, ODBC with OpenOffice Setup on LocalHost==
 +
===Packages To Install===
 
Start by installing the unixodbc and myodbc
 
Start by installing the unixodbc and myodbc
  # pacman -Sy unixodbc
+
  # pacman -S unixodbc myodbc
# pacman -Sy myodbc
+
  
==Configure Your ini Files==
+
===Configure the ini Files===
Configure your "odbc.ini", ".odbc.ini", and "odbcinst.ini" file.
+
Configure the "odbc.ini" and "odbcinst.ini" file.
Startinig with odbcinst.ini, Su to root and set up your /etc/odbcinst.ini file as follows
+
Starting with odbcinst.ini, which lists all installed drivers. Su to root and set up your /etc/odbcinst.ini file as follows
 
  [MySQL]
 
  [MySQL]
 
  Description    = ODBC Driver for MySQL
 
  Description    = ODBC Driver for MySQL
Line 16: Line 20:
 
  FileUsage      = 1
 
  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).
+
Next set up your data sources in "/etc/odbc.ini" (system wide) or "~/.odbc" (current user). If a data source is defined in both of these files, the one in your home directory take precedence.  
 +
 
 
  [MySQL-test]
 
  [MySQL-test]
 
  Description    = MySQL database test
 
  Description    = MySQL database test
 
  Driver          = MySQL
 
  Driver          = MySQL
 
  Server          = localhost
 
  Server          = localhost
  atabase        = test
+
  Database        = test
 
  Port            = 3306
 
  Port            = 3306
  Socket          = /tmp/mysql.sock
+
  Socket          = /var/run/mysqld/mysqld.sock
 
  Option          =
 
  Option          =
 
  Stmt            =
 
  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==
+
===Create A Symbolic Link===
On my machine I run phpmyadmin so I already had a test database to use. If you don't have a test database then
+
Next we need to create a symlink for libmyodbc.so. To do this we need to go to "/usr/lib/" and set up a symlink to libmyodbc.so
create one in MySQL.
+
  cd /usr/lib/
 +
  ln -s ./libmyodbc5w.so ./libmyodbc.so
  
==Testing the ODBC==
+
===Create A Test Database===
 +
Create a new database "test". You can use one of the MySQL front-ends {{AUR|mysql-gui-tools}} {{AUR|mysql-workbench}} or the commandline.
 +
mysqladmin -h localhost -u root -p create test
 +
 
 +
===Testing the ODBC===
 
To test the ODBC connection
 
To test the ODBC connection
 
  isql MySQL-test
 
  isql MySQL-test
Line 54: Line 55:
 
  +---------------------------------------+
 
  +---------------------------------------+
 
  SQL>
 
  SQL>
 
  
 
If you have a problem connecting then check the error message by running
 
If you have a problem connecting then check the error message by running
 
  isql MySQL-test -v
 
  isql MySQL-test -v
  
==A Couple Useful Websites==
+
===A Couple Useful Websites===
 
http://www.unixodbc.org/doc/OOoMySQL.pdf
 
http://www.unixodbc.org/doc/OOoMySQL.pdf
  
Line 69: Line 69:
 
To work around error messages this URL proved helpful so here it is as well.
 
To work around error messages this URL proved helpful so here it is as well.
  
=MySQL, ODBC with OpenOffice Setup on the Remote Server=
+
==MySQL, ODBC with OpenOffice Setup on the Remote Server==
==Edit my.cnf==
+
===Edit odbc.ini===
Edit /etc/my.cnf so that port 3306 will be running to do this comment out skip-networking and also add
+
From the Client side it is necessary to edit the odbc.ini and the .odbc.ini files slightly. Here is the odbc.ini that I used to login.
bind-address = your IP
+
[MySQL-test]
  #skip-networking
+
Description    = MySQL database test
  bind-address =192.168.0.100
+
  Driver          = MySQL
 
+
  Server          = 192.168.0.102
==Edit hosts.allow==
+
Trace          = Off
Next add a line in "hosts.allow"
+
TraceFile      = stderr
  mysqld:ALL
+
Database        = test
It is recommended to do something like mysql:192.168.0.101 or whatever your client
+
Port            = 3306
machine would be.
+
USER            = username
 
+
Password        = secretpassword
==Verify Port 3360 is Listening==
+
  Socket          = /var/run/mysqld/mysqld.sock
restart with the command "/etc/rc.d/mysqld restart" and then check to see that port 3306 is listening
+
  Option          =
on the server
+
  Stmt            =
  netstat -ant
+
==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> CREATE DATABASE foo;
+
mysql> GRANT ALL ON foo.* TO username@'192.168.0.101' IDENTIFIED BY 'PASSWORD';
+
  
 +
===Edit my.cnf===
 +
Edit /etc/mysql/my.cnf so that port 3306 will be running to do this comment out skip-networking. You can add
 +
{{ic|bind-address = IP}} with you incoming network card's IP.
 +
#skip-networking
 +
bind-address = 192.168.1.100
  
 +
===Verify Port 3306 is Listening===
 +
restart the MySQL server
 +
# systemctl restart mysqld
 +
Then check that port 3306 is listening on the server
 +
$ ss -ant
 +
If port 3306 is open, you'll see the following:
 +
State      Recv-Q Send-Q                                    Local Address:Port                                      Peer Address:Port
 +
LISTEN      0            50                                                    *:3306                                                  *:*
  
 +
===Permissions on MySQL for Remote Connection===
 +
Next it is necessary to give permissions on the server so that MySQL will allow a remote connection.
 +
Type
 +
mysql -h localhost -u username -p
 +
and log into your MySQL database.
 +
mysql> CREATE DATABASE test;
 +
mysql> GRANT ALL ON test.* TO username@'192.168.1.100' IDENTIFIED BY 'PASSWORD';
 +
An extensive tutorial on this subject can be found [http://www.cyberciti.biz/nixcraft/vivek/blogger/2006/03/how-do-i-enable-remote-access-to-mysql.php here].
  
 +
===Testing the ODBC===
 +
run the
 +
isql MySQL-test
 +
command and you should be able to log into your server.
  
 +
===Miscellaneous===
  
 +
From the client you can run the following command to verify that port 3306 is open
 +
telnet 192.168.0.100 3306
  
3) grant permission to individual IP in mysql
+
Good luck and happy computing now that you can get OpenOffice to connect to you databases.
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 202.54.10.20 then you need to type following commands at mysql> prompt:
+
 
+
mysql> CREATE DATABASE foo;
+
mysql> GRANT ALL ON foo.* TO bar@'202.54.10.20' IDENTIFIED BY 'PASSWORD';
+
Grant access to existing database
+
Let us assume that you are always making connection from remote IP called 202.54.10.20 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='202.54.10.20' where Db='webdb';
+
mysql> update user set Host='202.54.10.20' where user='webadmin';
+
 
+
 
+
From remote system type command:
+
 
+
$ mysql -u webadmin –h 65.55.55.2 –p
+
You can also use telnet to connect to port 3306 for testing purpose:
+
 
+
$ telnet 65.55.55.2 3306
+
 
+
____________________________________________________
+
[MySQL-test]
+
Description    = MySQL database test
+
Driver          = MySQL
+
Server          = 192.168.0.2
+
Trace          = Off
+
TraceFile      = stderr
+
Database        =foo
+
Port            = 3306
+
USER            = adowns
+
Password        = boston
+
Socket          = /tmp/mysql.sock
+
Option          =
+
Stmt            =
+

Revision as of 12:35, 2 March 2013

Template:Moveto

Open Database Connectivity, commonly ODBC, is an open specification for providing application developers with a predictable API with which to access Data Sources

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

MySQL, ODBC with OpenOffice Setup on LocalHost

Packages To Install

Start by installing the unixodbc and myodbc

# pacman -S unixodbc myodbc

Configure the ini Files

Configure the "odbc.ini" and "odbcinst.ini" file. Starting with odbcinst.ini, which lists all installed drivers. Su to root and set up your /etc/odbcinst.ini file as follows

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

Next set up your data sources in "/etc/odbc.ini" (system wide) or "~/.odbc" (current user). If a data source is defined in both of these files, the one in your home directory take precedence.

[MySQL-test]
Description     = MySQL database test
Driver          = MySQL
Server          = localhost
Database        = test
Port            = 3306
Socket          = /var/run/mysqld/mysqld.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/" and set up a symlink to libmyodbc.so

 cd /usr/lib/
 ln -s ./libmyodbc5w.so ./libmyodbc.so

Create A Test Database

Create a new database "test". You can use one of the MySQL front-ends mysql-gui-toolsAUR mysql-workbenchAUR or the commandline.

mysqladmin -h localhost -u root -p create test

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                                  |
|                                       |
+---------------------------------------+
SQL>

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

isql MySQL-test -v

A Couple Useful Websites

http://www.unixodbc.org/doc/OOoMySQL.pdf

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.

http://mail.easysoft.com/pipermail/unixodbc-support/2004-August/000111.html

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 odbc.ini

From the Client side it is necessary to edit the odbc.ini and the .odbc.ini files slightly. Here is the odbc.ini that I used to login.

[MySQL-test]
Description     = MySQL database test
Driver          = MySQL
Server          = 192.168.0.102
Trace           = Off
TraceFile       = stderr
Database        = test
Port            = 3306
USER            = username
Password        = secretpassword
Socket          = /var/run/mysqld/mysqld.sock
Option          =
Stmt            =

Edit my.cnf

Edit /etc/mysql/my.cnf so that port 3306 will be running to do this comment out skip-networking. You can add bind-address = IP with you incoming network card's IP.

#skip-networking
bind-address = 192.168.1.100

Verify Port 3306 is Listening

restart the MySQL server

# systemctl restart mysqld

Then check that port 3306 is listening on the server

$ ss -ant

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

State       Recv-Q Send-Q                                     Local Address:Port                                       Peer Address:Port
LISTEN      0            50                                                     *:3306                                                  *:*

Permissions on MySQL for Remote Connection

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

mysql -h localhost -u username -p

and log into your MySQL database.

mysql> CREATE DATABASE test;
mysql> GRANT ALL ON test.* TO username@'192.168.1.100' IDENTIFIED BY 'PASSWORD';

An extensive tutorial on this subject can be found here.

Testing the ODBC

run the

isql MySQL-test

command and you should be able to log into your server.

Miscellaneous

From the client you can run the following command to verify that port 3306 is open

telnet 192.168.0.100 3306

Good luck and happy computing now that you can get OpenOffice to connect to you databases.