玛丽亚数据库
位置
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 /var/lib/mysql/server_audit.log <p>So, we want to upgrade to the latest MariaDB-Audit-Plugin which is 1.1.7:<br>Download location:https://downloads.skysql.com/enterprise/MariaDB-Audit-Plugin/<br>Install the plugin at the MariaDB MySQL plugins directory:</p><pre class="brush:php;toolbar:false">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 <p>Now, replace the old one with the new server_audit.so.1 and reload the MariaDB 10.0.10 MySQL service:</p><pre class="brush:php;toolbar:false">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 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 /var/lib/mysql/server_audit.log <p>So, that was the test, with MariaDB 10.0.10 and its CONNECT ENGINE audited by the MariaDB Audit Plugin version 1.1.7.</p><p>Submitted by ivanstoykov on May 6, 2014 - 1:05pm GMT</p>
- ivanstoykov's blog
- Loginorregisterto post comments
声明
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
减少在Docker中使用MySQL内存的使用Mar 04, 2025 pm 03:52 PM本文探讨了Docker中的优化MySQL内存使用量。 它讨论了监视技术（Docker统计，性能架构，外部工具）和配置策略。 其中包括Docker内存限制，交换和cgroups
mysql无法打开共享库怎么解决Mar 04, 2025 pm 04:01 PM本文介绍了MySQL的“无法打开共享库”错误。 该问题源于MySQL无法找到必要的共享库（.SO/.DLL文件）。解决方案涉及通过系统软件包M验证库安装
如何使用Alter Table语句在MySQL中更改表？Mar 19, 2025 pm 03:51 PM本文讨论了使用MySQL的Alter Table语句修改表，包括添加/删除列，重命名表/列以及更改列数据类型。
在 Linux 中运行 MySQl（有/没有带有 phpmyadmin 的 podman 容器）Mar 04, 2025 pm 03:54 PM本文比较使用/不使用PhpMyAdmin的Podman容器直接在Linux上安装MySQL。 它详细介绍了每种方法的安装步骤，强调了Podman在孤立，可移植性和可重复性方面的优势，还
什么是 SQLite？全面概述Mar 04, 2025 pm 03:55 PM本文提供了SQLite的全面概述，SQLite是一个独立的，无服务器的关系数据库。 它详细介绍了SQLite的优势（简单，可移植性，易用性）和缺点（并发限制，可伸缩性挑战）。 c
在MacOS上运行多个MySQL版本：逐步指南Mar 04, 2025 pm 03:49 PM本指南展示了使用自制在MacOS上安装和管理多个MySQL版本。 它强调使用自制装置隔离安装，以防止冲突。 本文详细详细介绍了安装，起始/停止服务和最佳PRA
如何为MySQL连接配置SSL/TLS加密？Mar 18, 2025 pm 12:01 PM文章讨论了为MySQL配置SSL/TLS加密，包括证书生成和验证。主要问题是使用自签名证书的安全含义。[角色计数：159]
哪些流行的MySQL GUI工具（例如MySQL Workbench，PhpMyAdmin）是什么？Mar 21, 2025 pm 06:28 PM文章讨论了流行的MySQL GUI工具，例如MySQL Workbench和PhpMyAdmin，比较了它们对初学者和高级用户的功能和适合性。[159个字符]
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
AI Hentai Generator
免费生成ai无尽的。
热门文章
R.E.P.O.能量晶体解释及其做什么（黄色晶体）
2 周前By尊渡假赌尊渡假赌尊渡假赌
仓库：如何复兴队友
4 周前By尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒险：如何获得巨型种子
3 周前By尊渡假赌尊渡假赌尊渡假赌
击败分裂小说需要多长时间？
3 周前ByDDD
R.E.P.O.保存文件位置：在哪里以及如何保护它？
3 周前ByDDD
热工具
DVWA
Damn Vulnerable Web App (DVWA) 是一个PHP/MySQL的Web应用程序，非常容易受到攻击。它的主要目标是成为安全专业人员在合法环境中测试自己的技能和工具的辅助工具，帮助Web开发人员更好地理解保护Web应用程序的过程，并帮助教师/学生在课堂环境中教授/学习Web应用程序安全。DVWA的目标是通过简单直接的界面练习一些最常见的Web漏洞，难度各不相同。请注意，该软件中
Atom编辑器mac版下载
最流行的的开源编辑器
Dreamweaver Mac版
视觉化网页开发工具
PhpStorm Mac 版本
最新（2018.2.1 ）专业的PHP集成开发工具
SecLists
SecLists是最终安全测试人员的伙伴。它是一个包含各种类型列表的集合，这些列表在安全评估过程中经常使用，都在一个地方。SecLists通过方便地提供安全测试人员可能需要的所有列表，帮助提高安全测试的效率和生产力。列表类型包括用户名、密码、URL、模糊测试有效载荷、敏感数据模式、Web shell等等。测试人员只需将此存储库拉到新的测试机上，他就可以访问到所需的每种类型的列表。
