MySQL backup and cleanup bash scripts with mydumper

June 28, 2014 1 comment

1. Backup script

#!/bin/sh
# Fri Jun 27 10:44:49 2014
# done by dragkh
# usage: 
# cat /etc/cron.d/backupmysql 
# 0  3  *  *  *       root    /root/bin/clean.backup.hyperion.mysql.mydumper.daily.sh >>  /var/log/clean.backup.${HOSTNAME}.mysql.mydumper.daily.log 2>&1
# 35  3  *  *  *       root    /root/bin/backup.hyperion.mysql.mydumper.daily.sh >> /var/log/backup.${HOSTNAME}.mysql.mydumper.daily.log 2>&1

ROOT_BACKUP_DIR="/home/mydumper"

seik_date () {
if [ -z $1 ]
then
# cdate=`date +%Y-%m-%d\ %H:%M:%S\ %Z`; export cdate; echo $cdate
cdate=`date -R`; export cdate; echo $cdate
else

if [ -z ${2} ]
then
cdate=`date +%Y-%m-%d.%H.%M.%S`; export cdate; echo $cdate
else
cdate=`date "+%Y-%m-%d %H:%M:%S"`; export cdate; echo $cdate
fi

fi
}


function check_dir {
 test ! -d "${1}" && mkdir -p "${1}"
}


function set_cpu_threads {
    # set the threads one less than the existing 
    threads=$(cat /proc/cpuinfo  |  grep processor | tail -1 | awk '{print $3}')
    test $threads -lt 1 && threads=1
}

function dump_schema {
    mysqldump -d --dump-date --all-databases > ${DATA_DIR}/${HOSTNAME}.only.sql
}


function dump_data {
    echo "$(seik_date f) : executing : mydumper -o $DATA_DIR --long-query-guard 120 -r 100000 -c -e -m -L ${DATA_DIR}/mysql-backup.log -t ${threads} -v 3"
    mydumper -o $DATA_DIR --long-query-guard 120 -r 100000 -c -e -m -L ${DATA_DIR}/mysql-backup.log -t ${threads} -v 3
}

DATA_DIR="${ROOT_BACKUP_DIR}/$(seik_date d)"
check_dir "${DATA_DIR}" && echo "$(seik_date f) : ${DATA_DIR} is missing, creating it now .."
set_cpu_threads
echo "$(seik_date f) : star dumping the schema at ${DATA_DIR}.."
dump_schema && echo "$(seik_date f) : end dumping the schema at ${DATA_DIR} .."
echo "$(seik_date f) : start dumping the data at ${DATA_DIR} via ${threads} parallel threads .."
dump_data && echo "$(seik_date f) : end dumping the data at ${DATA_DIR} via ${threads} parallel threads .."

2. Clean up script keeping always backup directories intact

#!/bin/bash
# Sat Jun 28 03:16:38 EEST 2014
# done by dragkh
# usage: 
# cat /etc/cron.d/backupmysql
# 0       3       *       *       *       root    /root/bin/clean.backup.hyperion.mysql.mydumper.daily.sh >> /var/log/clean.backup.${HOSTNAME}.mysql.mydumper.daily.log 2>&1
# 35      3      *       *       *       root    /root/bin/backup.hyperion.mysql.mydumper.daily.sh >> /var/log/backup.${HOSTNAME}.mysql.mydumper.daily.log 2>&1

ROOT_BACKUP_DIR="/home/mydumper"

seik_date () {
if [ -z $1 ]
then
cdate=`date -R`; export cdate; echo $cdate
else

if [ -z ${2} ]
then
cdate=`date +%Y-%m-%d.%H.%M.%S`; export cdate; echo $cdate
else
cdate=`date "+%Y-%m-%d %H:%M:%S"`; export cdate; echo $cdate
fi

fi
}

day_limit=7;  
ls -t ${ROOT_BACKUP_DIR} | \
while read dir
do 
    ((dir_num++))
    test $dir_num -gt $day_limit && test -d "${ROOT_BACKUP_DIR}/${dir}" &&  rm -rf "${dir}" && echo "$(seik_date d) : removed [${dir_num}]::[${dir}]" && continue 
    test -d "${ROOT_BACKUP_DIR}/${dir}" && echo "$(seik_date d) : skipping [${dir_num}]::[${dir}]"
done

Fix 32 inch LCD LG TV model 32LG5600 problem menu on screen keeps popping up and disappearing

May 11, 2014 2 comments

Recently I had issues with my 32 inch LCD LG TV /32LG5600/. I was about to order new TV … :)
I read some posts like from the Google search Google Search LG TV popping up menu on screen and disappearing and I decided to disable the TV set side buttons.
All the procedure took 10 minutes, including the clean up.
All the photos about you may check here https://plus.google.com/photos/115378324869440910839/albums/6012302457181181569

At https://picasaweb.google.com/115378324869440910839/FixLGTV32LG5600?noredirect=1#6012302457178078274 you may see the buttons at the top of the photo.

At https://picasaweb.google.com/115378324869440910839/FixLGTV32LG5600?noredirect=1#6012302457002999506 you will see the side buttons before to disconnect them.

At https://picasaweb.google.com/115378324869440910839/FixLGTV32LG5600?noredirect=1#6012302454434989874 you see the disconnected side buttons, which fixed the issue permanently.

No more popping up menus on the screen.

Slackware4Life :)

Compile at CentOS 6.5 the new MySQL webscalesql-5.6.17 branch by Facebook, Google, LinkedIn, and Twitter

March 31, 2014 Leave a comment

http://webscalesql.org/

yeah , big buzz around that one :)

So I decided to check the install process:

1. Clone the repo from

root@webscalesql-5.6.clean:[Mon Mar 31 11:37:11][~]$ cd /opt/
root@webscalesql-5.6.clean:[Mon Mar 31 11:37:15][/opt]$ mkdir installs
root@webscalesql-5.6.clean:[Mon Mar 31 11:37:17][/opt]$ cd installs/
root@webscalesql-5.6.clean:[Mon Mar 31 11:37:19][/opt/installs]$ git clone https://github.com/webscalesql/webscalesql-5.6.git
Initialized empty Git repository in /opt/installs/webscalesql-5.6/.git/
remote: Counting objects: 30397, done.
remote: Compressing objects: 100% (12678/12678), done.
remote: Total 30397 (delta 18716), reused 27620 (delta 16936)
Receiving objects: 100% (30397/30397), 47.99 MiB | 460 KiB/s, done.
Resolving deltas: 100% (18716/18716), done.

2. Update the CentOs install just in case before to play with it:

root@webscalesql-5.6.clean:[Mon Mar 31 11:40:35][/opt/installs]$ yum update -y 

3. Compile it :
Note : The final part of the prompt here “[webscalesql-5.6.17]” id the actual git branch version.

root@webscalesql-5.6.clean:[Mon Mar 31 12:23:22][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ cmake . -DBUILD_CONFIG=mysql_release -DENABLE_DOWNLOADS=1
-bash: cmake: command not found

3.1 No Cmake ? The CentOS install was done without cmake etc dev tools as at production they are not needed.
Now, we need it: Install cmake

root@webscalesql-5.6.clean:[Mon Mar 31 12:23:34][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ yum install cmake -y 
....
Installed:
  cmake.x86_64 0:2.6.4-5.el6  
root@webscalesql-5.6.clean:[Mon Mar 31 12:24:10][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ cmake --version
cmake version 2.6-patch 4                                                                                                                                                               

3.2 Try to compile it again?

root@webscalesql-5.6.clean:[Mon Mar 31 12:24:20][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ cmake . -DBUILD_CONFIG=mysql_release -DENABLE_DOWNLOADS=1
-- Running cmake version 2.6.4
-- The C compiler identification is unknown
-- The CXX compiler identification is unknown
CMake Error: your C compiler: "CMAKE_C_COMPILER-NOTFOUND" was not found.   Please set CMAKE_C_COMPILER to a valid compiler path or name.
CMake Error: your CXX compiler: "CMAKE_CXX_COMPILER-NOTFOUND" was not found.   Please set CMAKE_CXX_COMPILER to a valid compiler path or name.

No luck, the development tools are missing
3.3 Install ‘Development Tools’ group

root@webscalesql-5.6.clean:[Mon Mar 31 12:24:54][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ yum groupinstall 'Development Tools'
Installed:
  autoconf.noarch 0:2.63-5.1.el6      automake.noarch 0:1.11.1-4.el6      bison.x86_64 0:2.4.1-5.el6                       byacc.x86_64 0:1.9.20070509-7.el6   cscope.x86_64 0:15.6-6.el6           
  ctags.x86_64 0:5.8-2.el6            diffstat.x86_64 0:1.51-2.el6        doxygen.x86_64 1:1.6.1-6.el6                     flex.x86_64 0:2.5.35-8.el6          gcc.x86_64 0:4.4.7-4.el6             
  gcc-c++.x86_64 0:4.4.7-4.el6        gcc-gfortran.x86_64 0:4.4.7-4.el6   indent.x86_64 0:2.2.10-7.el6                     intltool.noarch 0:0.41.0-1.1.el6    libtool.x86_64 0:2.2.6-15.5.el6      
  patchutils.x86_64 0:0.3.1-3.1.el6   rcs.x86_64 0:5.7-37.el6             redhat-rpm-config.noarch 0:9.0.3-42.el6.centos   rpm-build.x86_64 0:4.8.0-37.el6     subversion.x86_64 0:1.6.11-10.el6_5  
  swig.x86_64 0:1.3.40-6.el6          systemtap.x86_64 0:2.3-4.el6_5     

Dependency Installed:
  apr.x86_64 0:1.3.9-5.el6_2              apr-util.x86_64 0:1.3.9-3.el6_0.1            cloog-ppl.x86_64 0:0.15.7-1.2.el6      cpp.x86_64 0:4.4.7-4.el6       gettext-devel.x86_64 0:0.17-16.el6      
  gettext-libs.x86_64 0:0.17-16.el6       kernel-devel.x86_64 0:2.6.32-431.11.2.el6    libart_lgpl.x86_64 0:2.3.20-5.1.el6    libgcj.x86_64 0:4.4.7-4.el6    libstdc++-devel.x86_64 0:4.4.7-4.el6    
  mpfr.x86_64 0:2.4.1-6.el6               neon.x86_64 0:0.29.3-3.el6_4                 pakchois.x86_64 0:0.4-3.2.el6          ppl.x86_64 0:0.10.2-11.el6     systemtap-client.x86_64 0:2.3-4.el6_5   
  systemtap-devel.x86_64 0:2.3-4.el6_5   

3.4 Try to compile … one more time :)

root@webscalesql-5.6.clean:[Mon Mar 31 12:30:37][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ cmake . -DBUILD_CONFIG=mysql_release -DENABLE_DOWNLOADS=1
-- Running cmake version 2.6.4
-- The C compiler identification is GNU
-- The CXX compiler identification is GNU
-- Check for working C compiler: /usr/bin/gcc
-- Check for working C compiler: /usr/bin/gcc -- works
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- Check for working CXX compiler: /usr/bin/c++
-- Check for working CXX compiler: /usr/bin/c++ -- works
-- Detecting CXX compiler ABI info
-- Detecting CXX compiler ABI info - done
CMake Error at cmake/build_configurations/mysql_release.cmake:43 (MESSAGE):
        aio is required on Linux, you need to install the required library:

            Debian/Ubuntu:              apt-get install libaio-dev
            RedHat/Fedora/Oracle Linux: yum install libaio-devel
            SuSE:                       zypper install libaio-devel

          If you really do not want it, pass -DIGNORE_AIO_CHECK to cmake.
          
Call Stack (most recent call first):
  CMakeLists.txt:94 (INCLUDE)
-- Configuring incomplete, errors occurred!

So install libaio libs ..

root@webscalesql-5.6.clean:[Mon Mar 31 12:32:43][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ yum install libaio-devel

Installed:
  libaio-devel.x86_64 0:0.3.107-10.el6                                                                                                                                                               

3.5 Try to compile … again
Now the CMakeCache.txt file has to be removed in a view to start form scratch:

root@webscalesql-5.6.clean:[Mon Mar 31 12:33:23][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ rm CMakeCache.txt 
rm: remove regular file `CMakeCache.txt'? y
root@webscalesql-5.6.clean:[Mon Mar 31 12:33:57][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ cmake . -DBUILD_CONFIG=mysql_release -DENABLE_DOWNLOADS=1
--
---
-- Could NOT find Curses  (missing:  CURSES_LIBRARY CURSES_INCLUDE_PATH)
CMake Error at cmake/readline.cmake:82 (MESSAGE):
  Curses library not found.  Please install appropriate package,

      remove CMakeCache.txt and rerun cmake.On Debian/Ubuntu, package name is libncurses5-dev, on Redhat and derivates it is ncurses-devel.
Call Stack (most recent call first):
  cmake/readline.cmake:125 (FIND_CURSES)
  cmake/readline.cmake:194 (FIND_SYSTEM_LIBEDIT)
  CMakeLists.txt:397 (MYSQL_CHECK_READLINE)
-- Configuring incomplete, errors occurred!

So follow up the hint and installed the libs:

root@webscalesql-5.6.clean:[Mon Mar 31 12:34:25][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ yum install ncurses-devel

Installed:
  ncurses-devel.x86_64 0:5.7-3.20090208.el6                                                                                                                                                          

3.6 Try to compile … :)

root@webscalesql-5.6.clean:[Mon Mar 31 12:35:44][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ rm CMakeCache.txt 
rm: remove regular file `CMakeCache.txt'? y
root@webscalesql-5.6.clean:[Mon Mar 31 12:35:51][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ cmake . -DBUILD_CONFIG=mysql_release -DENABLE_DOWNLOADS=1
...
....
.....
-- Found Curses: /usr/lib64/libcurses.so
-- Looking for tputs in /usr/lib64/libcurses.so
-- Looking for tputs in /usr/lib64/libcurses.so - found
-- Looking for include files HAVE_READLINE_HISTORY_H
-- Looking for include files HAVE_READLINE_HISTORY_H - not found.
CMake Error at cmake/readline.cmake:186 (MESSAGE):
  Cannot find libreadline!
Call Stack (most recent call first):
  cmake/readline.cmake:194 (FIND_SYSTEM_LIBEDIT)
  CMakeLists.txt:397 (MYSQL_CHECK_READLINE)
-- Configuring incomplete, errors occurred!

readline-devel was needed indeed even we installed ncurses libs

root@webscalesql-5.6.clean:[Mon Mar 31 12:37:20][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ yum install readline-devel

Installed:
  readline-devel.x86_64 0:6.0-4.el6                                                                                                                                                                  

3.7 Try to compile … :)

root@webscalesql-5.6.clean:[Mon Mar 31 12:37:38][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ rm CMakeCache.txt 
rm: remove regular file `CMakeCache.txt'? y
root@webscalesql-5.6.clean:[Mon Mar 31 12:37:47][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ cmake . -DBUILD_CONFIG=mysql_release -DENABLE_DOWNLOADS=1
-- Successfully downloaded http://googlemock.googlecode.com/files/gmock-1.6.0.zip to /opt/installs/webscalesql-5.6/source_downloads
CMake Error: Problem with tar_extract_all(): Invalid argument
CMake Error: Problem extracting tar: /opt/installs/webscalesql-5.6/source_downloads/gmock-1.6.0.zip
-- Library mysqlserver depends on OSLIBS -lpthread;m;rt;crypt;dl;aio
-- Configuring done
-- Generating done
-- Build files have been written to: /opt/installs/webscalesql-5.6

3.8 Fix CMake Error: Problem with tar_extract_all(): Invalid argument at extracting the gmock-1.6.0.zip archive.

root@webscalesql-5.6.clean:[Mon Mar 31 12:46:05][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ cd source_downloads/
root@webscalesql-5.6.clean:[Mon Mar 31 12:46:08][/opt/installs/webscalesql-5.6/source_downloads] [webscalesql-5.6.17]$ ls
gmock-1.6.0.zip
root@webscalesql-5.6.clean:[Mon Mar 31 12:46:08][/opt/installs/webscalesql-5.6/source_downloads] [webscalesql-5.6.17]$ unzip -a gmock-1.6.0.zip 

3.9 Try to compile it …. :)

root@webscalesql-5.6.clean:[Mon Mar 31 12:47:10][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ rm CMakeCache.txt 
rm: remove regular file `CMakeCache.txt'? y
root@webscalesql-5.6.clean:[Mon Mar 31 12:47:12][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ cmake . -DBUILD_CONFIG=mysql_release -DENABLE_DOWNLOADS=1
 -- GMOCK_SOURCE_DIR:/opt/installs/webscalesql-5.6/source_downloads/gmock-1.6.0
-- Performing Test HAVE_NO_NULL
-- Performing Test HAVE_NO_NULL - Failed
-- Performing Test HAVE_NO_UNUSED_TYPEDEFS
-- Performing Test HAVE_NO_UNUSED_TYPEDEFS - Failed
-- GTEST_LIBRARIES:gmock;gtest
-- Library mysqlserver depends on OSLIBS -lpthread;m;rt;crypt;dl;aio
-- Configuring done
-- Generating done
-- Build files have been written to: /opt/installs/webscalesql-5.6
root@webscalesql-5.6.clean:[Mon Mar 31 12:47:20][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ make
.
..
...
Linking C static library libmysys.a
[ 15%] Built target mysys
Scanning dependencies of target dbug
[ 15%] Building C object dbug/CMakeFiles/dbug.dir/dbug.c.o
Linking C static library libdbug.a
[ 15%] Built target dbug
Scanning dependencies of target mysys_ssl
[ 15%] Building CXX object mysys_ssl/CMakeFiles/mysys_ssl.dir/crypt_genhash_impl.cc.o
cc1plus: error: unrecognized command line option "-std=c++11"
make[2]: *** [mysys_ssl/CMakeFiles/mysys_ssl.dir/crypt_genhash_impl.cc.o] Error 1
make[1]: *** [mysys_ssl/CMakeFiles/mysys_ssl.dir/all] Error 2
make: *** [all] Error 2

4 Fix -std=c++11 error.
This error is ude to old gcc c++. That is fixable by installing the CentOS devtools:

root@webscalesql-5.6.clean:[Mon Mar 31 12:47:25]:[/opt/installs/webscalesql-5.6]$ yum info  gcc-c++
Loaded plugins: fastestmirror, security
Loading mirror speeds from cached hostfile
 * base: sunsite.rediris.es
 * extras: sunsite.rediris.es
 * updates: sunsite.rediris.es
base                                                                                                                                                                          | 3.7 kB     00:00     
extras                                                                                                                                                                        | 3.4 kB     00:00     
testing-1.1-devtools-6                                                                                                                                                        |  951 B     00:00     
updates                                                                                                                                                                       | 3.4 kB     00:00     
Installed Packages
Name        : gcc-c++
Arch        : x86_64
Version     : 4.4.7
Release     : 4.el6
Size        : 11 M
Repo        : installed
From repo   : base
Summary     : C++ support for GCC
root@webscalesql-5.6.clean:[Mon Mar 31 12:48:39][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ g++ --version
g++ (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4)
Copyright (C) 2010 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

root@webscalesql-5.6.clean:[Mon Mar 31 12:50:11][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ cd /etc/yum.repos.d/
root@webscalesql-5.6.clean:[Mon Mar 31 12:50:30][/etc/yum.repos.d]$ wget  http://people.centos.org/tru/devtools-1.1/devtools-1.1.repo
--2014-03-31 12:50:38--  http://people.centos.org/tru/devtools-1.1/devtools-1.1.repo
Resolving people.centos.org... 204.15.73.242
Connecting to people.centos.org|204.15.73.242|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 174 [text/plain]
Saving to: “devtools-1.1.repo”

100%[===========================================================================================================================================================>] 174         --.-K/s   in 0s      

2014-03-31 12:50:39 (19.9 MB/s) - “devtools-1.1.repo” saved [174/174]

root@webscalesql-5.6.clean:[Mon Mar 31 12:50:39][/etc/yum.repos.d]$ yum repolist
Loaded plugins: fastestmirror, security
Loading mirror speeds from cached hostfile
 * base: sunsite.rediris.es
 * extras: sunsite.rediris.es
 * updates: sunsite.rediris.es
testing-1.1-devtools-6                                                                                                                                                        |  951 B     00:00     
testing-1.1-devtools-6/primary                                                                                                                                                |  13 kB     00:00     
testing-1.1-devtools-6                                                                                                                                                                         42/42
repo id                                                                                   repo name                                                                                            status
base                                                                                      CentOS-6 - Base                                                                                      6,367
extras                                                                                    CentOS-6 - Extras                                                                                       14
testing-1.1-devtools-6                                                                    testing 1.1 devtools for CentOS 6                                                                       42
updates                                                                                   CentOS-6 - Updates                                                                                     713
repolist: 7,136

root@webscalesql-5.6.clean:[Mon Mar 31 12:51:08][/etc/yum.repos.d]$ yum install devtoolset-1.1

Install      19 Package(s)

Total download size: 85 M
Installed size: 303 M
Installed:
  devtoolset-1.1.noarch 0:1-13.el6                                                                                                                                                                   

Dependency Installed:
  devtoolset-1.1-binutils.x86_64 0:2.23.51.0.3-3.el6                 devtoolset-1.1-dwz.x86_64 0:0.7-1.el6                            devtoolset-1.1-elfutils.x86_64 0:0.154-6.el6                   
  devtoolset-1.1-elfutils-libelf.x86_64 0:0.154-6.el6                devtoolset-1.1-elfutils-libs.x86_64 0:0.154-6.el6                devtoolset-1.1-gcc.x86_64 0:4.7.2-5.el6                        
  devtoolset-1.1-gcc-c++.x86_64 0:4.7.2-5.el6                        devtoolset-1.1-gcc-gfortran.x86_64 0:4.7.2-5.el6                 devtoolset-1.1-gdb.x86_64 0:7.5.0.20120926-26.el6              
  devtoolset-1.1-libquadmath-devel.x86_64 0:4.7.2-5.el6              devtoolset-1.1-libstdc++-devel.x86_64 0:4.7.2-5.el6              devtoolset-1.1-oprofile.x86_64 0:0.9.7-6.el6                   
  devtoolset-1.1-runtime.noarch 0:1-13.el6                           devtoolset-1.1-systemtap.x86_64 0:1.8-8.el6                      devtoolset-1.1-systemtap-client.x86_64 0:1.8-8.el6             
  devtoolset-1.1-systemtap-devel.x86_64 0:1.8-8.el6                  devtoolset-1.1-systemtap-runtime.x86_64 0:1.8-8.el6              devtoolset-1.1-valgrind.x86_64 1:3.8.1-3.2.el6                 

Complete!
root@webscalesql-5.6.clean:[Mon Mar 31 13:01:29][/etc/yum.repos.d]$ cd /opt/installs/webscalesql-5.6/
root@webscalesql-5.6.clean:[Mon Mar 31 13:01:48][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ rm CMakeCache.txt 
rm: remove regular file `CMakeCache.txt'? y   
root@webscalesql-5.6.clean:[Mon Mar 31 13:01:56][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ cmake . -DBUILD_CONFIG=mysql_release -DENABLE_DOWNLOADS=1
-- Configuring done
-- Generating done
-- Build files have been written to: /opt/installs/webscalesql-5.6
root@webscalesql-5.6.clean:[Mon Mar 31 13:02:33][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ make 
..
Linking C static library libdbug.a
[ 15%] Built target dbug
[ 15%] Building CXX object mysys_ssl/CMakeFiles/mysys_ssl.dir/crypt_genhash_impl.cc.o
cc1plus: error: unrecognized command line option "-std=c++11"
make[2]: *** [mysys_ssl/CMakeFiles/mysys_ssl.dir/crypt_genhash_impl.cc.o] Error 1
make[1]: *** [mysys_ssl/CMakeFiles/mysys_ssl.dir/all] Error 2
make: *** [all] Error 2

After the install of devtoos you have to enable the devtoolset-1.1 bash:

root@webscalesql-5.6.clean:[Mon Mar 31 13:04:41][/opt/installs/webscalesql-5.6] [webscalesql-5.6.17]$ scl enable devtoolset-1.1 bash
[root@webscalesql-5 webscalesql-5.6]# export PS1="\u@\H:[\d \t]:[\w]$ "
root@webscalesql-5.6.clean:[Mon Mar 31 13:05:50]:[/opt/installs/webscalesql-5.6]$ rm CMakeCache.txt 
rm: remove regular file `CMakeCache.txt'? y
root@webscalesql-5.6.clean:[Mon Mar 31 13:06:04]:[/opt/installs/webscalesql-5.6]$ cmake . -DBUILD_CONFIG=mysql_release -DENABLE_DOWNLOADS=1
-- Configuring done
-- Generating done
-- Build files have been written to: /opt/installs/webscalesql-5.6
root@webscalesql-5.6.clean:[Mon Mar 31 13:07:46]:[/opt/installs/webscalesql-5.6]$ make

Linking CXX executable my_safe_process
[100%] Built target my_safe_process
root@webscalesql-5.6.clean:[Mon Mar 31 13:36:58]:[/opt/installs/webscalesql-5.6]$ make install

The compile and the installation is done ! Finally .. :)
Done

5. Now, the post install:

root@webscalesql-5.6.clean:[Mon Mar 31 13:41:56]:[/opt/installs/webscalesql-5.6]$ cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql.server
root@webscalesql-5.6.clean:[Mon Mar 31 13:42:17]:[/opt/installs/webscalesql-5.6]$ mysql
bash: mysql: command not found

root@webscalesql-5.6.clean:[Mon Mar 31 13:42:36]:[/opt/installs/webscalesql-5.6]$ export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/scripts
root@webscalesql-5.6.clean:[Mon Mar 31 13:43:06]:[/opt/installs/webscalesql-5.6]$ mysql --version
mysql  Ver 14.14 Distrib 5.6.17, for Linux (x86_64) using readline 6.0
root@webscalesql-5.6.clean:[Mon Mar 31 13:43:10]:[/opt/installs/webscalesql-5.6]$ cd /var/lib/mysql/
root@webscalesql-5.6.clean:[Mon Mar 31 13:43:25]:[/var/lib/mysql]$ ls
root@webscalesql-5.6.clean:[Mon Mar 31 13:43:26]:[/var/lib/mysql]$ chown -R mysql .
chown: invalid user: `mysql'
root@webscalesql-5.6.clean:[Mon Mar 31 13:43:33]:[/var/lib/mysql]$ groupadd mysql
root@webscalesql-5.6.clean:[Mon Mar 31 13:43:40]:[/var/lib/mysql]$ useradd -r -g mysql mysql
root@webscalesql-5.6.clean:[Mon Mar 31 13:43:44]:[/var/lib/mysql]$ chown -R mysql .
root@webscalesql-5.6.clean:[Mon Mar 31 13:43:48]:[/var/lib/mysql]$ mysql_install_db --basedir=/usr/local/mysql --user=mysql --datadir=/var/lib/mysql
Installing MySQL system tables...2014-03-31 13:43:58 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-03-31 13:43:58 25002 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-03-31 13:43:58 25002 [Note] InnoDB: The InnoDB memory heap is disabled
2014-03-31 13:43:58 25002 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-03-31 13:43:58 25002 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-03-31 13:43:58 25002 [Note] InnoDB: Using Linux native AIO
2014-03-31 13:43:58 25002 [Note] InnoDB: Not using CPU crc32 instructions
2014-03-31 13:43:58 25002 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-03-31 13:43:58 25002 [Note] InnoDB: Completed initialization of buffer pool
2014-03-31 13:43:58 25002 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2014-03-31 13:43:58 25002 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2014-03-31 13:43:58 25002 [Note] InnoDB: Database physically writes the file full: wait...
2014-03-31 13:43:59 25002 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2014-03-31 13:44:04 25002 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2014-03-31 13:44:09 25002 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2014-03-31 13:44:09 25002 [Warning] InnoDB: New log files created, LSN=45781
2014-03-31 13:44:09 25002 [Note] InnoDB: Doublewrite buffer not found: creating new
2014-03-31 13:44:09 25002 [Note] InnoDB: Doublewrite buffer created
2014-03-31 13:44:09 25002 [Note] InnoDB: 128 rollback segment(s) are active.
2014-03-31 13:44:09 25002 [Warning] InnoDB: Creating foreign key constraint system tables.
2014-03-31 13:44:09 25002 [Note] InnoDB: Foreign key constraint system tables created
2014-03-31 13:44:09 25002 [Note] InnoDB: Creating tablespace and datafile system tables.
2014-03-31 13:44:10 25002 [Note] InnoDB: Tablespace and datafile system tables created.
2014-03-31 13:44:10 25002 [Note] InnoDB: Waiting for purge to start
2014-03-31 13:44:10 25002 [Note] InnoDB: 5.6.17 started; log sequence number 0
2014-03-31 13:44:14 25002 [Note] Binlog end
2014-03-31 13:44:14 25002 [Note] unregister_replicator OK
2014-03-31 13:44:14 25002 [Note] InnoDB: FTS optimize thread exiting.
2014-03-31 13:44:14 25002 [Note] InnoDB: Starting shutdown...
2014-03-31 13:44:15 25002 [Note] InnoDB: Shutdown completed; log sequence number 1625977
OK

Filling help tables...2014-03-31 13:44:15 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-03-31 13:44:15 25024 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-03-31 13:44:15 25024 [Note] InnoDB: The InnoDB memory heap is disabled
2014-03-31 13:44:15 25024 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-03-31 13:44:15 25024 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-03-31 13:44:15 25024 [Note] InnoDB: Using Linux native AIO
2014-03-31 13:44:15 25024 [Note] InnoDB: Not using CPU crc32 instructions
2014-03-31 13:44:15 25024 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-03-31 13:44:15 25024 [Note] InnoDB: Completed initialization of buffer pool
2014-03-31 13:44:15 25024 [Note] InnoDB: Highest supported file format is Barracuda.
2014-03-31 13:44:15 25024 [Note] InnoDB: 128 rollback segment(s) are active.
2014-03-31 13:44:15 25024 [Note] InnoDB: Waiting for purge to start
2014-03-31 13:44:15 25024 [Note] InnoDB: 5.6.17 started; log sequence number 1625977
2014-03-31 13:44:15 25024 [Note] Binlog end
2014-03-31 13:44:15 25024 [Note] unregister_replicator OK
2014-03-31 13:44:15 25024 [Note] InnoDB: FTS optimize thread exiting.
2014-03-31 13:44:15 25024 [Note] InnoDB: Starting shutdown...
2014-03-31 13:44:17 25024 [Note] InnoDB: Shutdown completed; log sequence number 1625987
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

  /usr/local/mysql/bin/mysqladmin -u root password 'new-password'
  /usr/local/mysql/bin/mysqladmin -u root -h webscalesql-5.6.clean password 'new-password'

Alternatively you can run:

  /usr/local/mysql/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

  cd . ; /usr/local/mysql/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

  cd mysql-test ; perl mysql-test-run.pl

Please report any problems at http://bugs.mysql.com/

The latest information about MySQL is available on the web at

http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

New default config file was created as /usr/local/mysql/my.cnf and
will be used by default by the server when you start it.
You may edit this file to change server settings

WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server


root@webscalesql-5.6.clean:[Mon Mar 31 13:44:17]:[/var/lib/mysql]$ chkconfig --list mysql.server
service mysql.server supports chkconfig, but is not referenced in any runlevel (run 'chkconfig --add mysql.server')

root@webscalesql-5.6.clean:[Mon Mar 31 13:45:31]:[/var/lib/mysql]$ chkconfig --add  mysql.server
root@webscalesql-5.6.clean:[Mon Mar 31 13:45:36]:[/var/lib/mysql]$ chkconfig --list mysql.server
mysql.server   	0:off	1:off	2:on	3:on	4:on	5:on	6:off

root@webscalesql-5.6.clean:[Mon Mar 31 13:47:53]:[/usr/local/mysql/support-files]$ service mysql.server status
MySQL is not running                                       [FAILED]
root@webscalesql-5.6.clean:[Mon Mar 31 13:48:04]:[/usr/local/mysql/support-files]$ 

root@webscalesql-5.6.clean:[Mon Mar 31 13:48:04]:[/usr/local/mysql/support-files]$ service mysql.server start
Starting MySQL.                                            [  OK  ]
root@webscalesql-5.6.clean:[Mon Mar 31 13:48:13]:[/usr/local/mysql/support-files]$ service mysql.server status
MySQL running (25273)                                      [  OK  ]

root@webscalesql-5.6.clean:[Mon Mar 31 13:48:17]:[/usr/local/mysql/support-files]$ mysql 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

root@webscalesql-5.6.clean:[Mon Mar 31 13:48:26]:[/usr/local/mysql/support-files]$ mysql -h 127.0.0.1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql root@127.0.0.1:[Mon Mar 31 13:49:21 2014][mysql]> select user,password,host from user;
+------+----------+-----------------------+
| user | password | host                  |
+------+----------+-----------------------+
| root |          | localhost             |
| root |          | webscalesql-5.6.clean |
| root |          | 127.0.0.1             |
| root |          | ::1                   |
|      |          | localhost             |
|      |          | webscalesql-5.6.clean |
+------+----------+-----------------------+
6 rows in set (0.00 sec)

mysql> set password=password('webscalesql');
Query OK, 0 rows affected (0.00 sec)

mysql> Bye
root@webscalesql-5.6.clean:[Mon Mar 31 13:51:17]:[/usr/local/mysql/support-files]$ nano /root/.my.cnf
root@webscalesql-5.6.clean:[Mon Mar 31 13:51:52]:[/usr/local/mysql/support-files]$ cat /root/.my.cnf 
[mysql]
user=root
password=webscalesql
prompt=mysql\_\u@\h:[\D][\d]>\_
host=127.0.0.1
root@webscalesql-5.6.clean:[Mon Mar 31 13:52:21]:[/usr/local/mysql/support-files]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql root@127.0.0.1:[Mon Mar 31 13:52:26 2014][(none)]> 

All that was done inspired by the SkySQL::Global Leaders in MariaDB & MySQL blog of Mr. Kolbe Kegel at WebScaleSQL! Will it build?

Slackware4Life!

Connect MySQL to sqlite3 database or to flat txt file via CONNECT engine of MariaDB 10.0.7 on Fedora/CentOS

January 31, 2014 Leave a comment

You might don’t know that, but the MariaDB engines are amazing ..
More info you can find here:
https://mariadb.com/kb/en/mariadb-storage-engines/

I was teased by CONNECT engine : https://mariadb.com/kb/en/connect/

What is good for ? Well, I leave that to your imagination, as you could have MariaDB as a spider connected and managing InnoDB tables, flat files, MS ACCESS databases, MS Excel files … all that in same time.
So how to use it?
By the documentation :
1. https://mariadb.com/kb/en/loading-the-connect-handler/
check if there is CONNECT already installed, if not, install it:

mysql root@mariadb-10.0.7:[Wed Jan 29 09:36:14 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         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | 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         |
| CASSANDRA          | YES     | Cassandra storage engine                                       | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)

Its not installed by default for MariaDB 10.0.7
So lets install it:

mysql root@mariadb-10.0.7:[Wed Jan 29 09:36:19 2014][(none)]> INSTALL PLUGIN CONNECT SONAME 'ha_connect';
ERROR 1126 (HY000): Can't open shared library '/usr/lib64/mysql/plugin/ha_connect.so' (errno: 2, cannot open shared object file: No such file or directory)

My first install failed and I had to install MariaDB-connect-engine package from MariaDB

$ yum info MariaDB-connect-engine
Name        : MariaDB-connect-engine
Arch        : x86_64
Version     : 10.0.7
Release     : 1
Size        : 4.3 M
Repo        : installed
From repo   : mariadb
Summary     : MariaDB: a very fast and robust SQL database server
URL         : http://mariadb.org
License     : GPL
Description : MariaDB: a very fast and robust SQL database server
            : 
            : It is GPL v2 licensed, which means you can use the it free of charge under the
            : conditions of the GNU General Public License Version 2 (http://www.gnu.org/licenses/).
            : 
            : MariaDB documentation can be found at http://kb.askmonty.org/
            : MariaDB bug reports should be submitted through https://mariadb.atlassian.net/


$ cat /etc/yum.repos.d/MariaDB.repo
# MariaDB 10.0 CentOS repository list - created 2013-12-18 18:07 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
#baseurl = ftp://ftp.ulak.net.tr/pub/MariaDB/yum/10.0/centos6-amd64
#baseurl = ftp://ftp.ulak.net.tr/pub/MariaDB/mariadb-10.0.6/yum/centos6-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1


then again

mysql root@mariadb-10.0.7:[Wed Jan 29 09:40:45 2014][(none)]> INSTALL PLUGIN CONNECT SONAME 'ha_connect';
Query OK, 0 rows affected (0.00 sec)
mysql root@mariadb-10.0.7:[Wed Jan 29 09:44:56 2014][(none)]> pager grep CONNECT
PAGER set to 'grep CONNECT'
mysql root@mariadb-10.0.7:[Wed Jan 29 09:45:24 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         |
| MyISAM             | YES     | MyISAM storage engine                                              | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)     | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables          | NO           | NO   | NO         |
| CONNECT            | YES     | Management of External Data (SQL/MED), including many file formats | NO           | NO   | NO         |
| InnoDB             | DEFAULT | 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         |
| CASSANDRA          | YES     | Cassandra storage engine                                           | NO           | NO   | NO         |
+--------------------+---------+--------------------------------------------------------------------+--------------+------+------------+
12 rows in set (0.00 sec)

Now, sqlite3
UnixODBC was installed along with the MariaDB-connect-engine package, but I was not a able to find SQLite UnixODBC driver installed. Neither I was able to get if from the Fedora repos.
Checking for the http://www.unixodbc.org/drivers.html I ended up with compiling the SQLite ODBC Driver from http://www.ch-werner.de/sqliteodbc/

The instructions are good enough for me:
1. get the source from

$ wget http://www.ch-werner.de/sqliteodbc/sqliteodbc-0.996.tar.gz

2. Untar it :

$ tar xvf sqliteodbc-0.996.tar.gz

3. go to the source directory:

$ cd sqliteodbc-0.996

4. compile it

$ ./configure
..
...
configure: WARNING: SQLite4 header file and source not found
configure: error: No usable SQLite header/library on this system

4.1 In case you get the No usable SQLite header/library error, installs the sqlite-devel package

$ yum install sqlite-devel
sqlite-devel.x86_64 0:3.6.20-1.el6 will be installed

4.2 run configure again

$ ./configure
..
...
checking for ODBC headers and libraries... no
configure: error: ODBC header files and/or libraries not found

4.3 In case you get error: ODBC header files and/or libraries not found, install the unixODBC-devel package:

$ yum install unixODBC-devel
Package unixODBC-devel.x86_64 0:2.2.14-12.el6_3 will be installed

4.4 run configure again:

$ ./configure
make 
make install

5. Check the installed ODBC drivers:

$ odbcinst -q -d
[PostgreSQL]
[MySQL]
$ 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

6. Install the sqlite3 odbc driver:

$ nano /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/libsqliteodbc.so
Setup=/usr/local/lib/libsqliteodbc.so
Threading=2
 
$ odbcinst -q -d
[PostgreSQL]
[MySQL]
[SQLite]

So the SQLite ODBC driver was installed.

In a view to trace the CONNECT engine , its good to create connect.ini file in the mysql directory

$ cat /var/lib/mysql/connect.ini 
[CONNECT]
Trace=2

The CONNECT engine errors will be in the mysql error.log, in my case mariadb-10.0.7.err

How to create a MySQL table using sqlite3 database?
create the sqlite3 table :

root@mariadb-10.0.7:[Wed Jan 29 10:57:39][/usr/lib64/mysql]$ 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> .tables
DEPARTMENT
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);

note,  its in /usr/lib64/mysql
its good to place the database in a place where the mysql user can read/write it.
root@mariadb-10.0.7:[Wed Jan 29 11:04:14][/usr/lib64/mysql]$ mv maria-sqlite3.db /tmp/
root@mariadb-10.0.7:[Wed Jan 29 11:04:20][/usr/lib64/mysql]$ chmod 0777 /tmp/maria-sqlite3.db
root@mariadb-10.0.7:[Wed Jan 29 11:07:26][/usr/lib64/mysql]$ mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.0.7-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql root@mariadb-10.0.7:[Wed Jan 29 11:07:27 2014][(none)]> create database sqlite3;
Query OK, 1 row affected (0.00 sec)

mysql root@mariadb-10.0.7:[Wed Jan 29 11:07:39 2014][(none)]> use sqlite3                                                                                                                            Database changed
mysql root@mariadb-10.0.7:[Wed Jan 29 11:07:57 2014][sqlite3]> create table my_dept engine=CONNECT table_type=ODBC tabname='DEPARTMENT' Connection='Driver=SQLite;Database=/tmp/maria-sqlite3.db;version=3;';
Query OK, 0 rows affected (0.13 sec)

mysql root@mariadb-10.0.7:[Wed Jan 29 11:08:03 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)

mysql root@mariadb-10.0.7:[Wed Jan 29 11:08:41 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=/tmp/maria-sqlite3.db;version=3;' `TABLE_TYPE`='ODBC' `TABNAME`='DEPARTMENT'
1 row in set (0.00 sec)

In case you have the CONNECT engine trace set to 2, you will see in the mysql error log something like:

 DBMS: SQLite, Version: 3.6.20, rc=0
ODBCColumns: max=4096 len=0,0,0
Getting col results ncol=12
Columns: NBCOL=12 NBLIN=3
s_init: CREATE TABLE whatever (`ID` INT(9) NOT NULL,`DEPT` VARCHAR(50) NOT NULL,`EMP_ID` INT(9) NOT NULL) TABLE_TYPE='ODBC' TABNAME='DEPARTMENT' CONNECTION='Driver=SQLite;Database=/tmp/maria-sqlite3.db;version=3;'
ColDB: am=100 colname=ID tabname=my_dept num=0
cdp(1).Name=ID cp=(nil)
 making new ODBCCOL C1 ID at 0x7f1259400420
colp=0x7f1259400420
ColDB: am=100 colname=DEPT tabname=my_dept num=0
cdp(2).Name=DEPT cp=(nil)
 making new ODBCCOL C2 DEPT at 0x7f12594004b8
colp=0x7f12594004b8
ColDB: am=100 colname=EMP_ID tabname=my_dept num=0
cdp(3).Name=EMP_ID cp=(nil)
 making new ODBCCOL C3 EMP_ID at 0x7f1259400550
colp=0x7f1259400550
....
DBMS: SQLite, Version: 3.6.20, rc=0
Prepare hstmt=0x7f12ebc59f00 INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (?,?,?)
 setting INTEGER to: 1
 setting INTEGER to: 1
S1000: [unixODBC][SQLite]attempt to write a readonly database (8), Native=8
ODBC CloseDB: closing my_dept

In case you get read only table :

mysql root@mariadb-10.0.7:[Wed Jan 29 11:09:51 2014][sqlite3]> insert into my_dept (ID,DEPT,EMP_ID) values(6,'IT-DBA',6);
ERROR 1296 (HY000): Got error 122 '[unixODBC][SQLite]attempt to write a readonly database (8)' from CONNECT

It seems you have your SQLite db in a folder where your database user /mysql/ must have write permissions,but does not.
Most probably you have selinux activated.
How to fix that without disabling the selinux? The best way is move the SQLite db file to the mysql data directory : /var/lib/mysql/, or to create a separated directory where mysql shell user has write permissions.

mysql root@mariadb-10.0.6:[Fri Jan 31 12:06:54 2014][sqlite3]> insert into my_dept (ID,DEPT,EMP_ID) values (6,'mysql',6);
ERROR 1296 (HY000): Got error 122 '[unixODBC][SQLite]attempt to write a readonly database (8)' from CONNECT
mysql root@mariadb-10.0.6:[Fri Jan 31 12:07:27 2014][sqlite3]> drop table my_dept;
Query OK, 0 rows affected (0.00 sec)

Move the db file to MySQL data directory :

root@mariadb-10.0.6:[Fri Jan 31 12:09:15][/var/lib/mysql]$ cp -rp /tmp/maria-sqlite3.db  ./
mysql root@mariadb-10.0.6:[Fri Jan 31 12:08:17 2014][sqlite3]> create table my_dept_new  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.11 sec)

mysql root@mariadb-10.0.6:[Fri Jan 31 12:09:57 2014][sqlite3]> select * from my_dept_new;
+----+------------+--------+
| ID | DEPT       | EMP_ID |
+----+------------+--------+
|  1 | sales      |      1 |
|  2 | sales      |      2 |
|  3 | sales      |      3 |
|  4 | marketing  |      4 |
|  5 | marketing  |      5 |
|  7 | IT-SUPPORT |      7 |
+----+------------+--------+
6 rows in set (0.01 sec)

mysql root@mariadb-10.0.6:[Fri Jan 31 12:10:02 2014][sqlite3]> insert into my_dept_new (ID,DEPT,EMP_ID) values (6,'mysql',6);                                                                        Query OK, 1 row affected (0.08 sec)

mysql root@mariadb-10.0.6:[Fri Jan 31 12:10:07 2014][sqlite3]> select * from my_dept_new;
+----+------------+--------+
| ID | DEPT       | EMP_ID |
+----+------------+--------+
|  1 | sales      |      1 |
|  2 | sales      |      2 |
|  3 | sales      |      3 |
|  4 | marketing  |      4 |
|  5 | marketing  |      5 |
|  7 | IT-SUPPORT |      7 |
|  6 | mysql      |      6 |
+----+------------+--------+
7 rows in set (0.01 sec)

So now we have R/W sqlite3 table managed by MariaDB 10.0.7

PostgreSQL 9.2.3 system queries

January 15, 2014 Leave a comment

1. check child tables which inherit a base partitioned table

	SELECT 
	pg_inherits.*
	, c.relname AS child
	, p.relname AS parent
	FROM
	pg_inherits JOIN pg_class AS c ON (inhrelid=c.oid)
	JOIN pg_class as p ON (inhparent=p.oid) 
	where true 
	and p.relname = 'bsms_in'
	--    and c.relname = %(inherited_table)s
	;
 inhrelid | inhparent | inhseqno |      child       | parent  
----------+-----------+----------+------------------+---------
 51790061 |   8753326 |        1 | bsms_in_p2014w02 | bsms_in
 52461300 |   8753326 |        1 | bsms_in_p2014w03 | bsms_in
 53187892 |   8753326 |        1 | bsms_in_p2014w04 | bsms_in
(3 rows)

2. How to drop old PostgreSQL table which sequence is used by some tables that inherited its structure.
Example of the issue:

dragkh@aegir.darkstar.org:5433::aws_bsms=[Mon Apr 14 01:08:56 EEST 2014]# drop table bsms_in_old_big;
ERROR:  cannot drop table bsms_in_old_big column id because other objects depend on it
DETAIL:  default for table bsms_in_p2014w01 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w02 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w03 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w04 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w05 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w06 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w07 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w08 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w09 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w10 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w11 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w12 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w13 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w14 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w15 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w16 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w17 column id depends on sequence bsms_in_id_seq
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
Time: 9.258 ms

Get The list of the sequences and the corresponding tables that are their “OWNERS”

SELECT s.relname as sequence_name,  
       n.nspname as sequence_schema,  
       t.relname as related_table, 
       a.attname as related_column 
  FROM pg_class s, pg_depend d, pg_class t, pg_attribute a, pg_namespace n 
  WHERE s.relkind     = 'S' 
    AND n.oid         = s.relnamespace 
    AND d.objid       = s.oid 
    AND d.refobjid    = t.oid 
    AND (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum) order by sequence_name;

Free the sequences :
more info about “owned by” here : http://www.postgresql.org/docs/9.2/static/sql-altersequence.html
Specifying OWNED BY NONE removes any existing association, making the sequence “free-standing”.

dragkh@aegir.darkstar.org:5433::aws_bsms=[Mon Apr 14 01:25:00 EEST 2014]# ALTER SEQUENCE bsms_in_id_seq OWNED BY NONE;
ALTER SEQUENCE
Time: 1.085 ms
dragkh@aegir.darkstar.org:5433::aws_bsms=[Mon Apr 14 01:25:13 EEST 2014]# drop table bsms_in_old_big;
DROP TABLE
Time: 18.084 ms

Slackre4Life

Categories: PostgreSQL Tags:

How to install MySQL 10.0.6-MariaDB and to compile lib_mysqludf_preg on CentOS 6.4

December 18, 2013 Leave a comment

In short, during an optimization or a MySQL query involving a lot of replace(replace(replace…))) I decided to compile PREG as lib_mysqludf_preg from UDF Repository for MySQL in a view to use PCRE functions directly in MySQL.

Too bad MySQL still cant not use functions for index creation, but this is not the theme of this post.

So, fist install MariaDB 10.0.6:
set the MariaDB 10.0.6 repo, I used the ftp mirror at ftp.ulak.net.tr


root@seik-centos-01:[Wed Dec 18 23:55:12][/tmp]$ cat /etc/yum.repos.d/MariaDB.repo 
# MariaDB 10.0 CentOS repository list - created 2013-12-18 18:07 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
#baseurl = http://yum.mariadb.org/10.0/centos6-amd64
baseurl = ftp://ftp.ulak.net.tr/pub/MariaDB/mariadb-10.0.6/yum/centos6-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
root@seik-centos-01:[Wed Dec 18 23:56:39][/tmp]$ yum repolist | grep -i maria
mariadb          MariaDB                                                      10
root@seik-centos-01:[Wed Dec 18 19:25:49[/tmp]$ yum install MariaDB-server MariaDB-client MariaDB-cassandra-engine  MariaDB-devel MariaDB-shared -y
root@seik-centos-01:[Wed Dec 18 19:31:26][/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@seik-centos-01:[Wed Dec 18 19:32:13][/etc/yum.repos.d]$ service mysql 
Usage: mysql  {start|stop|restart|reload|force-reload|status|configtest}  [ MySQL server options ]
root@seik-centos-01:[Wed Dec 18 19:32:18][/etc/yum.repos.d]$ service mysql  configtest
Testing MySQL configuration syntax SUCCESS! Syntax OK
root@seik-centos-01:[Wed Dec 18 19:32:26][/etc/yum.repos.d]$ service mysql start 
Starting MySQL. SUCCESS! 
root@seik-centos-01:[Wed Dec 18 19:32:32][/etc/yum.repos.d]$ service mysql status
 SUCCESS! MySQL running (1636)
root@seik-centos-01:[Wed Dec 18 19:32:37][/etc/yum.repos.d]$ mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.0.6-MariaDB MariaDB Server

How to compile lib_mysqludf_preg:


root@seik-centos-01:[Wed Dec 18 21:08:50]$ yum install git -y
root@seik-centos-01:[Wed Dec 18 21:09:12]$ mkdir -p /opt/installs
root@seik-centos-01:[Wed Dec 18 21:09:14]$ cd /opt/installs
root@seik-centos-01:[Wed Dec 18 21:10:50][/opt/installs]$ git clone https://github.com/mysqludf/lib_mysqludf_preg.git
Initialized empty Git repository in /opt/installs/lib_mysqludf_preg/.git/
remote: Counting objects: 296, done.
remote: Compressing objects: 100% (139/139), done.
remote: Total 296 (delta 192), reused 256 (delta 155)
Receiving objects: 100% (296/296), 504.10 KiB | 311 KiB/s, done.
Resolving deltas: 100% (192/192), done.
root@seik-centos-01:[Wed Dec 18 21:11:20][/opt/installs]$ cd lib_mysqludf_preg/

In case on configure you get error: no acceptable C compiler found, you have to set up the development environment

root@seik-centos-01:[Wed Dec 18 21:11:53][/opt/installs/lib_mysqludf_preg]$ ./configure 
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for a thread-safe mkdir -p... /bin/mkdir -p
checking for gawk... gawk
checking whether make sets $(MAKE)... yes
checking whether make supports nested variables... yes
checking build system type... x86_64-unknown-linux-gnu
checking host system type... x86_64-unknown-linux-gnu
checking how to print strings... printf
checking for style of include used by make... GNU
checking for gcc... no
checking for cc... no
checking for cl.exe... no
configure: error: in `/opt/installs/lib_mysqludf_preg':
configure: error: no acceptable C compiler found in $PATH
See `config.log' for more details
root@seik-centos-01:[Wed Dec 18 21:12:12][/opt/installs/lib_mysqludf_preg]$ yum groupinstall Development tools -y

If you get an error: “Can’t find libpcre” , you need to install the pcre dev libs

root@seik-centos-01:[Wed Dec 18 21:23:15][/opt/installs/lib_mysqludf_preg]$ ./configure 
.....
configure: setting libdir to mysql plugin dir /usr/lib64/mysql/plugin
checking for pcre-config... no
checking for PCRE - version >= 1... no
configure: error: "Can't find libpcre"
root@seik-centos-01:[Wed Dec 18 21:28:04][/opt/installs/lib_mysqludf_preg]$ yum install pcre-devel

If after the configure on make you get this WARNING: ‘aclocal-1.13′ is missing on your system

root@seik-centos-01:[Wed Dec 18 21:28:57][/opt/installs/lib_mysqludf_preg]$ make 
CDPATH="${ZSH_VERSION+.}:" && cd . && /bin/sh /opt/installs/lib_mysqludf_preg/config/missing aclocal-1.13 
/opt/installs/lib_mysqludf_preg/config/missing: line 81: aclocal-1.13: command not found
WARNING: 'aclocal-1.13' is missing on your system.
         You should only need it if you modified 'acinclude.m4' or
         'configure.ac' or m4 files included by 'configure.ac'.
         The 'aclocal' program is part of the GNU Automake package:

         It also requires GNU Autoconf, GNU m4 and Perl in order to run:

make: *** [aclocal.m4] Error 127

The most easy way to fix this is to execute this:
aclocal && libtoolize –force && autoreconf

root@seik-centos-01:[Wed Dec 18 21:52:42][/opt/installs/lib_mysqludf_preg]$  aclocal && libtoolize --force && autoreconf
libtoolize: putting auxiliary files in AC_CONFIG_AUX_DIR, `config'.
libtoolize: linking file `config/ltmain.sh'
libtoolize: Consider adding `AC_CONFIG_MACRO_DIR([m4])' to configure.ac and
libtoolize: rerunning libtoolize, to keep the correct libtool macros in-tree.
libtoolize: Consider adding `-I m4' to ACLOCAL_AMFLAGS in Makefile.am.
test/Makefile.am:13: wildcard *.test: non-POSIX variable name
test/Makefile.am:13: (probably a GNU make extension)
test/Makefile.am:22: `%'-style pattern rules are a GNU make extension
test/Makefile.am:25: `%'-style pattern rules are a GNU make extension

then again execute ./configure && make

root@seik-centos-01:[Wed Dec 18 21:52:55][/opt/installs/lib_mysqludf_preg]$ ./configure  
....
checking for pthread_getattr_np declaration... missing
configure: creating ./config.status
config.status: creating Makefile
config.status: creating test/Makefile
config.status: creating doc/Makefile
config.status: creating config.h
config.status: executing depfiles commands
config.status: executing libtool commands
root@seik-centos-01:[Wed Dec 18 21:53:05][/opt/installs/lib_mysqludf_preg]$ make -d
----
Must remake target `all-am'.
Successfully remade target file `all-am'.
make[2]: Leaving directory `/opt/installs/lib_mysqludf_preg'
Reaping winning child 0x0183f9f0 PID 3130 
Removing child 0x0183f9f0 PID 3130 from chain.
Successfully remade target file `all-recursive'.
make[1]: Leaving directory `/opt/installs/lib_mysqludf_preg'
Reaping winning child 0x01955110 PID 3129 
Removing child 0x01955110 PID 3129 from chain.
Successfully remade target file `all'.
root@seik-centos-01:[Wed Dec 18 21:53:13][/opt/installs/lib_mysqludf_preg]$ make install
root@seik-centos-01:[Wed Dec 18 22:03:05][/opt/installs/lib_mysqludf_preg]$ make installdb
/usr/bin/mysql  <./uninstalldb.sql
cat installdb.sql | sed 's/\.so/.dll/g' >installdb_win.sql
if test -f .libs/lib_mysqludf_preg.dll; then  \
		/usr/bin/mysql  <./installdb_win.sql; \
	else \
		/usr/bin/mysql  <./installdb.sql;\
	fi
root@seik-centos-01:[Wed Dec 18 22:03:19][/opt/installs/lib_mysqludf_preg]$ 

How to test the PCRE with MySQL MariaDB 10.0.6:

mysql root@seik-centos-01:[Thu Dec 19 00:09:27 2013][(none)]> SELECT CONVERT( PREG_REPLACE( '/fox/i' , 'dog' , 'The brown fox' ) USING UTF8) as replaced;
+---------------+
| replaced      |
+---------------+
| The brown dog |
+---------------+
1 row in set (0.00 sec)

and you are done

Slackware4Life :)

Categories: CentOS, MariaDB, MySQL Tags: , , , , ,

How to compile mydumper 0.5.2 on Debian 6.0.8 and MySQL Percona Server 5.5.34-rel32.0-591.squeeze

December 8, 2013 1 comment

How to start :
mydumper home page : https://launchpad.net/mydumper
how to build on Debian/Ubuntu : https://answers.launchpad.net/mydumper/+faq/349 , but there are some things missing like cmake and libpcre
Latest version is 0.5.2 released on 2012-11-19: https://launchpad.net/mydumper/0.5/0.5.2/+download/mydumper-0.5.2.tar.gz

MySQL installed version : percona-server-server-5.5 : 5.5.34-rel32.0-591.squeeze

1. get the source :

test ! -d ~/installs/ && mkdir -p ~/installs/
cd ~/installs/
wget https://launchpad.net/mydumper/0.5/0.5.2/+download/mydumper-0.5.2.tar.gz
tar xvf mydumper-0.5.2.tar.gz

2. install the dev packages :

sudo apt-get install libglib2.0-dev zlib1g-dev

3. Install cmake

apt-get install cmake

4. Install percoan mysql client dev files:

apt-get install libmysqlclient-dev

5. Build it :

cd ~/installs/mydumper-0.5.2
cmake . -DCMAKE_INSTALL_PREFIX=~/bin/mydumper

in case you get PCRE not found error:
6. Install the PCRE libs:

apt-get install libpcre3-dev

In case you get no cmake errors, execute make:

make

in case you get that error :

 [ 20%] Building C object CMakeFiles/mydumper.dir/binlog.c.o
 [ 40%] Building C object CMakeFiles/mydumper.dir/server_detect.c.o
 [ 60%] Building C object CMakeFiles/mydumper.dir/g_unix_signal.c.o
 make[2]: *** No rule to make target `/usr/lib/libmysqlclient_r.so', needed by `mydumper'.  Stop.
 make[1]: *** [CMakeFiles/mydumper.dir/all] Error 2
 make: *** [all] Error 2

you need to fix the /usr/lib/libmysqlclient_r.so, it seems to point to non existing libmysqlclient_r.so.18  in my case

ls -lrth /usr/lib/libmysqlclient_r.so
lrwxrwxrwx 1 root root 22 Nov 18 07:24 /usr/lib/libmysqlclient_r.so -> libmysqlclient_r.so.18
cd /usr/lib/
rm libmysqlclient_r.so &&  ln -s libmysqlclient.so.18 libmysqlclient_r.so
root@www:[Sun Dec 08 22:01:19][/usr/lib]$ ls -lrt  | grep  libmysqlclient.so.18
-rw-r--r--  1 root root 3162144 Oct 25 08:35 libmysqlclient.so.18.0.0
-rw-r--r--  1 root root 3551104 Oct 25 09:04 libmysqlclient.so.18.1.0
lrwxrwxrwx  1 root root      24 Nov 18 07:20 libmysqlclient_r.so.18.0.0 -> libmysqlclient.so.18.0.0
lrwxrwxrwx  1 root root      24 Nov 18 07:24 libmysqlclient_r.so.18.1.0 -> libmysqlclient.so.18.1.0
lrwxrwxrwx  1 root root      20 Nov 18 07:24 libmysqlclient.so -> libmysqlclient.so.18
lrwxrwxrwx  1 root root      26 Nov 18 07:24 libmysqlclient.so.18 -> libmysqlclient_r.so.18.1.0
lrwxrwxrwx  1 root root      20 Dec  7 20:41 libmysqlclient_r.so -> libmysqlclient.so.18

Then try again make
in case you get that error :

sql_common.h:26:18: fatal error: hash.h: No such file or directory

this is due to MySQL bug : #70672
in a view to fix that, get the percona source :

$ cd ~/installs/
 wget  http://www.percona.com/redir/downloads/Percona-Server-5.5/LATEST/source/Percona-Server-5.5.34-rel32.0.tar.gz
 tar xvf Percona-Server-5.5.34-rel32.0.tar.gz
 sudo cp ./Percona-Server-5.5.34-rel32.0/include/hash.h /usr/include/
 then again try to build the mydymper:
 cd ~/installs/mydumper-0.5.2
 make
 Scanning dependencies of target mydumper
 [ 20%] Building C object CMakeFiles/mydumper.dir/mydumper.c.o
 [ 40%] Building C object CMakeFiles/mydumper.dir/binlog.c.o
 [ 60%] Building C object CMakeFiles/mydumper.dir/server_detect.c.o
 [ 80%] Building C object CMakeFiles/mydumper.dir/g_unix_signal.c.o
 Linking C executable mydumper
 [ 80%] Built target mydumper
 Scanning dependencies of target myloader
 [100%] Building C object CMakeFiles/myloader.dir/myloader.c.o
 Linking C executable myloader
 [100%] Built target myloader
 make install
 [ 80%] Built target mydumper
 [100%] Built target myloader
 Install the project...
 -- Install configuration: ""
 -- Installing: /home/seik/bin/mydumper/bin/mydumper
 -- Installing: /home/seik/bin/mydumper/bin/myloader
 export PATH=$PATH:/home/seik/bin/mydumper/bin/
 myloader --version
 myloader 0.5.2, built against MySQL 5.6.14

seems to work :
so how to backup the database mysql:

mkdir /home/.mydumper
mydumper -o /home/.mydumper -r 100000 -c -e -m -L mysql-backup.log -u root -p mypass -h localhost -t 2 -v 3 -B mysql 
mysqldump -u root -p -d -R --skip-triggers mysql  > /home/.mydumper/mysql.schema
mysqldump -u root -p -d -t  mysql  > mysql.triggers
seik@www:[Sun Dec 08 22:28:35][/home/.mydumper]$ ls -lrth
total 260K
-rw-r--r-- 1 seik seik  250 Dec  8 04:02 mysql.db.sql.gz
-rw-r--r-- 1 seik seik   78 Dec  8 04:02 mysql.columns_priv.sql.gz
-rw-r--r-- 1 seik seik   78 Dec  8 04:02 mysql.event.sql.gz
-rw-r--r-- 1 seik seik   78 Dec  8 04:02 mysql.general_log.sql.gz
-rw-r--r-- 1 seik seik  166 Dec  8 04:02 mysql.func.sql.gz
-rw-r--r-- 1 seik seik  614 Dec  8 04:02 mysql.help_category.sql.gz
-rw-r--r-- 1 seik seik 3.4K Dec  8 04:02 mysql.help_relation.sql.gz
-rw-r--r-- 1 seik seik   78 Dec  8 04:02 mysql.plugin.sql.gz
-rw-r--r-- 1 seik seik   78 Dec  8 04:02 mysql.ndb_binlog_index.sql.gz
-rw-r--r-- 1 seik seik   78 Dec  8 04:02 mysql.host.sql.gz
-rw-r--r-- 1 seik seik 3.3K Dec  8 04:02 mysql.help_keyword.sql.gz
-rw-r--r-- 1 seik seik   78 Dec  8 04:02 mysql.procs_priv.sql.gz
-rw-r--r-- 1 seik seik   78 Dec  8 04:02 mysql.proc.sql.gz
-rw-r--r-- 1 seik seik   78 Dec  8 04:02 mysql.servers.sql.gz
-rw-r--r-- 1 seik seik  173 Dec  8 04:02 mysql.proxies_priv.sql.gz
-rw-r--r-- 1 seik seik   78 Dec  8 04:02 mysql.time_zone_transition_type.sql.gz
-rw-r--r-- 1 seik seik   78 Dec  8 04:02 mysql.time_zone_transition.sql.gz
-rw-r--r-- 1 seik seik   78 Dec  8 04:02 mysql.time_zone_name.sql.gz
-rw-r--r-- 1 seik seik   78 Dec  8 04:02 mysql.time_zone_leap_second.sql.gz
-rw-r--r-- 1 seik seik   78 Dec  8 04:02 mysql.time_zone.sql.gz
-rw-r--r-- 1 seik seik   78 Dec  8 04:02 mysql.tables_priv.sql.gz
-rw-r--r-- 1 seik seik   78 Dec  8 04:02 mysql.slow_log.sql.gz
-rw-r--r-- 1 seik seik  539 Dec  8 04:02 mysql.user.sql.gz
-rw-r--r-- 1 seik seik 128K Dec  8 04:02 mysql.help_topic.sql.gz
-rw-r--r-- 1 seik seik   75 Dec  8 04:02 metadata
-rw-r--r-- 1 seik seik  25K Dec  8 04:08 mysql.schema
-rw-r--r-- 1 seik seik 1.3K Dec  8 04:08 mysql.triggers

Slackware4Life ! :)

Follow

Get every new post delivered to your Inbox.