MySQL MariaDB version 10.0.10 Installation
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 = MariaDB baseurl = http://yum.mariadb.org/10.0/centos6-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1
1. Install the MariaDB 10.0.10:
root@centos-6.5-minimal:[Mon May 05 12:49:52][~]$ yum search Mariadb
Loaded plugins: fastestmirror
Loading 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 server
MariaDB-cassandra-engine.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-client.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-common.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-compat.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-connect-engine.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-devel.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-oqgraph-engine.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-server.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-shared.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-test.x86_64 : MariaDB: a very fast and robust SQL database server
Name 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: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirrors.atosworldline.com
* extras: sunsite.rediris.es
* updates: sunsite.rediris.es
Setting up Install Process
Resolving 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 Resolution
Error: 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 --nodigest
root@centos-6.5-minimal:[Mon May 05 12:53:42][~]$ yum whatprovides *libJudy
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirrors.ircam.fr
* extras: distrib-coffee.ipsl.jussieu.fr
* updates: mir01.syntis.net
base/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 found
root@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.
At https://mariadb.atlassian.net/browse/MDEV-5664 there 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.rpm root@centos-6.5-minimal:[Mon May 05 13:02:14][/etc/yum.repos.d]$ rpm -Uvh epel-release-6-8.noarch.rpm root@centos-6.5-minimal:[Mon May 05 13:02:29][/etc/yum.repos.d]$ yum repolist Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile epel/metalink | 25 kB 00:00 * base: sunsite.rediris.es * epel: ftp.cica.es * extras: ftp.rezopole.net * updates: centos.quelquesmots.fr epel | 4.4 kB 00:00 epel/primary_db | 6.1 MB 00:09 repo id repo name status base CentOS-6 - Base 6,367 epel Extra Packages for Enterprise Linux 6 - x86_64 10,762 extras CentOS-6 - Extras 14 mariadb MariaDB 12 updates 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 mysql status
ERROR! MySQL is not running
root@centos-6.5-minimal:[Mon May 05 13:15:53][/etc/yum.repos.d]$ service mysql start
Starting 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 3
Server version: 10.0.10-MariaDB MariaDB Server
Copyright (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 May 5 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 May 5 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 at https://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 PostgreSQL Driver = /usr/lib/psqlodbc.so Setup = /usr/lib/libodbcpsqlS.so Driver64 = /usr/lib64/psqlodbc.so Setup64 = /usr/lib64/libodbcpsqlS.so FileUsage = 1 # Driver from the mysql-connector-odbc package # Setup from the unixODBC package [MySQL] Description = ODBC for MySQL Driver = /usr/lib/libmyodbc5.so Setup = /usr/lib/libodbcmyS.so Driver64 = /usr/lib64/libmyodbc5.so Setup64 = /usr/lib64/libodbcmyS.so FileUsage = 1 root@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.6 root@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 at 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.gz root@centos-6.5-minimal:[Mon May 05 13:33:41][/opt/installs]$ tar xvf sqliteodbc-0.996.tar.gz root@centos-6.5-minimal:[Mon May 05 13:33:50][/opt/installs]$ cd sqliteodbc-0.996 root@centos-6.5-minimal:[Mon May 05 13:34:22][/opt/installs/sqliteodbc-0.996]$ ./configure checking build system type... x86_64-redhat-linux-gnu checking host system type... x86_64-redhat-linux-gnu checking for gcc... no checking for cc... no checking for cc... no checking for cl... no configure: error: no acceptable C compiler found in $PATH See `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 M Installed size: 272 M Is this ok [y/N]: y Complete! root@centos-6.5-minimal:[Mon May 05 13:54:57][/opt/installs/sqliteodbc-0.996]$ ./configure . .. checking for sqlite3_close_v2 in -lsqlite3... no no configure: WARNING: SQLite4 header file and source not found configure: 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-devel Installed: 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... no configure: 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-devel root@centos-6.5-minimal:[Mon May 05 13:58:11][/opt/installs/sqliteodbc-0.996]$ ./configure .. checking for gmtime_r... yes configure: creating ./config.status config.status: creating Makefile config.status: creating sqliteodbc.spec config.status: creating debian/changelog root@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.a sqlite3odbc.o ranlib .libs/libsqlite3odbc.a creating 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 -j3 install
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.ini root@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 PostgreSQL Driver = /usr/lib/psqlodbc.so Setup = /usr/lib/libodbcpsqlS.so Driver64 = /usr/lib64/psqlodbc.so Setup64 = /usr/lib64/libodbcpsqlS.so FileUsage = 1 # Driver from the mysql-connector-odbc package # Setup from the unixODBC package [MySQL] Description = ODBC for MySQL Driver = /usr/lib/libmyodbc5.so Setup = /usr/lib/libodbcmyS.so Driver64 = /usr/lib64/libmyodbc5.so Setup64 = /usr/lib64/libodbcmyS.so FileUsage = 1 # added for MySQL Connect engine [SQLite] Description=SQLite ODBC Driver Driver=/usr/local/lib/libsqliteodbc.so Setup=/usr/local/lib/libsqliteodbc.so Threading=2
Check the SELinux status:
root@centos-6.5-minimal:[Mon May 05 14:02:48][/opt/installs/sqliteodbc-0.996]$ sestatus SELinux status: enabled SELinuxfs mount: /selinux Current mode: enforcing Mode from config file: enforcing Policy version: 24 Policy 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.db root@centos-6.5-minimal:[Mon May 05 14:04:45][/var/lib/mysql]$ chown -R mysql:mysql /var/lib/mysql.connnect.db root@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.db SQLite version 3.6.20 Enter ".help" for instructions Enter 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> . tables DEPARTMENT sqlite> select * from DEPARTMENT; 1|sales|1 2|sales|2 3|sales|3 4|marketing|4 5|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 -lrth total 4.0K -rw-r--r--. 1 root root 3.0K May 5 14:45 maria-sqlite3.db root@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 -lrth total 4.0K -rw-r--r--. 1 mysql mysql 3.0K May 5 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 4 Server version: 10.0.10-MariaDB MariaDB Server Copyright (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 May 5 14:47:26 2014][(none)]> create database sqlite3; Query OK, 1 row affected (0.01 sec) mysql root@centos-6.5-minimal:[Mon May 5 14:47:28 2014][(none)]> use sqlite3; Database changed mysql root@centos-6.5-minimal:[Mon May 5 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.so Setup=/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.ini root@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 PostgreSQL Driver = /usr/lib/psqlodbc.so Setup = /usr/lib/libodbcpsqlS.so Driver64 = /usr/lib64/psqlodbc.so Setup64 = /usr/lib64/libodbcpsqlS.so FileUsage = 1 # Driver from the mysql-connector-odbc package # Setup from the unixODBC package [MySQL] Description = ODBC for MySQL Driver = /usr/lib/libmyodbc5.so Setup = /usr/lib/libodbcmyS.so Driver64 = /usr/lib64/libmyodbc5.so Setup64 = /usr/lib64/libodbcmyS.so FileUsage = 1 [SQLite] Description=SQLite ODBC Driver Driver=/usr/local/lib/libsqlite3odbc.so Setup=/usr/local/lib/libsqlite3odbc.so Threading=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 May 5 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.db
mysql root@centos-6.5-minimal:[Mon May 5 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 May 5 15:05:06 2014][sqlite3]> show create table my_dept \G
*************************** 1. row ***************************
Table: my_dept
Create 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 May 5 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/enforce mysql root@centos-6.5-minimal:[Mon May 5 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 May 5 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.20 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .tables DEPARTMENT sqlite> select * from DEPARTMENT; 1|sales|1 2|sales|2 3|sales|3 4|marketing|4 5|marketing|5 6|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 May 5 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 May 5 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 May 5 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.so
PLUGIN_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.5
1 row in set (0.06 sec)
mysql root@centos-6.5-minimal:[Mon May 5 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 May 5 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 May 5 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 May 5 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 May 5 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
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_audit-1.1.7/linux-64/server_audit.so --2014-05-05 15:20:05-- https://downloads.skysql.com/enterprise/MariaDB-Audit-Plugin/server_audit-1.1.7/linux-64/server_audit.so Resolving downloads.skysql.com... 46.105.96.115, 2001:41d0:2:cf73::1 Connecting to downloads.skysql.com|46.105.96.115|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 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 -lrth total 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 root 20M 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 root 15M 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 root 19K 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 root 20K 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 root 13K 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 May 6 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,,,0 20140506 13:43:23,centos-6.5-minimal,root,localhost,7,32,QUERY,,'select @@version_comment limit 1',0 20140506 13:43:23,centos-6.5-minimal,root,localhost,7,33,QUERY,,'select USER()',0 20140506 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
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.5 root@centos-6.5-minimal:[Tue May 06 13:47:05][/usr/lib64/mysql/plugin]$ mv server_audit.so.1 server_audit.so root@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 May 6 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 May 6 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 May 6 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 May 6 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
So, that was the test, with MariaDB 10.0.10 and its CONNECT ENGINE audited by the MariaDB Audit Plugin version 1.1.7.