☰

在 Debian 上使用 unixODBC 通过本地主机连接到 MariaDB (mysql) 和 MS Access mdb 数据库？

tag-icon • tag-icon debian mariadb odbc
在 Debian 上使用 unixODBC 通过本地主机连接到 MariaDB (mysql) 和 MS Access mdb 数据库？

我是一个相对的 Linux 新手，只是想将我的计算从 Windows 10 迁移到 Debian。我一直在努力设置对我的 MS Access 数据库的共享双启动访问。我发现让 unixODBC 与 MariaDB（或 MySQL）一起使用非常困难，因此将与您分享下面的工作原理，因为我在这里回答了我自己的问题。

答案1

以下是如何使用 unixODBC 管理器使用 ODBC 连接到 Debian 8.6 (Jessie-stable) 上的 MariaDB 和 .mdb 数据库。

首先加载一些东西：

  • 加载包：mariadb-server-10.1（适用于 MySQL 5.6）
  • 加载包：unixodbc 2.3.1.3（unixODBC 2.3.0-4）

  • 从 MariaDB.com 下载的库：mariadb-connector-odbc-2.0.12-ga-linux-x86_64

    • 您必须直接从 MariaDB.com 获取此信息，因为 Debian Jessie 还没有相应的软件包。

    • 然后：将解压后的 libmaodbc.so 文件移动到 /usr/lib/x86_64-linux-gnu/libmaodbc.so

  • 可选加载包 unixodbc-bin 2.3.0-4（用于 ODBC 管理和浏览的图形工具）

（注意：我没有尝试让 iODBC 管理器工作，但看起来很相似。）

unixODBC 有两个安装文件/etc/odbcinst.ini和/etc/odbc.ini。它们可以直接编辑，也可以使用 GUI 工具进行设置。如果驱动程序参数不正确，GUI 工具将不会设置 DNS。您会收到类似参数错误消息的信息。

/etc/odbcinst.ini 设置 ODBC 驱动程序信息（ODBC 驱动程序库文件），然后 /etc/odbc.ini 引用此驱动程序信息来设置 DSN 或数据服务器名称（您将访问的数据库） 。

另外，/etc/odbcinst.ini 中还需要引用两个驱动程序，即主 odbc 驱动程序（或 Driver=.. ）和“Setup”驱动程序（或 Setup=..）。看来安装驱动程序是 GUI 编辑器的助手，可以设置默认参数（并可能检查它们）。但是 maria ODBC 发行版只为您提供了这些驱动程序中的第一个。我发现使用 mysql 安装驱动程序适用于第二个驱动程序。请注意，驱动程序中有一个“S”，例如 /usr/lib/x86_64-linux-gnu/odbc/libodbcmyS.so。当您选择一个软件包，然后单击“属性”和“已安装的文件”选项卡时，您会发现 Synaptic 中列出了可用的驱动程序。

这是我的 /etc/odbcinst.ini 文件，其中包含注释。 （下面是我的 /etc/odbc.ini 文件。）

请注意，驱动程序似乎需要完整的路径名。我还包含了 mdbtools 的设置参数（使用 ODBC 访问 MS Access *.mdb 文件）。还请注意，密码和 mdb 数据库路径名/文件名似乎不需要用引号引起来。它们可以很好地处理空格和其他特殊字符。（也许除了“=”之外）？

我还添加了注释掉的选项，这些选项神奇地出现在下面的 GUI 编辑器中，以供将来参考。

[mariadb]
Description=Maria DB
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmaodbc.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libodbcmyS.so

#The rest of these magically appear in the GUI driver (advanced) setup utility:
#Driver64=
#Setup64=
UsageCount=1
#CPTimeout=
#CPTImeToLive=
#DisableGetFunctions=
#DontDLCLose=
#ExFetchMapping=
#Threading=
#FakeUnicode=
#IconvEncoding=
#Trace=
#TraceFile=


#=================
[mdbtools]
Description=MDBTools Driver
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmdbodbc.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libmdbodbc.so

#The rest of these magicaly appear in the GUI driver (Advanced) setup utility:
#Driver64=
#Setup64=       Get error if include this param for mdbtools
#               (even though it appears in the GUI list as null)
UsageCount=1
#CPTimeout=
#CPTImeToLive=
#DisableGetFunctions=
#DontDLCLose=
#ExFetchMapping=
#Threading=
#FakeUnicode=
#IconvEncoding=
#Trace=
#TraceFile=
#TraceLibrary=

/etc/odbc.ini（请注意，下面需要更改套接字以指向 MariaDB 在 Debian 中公开的默认套接字）：

[mariadb-bb]
#Description    - defaults to $Driver
Description=Bigbase

#Driver: Required. The name of the MariaDB ODBC Driver. Must be always {MariaDB ODBC 1.0 Driver}
#       the 1.0 comment above is possibly outdated
Driver=mariadb

#Server: name or IP of the MariaDB database server. Aliases: Servername - defaults to null
Server=localhost

#Database: default database. Aliases: DB - defaults to 'test'
Database=somedatabase

#Port: TCP/IP Port of the database server - defaults to null
Port=3306

#When using Server=localhost we will be connecting thru the socket exposed by mariadb.
#You can find this socket file location&name in /etc/mysql/my.ini  - defaults to null if missing
Socket=/var/run/mysqld/mysqld.sock

#Option: For MySQL Connector/ODBC compatibility. Aliases: Options - defaults to null
#(not sure what this is used for?)
#Option=

#Stmt - defaults to null  (not sure what this is used for?)
#Stmt=

#---Optional----------------:
#DSN: Name of the DSN  (not sure what this is used for?)
#DSN=

#User: user name for database authentication. Aliases: UID,
User=root

#Password: password for database authentication. Aliases: PWD
Password=bigdog


#=========
[PW]
Description=mdbtools
#Driver needs to point to a driver in /etc/odbcinst.ini
Driver=mdbtools
Database=/home/user/Shared/Documents/test.mdb

然后，您可以使用以下两个工具测试 ODBC：

/usr/bin/isql   - Utility to submit SQL queries to a data source.
/usr/bin/iusql  - Unicode version of isql.

注意：您可以将 sql 命令作为标准输入传递到这些命令中，例如，echo "select * from tableA" | isql或者它会在运行时提示输入 sql 行sql>。看man isql。如果您提供 isql 选项（例如密码），它将覆盖 /etc/odbc.ini 文件中的密码。

希望这对其他人有帮助。

最后，这里是一些相关包中的文件的注释：

* unixodbc:

     unixodbc-bin       2.3.0-4     - Graphical tools for ODBC management and browsing
    /usr/bin/ODBCManageDataSourcesQ4    - 1&2 ODBC Administrator
    /usr/bin/ODBCCreateDataSourceQ4     - 2 create Data Source Name wizard

    These are mentioned in the README file but are not in the package:
      /usr/bin/odbcinstQ4               - a Qt based installer library/plugin (don't have)
      /usr/bin/ODBCTestQ4               - an interactive test program (don't have)
      /usr/bin/ODBCDataManagerQ4        - Data Manager (not done)

    /usr/share/doc/unixodbc-bin/README

    /usr/share/menu/unixodbc-bin        - some sort of menu data file

    Notes: Only works if both main driver and setup drivers exist.


   unixodbc     2.3.1-3         - Basic ODBC tools
    /usr/bin/isql                       - Utility to submit SQL queries to a data source.
    /usr/bin/iusql                          - Unicode version of isql.
        notes: Can either pipe standard input into them, e.g. echo "select * from m" | isql ...
        or it will prompt for the sql> when run.

    /usr/lib/odbc                       - the lib

    /usr/share/doc/unixodbc/README

    /usr/share/man/man1/isql.1.gz
    /usr/share/man/man1/iusql.1.gz


     libodbc1           2.3.1-3 ODBC library for Unix
    /usr/lib/x86_64-linux-gnu/libodbc.so.1      -> libodbc.so.2
    /usr/lib/x86_64-linux-gnu/libodbc.so.2      -> libodbc.so.2.0.0
    /usr/lib/x86_64-linux-gnu/libodbc.so.2.0.0      - lib odbc

    /usr/lib/x86_64-linux-gnu/libodbccr.so.1        -> libodbccr.so.2
    /usr/lib/x86_64-linux-gnu/libodbccr.so.2        -> libodbccr.so.2.0.0
    /usr/lib/x86_64-linux-gnu/libodbccr.so.2.0.0        - lib odbc cr  ?? cr, but I think like .rc (setup?)

    /usr/lib/x86_64-linux-gnu/odbc/libnn.so         - lib nn

    /usr/share/lintian/overrides/libodbc1               - ?



-----------------------------------------------------------------------------------------
* myodbc:
   libmyodbc        5.1.10-3    the MySQL ODBC driver
    /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so         - the mysql driver

    /usr/share/libmyodbc/odbcinst.ini                   - table of drivers

    /usr/share/doc/libmyodbc/examples/odbc.ini          - Example table of data sources
    /usr/share/doc/libmyodbc/README.Debian              - note about not building a -dev package


-----------------------------------------------------------------------------------------
* odbcinst:
   libodbcinstq4-1  2.3.0-4 Qt-based ODBC configuration library
    /usr/lib/x86_64-linux-gnu/libodbcinstQ4.so.1        -> libodbcinstQ4.so.1.1.1
    /usr/lib/x86_64-linux-gnu/libodbcinstQ4.so.1.1.1

   odbcinst     2.3.1-3 Helper program for accessing odbc ini files
    /etc/odbc.ini       - Setup table for list of DSN's.  Each one points to a driver in /etc/odbcinst.ini
        notes: don't need to quote special characters in file names like spaces or parens.

    /usr/bin/odbcinst   - - command line tool for batch ODBC configuration
        run /usr/bin/odbinst or 'man odbcinst' for usage, e.g. syntax and options

    /usr/share/man/man1/odbcinst.1.gz

    odbcinst1debian2    2.3.1-3 Support library  for accessing odbc ini files
    /etc/ODBCDataSources            - etc

    /usr/lib/x86_64-linux-gnu/libodbcinst.so.1      -> libodbcinst.so.2
    /usr/lib/x86_64-linux-gnu/libodbcinst.so.2      -> libodbcinst.so.2.0.0
    /usr/lib/x86_64-linux-gnu/libodbcinst.so.2.0.0      - inst


    /usr/lib/x86_64-linux-gnu/odbc/libesoobS.so     - setup driver
    /usr/lib/x86_64-linux-gnu/odbc/libmimerS.so     - setup driver

    /usr/lib/x86_64-linux-gnu/odbc/libodbcdrvcfg1S.so   - setup driver - drvcfg1
    /usr/lib/x86_64-linux-gnu/odbc/libodbcdrvcfg2S.so   - setup driver - drvcfg2
    /usr/lib/x86_64-linux-gnu/odbc/libodbcminiS.so  - setup driver - mini
    /usr/lib/x86_64-linux-gnu/odbc/libodbcmyS.so        - setup driver - my (mysql)
    /usr/lib/x86_64-linux-gnu/odbc/libodbcnnS.so        - setup driver - nn
    /usr/lib/x86_64-linux-gnu/odbc/libodbcpsqlS.so  - setup driver - psql
    /usr/lib/x86_64-linux-gnu/odbc/libodbctxtS.so       - setup driver - txt

    /usr/lib/x86_64-linux-gnu/odbc/liboplodbcS.so       - setup driver - opl odbc
    /usr/lib/x86_64-linux-gnu/odbc/liboraodbcS.so       - setup driver - ora odbc

j   /usr/lib/x86_64-linux-gnu/odbc/libsapdbS.so     - setup driver - sap db

    /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so           - setup driver - tds


    /usr/share/lintian/overrides/odbcinst1debian2       - inst1 debian2



-----------------------------------------------------------------------------------------
* MDB:
   odbc-mdbtools        0.7.1-2 MDB tools ODBC driver
    /usr/lib/x86_64-linux-gnu/odbc/libmdbodbc.so        - driver
    /usr/lib/x86_64-linux-gnu/odbc/libmdbodbcW.so   - ?


   mdbtools         0.7.1-2 JET / MS Access database (MDB) tools
    /etc/bash_completion.d/mdbtools - bash command completion extension

    /usr/bin/mdb-array              - Export data in an MDB database table to a C array
    /usr/bin/mdb-export             - Export data in an MDB database table to CSV format
    /usr/bin/mdb-header         - dump  the  names and types of the tables and columns
    /usr/bin/mdb-hexdump            - Hexdump utility from MDB Tools
    /usr/bin/mdb-parsecsv           - Convert CSV table dump into C file
    /usr/bin/mdb-prop               - Get properties list from MDB database
    /usr/bin/mdb-schema         - produces  DDL  (data definition language) output
    /usr/bin/mdb-sql                - allows querying MDB database using a limited SQL subset
    /usr/bin/mdb-tables             - Get listing of tables in an MDB database
    /usr/bin/mdb-ver                - Return the format of a given MDB database.

    /usr/share/man/man1/mdb-array.1.gz
    /usr/share/man/man1/mdb-export.1.gz
    /usr/share/man/man1/mdb-header.1.gz
    /usr/share/man/man1/mdb-hexdump.1.gz
    /usr/share/man/man1/mdb-parsecsv.1.gz
    /usr/share/man/man1/mdb-prop.1.gz
    /usr/share/man/man1/mdb-schema.1.gz
    /usr/share/man/man1/mdb-sql.1.gz
    /usr/share/man/man1/mdb-tables.1.gz
    /usr/share/man/man1/mdb-ver.1.gz


    mdbtools-doc
    /usr/share/doc-base/mdbtools-faq
    /usr/share/doc-base/mdbtools-install

    /usr/share/doc/mdbtools-doc/HACKING.gz
    /usr/share/doc/mdbtools-doc/NEWS.gz
    /usr/share/doc/mdbtools-doc/README.gz
    /usr/share/doc/mdbtools-doc/TODO
    /usr/share/doc/mdbtools-doc/faq.html

    /usr/share/doc/mdbtools-doc/html/index.html - A Guide to Installing and Configuring MDB Tools

    /usr/share/doc/mdbtools-doc/html/a304.htm
    /usr/share/doc/mdbtools-doc/html/book1.htm
    /usr/share/doc/mdbtools-doc/html/c29.htm
    /usr/share/doc/mdbtools-doc/html/c42.htm
    /usr/share/doc/mdbtools-doc/html/c98.htm
    /usr/share/doc/mdbtools-doc/html/c180.htm
    /usr/share/doc/mdbtools-doc/html/c202.htm
    /usr/share/doc/mdbtools-doc/html/c260.htm

    /usr/share/doc/mdbtools-doc/html/f19.htm

    /usr/share/doc/mdbtools-doc/html/x53.htm
    /usr/share/doc/mdbtools-doc/html/x86.htm
    /usr/share/doc/mdbtools-doc/html/x90.htm
    /usr/share/doc/mdbtools-doc/html/x94.htm

    /usr/share/doc/mdbtools-doc/html/x196.htm
    /usr/share/doc/mdbtools-doc/html/x199.htm
    /usr/share/doc/mdbtools-doc/html/x231.htm
    /usr/share/doc/mdbtools-doc/html/x244.htm
    /usr/share/doc/mdbtools-doc/html/x251.htm
    /usr/share/doc/mdbtools-doc/html/x276.htm
    /usr/share/doc/mdbtools-doc/html/x289.htm
    /usr/share/doc/mdbtools-doc/html/x314.htm
    /usr/share/doc/mdbtools-doc/html/x324.htm
    /usr/share/doc/mdbtools-doc/html/x328.htm
    /usr/share/doc/mdbtools-doc/html/x334.htm
    /usr/share/doc/mdbtools-doc/html/x370.htm
    /usr/share/doc/mdbtools-doc/html/x375.htm
    /usr/share/doc/mdbtools-doc/html/x379.htm
    /usr/share/doc/mdbtools-doc/html/x383.htm
    /usr/share/doc/mdbtools-doc/html/x386.htm
    /usr/share/doc/mdbtools-doc/html/x389.htm
    /usr/share/doc/mdbtools-doc/html/x394.htm

    /usr/share/doc/mdbtools-doc/install.sgml.gz


  mdbtools-gmdb         0.7.1-2 - JET / MS Access database (MDB) file viewer
    /etc/bash_completion.d/mdbtools-gmdb    - bash command completion extension

    /usr/bin/gmdb2                          - THE application (graphical interface)

    /usr/share/applications/mdbtools-gmdb.desktop   - setup?

    /usr/share/gmdb/glade/gmdb-debug.glade      - graphical
    /usr/share/gmdb/glade/gmdb-export.glade
    /usr/share/gmdb/glade/gmdb-prefs.glade
    /usr/share/gmdb/glade/gmdb-props.glade
    /usr/share/gmdb/glade/gmdb-schema.glade
    /usr/share/gmdb/glade/gmdb-sql.glade
    /usr/share/gmdb/glade/gmdb-tabledef.glade
    /usr/share/gmdb/glade/gmdb.glade

    /usr/share/gmdb/glade/code.xpm      - images
    /usr/share/gmdb/glade/debug.xpm
    /usr/share/gmdb/glade/form_big.xpm
    /usr/share/gmdb/glade/forms.xpm
    /usr/share/gmdb/glade/logo.xpm
    /usr/share/gmdb/glade/macro_big.xpm
    /usr/share/gmdb/glade/macros.xpm
    /usr/share/gmdb/glade/module_big.xpm
    /usr/share/gmdb/glade/pk.xpm
    /usr/share/gmdb/glade/query.xpm
    /usr/share/gmdb/glade/query_big.xpm
    /usr/share/gmdb/glade/report_big.xpm
    /usr/share/gmdb/glade/reports.xpm
    /usr/share/gmdb/glade/table.xpm
    /usr/share/gmdb/glade/table_big.xpm
    /usr/share/pixmaps/gmdb2.xpm

    /usr/share/gmdb/glade/stock_export-16.png
    /usr/share/gmdb/glade/stock_export.png
    /usr/share/gnome/help/gmdb/C/figures/gmdb2_sql_window.png
    /usr/share/gnome/help/gmdb/C/figures/gmdb2_window.png
    /usr/share/gnome/help/gmdb/C/gmdb.xml
    /usr/share/gnome/help/gmdb/C/legal.xml

    /usr/share/man/man1/gmdb2.1.gz  - GNOME UI for MDB Tools

    /usr/share/menu/mdbtools-gmdb



-----------------------------------------------------------------------------------------
* mariadb-connect-engine=10.1
    /etc/mysql/conf.d/connect.cnf

    /usr/lib/mysql/plugin/ha_connect.so


* mariadb-connector-odbc-2.0.12-ga-linux-x86_64
    /lib/libmaodbc.so   -- moved this to: /usr/lib/x86_64-linux-gnu/libmaodbc.so
    /share/doc/mariadb_connector_odbc/README



-----------------------------------------------------------------------------------------
* iodbc:
  libiodbc2     3.52.9-2    iODBC Driver Manager
  iodbc         3.52.9-2    GTK+ config frontend for the iODBC Driver Manager
    /usr/bin/iodbcadm-gtk   - graphical ODBC administration program using the GTK+ widget set.
    /usr/bin/iodbctest      - iODBC Demonstration programs

    /usr/lib/x86_64-linux-gnu/libdrvproxy.so.2      - a link?   lib drv proxy so
    /usr/lib/x86_64-linux-gnu/libdrvproxy.so.2.1.20

    /usr/lib/x86_64-linux-gnu/libiodbcadm.so.2      - a link?   lib iodb adm
    /usr/lib/x86_64-linux-gnu/libiodbcadm.so.2.1.20

    /usr/share/doc/iodbc/README.Debian  - not much help

    /usr/share/lintian/overrides/iodbc

    /usr/share/man/man1/iodbcadm-gtk.1.gz       - graphical ODBC administration program
    /usr/share/man/man1/iodbctest.1.gz      - iODBC Demonstration programsq

相关内容