MariaDB
I needed to test the new SkySQL server audit plug-in 1.1.7 on latest MariaDB 10.0.10
More info about that handy plug in you may find here :http://www.skysql.com/downloads/mariadb-audit-plugin
Take a moment to read the info, along with that nice blog :http://www.skysql.com/blogs/ralf-gebhardt/activating-auditing-mariadb-an...
Then I decided to test the CONNECT engine install as I have a friend who needs to import unknown DB data exported into XML.
And I was interested to import the Skype sqlite3 database into MySQL... :)
More about that MariaDB engine you may find here :https://mariadb.com/kb/en/connect/
So.
The test was done on CentOS 6.5 minimal install.
I will go step by step from the start.
1. Configure the MariaDB repository for CentOS 6.5, the best way to do it the the MariaDB repository configuration tool:
https://downloads.mariadb.org/mariadb/repositories/
Check the CentOS link, then choose the release, in my case CentOS 6 (64 bit), then the MariaDB version : 10.0
As a direct link :
https://downloads.mariadb.org/mariadb/repositories/#mirror=nucleus&distr...
In short: the content of the MariaDB.repo should be as follows:
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. Install the 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][~]$
So, it seems the MariaDB-oqgraph-engine-10.0.10-1 needs the libJudy which is missing at CentOS minimal install defaut repos.
Athttps://mariadb.atlassian.net/browse/MDEV-5664there is detailed explanation, in short you have to set the epel repo.
How to set the 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
Now, install libJudy:
root@centos-6.5-minimal:[Mon May 05 13:05:44][/etc/yum.repos.d]$ yum install Judy
Then install the MariaDB server, client and the available engines:
root@centos-6.5-minimal:[Mon May 05 13:06:12][/etc/yum.repos.d]$ yum install MariaDB-server MariaDB*engine MariaDB-client
Start the MariaDB 10.0.10 MySQL service, check the installed engines and the configured plug-ins directory:
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)As you can see above, the OQGRAPH and CONNECT engines are available now.
So, how to test the CONNECT engine sqlite3 db handler?
First, take a look athttps://mariadb.com/kb/en/connect-table-types-odbc-table-type-accessing-...page
In short, you need to setup the unixODBC driver for the CONNECT engine sqlite3 handler.
How to setup sqliute3 unixODBC driver on CentOS 6.5 minimal install:
1. Check the existing odbc setup :
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]
It seems the sqlite3 unixODBC is missing:
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
We find nothing.
So the good old way /my preferred one/ to do it is to compile the sqlite3 unixODBC driver as explained athttp://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.
As expected, the minimal install of the CentOS 6.5 does not have the needed gcc make etc dev tools.
How to install the development environment:
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
Here the issue is the missing sqlite3 devel headers. This can be fixed by installing the sqlite-devel package with yum:
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
New issue : ODBC header files are missing. Again, fix that by installing unixODBC-devel package:
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
Check the UnixUDBC recent setup:
root@centos-6.5-minimal:[Mon May 05 13:59:59][/opt/installs/sqliteodbc-0.996]$ odbcinst -q -d[PostgreSQL][MySQL]
Add the SQLite driver:
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
Check the SELinux status:
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
Keep in mind that at the moment SELinux is enabled!
Create a custom directory that will host the external db files handled by the MariaDB 10.0.10 CONNECT engine
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
Create the test sqlite3 database:
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
Set the proper file permissions for that sqlite3 database:
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
Create the MariaDB 10.0.10 CONNECT engine table to handle that sqlite3 database:
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
Now, it seems we have non working setup of the UnixUDBC sqlite3 driver, the MariaDB 10.0.10 error is explanatory enough.
How to fix that:
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
So it seems the name of the sqlite3 unixODBC file is not set right.
'/usr/local/lib/libsqliteodbc.so' is missing, '/usr/local/lib/libsqlite3odbc.so' is available
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
Change the /etc/odbcinst.ini configuration file:
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
Reload the MariaDB 10.0.10 MySQL service:
root@centos-6.5-minimal:[Mon May 05 14:51:44][/var/lib/mysql.connnect.db]$ service mysql reload SUCCESS! Reloading service MySQL
Try to create again the sqlite3 CONNECT ENGINE table at MariaDB 10.0.10 MySQL:
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
ERROR again, this time a different one.
In general this is due to the SELinux restrictions on the mysql accessing other directories:
One work around is to put the sqlite3 db file at the mysql data directory, it will work.
Example:
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)
The other way is to disable the SELinux:
Example:
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)
Now you may access the sqlite3 db and to check the data for the inserted new row:
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
So again, keep in mind the SELinux restrictions when you use the MariaDB CONNECT ENGINE with local db files.
Now, lets take a look at the MariaDB-Server-Audit plugin installation process:
http://www.skysql.com/blogs/ralf-gebhardt/activating-auditing-mariadb-an...has everything explained for you.
In short:
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)
At the MariaDB 10.0.10 MySQL error log you see the audit plugin info:
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>
![[Web-Frontend] Node.js-Schnellstart](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
