使用 CONNECT 和 OQGRA_MySQL 安装 MySQL MariaDB 版本 10.0.10

玛丽亚数据库

位置

SkySQLSpain

我需要在最新的 MariaDB 10.0.10 上测试新的 SkySQL 服务器审核插件 1.1.7
有关该方便插件的更多信息，您可以在这里找到：http:// /www.skysql.com/downloads/mariadb-audit-plugin
花点时间阅读信息以及那个不错的博客：http://www.skysql.com/blogs/ralf-gebhardt/activating-auditing -mariadb-an...
然后我决定测试 CONNECT 引擎安装，因为我有一个朋友需要导入导出到 XML 的未知数据库数据。
我有兴趣将 Skype sqlite3 数据库导入 MySQL ... :)
有关 MariaDB 引擎的更多信息，您可以在这里找到：https://mariadb.com/kb/en/connect/

所以。
测试是在 CentOS 6.5 上完成的最小安装。
我将从头开始一步步进行。

1.为 CentOS 6.5 配置 MariaDB 存储库，最好的方法是使用 MariaDB 存储库配置工具：
https://downloads.mariadb.org/mariadb/repositories/
检查 CentOS 链接，然后选择版本，在我的例子中是 CentOS 6（64 位），然后是 MariaDB 版本：10.0
作为直接链接：
https://downloads.mariadb.org/mariadb/repositories/#mirror=nucleus&distr...

简而言之：MariaDB.repo 的内容应如下所示：

cat /etc/yum.repos.d/MariaDB.repo # MariaDB 10.0 CentOS repository list - created 2014-05-05 10:31 UTC# http://mariadb.org/mariadb/repositories/[mariadb]name = MariaDBbaseurl = http://yum.mariadb.org/10.0/centos6-amd64gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDBgpgcheck=1

1.安装 MariaDB 10.0.10：

root@centos-6.5-minimal:[Mon May 05 12:49:52][~]$ yum search MariadbLoaded plugins: fastestmirrorLoading mirror speeds from cached hostfile * base: sunsite.rediris.es * extras: sunsite.rediris.es * updates: sunsite.rediris.es================================= N/S Matched: Mariadb ==============================MariaDB-Galera-server.x86_64 : MariaDB: a very fast and robust SQL database serverMariaDB-cassandra-engine.x86_64 : MariaDB: a very fast and robust SQL database serverMariaDB-client.x86_64 : MariaDB: a very fast and robust SQL database serverMariaDB-common.x86_64 : MariaDB: a very fast and robust SQL database serverMariaDB-compat.x86_64 : MariaDB: a very fast and robust SQL database serverMariaDB-connect-engine.x86_64 : MariaDB: a very fast and robust SQL database serverMariaDB-devel.x86_64 : MariaDB: a very fast and robust SQL database serverMariaDB-oqgraph-engine.x86_64 : MariaDB: a very fast and robust SQL database serverMariaDB-server.x86_64 : MariaDB: a very fast and robust SQL database serverMariaDB-shared.x86_64 : MariaDB: a very fast and robust SQL database serverMariaDB-test.x86_64 : MariaDB: a very fast and robust SQL database serverName and summary matches only, use "search all" for everything.root@centos-6.5-minimal:[Mon May 05 12:51:17][~]$ yum install MariaDB-server MariaDB*engine MariaDB-client Loaded plugins: fastestmirrorLoading mirror speeds from cached hostfile * base: mirrors.atosworldline.com * extras: sunsite.rediris.es * updates: sunsite.rediris.esSetting up Install ProcessResolving Dependencies--> Running transaction check---> Package MariaDB-cassandra-engine.x86_64 0:10.0.10-1.el6 will be installed---> Package MariaDB-client.x86_64 0:10.0.10-1.el6 will be installed--> Processing Dependency: MariaDB-common for package: MariaDB-client-10.0.10-1.el6.x86_64---> Package MariaDB-connect-engine.x86_64 0:10.0.10-1.el6 will be installed--> Processing Dependency: libodbc.so.2()(64bit) for package: MariaDB-connect-engine-10.0.10-1.el6.x86_64---> Package MariaDB-oqgraph-engine.x86_64 0:10.0.10-1.el6 will be installed--> Processing Dependency: libJudy.so.1()(64bit) for package: MariaDB-oqgraph-engine-10.0.10-1.el6.x86_64---> Package MariaDB-server.x86_64 0:10.0.10-1.el6 will be installed--> Processing Dependency: perl(DBI) for package: MariaDB-server-10.0.10-1.el6.x86_64--> Running transaction check---> Package MariaDB-common.x86_64 0:10.0.10-1.el6 will be installed--> Processing Dependency: MariaDB-compat for package: MariaDB-common-10.0.10-1.el6.x86_64---> Package MariaDB-oqgraph-engine.x86_64 0:10.0.10-1.el6 will be installed--> Processing Dependency: libJudy.so.1()(64bit) for package: MariaDB-oqgraph-engine-10.0.10-1.el6.x86_64---> Package perl-DBI.x86_64 0:1.609-4.el6 will be installed---> Package unixODBC.x86_64 0:2.2.14-12.el6_3 will be installed--> Processing Dependency: libltdl.so.7()(64bit) for package: unixODBC-2.2.14-12.el6_3.x86_64--> Running transaction check---> Package MariaDB-compat.x86_64 0:10.0.10-1.el6 will be obsoleting---> Package MariaDB-oqgraph-engine.x86_64 0:10.0.10-1.el6 will be installed--> Processing Dependency: libJudy.so.1()(64bit) for package: MariaDB-oqgraph-engine-10.0.10-1.el6.x86_64---> Package libtool-ltdl.x86_64 0:2.2.6-15.5.el6 will be installed---> Package mysql-libs.x86_64 0:5.1.73-3.el6_5 will be obsoleted--> Finished Dependency ResolutionError: Package: MariaDB-oqgraph-engine-10.0.10-1.el6.x86_64 (mariadb) Requires: libJudy.so.1()(64bit) You could try using --skip-broken to work around the problem You could try running: rpm -Va --nofiles --nodigestroot@centos-6.5-minimal:[Mon May 05 12:53:42][~]$ yum whatprovides *libJudyLoaded plugins: fastestmirrorLoading mirror speeds from cached hostfile * base: mirrors.ircam.fr * extras: distrib-coffee.ipsl.jussieu.fr * updates: mir01.syntis.netbase/filelists_db | 5.9 MB 00:11 extras/filelists_db |11 kB 00:00 mariadb/filelists_db|56 kB 00:00 updates/filelists_db| 1.7 MB 00:03 No Matches foundroot@centos-6.5-minimal:[Mon May 05 12:54:20][~]$

所以，MariaDB-oqgraph-engine-10.0.10-1 似乎需要 libJudy，而 CentOS 最小安装默认存储库中缺少该 libJudy。
Athttps: //mariadb.atlassian.net/browse/MDEV-5664有详细的解释，总之你必须设置epel repo。
如何设置Epel CentOS 6.5 repo：

root@centos-6.5-minimal:[Mon May 05 13:01:59][/etc/yum.repos.d]$ wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpmroot@centos-6.5-minimal:[Mon May 05 13:02:14][/etc/yum.repos.d]$ rpm -Uvh epel-release-6-8.noarch.rpmroot@centos-6.5-minimal:[Mon May 05 13:02:29][/etc/yum.repos.d]$ yum repolistLoaded plugins: fastestmirrorLoading mirror speeds from cached hostfileepel/metalink |25 kB 00:00* base: sunsite.rediris.es * epel: ftp.cica.es * extras: ftp.rezopole.net * updates: centos.quelquesmots.frepel| 4.4 kB 00:00 epel/primary_db | 6.1 MB 00:09 repo id repo namestatusbaseCentOS-6 - Base 6,367epelExtra Packages for Enterprise Linux 6 - x86_64 10,762extrasCentOS-6 - Extras14mariadb MariaDB12updates CentOS-6 - Updates

现在，安装libJudy：

root@centos-6.5-minimal:[Mon May 05 13:05:44][/etc/yum.repos.d]$ yum install Judy

然后安装MariaDB服务器、客户端和可用的引擎：

root@centos-6.5-minimal:[Mon May 05 13:06:12][/etc/yum.repos.d]$ yum install MariaDB-server MariaDB*engine MariaDB-client

启动MariaDB 10.0.10 MySQL服务，检查已安装的引擎和配置的插件-ins 目录：

root@centos-6.5-minimal:[Mon May 05 13:13:53][/etc/yum.repos.d]$ mysql ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2 "No such file or directory")root@centos-6.5-minimal:[Mon May 05 13:15:38][/etc/yum.repos.d]$ service mysql Usage: mysql{start|stop|restart|reload|force-reload|status|configtest}[ MySQL server options ]root@centos-6.5-minimal:[Mon May 05 13:15:47][/etc/yum.repos.d]$ service mysqlstatus ERROR! MySQL is not runningroot@centos-6.5-minimal:[Mon May 05 13:15:53][/etc/yum.repos.d]$ service mysqlstartStarting MySQL. SUCCESS! root@centos-6.5-minimal:[Mon May 05 13:16:06][/etc/yum.repos.d]$ mysql Welcome to the MariaDB monitor.Commands end with ; or /g.Your MariaDB connection id is 3Server version: 10.0.10-MariaDB MariaDB ServerCopyright (c) 2000, 2014, Oracle, SkySQL Ab and others.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.mysql root@centos-6.5-minimal:[Mon May5 13:16:08 2014][(none)]> show engines;+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+| Engine | Support | Comment| Transactions | XA | Savepoints |+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+| CSV| YES | CSV storage engine | NO | NO | NO || MRG_MyISAM | YES | Collection of identical MyISAM tables| NO | NO | NO || MEMORY | YES | Hash based, stored in memory, useful for temporary tables| NO | NO | NO || BLACKHOLE| YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO || MyISAM | YES | MyISAM storage engine| NO | NO | NO || InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES| YES| YES|| ARCHIVE| YES | Archive storage engine | NO | NO | NO || FEDERATED| YES | FederatedX pluggable storage engine| YES| NO | YES|| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO || Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO || OQGRAPH| YES | Open Query Graph Computation Engine (http://openquery.com/graph) | NO | NO | NO || CASSANDRA| YES | Cassandra storage engine | NO | NO | NO || CONNECT| YES | Management of External Data (SQL/MED), including many file formats | NO | NO | NO |+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+13 rows in set (0.00 sec)mysql root@centos-6.5-minimal:[Mon May5 13:16:30 2014][(none)]> SHOW GLOBAL VARIABLES LIKE 'plugin_dir';+---------------+--------------------------+| Variable_name | Value|+---------------+--------------------------+| plugin_dir| /usr/lib64/mysql/plugin/ |+---------------+--------------------------+1 row in set (0.02 sec)

如上所示，OQGRAPH 和 CONNECT 引擎现已可用。

那么，如何测试 CONNECT 引擎 sqlite3 数据库处理程序？
首先，看一下https://mariadb.com/kb/en/connect-table-types-odbc-table-type-accessing-...page
总之，需要为CONNECT设置unixODBC驱动引擎 sqlite3 处理程序。

如何在 CentOS 6.5 最小安装上设置 sqliute3 unixODBC 驱动程序：
1.检查现有的 odbc 设置：

root@centos-6.5-minimal:[Mon May 05 13:29:16][~]$ cat /etc/odbcinst.ini# Example driver definitions# Driver from the postgresql-odbc package# Setup from the unixODBC package[PostgreSQL]Description	= ODBC for PostgreSQLDriver		= /usr/lib/psqlodbc.soSetup		= /usr/lib/libodbcpsqlS.soDriver64	= /usr/lib64/psqlodbc.soSetup64		= /usr/lib64/libodbcpsqlS.soFileUsage	= 1# Driver from the mysql-connector-odbc package# Setup from the unixODBC package[MySQL]Description	= ODBC for MySQLDriver		= /usr/lib/libmyodbc5.soSetup		= /usr/lib/libodbcmyS.soDriver64	= /usr/lib64/libmyodbc5.soSetup64		= /usr/lib64/libodbcmyS.soFileUsage	= 1root@centos-6.5-minimal:[Mon May 05 13:29:22][~]$ odbcinst -q -d[PostgreSQL][MySQL]

似乎缺少 sqlite3 unixODBC：

root@centos-6.5-minimal:[Mon May 05 13:30:38][~]$ locate libsqlite/usr/lib64/libsqlite3.so.0/usr/lib64/libsqlite3.so.0.8.6root@centos-6.5-minimal:[Mon May 05 13:32:46][~]$ yum search sqlite | grep -i odbc

我们什么也没找到。

所以好的旧方法/我的首选一/要做的是编译 sqlite3 unixODBC 驱动程序，如 http://www.ch-werner.de/sqliteodbc/ 中所述：

root@centos-6.5-minimal:[Mon May 05 13:33:37][/opt/installs]$ wget http://www.ch-werner.de/sqliteodbc/sqliteodbc-0.996.tar.gzroot@centos-6.5-minimal:[Mon May 05 13:33:41][/opt/installs]$ tar xvf sqliteodbc-0.996.tar.gzroot@centos-6.5-minimal:[Mon May 05 13:33:50][/opt/installs]$ cd sqliteodbc-0.996root@centos-6.5-minimal:[Mon May 05 13:34:22][/opt/installs/sqliteodbc-0.996]$ ./configure checking build system type... x86_64-redhat-linux-gnuchecking host system type... x86_64-redhat-linux-gnuchecking for gcc... nochecking for cc... nochecking for cc... nochecking for cl... noconfigure: error: no acceptable C compiler found in $PATHSee `config.log' for more details.

正如预期的那样，CentOS 6.5 的最小安装不会有所需的 gcc make 等开发工具。
如何安装开发环境：

root@centos-6.5-minimal:[Mon May 05 13:34:35][/opt/installs/sqliteodbc-0.996]$ yum groupinstall 'Development Tools'.....Install 104 Package(s)Total download size: 93 MInstalled size: 272 MIs this ok [y/N]: yComplete! root@centos-6.5-minimal:[Mon May 05 13:54:57][/opt/installs/sqliteodbc-0.996]$ ./configure ...checking for sqlite3_close_v2 in -lsqlite3... nonoconfigure: WARNING: SQLite4 header file and source not foundconfigure: error: No usable SQLite header/library on this system

这里的问题是缺少 sqlite3 开发标头。这可以通过使用 yum 安装 sqlite-devel 包来解决：

root@centos-6.5-minimal:[Mon May 05 13:55:33][/opt/installs/sqliteodbc-0.996]$ yum install sqlite-develInstalled:sqlite-devel.x86_64 0:3.6.20-1.el6 Complete!root@centos-6.5-minimal:[Mon May 05 13:56:53][/opt/installs/sqliteodbc-0.996]$ ./configure ...checking for ODBC headers and libraries... noconfigure: error: ODBC header files and/or libraries not found

新问题：ODBC 头文件丢失。再次，通过安装 unixODBC-devel 包来解决这个问题：

root@centos-6.5-minimal:[Mon May 05 13:57:11][/opt/installs/sqliteodbc-0.996]$ yum install unixODBC-develroot@centos-6.5-minimal:[Mon May 05 13:58:11][/opt/installs/sqliteodbc-0.996]$ ./configure ..checking for gmtime_r... yesconfigure: creating ./config.statusconfig.status: creating Makefileconfig.status: creating sqliteodbc.specconfig.status: creating debian/changelogroot@centos-6.5-minimal:[Mon May 05 13:58:29][/opt/installs/sqliteodbc-0.996]$ make -j3 gcc -shared.libs/sqlite3odbc.o-lsqlite3 -ldl-Wl,-soname -Wl,libsqlite3odbc-0.996.so -o .libs/libsqlite3odbc-0.996.so(cd .libs && rm -f libsqlite3odbc.so && ln -s libsqlite3odbc-0.996.so libsqlite3odbc.so)ar cru .libs/libsqlite3odbc.asqlite3odbc.oranlib .libs/libsqlite3odbc.acreating libsqlite3odbc.la(cd .libs && rm -f libsqlite3odbc.la && ln -s ../libsqlite3odbc.la libsqlite3odbc.la)root@centos-6.5-minimal:[Mon May 05 13:59:24][/opt/installs/sqliteodbc-0.996]$ make -j3install

检查 UnixUDBC 最近的设置：

root@centos-6.5-minimal:[Mon May 05 13:59:59][/opt/installs/sqliteodbc-0.996]$ odbcinst -q -d[PostgreSQL][MySQL]

添加 SQLite 驱动程序：

root@centos-6.5-minimal:[Mon May 05 14:00:27][/opt/installs/sqliteodbc-0.996]$ nano /etc/odbcinst.iniroot@centos-6.5-minimal:[Mon May 05 14:00:53][/opt/installs/sqliteodbc-0.996]$odbcinst -q -d[PostgreSQL][MySQL][SQLite]root@centos-6.5-minimal:[Mon May 05 14:01:52][/opt/installs/sqliteodbc-0.996]$ cat /etc/odbcinst.ini# Example driver definitions# Driver from the postgresql-odbc package# Setup from the unixODBC package[PostgreSQL]Description	= ODBC for PostgreSQLDriver		= /usr/lib/psqlodbc.soSetup		= /usr/lib/libodbcpsqlS.soDriver64	= /usr/lib64/psqlodbc.soSetup64		= /usr/lib64/libodbcpsqlS.soFileUsage	= 1# Driver from the mysql-connector-odbc package# Setup from the unixODBC package[MySQL]Description	= ODBC for MySQLDriver		= /usr/lib/libmyodbc5.soSetup		= /usr/lib/libodbcmyS.soDriver64	= /usr/lib64/libmyodbc5.soSetup64		= /usr/lib64/libodbcmyS.soFileUsage	= 1# added for MySQL Connect engine [SQLite]Description=SQLite ODBC DriverDriver=/usr/local/lib/libsqliteodbc.soSetup=/usr/local/lib/libsqliteodbc.soThreading=2

检查SELinux 状态：

root@centos-6.5-minimal:[Mon May 05 14:02:48][/opt/installs/sqliteodbc-0.996]$ sestatus SELinux status: enabledSELinuxfs mount:/selinuxCurrent mode: enforcingMode from config file:enforcingPolicy version: 24Policy from config file:targeted

请记住，目前 SELinux 已启用！

创建一个自定义目录，用于托管由 MariaDB 10.0.10 CONNECT 引擎处理的外部数据库文件

root@centos-6.5-minimal:[Mon May 05 14:04:22][/var/lib/mysql]$ mkdir /var/lib/mysql.connnect.dbroot@centos-6.5-minimal:[Mon May 05 14:04:45][/var/lib/mysql]$ chown -R mysql:mysql /var/lib/mysql.connnect.dbroot@centos-6.5-minimal:[Mon May 05 14:44:01][/var/lib/mysql]$ cd /var/lib/mysql.connnect.db

创建测试 sqlite3 数据库：

root@centos-6.5-minimal:[Mon May 05 14:44:01][/var/lib/mysql.connnect.db]$sqlite3 maria-sqlite3.dbSQLite version 3.6.20Enter ".help" for instructionsEnter SQL statements terminated with a ";"sqlite> CREATE TABLE DEPARTMENT(	...>	 ID INT PRIMARY KEY		NOT NULL,	...>	 DEPT			CHAR(50) NOT NULL,	...>	 EMP_ID			INT		NOT NULL	...> );sqlite> sqlite> insert into DEPARTMENT (ID,DEPT,EMP_ID) values(1,'sales',1);sqlite> insert into DEPARTMENT (ID,DEPT,EMP_ID) values(2,'sales',2);sqlite> insert into DEPARTMENT (ID,DEPT,EMP_ID) values(3,'sales',3);sqlite> insert into DEPARTMENT (ID,DEPT,EMP_ID) values(4,'marketing',4);sqlite> insert into DEPARTMENT (ID,DEPT,EMP_ID) values(5,'marketing',5);sqlite> . tablesDEPARTMENTsqlite> select * from DEPARTMENT;1|sales|12|sales|23|sales|34|marketing|45|marketing|5

为该 sqlite3 数据库设置适当的文件权限：

root@centos-6.5-minimal:[Mon May 05 14:46:29][/var/lib/mysql.connnect.db]$ ls -lrthtotal 4.0K-rw-r--r--. 1 root root 3.0K May5 14:45 maria-sqlite3.dbroot@centos-6.5-minimal:[Mon May 05 14:46:31][/var/lib/mysql.connnect.db]$ chown -R mysql:mysql maria-sqlite3.db root@centos-6.5-minimal:[Mon May 05 14:46:42][/var/lib/mysql.connnect.db]$ ls -lrthtotal 4.0K-rw-r--r--. 1 mysql mysql 3.0K May5 14:45 maria-sqlite3.db

创建 MariaDB 10.0.10 CONNECT处理 sqlite3 数据库的引擎表：

root@centos-6.5-minimal:[Mon May 05 14:46:43][/var/lib/mysql.connnect.db]$ mysql Welcome to the MariaDB monitor.Commands end with ; or /g.Your MariaDB connection id is 4Server version: 10.0.10-MariaDB MariaDB ServerCopyright (c) 2000, 2014, Oracle, SkySQL Ab and others.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.mysql root@centos-6.5-minimal:[Mon May5 14:47:26 2014][(none)]>create database sqlite3;Query OK, 1 row affected (0.01 sec)mysql root@centos-6.5-minimal:[Mon May5 14:47:28 2014][(none)]> use sqlite3;Database changedmysql root@centos-6.5-minimal:[Mon May5 14:47:34 2014][sqlite3]> create table my_dept engine=CONNECT table_type=ODBC tabname='DEPARTMENT' Connection='Driver=SQLite;Database=/var/lib/mysql.connnect.db/maria-sqlite3.db;version=3;';ERROR 1105 (HY000): [unixODBC][Driver Manager]Can't open lib '/usr/local/lib/libsqliteodbc.so' : file not found

现在，我们似乎没有 UnixUDBC sqlite3 驱动程序的工作设置，MariaDB 10.0.10 错误已经足够解释了。
如何解决这个问题：

root@centos-6.5-minimal:[Mon May 05 14:49:49][/var/lib/mysql.connnect.db]$ locate libsqlite | grep -i odbc | grep -v install/usr/local/lib/libsqlite3odbc-0.996.so/usr/local/lib/libsqlite3odbc.a/usr/local/lib/libsqlite3odbc.la/usr/local/lib/libsqlite3odbc.so

看来sqlite3 unixODBC文件的名称设置不正确。
'/usr/local/lib/libsqliteodbc.so'丢失，'/usr/local/lib/ libsqlite3odbc.so'可用

root@centos-6.5-minimal:[Mon May 05 14:48:40][/var/lib/mysql.connnect.db]$ fgrep libsqliteodbc.so /etc/odbcinst.ini Driver=/usr/local/lib/libsqliteodbc.soSetup=/usr/local/lib/libsqliteodbc.so

更改/etc/odbcinst.ini配置文件：

root@centos-6.5-minimal:[Mon May 05 14:50:29][/var/lib/mysql.connnect.db]$ nano /etc/odbcinst.iniroot@centos-6.5-minimal:[Mon May 05 14:50:57][/var/lib/mysql.connnect.db]$odbcinst -q -d[PostgreSQL][MySQL][SQLite]root@centos-6.5-minimal:[Mon May 05 14:51:06][/var/lib/mysql.connnect.db]$ cat /etc/odbcinst.ini# Example driver definitions# Driver from the postgresql-odbc package# Setup from the unixODBC package[PostgreSQL]Description	= ODBC for PostgreSQLDriver		= /usr/lib/psqlodbc.soSetup		= /usr/lib/libodbcpsqlS.soDriver64	= /usr/lib64/psqlodbc.soSetup64		= /usr/lib64/libodbcpsqlS.soFileUsage	= 1# Driver from the mysql-connector-odbc package# Setup from the unixODBC package[MySQL]Description	= ODBC for MySQLDriver		= /usr/lib/libmyodbc5.soSetup		= /usr/lib/libodbcmyS.soDriver64	= /usr/lib64/libmyodbc5.soSetup64		= /usr/lib64/libodbcmyS.soFileUsage	= 1[SQLite]Description=SQLite ODBC DriverDriver=/usr/local/lib/libsqlite3odbc.soSetup=/usr/local/lib/libsqlite3odbc.soThreading=2

重新加载MariaDB 10.0.10 MySQL服务：

root@centos-6.5-minimal:[Mon May 05 14:51:44][/var/lib/mysql.connnect.db]$ service mysql reload SUCCESS! Reloading service MySQL

尝试在 MariaDB 10.0.10 MySQL 上再次创建 sqlite3 CONNECT ENGINE 表：

mysql root@centos-6.5-minimal:[Mon May5 14:52:16 2014][sqlite3]> create table my_dept engine=CONNECT table_type=ODBC tabname='DEPARTMENT' Connection='Driver=SQLite;Database=/var/lib/mysql.connnect.db/maria-sqlite3.db;version=3;';ERROR 1105 (HY000): [unixODBC][SQLite]connect failed

再次出现错误，这次是不同的。
一般来说，这是由于 SELinux 对mysql 访问其他目录：
一种解决方法是将 sqlite3 db 文件放在 mysql 数据目录中，它将起作用。

示例：

root@centos-6.5-minimal:[Mon May 05 15:04:07][/var/lib/mysql.connnect.db]$ cp -p /var/lib/mysql.connnect.db/maria-sqlite3.db /var/lib/mysql/maria-sqlite3.dbmysql root@centos-6.5-minimal:[Mon May5 15:04:24 2014][sqlite3]> create table my_dept engine=CONNECT table_type=ODBC tabname='DEPARTMENT' Connection='Driver=SQLite;Database=/var/lib/mysql/maria-sqlite3.db;version=3;';Query OK, 0 rows affected (0.24 sec)mysql root@centos-6.5-minimal:[Mon May5 15:05:06 2014][sqlite3]> show create table my_dept /G*************************** 1. row *************************** Table: my_deptCreate Table: CREATE TABLE `my_dept` (`ID` int(9) NOT NULL,`DEPT` varchar(50) NOT NULL,`EMP_ID` int(9) NOT NULL) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='Driver=SQLite;Database=/var/lib/mysql/maria-sqlite3.db;version=3;' `TABLE_TYPE`='ODBC' `TABNAME`='DEPARTMENT'1 row in set (0.01 sec)mysql root@centos-6.5-minimal:[Mon May5 15:07:35 2014][sqlite3]> select * from my_dept;+----+-----------+--------+| ID | DEPT| EMP_ID |+----+-----------+--------+|1 | sales |1 ||2 | sales |2 ||3 | sales |3 ||4 | marketing |4 ||5 | marketing |5 |+----+-----------+--------+5 rows in set (0.00 sec)

另一种方法是禁用 SELinux：
示例：

root@centos-6.5-minimal:[Mon May 05 15:07:51][/var/lib/mysql.connnect.db]$ echo 0 >/selinux/enforcemysql root@centos-6.5-minimal:[Mon May5 15:09:16 2014][sqlite3]> create table se_my_dept engine=CONNECT table_type=ODBC tabname='DEPARTMENT' Connection='Driver=SQLite;Database=/var/lib/mysql.connnect.db/maria-sqlite3.db;version=3;';Query OK, 0 rows affected (0.18 sec)mysql root@centos-6.5-minimal:[Mon May5 15:09:27 2014][sqlite3]> insert into se_my_dept values(6,'dev',6);Query OK, 1 row affected (0.18 sec)

现在您可以访问 sqlite3 数据库并检查插入的新行的数据：

root@centos-6.5-minimal:[Mon May 05 15:16:18][/var/lib/mysql.connnect.db]$ sqlite3 maria-sqlite3.db SQLite version 3.6.20Enter ".help" for instructionsEnter SQL statements terminated with a ";"sqlite> .tablesDEPARTMENTsqlite> select * from DEPARTMENT;1|sales|12|sales|23|sales|34|marketing|45|marketing|56|dev|6

所以再次，保持请记住，当您将 MariaDB CONNECT ENGINE 与本地数据库文件一起使用时，SELinux 的限制。

现在，让我们看一下 MariaDB-Server-Audit 插件的安装过程：
http://www.skysql .com/blogs/ralf-gebhardt/activating-auditing-mariadb-an...已为您解释了一切。
简而言之：

mysql root@centos-6.5-minimal:[Mon May5 15:21:52 2014][sqlite3]> INSTALL PLUGIN server_audit SONAME 'server_audit.so';Query OK, 0 rows affected (0.00 sec)mysql root@centos-6.5-minimal:[Mon May5 15:21:56 2014][sqlite3]> SELECT * from information_schema.plugins where plugin_name='server_audit';+--------------+----------------+---------------+-------------+---------------------+-----------------+------------------------+---------------------------+----------------------------+----------------+-------------+-----------------+---------------------+| PLUGIN_NAME| PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE | PLUGIN_TYPE_VERSION | PLUGIN_LIBRARY| PLUGIN_LIBRARY_VERSION | PLUGIN_AUTHOR				 | PLUGIN_DESCRIPTION			| PLUGIN_LICENSE | LOAD_OPTION | PLUGIN_MATURITY | PLUGIN_AUTH_VERSION |+--------------+----------------+---------------+-------------+---------------------+-----------------+------------------------+---------------------------+----------------------------+----------------+-------------+-----------------+---------------------+| SERVER_AUDIT | 1.1				| ACTIVE		| AUDIT		 | 3.2					| server_audit.so | 1.8						| Alexey Botchkov (MariaDB) | Audit the server activity. | GPL				| ON			 | Beta				| 1.1.5					|+--------------+----------------+---------------+-------------+---------------------+-----------------+------------------------+---------------------------+----------------------------+----------------+-------------+-----------------+---------------------+1 row in set (0.01 sec)mysql root@centos-6.5-minimal:[Mon May5 15:22:13 2014][sqlite3]> SELECT * from information_schema.plugins where plugin_name='server_audit' /G*************************** 1. row ***************************			PLUGIN_NAME: SERVER_AUDIT		PLUGIN_VERSION: 1.1			PLUGIN_STATUS: ACTIVE			PLUGIN_TYPE: AUDIT	PLUGIN_TYPE_VERSION: 3.2		PLUGIN_LIBRARY: server_audit.soPLUGIN_LIBRARY_VERSION: 1.8			PLUGIN_AUTHOR: Alexey Botchkov (MariaDB)	 PLUGIN_DESCRIPTION: Audit the server activity.		PLUGIN_LICENSE: GPL			LOAD_OPTION: ON		 PLUGIN_MATURITY: Beta	PLUGIN_AUTH_VERSION: 1.1.51 row in set (0.06 sec)mysql root@centos-6.5-minimal:[Mon May5 15:22:18 2014][sqlite3]> SHOW GLOBAL VARIABLES LIKE 'server_audit%';+-------------------------------+-----------------------+| Variable_name					| Value					|+-------------------------------+-----------------------+| server_audit_events			|							|| server_audit_excl_users		 |							|| server_audit_file_path		| server_audit.log		|| server_audit_file_rotate_now| OFF						 || server_audit_file_rotate_size | 1000000					|| server_audit_file_rotations	| 9							|| server_audit_incl_users		 |							|| server_audit_logging			 | OFF						 || server_audit_mode				 | 0							|| server_audit_output_type		| file						|| server_audit_syslog_facility| LOG_USER				|| server_audit_syslog_ident	| mysql-server_auditing || server_audit_syslog_info		|							|| server_audit_syslog_priority| LOG_INFO				|+-------------------------------+-----------------------+14 rows in set (0.01 sec)mysql root@centos-6.5-minimal:[Mon May5 15:22:42 2014][sqlite3]> SET GLOBAL server_audit_events='CONNECT,QUERY,TABLE';Query OK, 0 rows affected (0.00 sec)mysql root@centos-6.5-minimal:[Mon May5 15:22:56 2014][sqlite3]> SHOW GLOBAL VARIABLES LIKE 'server_audit%';+-------------------------------+-----------------------+| Variable_name					| Value					|+-------------------------------+-----------------------+| server_audit_events			| CONNECT,QUERY,TABLE	|| server_audit_excl_users		 |							|| server_audit_file_path		| server_audit.log		|| server_audit_file_rotate_now| OFF						 || server_audit_file_rotate_size | 1000000					|| server_audit_file_rotations	| 9							|| server_audit_incl_users		 |							|| server_audit_logging			 | OFF						 || server_audit_mode				 | 0							|| server_audit_output_type		| file						|| server_audit_syslog_facility| LOG_USER				|| server_audit_syslog_ident	| mysql-server_auditing || server_audit_syslog_info		|							|| server_audit_syslog_priority| LOG_INFO				|+-------------------------------+-----------------------+14 rows in set (0.00 sec)mysql root@centos-6.5-minimal:[Mon May5 15:22:58 2014][sqlite3]> SET GLOBAL server_audit_logging=ON;Query OK, 0 rows affected (0.00 sec)mysql root@centos-6.5-minimal:[Mon May5 15:23:32 2014][sqlite3]> SHOW GLOBAL VARIABLES LIKE 'server_audit%';+-------------------------------+-----------------------+| Variable_name					| Value					|+-------------------------------+-----------------------+| server_audit_events			| CONNECT,QUERY,TABLE	|| server_audit_excl_users		 |							|| server_audit_file_path		| server_audit.log		|| server_audit_file_rotate_now| OFF						 || server_audit_file_rotate_size | 1000000					|| server_audit_file_rotations	| 9							|| server_audit_incl_users		 |							|| server_audit_logging			 | ON						|| server_audit_mode				 | 0							|| server_audit_output_type		| file						|| server_audit_syslog_facility| LOG_USER				|| server_audit_syslog_ident	| mysql-server_auditing || server_audit_syslog_info		|							|| server_audit_syslog_priority| LOG_INFO				|+-------------------------------+-----------------------+14 rows in set (0.00 sec)

在 MariaDB 10.0.10 MySQL 错误日志中，您可以查看查看审核插件信息：

root@centos-6.5-minimal:[Mon May 05 15:23:53][/var/lib/mysql.connnect.db]$ tail -f /var/lib/mysql/centos-6.5-minimal.err /var/lib/mysql/server_audit.log ==> /var/lib/mysql/centos-6.5-minimal.err <==140505 14:55:42 [Note] InnoDB: Waiting for purge to start140505 14:55:42 [Note] InnoDB:Percona XtraDB (http://www.percona.com) 5.6.15-63.0 started; log sequence number 1619068140505 14:55:42 [Note] Plugin 'FEEDBACK' is disabled.140505 14:55:42 [Note] CONNECT: Version 1.02.0002 March 16, 2014140505 14:55:42 [Note] Server socket created on IP: '0.0.0.0'.140505 14:55:42 [Note] Event Scheduler: Loaded 0 events140505 14:55:42 [Note] /usr/sbin/mysqld: ready for connections.Version: '10.0.10-MariaDB'socket: '/var/lib/mysql/mysql.sock'port: 3306MariaDB Server140505 15:21:56 server_audit: MariaDB Audit Plugin version 1.1.5 STARTED.140505 15:23:32 server_audit: logging started to the file server_audit.log.==> /var/lib/mysql/server_audit.log <==20140505 15:23:32,centos-6.5-minimal,root,localhost,3,21,QUERY,sqlite3,'SET GLOBAL server_audit_logging=ON',020140505 15:23:35,centos-6.5-minimal,root,localhost,3,22,QUERY,sqlite3,'SHOW GLOBAL VARIABLES LIKE /'server_audit%/'',0

So, we want to upgrade to the latest MariaDB-Audit-Plugin which is 1.1.7:
Download location:https://downloads.skysql.com/enterprise/MariaDB-Audit-Plugin/
Install the plugin at the MariaDB MySQL plugins directory:

root@centos-6.5-minimal:[Mon May 05 15:15:55][/var/lib/mysql.connnect.db]$ cd /usr/lib64/mysql/plugin/root@centos-6.5-minimal:[Mon May 05 15:19:56][/usr/lib64/mysql/plugin]$ wget https://downloads.skysql.com/enterprise/MariaDB-Audit-Plugin/server_audi... --2014-05-05 15:20:05--https://downloads.skysql.com/enterprise/MariaDB-Audit-Plugin/server_audi...Resolving downloads.skysql.com... 46.105.96.115, 2001:41d0:2:cf73::1Connecting to downloads.skysql.com|46.105.96.115|:443... connected.HTTP request sent, awaiting response... 200 OKLength: 166618 (163K) [application/octet-stream]Saving to: “server_audit.so.1”

Note the name of the new file: "server_audit.so.1"

root@centos-6.5-minimal:[Mon May 05 15:21:03][/usr/lib64/mysql/plugin]$ ls -lrthtotal 64M-rwxr-xr-x. 1 root root 5.4M Mar 30 23:37 ha_connect.so-rwxr-xr-x. 1 root root 9.6M Mar 30 23:38 ha_cassandra.so-rwxr-xr-x. 1 root root 2.6M Mar 30 23:38 ha_oqgraph.so-rwxr-xr-x. 1 root root20M Mar 30 23:41 ha_innodb.so-rwxr-xr-x. 1 root root 6.2M Mar 30 23:43 ha_spider.so-rwxr-xr-x. 1 root root15M Mar 30 23:49 ha_tokudb.so-rwxr-xr-x. 1 root root 253K Mar 30 23:54 ha_sequence.so-rwxr-xr-x. 1 root root 819K Mar 30 23:54 ha_sphinx.so-rwxr-xr-x. 1 root root19K Mar 30 23:59 auth_pam.so-rwxr-xr-x. 1 root root 159K Mar 30 23:59 locales.so-rwxr-xr-x. 1 root root 2.6M Mar 30 23:59 handlersocket.so-rwxr-xr-x. 1 root root 269K Mar 30 23:59 metadata_lock_info.so-rwxr-xr-x. 1 root root 664K Mar 30 23:59 semisync_master.so-rwxr-xr-x. 1 root root20K Mar 30 23:59 sql_errlog.so-rwxr-xr-x. 1 root root 562K Mar 30 23:59 semisync_slave.so-rwxr-xr-x. 1 root root 206K Mar 30 23:59 server_audit.so-rwxr-xr-x. 1 root root13K Mar 31 00:00 auth_socket.so-rwxr-xr-x. 1 root root 346K Mar 31 00:00 query_response_time.so-rwxr-xr-x. 1 root root 273K Mar 31 00:00 query_cache_info.so-rw-r--r--. 1 root root 163K Apr 17 09:44 server_audit.so.1

Uninstall the old plugin:

mysql root@centos-6.5-minimal:[Tue May6 13:43:23 2014][(none)]> uninstall plugin server_audit;

At the error logs and the audit logs you will see messages like these ones:

20140506 13:43:23,centos-6.5-minimal,root,localhost,7,0,CONNECT,,,020140506 13:43:23,centos-6.5-minimal,root,localhost,7,32,QUERY,,'select @@version_comment limit 1',020140506 13:43:23,centos-6.5-minimal,root,localhost,7,33,QUERY,,'select USER()',020140506 13:43:28,centos-6.5-minimal,root,localhost,7,34,WRITE,mysql,plugin,20140506 13:43:28,centos-6.5-minimal,root,localhost,7,34,QUERY,mysql,'uninstall plugin server_audit',0==> /var/lib/mysql/centos-6.5-minimal.err <==140506 13:43:28 server_audit: STOPPED

Now, replace the old one with the new server_audit.so.1 and reload the MariaDB 10.0.10 MySQL service:

root@centos-6.5-minimal:[Tue May 06 13:26:55][/usr/lib64/mysql/plugin]$ mv server_audit.so server_audit.so.1.1.5root@centos-6.5-minimal:[Tue May 06 13:47:05][/usr/lib64/mysql/plugin]$ mv server_audit.so.1 server_audit.soroot@centos-6.5-minimal:[Tue May 06 13:47:12][/usr/lib64/mysql/plugin]$ service mysql reload SUCCESS! Reloading service MySQL

Install the server_audit plugin again:

mysql root@centos-6.5-minimal:[Tue May6 13:44:09 2014][(none)]> INSTALL PLUGIN server_audit SONAME 'server_audit.so';Query OK, 0 rows affected (0.01 sec)mysql root@centos-6.5-minimal:[Tue May6 13:48:17 2014][(none)]>SHOW GLOBAL VARIABLES LIKE 'server_audit%';+-------------------------------+-----------------------+| Variable_name | Value |+-------------------------------+-----------------------+| server_audit_events | || server_audit_excl_users | || server_audit_file_path| server_audit.log|| server_audit_file_rotate_now| OFF || server_audit_file_rotate_size | 1000000 || server_audit_file_rotations | 9 || server_audit_incl_users | || server_audit_logging| OFF || server_audit_mode | 0 || server_audit_output_type| file|| server_audit_syslog_facility| LOG_USER|| server_audit_syslog_ident | mysql-server_auditing || server_audit_syslog_info| || server_audit_syslog_priority| LOG_INFO|+-------------------------------+-----------------------+14 rows in set (0.00 sec)

You will see at the error log that the latest MariaDB 10.0.10 Audit Plugin version 1.1.7 was loaded :

==> /var/lib/mysql/centos-6.5-minimal.err <==140506 13:48:17 server_audit: MariaDB Audit Plugin version 1.1.7 STARTED.Activate the Audit plugin again:mysql root@centos-6.5-minimal:[Tue May6 13:50:59 2014][(none)]> SET GLOBAL server_audit_events='CONNECT,QUERY,TABLE';Query OK, 0 rows affected (0.00 sec)mysql root@centos-6.5-minimal:[Tue May6 13:51:56 2014][(none)]> SET GLOBAL server_audit_logging=ON;Query OK, 0 rows affected (0.00 sec)mysql root@centos-6.5-minimal:[Tue May6 13:52:07 2014][(none)]>SHOW GLOBAL VARIABLES LIKE 'server_audit%';+-------------------------------+-----------------------+| Variable_name | Value |+-------------------------------+-----------------------+| server_audit_events | CONNECT,QUERY,TABLE || server_audit_excl_users | || server_audit_file_path| server_audit.log|| server_audit_file_rotate_now| OFF || server_audit_file_rotate_size | 1000000 || server_audit_file_rotations | 9 || server_audit_incl_users | || server_audit_logging| ON|| server_audit_mode | 0 || server_audit_output_type| file|| server_audit_syslog_facility| LOG_USER|| server_audit_syslog_ident | mysql-server_auditing || server_audit_syslog_info| || server_audit_syslog_priority| LOG_INFO|+-------------------------------+-----------------------+14 rows in set (0.01 sec)

You will notice at the /var/lib/mysql/server_audit.log the new records:

==> /var/lib/mysql/centos-6.5-minimal.err <==140506 13:48:17 server_audit: MariaDB Audit Plugin version 1.1.7 STARTED.140506 13:52:07 server_audit: logging started to the file server_audit.log.==> /var/lib/mysql/server_audit.log <==20140506 13:52:07,centos-6.5-minimal,root,localhost,7,42,QUERY,,'SET GLOBAL server_audit_logging=ON',020140506 13:52:13,centos-6.5-minimal,root,localhost,7,43,QUERY,,'SHOW GLOBAL VARIABLES LIKE /'server_audit%/'',0

So, that was the test, with MariaDB 10.0.10 and its CONNECT ENGINE audited by the MariaDB Audit Plugin version 1.1.7.

Submitted by ivanstoykov on May 6, 2014 - 1:05pm GMT

• ivanstoykov's blog
• Loginorregisterto post comments