Sunday, 25 August 2013

Configuring ODBC to MySQL from Oracle

Sometimes people want to connect to MySQL from Oracle and copy table data between the databases.  You can do that with Oracle Hetrogenous Services via ODBC.  This post will show how to create an odbc connection to your MySQL database which is the first part of this.

For my example, I'm using unixODBC and its on the Oracle public yum repository
[root@localEL5 ~]$ yum install unixODBC
Loaded plugins: security
Setting up Install Process
Resolving Dependencies
> Running transaction check
> Processing Dependency: libboundparam.so.1 for package: unixODBC-devel
> Processing Dependency: libesoobS.so.1 for package: unixODBC-devel
> Processing Dependency: libgtrtst.so.1 for package: unixODBC-devel
> Processing Dependency: libmimerS.so.1 for package: unixODBC-devel
> Processing Dependency: libnn.so.1 for package: unixODBC-devel
> Processing Dependency: libodbc.so.1 for package: unixODBC
.....
> Running transaction check
> Package unixODBC-devel.i386 0:2.2.11-10.el5 set to be updated
> Package unixODBC-libs.i386 0:2.2.11-10.el5 set to be updated
> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package               Arch        Version              Repository         Size
================================================================================
Updating:
 unixODBC              i386        2.2.11-10.el5        el5_latest        290 k
Installing for dependencies:
 unixODBC-libs         i386        2.2.11-10.el5        el5_latest        551 k
Updating for dependencies:
 unixODBC-devel        i386        2.2.11-10.el5        el5_latest        738 k

Transaction Summary
================================================================================
Install       1 Package(s)
Upgrade       2 Package(s)

Total download size: 1.5 M
Is this ok [y/N]: y
Downloading Packages:
(1/3): unixODBC-2.2.11-10.el5.i386.rpm                   | 290 kB     00:02     
(2/3): unixODBC-libs-2.2.11-10.el5.i386.rpm              | 551 kB     00:04     
(3/3): unixODBC-devel-2.2.11-10.el5.i386.rpm             | 738 kB     00:17     
--------------------------------------------------------------------------------
Total                                            60 kB/s | 1.5 MB     00:26     
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing     : unixODBC-libs                                            1/5 
warning: /etc/odbcinst.ini created as /etc/odbcinst.ini.rpmnew
  Updating       : unixODBC                                                 2/5 
  Updating       : unixODBC-devel                                           3/5 
  Cleanup        : unixODBC                                                 4/5 
  Cleanup        : unixODBC-devel                                           5/5 

Dependency Installed:
  unixODBC-libs.i386 0:2.2.11-10.el5                                            

Updated:
  unixODBC.i386 0:2.2.11-10.el5                                                 

Dependency Updated:
  unixODBC-devel.i386 0:2.2.11-10.el5                                           

Complete!
[root@localEL5 ~]$ 


Now make sure odbc connector is installed for MySQL. Again, we're using our friend yum to provide it


[root@localEL5 ~]$  yum install mysql-connector-odbc
Loaded plugins: security
Setting up Install Process
Resolving Dependencies
There are unfinished transactions remaining. You might consider running yum-complete-transaction first to finish them.
The program yum-complete-transaction is found in the yum-utils package.
> Running transaction check
...
> Finished Dependency Resolution

Dependencies Resolved

=================================================================================================
 Package                      Arch         Version                      Repository          Size
=================================================================================================
Installing:
 mysql-connector-odbc         i386         3.51.26r1127-2.el5           el5_latest         159 k
Installing for dependencies:
 libtool-ltdl                 i386         1.5.22-7.el5_4               el5_latest          37 k

Transaction Summary
=================================================================================================
Install       2 Package(s)
Upgrade       0 Package(s)

Total download size: 196 k
Is this ok [y/N]: y
Downloading Packages:
(1/2): libtool-ltdl-1.5.22-7.el5_4.i386.rpm                               |  37 kB     00:04     
(2/2): mysql-connector-odbc-3.51.26r1127-2.el5.i386.rpm                   | 159 kB     00:01     
-------------------------------------------------------------------------------------------------
Total                                                             21 kB/s | 196 kB     00:09     
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing     : libtool-ltdl                                                              1/2 
  Installing     : mysql-connector-odbc                                                      2/2 

Installed:
  mysql-connector-odbc.i386 0:3.51.26r1127-2.el5                                                 

Dependency Installed:
  libtool-ltdl.i386 0:1.5.22-7.el5_4                                                             

Complete!
[root@localEL5 ~]$ 

Now lets  check driver locations and DSNs. Firstly we can check the installed drivers now in the file /etc/odbcinst.ini

# driver definitinions
#
#

# Included in the unixODBC package
[PostgreSQL]
Description     = ODBC for PostgreSQL
Driver          = /usr/lib/libodbcpsql.so
Setup           = /usr/lib/libodbcpsqlS.so
FileUsage       = 1


# Driver from the MyODBC package
# Setup from the unixODBC package
[MySQL]
Description     = ODBC for MySQL
Driver          = /usr/lib/libmyodbc.so
Setup           = /usr/lib/libodbcmyS.so
FileUsage       = 1

Then, we can specify a DSN to connect with in /etc/odbc.ini (Be careful here the option names are case sensitive.

[sakila-connector]
driver=MySQL
Database=sakila
Socket=/var/lib/mysql/mysql.sock
User=root
Password=oracle
Finally, we can now check our dsn defined above.  We'll use iSQL from the unixODBC package here.
[oracle@Unknown-08:00:27:c8:2a:1c lib]$ isql -v sakila-connector
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
Cool. When we get this we are connected via odbc to the DSN.  Now we can prove it by doing a show tables or something to prove its working.
NB: If you get an error at this stage asking for libraries, its likely you specified your Drivers incorrectly in the odbcinst.ini.
Now we have this working we can setup HS on the Oracle side.

Saturday, 24 August 2013

Configuring MySQL on EL5, Setting Passwords and Network Access

I find myself installing and running mysql of different versions in different places for different reasons all the time (well often enough to do it and not remember the little things that cost time when setting up)   Its with that in mind, I'm making notes for myself and you guys as well to help you along.

We use MySQL a lot with Oracle SQLDeveloper and many use SQLDeveloper to co-exist between MySQL and Oracle.

For most versions of Oracle Linux, we will install SQL Server from the Yum repository. If you dont have one set up you can configure one under /etc/yum.repos.d.  These notes for yum are a reference (blatant copy) from the Oracle Linux Admin guide
  1. As root, change directory to /etc/yum.repos.d.
    # cd /etc/yum.repos.d
  2. Use the wget utility to download the repository configuration file that is appropriate for your system.
    # wget http://public-yum.oracle.com/public-yum-release.repo
    For Oracle Linux 6, enter:
    # wget http://public-yum.oracle.com/public-yum-ol6.repo
    The /etc/yum.repos.d directory is updated with the repository configuration file, in this example, public-yum-ol6.repo.
  3. You can enable or disable repositories in the file by setting the value of the enabled directive to 1 or 0 as required.
Now we are ready to install MySQL. If you havent used yum before play with some of the options to list packages and switch repos as you need them.  Its a great tool saving us all lots of time with dependencies.

root@localEl5# yum install mysql-server
You can see if its installed by doing
root@localEl5> yum list mysql-server
Loaded plugins: security
el5_latest                      | 1.4 kB     00:00  
Installed Packages
mysql-server.i386        5.0.95-5.el5_9    installed
root@localEl5>
You can then start it with
root@localEL5> /etc/init.d/mysqld start
and check its running by
root@localEL5> /etc/init.d/mysqld status

mysqld (pid 31298) is running...
In general, you can start mysql on the server without a server password in order to set one up for yourself. My one caveat here, is that all this is for development folks, some one with a security hat on will complain (bitterly).  I'm going to show you how to clear down all permissions so you can connect from any machine.
root@localEL5> /etc/init.d/mysqld stop
root@localEL5> /etc/init.d/mysqld status
root@localEL5> mysqld_safe --skip-grant-tables &
mysql -uroot
Now we are logged into mysql as root with no passwords.  We can check what users are here and what permissions they have. Now, in this case, I have 
mysql> select user, host, password from user; 
 +-------+-------------+-------------------------------------------+
| user  | host         | password                                  |
+-------+--------------+-------------------------------------------+
| root  | localhost    | *2447D497B9A6A15F2776055CB2D1E9F86758182F | 
| root  | 192.168.1.201| *2447D497B9A6A15F2776055CB2D1E9F86758182F | 
| barry | localhost    | *2447D497B9A6A15F2776055CB2D1E9F86758182F | 
+-------+--------------+-------------------------------------------+

The first thing I want to do is to remove duplicate entries for my user
mysql> delete from user where user='root' and host ='192.168.1.201';
now we have
+-------+--------------+-------------------------------------------+
| user  | host         | password                                  |
+-------+--------------+-------------------------------------------+
| root  | localhost    | *2447D497B9A6A15F2776055CB2D1E9F86758182F | 
| barry | localhost    | *2447D497B9A6A15F2776055CB2D1E9F86758182F | 
+-------+--------------+-------------------------------------------+
Now, next I want to update the hosts to any host which is '%' in mysql

 mysql> update user set host='%';

which now gives me

+-------+------+-------------------------------------------+
| user  | host | password                                  |
+-------+------+-------------------------------------------+
| root  | %    | *2447D497B9A6A15F2776055CB2D1E9F86758182F | 
| barry | %    | *2447D497B9A6A15F2776055CB2D1E9F86758182F | 
+-------+------+-------------------------------------------+
2 rows in set (0.00 sec)


Now, if you want to change your passwords, make sure you do that now.  If you are on 5.1 and over secure_auth is set on and old passwords are off  by default. In my version 5.0, I need to set them to get new passwords and secure_auth which is default on all mysql clients now.  This is done in /etc/my.conf followed by a restart of mysql

old_passwords=0
secure-auth=1

mysql> update user set password=PASSWORD('oracle') where user='root';

lastly flush privileges and exit

mysql> flush privileges;

Lastly, I like my prompts to be informative so, You can also set this in your profile to setup your prompts.

export MYSQL_PS1="\u@\h [\d] > "

It'll give you a prompt like this when I log in with

root@localEl5> mysql -uroot -poracle -Dmysql

giving this prompt in mysql

root@localEL5 [mysql] >

Now, you are all set to connect from SQL Developer to the this instance.  We can also install the sample databases from http://dev.mysql.com/doc/index-other.html