Archive
Archive for the ‘CentOS’ Category
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
Categories: AWS, bash, CentOS, Fedora 15 64 bit, Fedora 17 64 bit, MySQL, Uncategorized
backup, clean up, mydumper, mysql
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
yeah , big buzz around that one 🙂
So I decided to check the install process:
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
Categories: CentOS, Fedora 17 64 bit, MariaDB, MySQL, RHEL, Uncategorized
CentOS, CONNECT engine, Fedora, MariaDB, mysql, sqliite3
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 🙂
Public DNS resolve for VM instances hosted at OpenStack nova compute grizzly edition
July 25, 2013
1 comment
Issue – the OpenStack instances hostnames are not resolved at the moment by their floating IPs.
Fast solution – use PowerDNS
Short story:
1. Boot an instance , assign it a floating IP, open tcp ports 22 8001 and udp port 53.
2. Install PowerDNS, the particular yum package is named pdns.
3. install the mysql backend for PowerDNS, its available as yum package as well.
4. Install MariaDB , set the proper configuration, the initialize it at the default location.
5. Set the pdns user, create the pdns database and create the tables needed.
6. Configure the pdns service to use the gmysql backend.
7. Create and user at the OpenStack MySQL able to connect from the floating IP subnet.
8. Grant that user select permissions on all nova.* tables.
9. At the PowerDNS instance, set simple python / php / bash script to query the nova database and to inject the hostnames and the floating IPs as DNS records for the particular OpenStack domains at the pdns db at the pdns OpenStack instance.
In fact, what I do is to clean up all the available OpenStack DNS records, and to inject them again in one transaction, in a view to clean up the deleted and changed hostnames / floating IPs.
10. Set up the external DNS server to forward all queries related to the OpenStack domains to the PowerDNS floating UP.
In general, its good to start a python daemon, in a view to keep the recent status of the OpenStack DNS as a simple hash,
and only when there are changes in OpenStack instances / assigned floating IPs, to push the updates to the RowerDNS db backend.
I hate when the database is used in a lazy way.
All that can be done by hacking the nova python code, as PowerDNS has restful API as well,
but I know there are incoming new OpenStack modules which will do that.
The long story with examples and links TBD.
Update : the source code is here : https://github.com/seikath/openstack-external-dns
One more update to be pushed to the git source:
In a view to push the VM hostname changes without recreating the VM, I decided to use short_description field instead of the hostname,
as the nova rename instance command changes only that field, and the hostname remains intact.
The production code is changed and tested, just not pushed to the public repo.
Yum clean up / rollback RHEL 6 / CentOS packages to the initial status.
July 20, 2013
Leave a comment
In short, playing with RDO – the RedHat OpenStack install script /puppet based .. yummm / I needed several times to rollback the RHEL installed machines to their initial state.
So, in short, in a view to clean everything to the “after install” status :
# if you are brave heart, execute it right away 🙂
yum history list all | sed '/D, E/d;/Erase/d' | awk '/^ *[[:digit:]]/ {print $1}' | sed '/^1$/d;' | while read crap; do echo yum history undo $crap -y;done
# check what has been done :
yum history list all
Categories: CentOS, Linux, OpenStack, RHEL, Uncategorized
CentOS, OpenStack, packstack, RHEL, yum, yum history, yum rollback
Upgrade MySQL cluster severalnines install from 7.2.10-1 to 7.3.2-1
July 17, 2013
Leave a comment
In short, I have one MySQL Cluster of 2 managament and 2 data nodes installed by http://www.severalnines.com/ script generated at their install script generator at http://www.severalnines.com/cluster-configurator/
The NDB version was 7.2.10-1
Some new clients requested an update , in a view to make use of the new bug fixes, features and optimizations.
More info : Changes in MySQL Cluster NDB 7.3.2 (5.6.11-ndb-7.3.2)
So, the steps were:
0. THE MOST IMPORTANT: make a backup 🙂
1. get the RPMS from http://dev.mysql.com/downloads/cluster/
MySQL-Cluster-test-gpl-7.3.2-1.el6.x86_64.rpm MySQL-Cluster-shared-gpl-7.3.2-1.el6.x86_64.rpm MySQL-Cluster-shared-compat-gpl-7.3.2-1.el6.x86_64.rpm MySQL-Cluster-devel-gpl-7.3.2-1.el6.x86_64.rpm MySQL-Cluster-client-gpl-7.3.2-1.el6.x86_64.rpm MySQL-Cluster-server-gpl-7.3.2-1.el6.x86_64.rpm MySQL-Cluster-embedded-gpl-7.3.2-1.el6.x86_64.rpm
2. Upload them to the first head node, note from there I have ssh password-less sudo access to the other nodes.
3. Make sure the cmon service is stopped at all nodes before to stop any of the ndb_mgmd or ndbd services.
$ service cmon stop $ service cmon status
4. Start the rolling stop, upgrade , restart, mysql_upgrade of the ndb_mgmd nodes, one by one.
4.1 ndb_mgmd node 1:
# stop ndb_mgmd service: $ service ndb_mgmd stop $ service ndb_mgmd status # check what to be updated : $ yum search | grep MySQL-Cluster MySQL-Cluster-client-gpl.x86_64 : MySQL Cluster - Client MySQL-Cluster-server-gpl.x86_64 : MySQL: a very fast and reliable SQL database server MySQL-Cluster-shared-compat-gpl.x86_64 : MySQL shared client libraries for MySQL 5.1.67-1, 5.0.96-1, 4.1.23-0, 4.0.27-0 MySQL-Cluster-shared-gpl.x86_64 : MySQL Cluster - Shared libraries $ yum info MySQL-Cluster-server-gpl Loaded plugins: product-id, subscription-manager Updating certificate-based repositories. Unable to read consumer identity Installed Packages Name : MySQL-Cluster-server-gpl Arch : x86_64 Version : 7.2.10 Release : 1.el6 Size : 470 M Repo : installed # go to the directory with the new packages: $ cd ~/mysqlcluster-72-rpm/cluster/repo/MySQL-Cluster-7.3.2-1 # execute the update $ sudo yum upgrade MySQL-Cluster-client-gpl-7.3.2-1.el6.x86_64.rpm MySQL-Cluster-server-gpl-7.3.2-1.el6.x86_64.rpm MySQL-Cluster-shared-gpl-7.3.2-1.el6.x86_64.rpm MySQL-Cluster-shared-compat-gpl-7.3.2-1.el6.x86_64.rpm # Now the update will face an script error: $ sudo yum upgrade MySQL-Cluster-client-gpl-7.3.2-1.el6.x86_64.rpm MySQL-Cluster-server-gpl-7.3.2-1.el6.x86_64.rpm MySQL-Cluster-shared-gpl-7.3.2-1.el6.x86_64.rpm MySQL-Cluster-shared-compat-gpl-7.3.2-1.el6.x86_64.rpm b Loaded plugins: product-id, subscription-manager Updating certificate-based repositories. Unable to read consumer identity epel/metalink | 21 kB 00:00 epel | 4.2 kB 00:00 epel/primary_db | 5.3 MB 00:09 epg | 1.2 kB 00:00 puppetlabs-deps | 1.9 kB 00:00 puppetlabs-deps/primary_db | 20 kB 00:00 puppetlabs-products | 1.9 kB 00:00 puppetlabs-products/primary_db | 93 kB 00:00 vmware-tools | 951 B 00:00 Setting up Upgrade Process Examining MySQL-Cluster-client-gpl-7.3.2-1.el6.x86_64.rpm: MySQL-Cluster-client-gpl-7.3.2-1.el6.x86_64 Marking MySQL-Cluster-client-gpl-7.3.2-1.el6.x86_64.rpm as an update to MySQL-Cluster-client-gpl-7.2.10-1.el6.x86_64 Examining MySQL-Cluster-server-gpl-7.3.2-1.el6.x86_64.rpm: MySQL-Cluster-server-gpl-7.3.2-1.el6.x86_64 Marking MySQL-Cluster-server-gpl-7.3.2-1.el6.x86_64.rpm as an update to MySQL-Cluster-server-gpl-7.2.10-1.el6.x86_64 Examining MySQL-Cluster-shared-gpl-7.3.2-1.el6.x86_64.rpm: MySQL-Cluster-shared-gpl-7.3.2-1.el6.x86_64 Marking MySQL-Cluster-shared-gpl-7.3.2-1.el6.x86_64.rpm as an update to MySQL-Cluster-shared-gpl-7.2.10-1.el6.x86_64 Examining MySQL-Cluster-shared-compat-gpl-7.3.2-1.el6.x86_64.rpm: MySQL-Cluster-shared-compat-gpl-7.3.2-1.el6.x86_64 Marking MySQL-Cluster-shared-compat-gpl-7.3.2-1.el6.x86_64.rpm as an update to MySQL-Cluster-shared-compat-gpl-7.2.10-1.el6.x86_64 No Match for argument: b No package b available. Resolving Dependencies --> Running transaction check ---> Package MySQL-Cluster-client-gpl.x86_64 0:7.2.10-1.el6 will be updated ---> Package MySQL-Cluster-client-gpl.x86_64 0:7.3.2-1.el6 will be an update ---> Package MySQL-Cluster-server-gpl.x86_64 0:7.2.10-1.el6 will be updated ---> Package MySQL-Cluster-server-gpl.x86_64 0:7.3.2-1.el6 will be an update ---> Package MySQL-Cluster-shared-compat-gpl.x86_64 0:7.2.10-1.el6 will be updated ---> Package MySQL-Cluster-shared-compat-gpl.x86_64 0:7.3.2-1.el6 will be an update ---> Package MySQL-Cluster-shared-gpl.x86_64 0:7.2.10-1.el6 will be updated ---> Package MySQL-Cluster-shared-gpl.x86_64 0:7.3.2-1.el6 will be an update --> Finished Dependency Resolution Dependencies Resolved ===================================================================================================================================================================================================== Package Arch Version Repository Size ===================================================================================================================================================================================================== Updating: MySQL-Cluster-client-gpl x86_64 7.3.2-1.el6 /MySQL-Cluster-client-gpl-7.3.2-1.el6.x86_64 81 M MySQL-Cluster-server-gpl x86_64 7.3.2-1.el6 /MySQL-Cluster-server-gpl-7.3.2-1.el6.x86_64 594 M MySQL-Cluster-shared-compat-gpl x86_64 7.3.2-1.el6 /MySQL-Cluster-shared-compat-gpl-7.3.2-1.el6.x86_64 11 M MySQL-Cluster-shared-gpl x86_64 7.3.2-1.el6 /MySQL-Cluster-shared-gpl-7.3.2-1.el6.x86_64 8.4 M Transaction Summary ===================================================================================================================================================================================================== Upgrade 4 Package(s) Total size: 694 M Is this ok [y/N]: y Downloading Packages: Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Updating : MySQL-Cluster-shared-compat-gpl-7.3.2-1.el6.x86_64 1/8 Updating : MySQL-Cluster-client-gpl-7.3.2-1.el6.x86_64 2/8 Updating : MySQL-Cluster-shared-gpl-7.3.2-1.el6.x86_64 3/8 Error in PREIN scriptlet in rpm package MySQL-Cluster-server-gpl-7.3.2-1.el6.x86_64 ****************************************************************** A MySQL server package (MySQL-Cluster-server-gpl-7.2.10-1.el6.x86_64) is installed. Upgrading directly from MySQL 7.2 to MySQL 7.3 may not be safe in all cases. A manual dump and restore using mysqldump is recommended. It is important to review the MySQL manual's Upgrading section for version-specific incompatibilities. A manual upgrade is required. - Ensure that you have a complete, working backup of your data and my.cnf files - Shut down the MySQL server cleanly - Remove the existing MySQL packages. Usually this command will list the packages you should remove: rpm -qa | grep -i '^mysql-' You may choose to use 'rpm --nodeps -ev <package-name>' to remove the package which contains the mysqlclient shared library. The library will be reinstalled by the MySQL-Cluster-shared-compat package. - Install the new MySQL packages supplied by Oracle and/or its affiliates - Ensure that the MySQL server is started - Run the 'mysql_upgrade' program This is a brief description of the upgrade process. Important details can be found in the MySQL manual, in the Upgrading section. ****************************************************************** error: %pre(MySQL-Cluster-server-gpl-7.3.2-1.el6.x86_64) scriptlet failed, exit status 1 error: install: %pre scriptlet failed (2), skipping MySQL-Cluster-server-gpl-7.3.2-1.el6 Cleanup : MySQL-Cluster-shared-compat-gpl-7.2.10-1.el6.x86_64 5/8 Cleanup : MySQL-Cluster-client-gpl-7.2.10-1.el6.x86_64 6/8 Cleanup : MySQL-Cluster-shared-gpl-7.2.10-1.el6.x86_64 7/8 Installed products updated. Verifying : MySQL-Cluster-shared-gpl-7.3.2-1.el6.x86_64 1/8 Verifying : MySQL-Cluster-client-gpl-7.3.2-1.el6.x86_64 2/8 Verifying : MySQL-Cluster-shared-compat-gpl-7.3.2-1.el6.x86_64 3/8 Verifying : MySQL-Cluster-server-gpl-7.3.2-1.el6.x86_64 4/8 MySQL-Cluster-server-gpl-7.2.10-1.el6.x86_64 was supposed to be removed but is not! Verifying : MySQL-Cluster-server-gpl-7.2.10-1.el6.x86_64 5/8 Verifying : MySQL-Cluster-shared-gpl-7.2.10-1.el6.x86_64 6/8 Verifying : MySQL-Cluster-shared-compat-gpl-7.2.10-1.el6.x86_64 7/8 Verifying : MySQL-Cluster-client-gpl-7.2.10-1.el6.x86_64 8/8 Updated: MySQL-Cluster-client-gpl.x86_64 0:7.3.2-1.el6 MySQL-Cluster-shared-compat-gpl.x86_64 0:7.3.2-1.el6 MySQL-Cluster-shared-gpl.x86_64 0:7.3.2-1.el6 Failed: MySQL-Cluster-server-gpl.x86_64 0:7.2.10-1.el6 MySQL-Cluster-server-gpl.x86_64 0:7.3.2-1.el6 Complete! # fix : manually remove the MySQL-Cluster-server before the install $ sudo rpm -e MySQL-Cluster-server-gpl-7.2.10-1.el6.x86_64 $ sudo yum localinstall MySQL-Cluster-server-gpl-7.3.2-1.el6.x86_64.rpm # start the mysqld $ service mysql start
IMPORTANT: It will fail with the following error in case you have engine-condition-pushdown set at my.cnf :
[ERROR] /usr/sbin/mysqld: unknown variable ‘engine-condition-pushdown=1’
Details :
2013-07-17 11:04:55 25507 [Note] InnoDB: Tablespace and datafile system tables created. 2013-07-17 11:04:55 25507 [Note] InnoDB: Waiting for purge to start 2013-07-17 11:04:55 25507 [Note] InnoDB: 5.6.11 started; log sequence number 304036700452 2013-07-17 11:04:55 25507 [Note] NDB: Changed global value of binlog_format from STATEMENT to MIXED 2013-07-17 11:04:55 25507 [Note] NDB: NodeID is 23, management server '10.95.109.216:1186' 2013-07-17 11:04:56 25507 [Note] NDB[0]: NodeID: 23, all storage nodes connected 2013-07-17 11:04:56 25507 [Note] NDB[1]: NodeID: 25, all storage nodes connected 2013-07-17 11:04:57 25507 [Note] NDB[2]: NodeID: 5, all storage nodes connected 2013-07-17 11:04:58 25507 [Note] NDB[3]: NodeID: 6, all storage nodes connected 2013-07-17 11:04:59 25507 [Note] NDB[4]: NodeID: 7, all storage nodes connected 2013-07-17 11:04:59 25507 [Note] NDB[5]: NodeID: 8, all storage nodes connected 2013-07-17 11:04:59 25507 [Note] Starting Cluster Binlog Thread 2013-07-17 11:04:59 25507 [ERROR] /usr/sbin/mysqld: unknown variable 'engine-condition-pushdown=1' 2013-07-17 11:04:59 25507 [ERROR] Aborting
# comment that engine-condition-pushdown at /etc/my.cnf and start the service again:
$ service mysql start $ service mysql status # execute mysql_update in a view to implement the changes at MySQL v 5.6 $ mysql_update -p Enter password: Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck' with connection arguments: '--socket=/var/lib/mysql/mysql.sock' Warning: Using a password on the command line interface can be insecure. Running 'mysqlcheck' with connection arguments: '--socket=/var/lib/mysql/mysql.sock' Warning: Using a password on the command line interface can be insecure. mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.general_log OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK mysql.ndb_apply_status OK mysql.ndb_binlog_index OK mysql.ndb_index_stat_head OK mysql.ndb_index_stat_sample OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK mysql.slow_log OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK Running 'mysql_fix_privilege_tables'... Warning: Using a password on the command line interface can be insecure. Running 'mysqlcheck' with connection arguments: '--socket=/var/lib/mysql/mysql.sock' Warning: Using a password on the command line interface can be insecure. Running 'mysqlcheck' with connection arguments: '--socket=/var/lib/mysql/mysql.sock' Warning: Using a password on the command line interface can be insecure. cmon.alarm OK cmon.alarm_hosts OK cmon.alarm_log OK cmon.backup OK cmon.backup_log OK cmon.backup_schedule OK cmon.cluster OK cmon.cluster_config OK cmon.cluster_configuration OK cmon.cluster_configuration_templates OK cmon.cluster_event_types OK cmon.cluster_log OK cmon.cluster_severity_types OK cmon.cluster_state OK cmon.cluster_statistics OK cmon.cluster_statistics_history OK cmon.cmon_cluster_counters OK cmon.cmon_cluster_graphs OK cmon.cmon_configuration OK cmon.cmon_daily_job OK cmon.cmon_galera_counters OK cmon.cmon_host_log OK cmon.cmon_job OK cmon.cmon_job_message OK cmon.cmon_local_mysql_job OK cmon.cmon_mysql_counters OK cmon.cmon_mysql_grants OK cmon.cmon_mysql_graphs OK cmon.cmon_mysql_manual_grants OK cmon.cmon_mysql_users OK cmon.cmon_schema_uploads OK cmon.cmon_status OK cmon.cmon_sw_package OK cmon.cmon_uploads OK cmon.cmon_user OK cmon.configurator_nodemap OK cmon.cpu_info OK cmon.cpu_stats OK cmon.cpu_stats_history OK cmon.database_conf OK cmon.db_growth OK cmon.db_growth_hashmap OK cmon.db_notifications OK cmon.disk_stats OK cmon.disk_stats_history OK cmon.diskdata OK cmon.diskdata_history OK cmon.email_notification OK cmon.ext_proc OK cmon.galera_garbd_proc OK cmon.galera_status OK cmon.haproxy_server OK cmon.hosts OK cmon.license OK cmon.mailserver OK cmon.memcache_statistics OK cmon.memory_usage OK cmon.memory_usage_history OK cmon.mysql_advisor OK cmon.mysql_advisor_history OK cmon.mysql_advisor_reco OK cmon.mysql_backup OK cmon.mysql_explains OK cmon.mysql_global_statistics OK cmon.mysql_global_statistics_history OK cmon.mysql_innodb_status OK cmon.mysql_master_status OK cmon.mysql_memory_usage OK cmon.mysql_performance_meta OK cmon.mysql_performance_probes OK cmon.mysql_performance_results OK cmon.mysql_processlist OK cmon.mysql_query_histogram OK cmon.mysql_repl_bw OK cmon.mysql_repl_link OK cmon.mysql_replication_recovery OK cmon.mysql_server OK cmon.mysql_slave_status OK cmon.mysql_slow_queries OK cmon.mysql_states OK cmon.mysql_statistics OK cmon.mysql_statistics_history OK cmon.mysql_variables OK cmon.ndbinfo_diskpagebuffer OK cmon.ndbinfo_logbuffers OK cmon.ndbinfo_logbuffers_history OK cmon.ndbinfo_logspaces OK cmon.ndbinfo_logspaces_history OK cmon.net_stats OK cmon.net_stats_history OK cmon.node_state OK cmon.node_statistics OK cmon.node_statistics_history OK cmon.processes OK cmon.ram_stats OK cmon.ram_stats_history OK cmon.restore OK cmon.restore_log OK cmon.schema_object OK cmon.table_growth OK cmon.table_growth_hashmap OK cmon.top OK cmon.user_event_categories OK cmon.user_events OK connect.auth_group OK connect.auth_group_permissions OK connect.auth_message OK connect.auth_permission OK connect.auth_user OK connect.auth_user_groups OK connect.auth_user_user_permissions OK connect.crm_adminprofile OK connect.devices OK connect.django_admin_log OK connect.django_content_type OK connect.django_session OK connect.registration_attempts OK connect.users OK ndbinfo.blocks OK ndbinfo.config_params OK OK # now restart the ndb_mgmd service $ service ndb_mgmd start $ service ndb_mgmd status
4.2 ndb_mgmd node 2:
Exactly the same install, including the my.cnf fix
4.3 ndbd node 1:
Here we need only to update the MySQL-cluster-server package
# stop the local ndbd service: $ service ndbd_3 stop $ service ndbd_3 status # go to the directory with the new packages: $ cd ~/mysqlcluster-72-rpm/cluster/repo/MySQL-Cluster-7.3.2-1 $ sudo rpm -e MySQL-Cluster-server-gpl-7.2.10-1.el6.x86_64 $ sudo yum localinstall MySQL-Cluster-server-gpl-7.3.2-1.el6.x86_64.rpm # start the local ndbd service: $ service ndbd_3 start $ service ndbd_3 status # at a management node start the updated ndbd node [ndbd(NDB)] 2 node(s) id=3 @10.95.109.195 (mysql-5.6.11 ndb-7.3.2, not started) id=4 @10.95.109.196 (mysql-5.5.29 ndb-7.2.10, Nodegroup: 0) [ndb_mgmd(MGM)] 2 node(s) id=1 @10.95.109.216 (mysql-5.6.11 ndb-7.3.2) id=2 @10.95.109.217 (mysql-5.6.11 ndb-7.3.2) ndb_mgm> 3 start ndb_mgm> Node 3: Start initiated (version 7.3.2)
4.4 ndbd node 2:
Same procedure here, after its start, the status should be like :
Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=3 @10.95.109.195 (mysql-5.6.11 ndb-7.3.2, Nodegroup: 0) id=4 @10.95.109.196 (mysql-5.6.11 ndb-7.3.2, Nodegroup: 0, Master) [ndb_mgmd(MGM)] 2 node(s) id=1 @10.95.109.216 (mysql-5.6.11 ndb-7.3.2) id=2 @10.95.109.217 (mysql-5.6.11 ndb-7.3.2) [mysqld(API)] 21 node(s)
5. Now start all the cmon monitors at all the 4 nodes
$ for crap in 10.95.109.195 10.95.109.196 10.95.109.216 10.95.109.217; do ssh -T -p 22 $crap sudo /etc/init.d/cmon restart; done Stopping cmon: ok Starting cmon: ok Stopping cmon: ok Starting cmon: ok Stopping cmon: ok Starting cmon: ok Stopping cmon: ok Starting cmon: ok
6. If its appropriate, restore the full production database from the backup
For the backups I use my own script at bitbucket
cheers folks, a ver si ebay habla con Solo
SlackWare4Life !
Categories: CentOS, Linux, MySQL
7.3.2, MySQL 5.6, MySQL Cluster, MySQL upgrade
oneliner how to clear store of particular old MO/DLR messages of Kannel bearerbox version `svn-r5011M’.
February 10, 2013
Leave a comment
We assume the kannel stora is spooled at /var/log/kannel/kannel.spool/
How to clean the old non sent/routed MO messages from 1st to 9th Feb 2013
root@darkstar:[Sun Feb 10 17:28:56]:[~/bin]$ curl -s "http://127.0.0.1:13000/store-status.txt?username=xxxxx&password=xxxx" | awk '$2~/\[MO\]/{print $0};$3~/\[2013-02-0/{ gsub(/[\[\]]/,"",$1);print $1}' | xargs --no-run-if-empty -icrap find /var/log/kannel/kannel.spool/ -type f -name crap -exec rm -f {} \;
serice kannel restart
MySQL Cluster mysql-5.5.22 ndb-7.2.6 on Linux RHEL 6.3 (Santiago) : restore backup script
February 4, 2013
Leave a comment
Latest update : restore has been done with the exception of the full MySQL cluster restore – I need to do more tests here.
Some code clean up to be done as well , so is someone is in interested check the git from now on.
Update – migrated to bitbucket: https://bitbucket.org/seikath/epg-mysql-cluster , still under development
After digging for more detailed info whats the proper execution of the MySQL cluster restore procedure , I came up with that script:
Note, the real restore is deactivated here and there is one “s” added to the ndb_* commands in a view to avoid any fuckups with the PROD ENV where I wrote it
The script has been tested at the DEV ENV with some small changes
Second note: due to security restrictions I decided to execute the restore from one of the data nodes, in a view to save some extra scripting.
#!/bin/sh
# epgbcn4 aka seikath@gmail.com
# is410@epg-mysql-memo1:~/bin/epg.mysql.cluster.restore.sh
# moved to bitbucket : 2013-02-06.15.26.48
# 2013-02-17.03.14.12
# 2013-02-17.14.07.09 - add backup data maintenance feature
# So far on RHEL .. porting to other distors after its done for RHEL
SCRIPT_NAME=${0%.*}
LOG_FILE="$(basename ${SCRIPT_NAME}).$(date +%Y-%m-%d.%H.%M.%S).log"
CONF_FILE=${SCRIPT_NAME}.conf
TMP_WORL_FILE="/tmp/${HOSTNAME}.$(basename ${SCRIPT_NAME}).tmp"
# Loading configuraion
if [ -f "${CONF_FILE}" ]
then
source "${CONF_FILE}"
else
logit "Missing config file ${CONF_FILE} ! Exiting now."
exit 0
fi
# activating debug
test $DEBUG -eq 1 && set -x
# initialize the tmp file
test `echo "" > "${TMP_WORL_FILE}"` && logit "${TMP_WORL_FILE} initialized!"
function logit () {
echo "$(date)::[${HOSTNAME}] : ${1}"
echo "$(date)::[${HOSTNAME}] : ${1}" >> "${LOG_FILE}"
}
# getting the user ID, check sudo, ndbd restart command
check_is_root=$(id | sed '/^uid=0/!d' | wc -l)
user_name=$(id -nu)
local_command_ndbd=$(chkconfig --list| grep ndbd | awk '{print $1}')
command_ndbd="service ${local_command_ndbd} restart-initial"
command_restar_ndbd="sudo ${command_ndbd}"
sudo_status=$(sudo -l | tr '\n|\r' ' ' | sed 's/^.*User /User /;s/ */ /g' | grep -i "${user_name}")
no_passwd_check=0
test `echo ${sudo_status} | grep "(ALL) NOPASSWD: ALL" | wc -l ` -gt 0 && no_passwd_check=1
test $check_is_root -eq 1 && command_restar_ndbd="${command_ndbd}"
if [ ${no_passwd_check} -eq 1 ]
then
add_sudo="sudo ";
else
add_sudo="";
fi
# check initial credential
logit "UID check : ${user_name}"
logit "Sudo check : ${sudo_status}"
test ${no_passwd_check} -eq 1 && logit "No Passwd sudo check : Confirmed!"
test ${no_passwd_check} -eq 0 && logit "No Passwd sudo check : NOTE -> Missing passwordless sudo!"
#logit "Got ndbd sercice restart command to be run by user ${user_name} : ${command_restar_ndbd}"
# get the available active IPs:
local_ip_array=$(${add_sudo}ifconfig | grep "inet addr:" | grep -v grep | awk '{print $2}' | sed 's/^addr://')
# get the ndbd data
data=$(${add_sudo}ndb_config -c ${ndb_mgmd[1]},${ndb_mgmd[2]} --type=ndbd --query=id,host,datadir -f ' ' -r '\n')
# get the recent data node ID, its IP and the data directory used
# check the ndbd start-stop command name of all ndbd data nodes
echo "${data}" | \
while read nodeID nodeIP backupDir
do
logit "Getting the ndbd start script name from ${user_name}@${nodeIP}"
command_ndbd[${nodeID}]=$(echo "${add_sudo}/sbin/chkconfig --list" | ${ssh_command} ${user_name}@${nodeIP} | grep ndb | awk '{print $1}')
localHit=0;
for IP in ${local_ip_array}
do
test "${nodeIP}" == "${IP}" && localHit=1 && break;
done
echo -e "${nodeIP}\t${nodeID}\t${backupDir}${LocalBackupDirName}\t${command_ndbd[${nodeID}]}\t${localHit}" >> "${TMP_WORL_FILE}"
done
# load the the recent data node ID, its IP and the data directory used
if [ -f "${TMP_WORL_FILE}" ]
then
ndbd_cntr=0;
while read tmp_IP tmp_nodeID tmp_backupDir tmp_command_ndbd tmp_localHit
do
test -z ${tmp_localHit} && continue;
if [ ${tmp_localHit} -eq 1 ]
then
IP="${tmp_IP}";
nodeID="${tmp_nodeID}";
backupDir="${tmp_backupDir}"
fi
command_ndbd[${nodeID}]="${tmp_command_ndbd}";
ndbd_data_node_id[$ndbd_cntr]="${tmp_nodeID}";
ndbd_data_IP[$ndbd_cntr]="${tmp_IP}";
ndbd_data_bckp_dir[$ndbd_cntr]="${tmp_backupDir}";
ndbd_data_cmd[$ndbd_cntr]="${tmp_command_ndbd}";
ndbd_data_local[$ndbd_cntr]=${tmp_localHit};
((++ndbd_cntr))
done < "${TMP_WORL_FILE}"
else
logit "Missing data collection at ${TMP_WORL_FILE}. Exiting no." && exit 0;
fi
logit "got Local machine IP ${IP}";
logit "got Local machine MySQL cluster nodeID : ${nodeID}";
logit "got MySQL cluster local backup Dir : ${backupDir}";
logit "got MySQL cluster RHEL local service command : ${local_command_ndbd}";
# choose other backup available
while [ 1 ]
do
read -r -p "$(date)::[${HOSTNAME}] : Do you want to choose ANOTHER backup forder : Yes/No [y/n] : " choice
if [ "$choice" != "" ]
then
case $choice in
"Yes" | "yes" | "y" | "Si" | "si" | "Y")
while [ 1 ]
do
read -r -p "$(date)::[${HOSTNAME}] : Please provide the full name of the PARENT backup forder or hit CTRL+C to terminate...: " chosenDIR
if [ -d "${chosenDIR}" ]
then
echo "";
backupDir="${chosenDIR}"
break 2;
else
logit "We can not find the PARENT backup forder of ${chosenDIR}"
echo ""
fi
done
;;
"No" | "n" | "N" )
logit "Proceeding with the condifured nightly backup.."
break;
;;
*)
logit "Empty imput, please provide the full name of the backup forder or hit CTRL+C to terminate:"
;;
esac
fi
done
# check read permissions at backupDir
add_sudo="";
logit "Cheking the read permissions of ${backupDir}.."
if [ ! -r "${backupDir}" ]
then
logit "User ${user_name} can not read the backup directory of ${backupDir}!";
logit "Switching to sudo .."
if [ ${no_passwd_check} -eq 0 ]
then
logit "User ${user_name} can not read the backup directory of ${backupDir} with sudo neither!";
exit 0;
else
add_sudo="sudo "
fi
fi
# check the content of the backup directory provided
logit "DEBUG : check the content of the backup directory provided [${backupDir}]"
if [ -d "${backupDir}" ]
then
${add_sudo}ls -1rt "${backupDir}/" | while read crap; do logit "Found possible local backup of ndb_mgmd id ${nodeID}::${IP} : [$crap]";done
fi
while [ 1 ]
do
read -r -p "$(date)::[${HOSTNAME}] : Please choose local backup to restore or hit CTRL+C to terminate...: " paused
if [ "$paused" != "" -a -d "${backupDir}/${paused}" ]
then
paused=${paused%%/}
NDB_BACKUP_NUMBER=${paused/*-/}
NDB_BACKUP_DIR="${backupDir}/${paused}"
NDB_BACKUP_LOG="${backupDir}/${paused}/${paused}.${nodeID}.log"
break;
else
echo ""
fi
done
# check sudo availability
add_sudo="";
logit "Cheking the read permissions of ${NDB_BACKUP_DIR}.."
if [ ! -r "${NDB_BACKUP_DIR}" ]
then
logit "User ${user_name} can not read the backup directory of ${NDB_BACKUP_DIR}!";
logit "Switching to sudo .."
if [ ${no_passwd_check} -eq 0 ]
then
logit "User ${user_name} is missing sudo and can not read the backup directory of ${NDB_BACKUP_DIR}!";
exit 0;
else
add_sudo="sudo ";
fi
fi
# check if there is backup log file in the backup directory
logit "Cheking the read permissions of ${NDB_BACKUP_LOG}.."
${add_sudo}ls ${NDB_BACKUP_LOG} >> /dev/null 2>&1
test $? -gt 1 && logit "Error : ${NDB_BACKUP_LOG} is missing at ${NDB_BACKUP_DIR} ! Exiting now." && exit 0;
# checking the backup consistency:
if [ -d "${NDB_BACKUP_DIR}" ]
then
logit "We are about to proceed with the restore of the backup at ${NDB_BACKUP_DIR}: $(${add_sudo}ls -lrth ${NDB_BACKUP_DIR})"
logit "Checking the backup consistency:"
NDB_BACKUP_STATUS=$(${add_sudo}ndb_print_backup_file "${NDB_BACKUP_LOG}")
test `echo ${NDB_BACKUP_STATUS} | grep -i "NDBBCKUP" | wc -l ` -eq 0 && logit "${NDB_BACKUP_LOG} is NOT NDB consistane Backup file!" && exit 0
# echo "${NDB_BACKUP_STATUS}"
logit "Confirmed : ${NDB_BACKUP_DIR} contains consistent backup"
else
logit "ERROR : Missing NDB BACKUP directory ${NDB_BACKUP_DIR}!"
fi
# choose the restore type: full restore with drop database or table restore
logit "Starting the restore type questionaire: "
restoreStringInclude="";
while [ 1 ]
do
read -r -p "$(date)::[${HOSTNAME}] : Please choose the restore type : FULL MySQL cluster [F], DATABASE [D] or TABLE [T] to restore OR hit CTRL+C to terminate : " restore
if [ "$restore" != "" ]
then
case $restore in
"F" | "f" | "FULL" | "Full" )
restoreStringInclude="-m"; # restore MySQL cluster table metadata
logit "Proceeding with the FULL MySQL BACKUP restore.";
break;
;;
"D" | "d" | "Database" | "DATABASE" )
logit "Make sure the database is existing, otherwise the restore will fail and you would need full MySQL initialization restore"
# add here check of the MySQL cluster data nodes status
logit "Fetching the databases from the MySQL cluster ... "
# Fetch the databases from the MySQL Cluster :
data_ndb_databases_online=$(${add_sudo}ndb_show_tables -c ${ndb_mgmd[1]},${ndb_mgmd[2]} -t 2 | awk '$1 ~ /^[[:digit:]]/ && $2 == "UserTable" && $3 == "Online" {print $5}' | sort | uniq)
cntr=0;
for DbName in ${data_ndb_databases_online}
do
((++cntr));
dbArrayName[${cntr}]="${DbName}";
comma=" => ";
test $cntr -gt 9 && comma=" : "
logit "Found database${comma}[${DbName}]";
lastdbArrayName="${DbName}";
done
# Get the users Database choice
while [ 1 ]
do
logit "You may provide a comma separated list of databases to restore.";
test ${#dbArrayName[@]} -gt 1 && logit "Example: ${dbArrayName[1]},${lastdbArrayName}";
test ${#dbArrayName[@]} -eq 1 && logit "Example: ${dbArrayName[1]}";
read -r -p "$(date)::[${HOSTNAME}] : Please provide the DATABASE NAMES OR hit CTRL+C to terminate : " userDbNames;
if [ "${userDbNames}" != "" ]
then
# Read the user choices
IFS=', ' read -a ArrayUserDbNames <<< "${userDbNames}"
# checking the user data consistency
logit "Checking the databases.."
DbNameOnly_restrore_string="";
for idx in "${!ArrayUserDbNames[@]}"
do
crap[$idx]=1;
for DbNameOnly in ${data_ndb_databases_online}
do
if [ "${ArrayUserDbNames[idx]}" == "${DbNameOnly}" ]
then
commat="";
test "${DbNameOnly_restrore_string}" != "" && commat=",";
crap[$idx]=0;
logit "[${ArrayUserDbNames[idx]}] : Confirmed";
break;
fi
done
DbNameOnly_restrore_string="${DbNameOnly_restrore_string}${commat}${ArrayUserDbNames[idx]}";
test ${crap[idx]} -eq 1 \
&& logit "Note : the database ${ArrayUserDbNames[idx]} is missing in the curent MySQL Cluster!" \
&& logit "We recommend restore witj DDL/metadata" \
&& logit "After a successfull restore of a MISSING database you HAVE TO CREATE IT by \"mysql> create database ${ArrayUserDbNames[idx]};\"" \
&& logit "Then all the restored tables and data will be accessible.";
done
# check if the DDL should be restored as well :
while [ 1 ]
do
read -r -p "$(date)::[${HOSTNAME}] : Do you want the table metadata to be restored as well? Y/N : " restoreDDL;
if [ "${restoreDDL}" != "" ]
then
case ${restoreDDL} in
"Y" | "y" | "yes" | "Yes" | "YES" )
logit "Including the DDLL/meta table data restore";
restoreStringInclude="-m --include-databases=${DbNameOnly_restrore_string}";
break;
;;
"N" | "n" | "No" | "NO" | "Non" )
logit "Skipping the DDL/meta table data restore";
restoreStringInclude="--include-databases=${DbNameOnly_restrore_string}";
break;
;;
*)
logit "Please choose [Y]es or [N]O!"
;;
esac
fi
done
#logit "Proceeding with the BACKUP of the database(s) ${DbNameOnly_restrore_string}"
#logit "DEBUG : restoreStringInclude : ${restoreStringInclude}";
# restoreStringInclude="--include-databases=${DbNameOnly_restrore_string}";
break 2;
else
logit "Empry database(s) name to be restored!"
fi
done
logit "Proceeding with the FULL DATABASE BACKUP. To be done just like the table backup"
break;
;;
"T" | "t" )
logit "Make sure the database.table is existing, otherwise the restore will fail."
logit "Fetching the databases and its tables from the MySQL cluster ... "
# get the database.table list from the mysql cluster
data_ndb_databases_tables_online=$(${add_sudo}ndb_show_tables -c ${ndb_mgmd[1]},${ndb_mgmd[2]} -t 2 | awk ' ($1 ~ /^[[:digit:]]/ && $7 !~ /^NDB\$BLOB/) {print $5"."$7}' | sort | uniq) cntr=0
# print a list of the db.tables available atm
cntr=0;
for DbNameAndTable in ${data_ndb_databases_tables_online}
do
((++cntr));
dbArray[${cntr}]="${DbNameAndTable}";
comma=" : ";
test $cntr -gt 9 && comma=" : "
logit "[${cntr}]${comma}[${DbNameAndTable}]";
lastdbArray="${DbNameAndTable}";
done
# Get the users Database and table choice
DbNameTable_restrore_string="";
while [ 1 ]
do
logit "You may provide a comma separated list of tables to restore.";
test ${#dbArray[@]} -gt 1 && logit "Example: ${dbArray[1]},${lastdbArray}";
test ${#dbArray[@]} -eq 1 && logit "Example: ${dbArray[1]}";
read -r -p "$(date)::[${HOSTNAME}] : Please provide the full name of the table(s) OR hit CTRL+C to terminate : " tableName;
if [ "${tableName}" != "" ]
then
# Read the user choices
IFS=', *' read -a userTables <<< "${tableName}"
# checking the user data consistency
logit "Checking the tables.."
for idx in "${!userTables[@]}"
do
crap[$idx]=1;
for DbNameAndTable in ${data_ndb_databases_tables_online}
do
if [ "${userTables[idx]}" == "${DbNameAndTable}" ]
then
commat="";
test "${DbNameTable_restrore_string}" != "" && commat=",";
crap[$idx]=0;
logit "[${userTables[idx]}] : Confirmed";
break;
fi
done
DbNameTable_restrore_string="${DbNameTable_restrore_string}${commat}${userTables[idx]}";
test ${crap[idx]} -eq 1 && logit "NOTE : Table ${userTables[idx]} is missing in the curent MySQL Cluster!";
done
# check if the DDL should be restored as well :
while [ 1 ]
do
read -r -p "$(date)::[${HOSTNAME}] : Do you want the table metadata to be restored as well? Y/N : " restoreDDL;
if [ "${restoreDDL}" != "" ]
then
case ${restoreDDL} in
"Y" | "y" | "yes" | "Yes" | "YES" )
logit "Including the DDLL/meta table data restore";
restoreStringInclude="-m --include-tables=${DbNameTable_restrore_string}";
break;
;;
"N" | "n" | "No" | "NO" | "Non" )
logit "Skipping the DDL/meta table data restore";
restoreStringInclude="--include-tables=${DbNameTable_restrore_string}";
break;
;;
*)
logit "Please choose [Y]es or [N]O!"
;;
esac
fi
done
#restoreStringInclude="--include-tables=${DbNameTable_restrore_string}";
logit "Proceeding with the BACKUP of the tables ${DbNameTable_restrore_string}"
break 2;
else
logit "Empry table name to be restored!"
fi
done
;;
*)
logit ": Please choose the restore type : FULL DATABASE restore including database [F] or TABLE [T]restore OR hit CTRL+C to terminate... [F(ull)]/[T(able)]: "
;;
esac
fi
done
logit "About to execute the restore procedure with the following options : [${restoreStringInclude}]."
# possible stupid question to add : Do you want to proceed ? Y/N [Y]
# checking the available API nodes :
logit "Checking the available API nodes:"
api_data=$(${add_sudo}ndb_mgm --ndb-mgmd-host=${ndb_mgmd[1]},${ndb_mgmd[2]} -e 'show' | sed '/^\[mysqld(API)\]/,$!d;/^ *$/d')
echo "${api_data}"
#get the first node :
echo "${api_data}" | sed '/^\[mysqld(API)\]/d' | \
while read API_NODE_ID API_NODE_IP crap
do
API_NODE_ID=${API_NODE_ID/*=/}
test `echo "${crap}" | grep "not connected" | wc -l` -gt 0 && logit "Skipping NOT CONNECTED API Node ID [${API_NODE_ID}] ${API_NODE_IP}{$crap}" && continue;
API_NODE_IP=${API_NODE_IP/@/}
logit "Procceding MySQL CLuster API NODE [${API_NODE_ID}] at [${API_NODE_IP}]"
API_NODE_ID=${API_NODE_ID/*=/}
# set the API node in single user more :
case $restore in
"F" | "f" | "FULL" | "Full" )
# loop again the data nodes
logit "The Full MySQL custer restore has been deactivated at that time. The proceeding will be added after extensive testing."
exit 0;
ndbd_initial_status=1;
for idx in $(seq 0 $((${#ndbd_data_node_id[@]} - 1)))
do
ndbd_start_status[$idx]=$(echo "ps aux | grep -v grep | grep -i ndbd | sed '1,1!d'" | ${ssh_command} ${user_name}@${ndbd_data_IP[idx]})
if [ "${ndbd_start_status[idx]}" != "" -a "${ndbd_start_status[idx]}" != "${ndbd_start_status[idx]/--initial/}" ]
then
logit "MySQL Cluster NDB DATA NODE [${ndbd_data_node_id[idx]}] runnig in initial mode, no restart needed";
elif [ "${ndbd_start_status[idx]}" == "" ]
then
ndbd_initial_status=0;
logit "MySQL Cluster NDB DATA NODE [${ndbd_data_node_id[idx]}] is runnig in start mode, restart in initial mode is needed.";
logit "Executing restart initial at NDBD node [${ndbd_data_node_id[idx]}]";
else
ndbd_initial_status=0;
logit "MySQL Cluster NDB DATA NODE [${ndbd_data_node_id[idx]}] is NOT runnig ";
fi
logit "DEBUG: idx: [${idx}] : ${ndbd_start_status[idx]}";
done
if [ ${ndbd_initial_status} -eq 1 ]
then
logit "Check MySQL CLuster single user mode status";
mysql_sluster_status=$(${add_sudo}ndb_mgm --ndb-mgmd-host=${ndb_mgmd[1]},${ndb_mgmd[2]} -e 'show' | sed '/ndbd/,/^ *$/!d;/^ *$/d;/^id/!d;/single user mode/!d' | wc -l)
if [ ${mysql_sluster_status} -eq $((${#ndbd_data_node_id[@]}-1)) ]
then
logit "Setting the MySQL CLuster DATA NODE [${API_NODE_ID}] at ${API_NODE_IP}] in single user mode";
mysql_sluster_set_sinlge_user_mode=$(${add_sudo}ndb_mgm --ndb-mgmd-host=${ndb_mgmd[1]},${ndb_mgmd[2]} -e 'enter single user mode ${API_NODE_ID}');
else
logit "No need to set the single user mode as its already activated";
logit "Executing FULL restore with table metadata."
cmd_restore="${add_sudo}ndb_restore -c ${API_NODE_IP} ${restoreStringInclude} -b ${NDB_BACKUP_NUMBER} -n ${nodeID} -r ${NDB_BACKUP_DIR}"
logit "${cmd_restore}"
mysql_sluster_restore_result=$(${cmd_restore})
echo "${mysql_sluster_restore_result}"
fi
else
logit ""
fi
exit 0 ;
logit "ssh -q -nqtt -p22 ${user_name}@${ndbd[1]} '${command_restar_ndbd}' restart-initial"
logit "DEBUG : have to find the restart command at the other node !"
logit "ssh -q -nqtt -p22 ${user_name}@${ndbd[2]} '${command_restar_ndbd}' restart-initial"
logit "Cheking the status of ndbd at ${ndbd[1]}"
logit "${ssh_command} ${user_name}@${ndbd[1]} '${command_restar_ndbd} status'"
ndbd_status[]echo "${command_restar_ndbd} status" | ${ssh_command} ${user_name}@${ndbd[1]}
logit "Cheking the status of ndbd at ${ndbd[2]}"
logit "ssh -q -nqtt -p22 ${user_name}@${ndbd[2]} '${command_restar_ndbd} status'"
logit "Setting the API node [${API_NODE_ID}] in single user"
# possible check if the user wants to clean up the mysql cluster DB like executing drop database ... create database
logit "${add_sudo}ndb_mgms --ndb-mgmd-host=${ndb_mgmd[1]},${ndb_mgmd[2]} -e 'enter single user mode ${API_NODE_ID}'"
status=$(${add_sudo}ndb_mgm --ndb-mgmd-host=${ndb_mgmd[1]},${ndb_mgmd[2]} -e 'show' | grep "^id={$nodeID}" | grep "@${IP}")
logit "Cluster status of ndbd id ${nodeID} : ${status}"
logit "${add_sudo}ndb_restores -c ${API_NODE_IP} ${restoreStringInclude} -b ${NDB_BACKUP_NUMBER} -n ${nodeID} -r ${NDB_BACKUP_DIR}"
logit "Exiting the single user more:"
logit "${add_sudo}ndb_mgms --ndb-mgmd-host=${ndb_mgmd[1]},${ndb_mgmd[2]} -e 'exit single user mode'"
;;
"D" | "d" | "Database" | "DATABASE" )
logit "Starting the restore process for databases(s) ${DbNameOnly_restrore_string}, please wait a bit .. "
restore_result=$(${add_sudo}ndb_restore -c ${API_NODE_IP} ${restoreStringInclude} -b ${NDB_BACKUP_NUMBER} -n ${nodeID} -r "${NDB_BACKUP_DIR}" 2>&1 | tee -a "${LOG_FILE}")
what_to_see=$(echo ${restore_result} | sed '/^Processing data in table/d')
if [ "${what_to_see}" != "${what_to_see/NDBT_ProgramExit: 0 - OK/}" ]
then
logit "The restore was successful! detailed log at ${LOG_FILE} ."
logit "Slackware4File!";
elif [ "${what_to_see}" != "${what_to_see/Unable to find table:/}" ]
then
logit "The restore FAILED due to missing/broken tables! Detailed log at ${LOG_FILE}"
logit "We recommed restore the table metadata of $(echo ${what_to_see} | sed 's/^.*Unable to find table:/Unable to find table:/;s/^Unable to find table: //;s/ .*$//' ) table";
elif [ "${what_to_see}" != "${what_to_see/Missing column/}" ]
then
logit "The restore FAILED due to missing/broken fields in a table! Detailed log at ${LOG_FILE}";
logit "We recommed full full restore with table metadata.";
elif [ "${what_to_see}" != "${what_to_see/Schema object with given name already exists/}" ]
then
logit "The restore FAILED due to attempt to create an exsisting table! Detailed log at ${LOG_FILE}";
logit "We recommed the following steps:";
logit "1. Restore without the table metadata OR";
logit "2. In case the step fails due to missing tables we reccomend FULL restore with dropping the database";
else
logit "The restore FAILED";
fi
;;
"T" | "t" )
logit "Starting the restore process for table(s) ${DbNameTable_restrore_string}, please wait a bit .. "
restore_result=$(${add_sudo}ndb_restore -c ${API_NODE_IP} ${restoreStringInclude} -b ${NDB_BACKUP_NUMBER} -n ${nodeID} -r "${NDB_BACKUP_DIR}" 2>&1 | tee -a "${LOG_FILE}")
what_to_see=$(echo ${restore_result} | sed '/^Processing data in table/d')
if [ "${what_to_see}" != "${what_to_see/NDBT_ProgramExit: 0 - OK/}" ]
then
logit "The restore was successful! detailed log at ${LOG_FILE} ."
logit "Slackware4File!";
elif [ "${what_to_see}" != "${what_to_see/Unable to find table:/}" ]
then
logit "The restore FAILED due to missing/broken tables! Detailed log at ${LOG_FILE}"
logit "We recommed full full restore with table metadata.";
elif [ "${what_to_see}" != "${what_to_see/Missing column/}" ]
then
logit "The restore FAILED due to missing/broken fields in a table! Detailed log at ${LOG_FILE}";
logit "We recommed the following steps:";
logit "1. We recommed table restore with DDL/table metadata restore";
logit "2. In case the step fails due to existing tables we recomend FULL restore with dropping the database";
elif [ "${what_to_see}" != "${what_to_see/Schema object with given name already exists/}" ]
then
logit "The restore FAILED due to attempt to create an exsisting table! Detailed log at ${LOG_FILE}";
logit "We recommed the following steps:";
logit "1. Restore without the table metadata OR";
logit "2. In case the step fails due to missing tables we recomend FULL restore with dropping the database";
else
logit "The restore FAILED";
fi
;;
*)
logit "Nothing to do here"
;;
esac
status=$(${add_sudo}ndb_mgm --ndb-mgmd-host=${ndb_mgmd[1]},${ndb_mgmd[2]} -e 'show' | grep "^id={$nodeID}" | grep "@${IP}")
logit "Cluster status of ndbd id ${nodeID} : ${status}"
break; # we execute on the first acive API node
done
and the config file used:
root@darkstar:[Tue Feb 05 00:06:27]:[~/bin]$ cat mysql.cluster.restore.conf # epgbcn4 aka seikath@gmail.com # is410@epg-mysql-memo1:~/bin/epg.mysql.cluster.restore.sh # 2013-02-17.03.42.08 ndbd[1]=10.95.109.195 ndbd[2]=10.95.109.196 ndb_mgmd[1]=10.95.109.216 ndb_mgmd[2]=10.95.109.217 LocalBackupDirName="backup/BACKUP" ssh_command="ssh -T -p 22" DEBUG=1 # active DEBUG=0 # deactivated # add error handling array here for later handling via looping the array restore_error_match[0]="Unable to find table:" restore_error_text[0]="The restore FAILED due to missing/broken tables!"
Categories: bash, CentOS, Linux, MySQL, RHEL
bash. backup, linux, MySQL Cluster, mysql-5.5.22, ndb-7.2.6, restore, RHEL 63
Migrating MySQL 5.5.25a jiradb ERROR 2013 (HY000) on huge single db import
January 18, 2013
Leave a comment
well, I incremented max_allowed_packet from 16M to 512M
anyway, I got the same error on the next clean import.
so decided to find a workaround.
so, how to get the data separated from the ddl statements:
# get the tables names into the insert statement, its better to have that in file for future usage sed '/^INSERT INTO/!d;s/ VALUES.*$//' jiradb.20130118.sql | sort | uniq > tablas.como.nombres.txt
then how to get the data separated:
root@jiragg:[Fri Jan 18 15:26:33]:[/usr/local/BACKUP]$ cat make.inserts.sh
#!/bin/sh
# trim function thank to http://stackoverflow.com/questions/369758/how-to-trim-whitespace-from-bash-variable
# and http://codesnippets.joyent.com/posts/show/1816
trim() {
local var=$1
var="${var#"${var%%[![:space:]]*}"}" # remove leading whitespace characters
var="${var%"${var##*[![:space:]]}"}" # remove trailing whitespace characters
echo -n "$var"
}
while read tabname
do
tablename=$(trim $(echo $tabname | sed 's/INSERT INTO//;s/[[:punct:]]*//g'))
echo "${tabname}:=>${tablename}"
sed "/^INSERT INTO \`${tablename}\` VALUES/!d" /usr/local/BACKUP/jiradb.20130118.sql | gzip > "/usr/local/BACKUP/${tablename}.jiradb.20130118.sql.gz"
done < tablas.como.nombres.txt
how to get the DDLs:
sed '/^INSERT INTO/d' jiradb.20130118.sql > non.insert.jiradb.20130118.sql
how to import the whole jiradb:
# create the empty database
mysql --defaults-file=~/..credentials.jira -e 'create database jiradb;'
#import the DDLs:
mysql --defaults-file=~/..credentials.jira jiradb < /usr/local/BACKUP/non.insert.jiradb.20130118.sql
# make a list of the complressed datafiles per table:
find /usr/local/BACKUP/ -type f -name "*sql.gz" > list.import.tables.txt
# execute simple export script
root@jiragg:[Fri Jan 18 15:35:05]:[/usr/local/BACKUP]$ cat import.tables.sh
#!/bin/sh
while read tablefile
do
echo -n "importing ${tablefile}: "
zcat "${tablefile}" | mysql --defaults-file=~/..credentials.jira jiradb
echo ""
done < list.import.tables.txt
and that is it
anyway do not forget to increment the max_allowed_packet
Slackware4Life !
Popular Science – New Technology, Science News, The Future Now
- An error has occurred; the feed is probably down. Try again later.
BBC News | Technology | World Edition
- Australia PM calls Elon Musk an 'arrogant billionaire'
- How robots are taking over warehouse work
- Three-year-olds groomed online, charity warns
- EU may suspend money-for-views TikTok feature
- Grindr sued for allegedly revealing users' HIV status
- Can touchless tech create 'equitable' gaming?
- Tesla cuts prices in major markets as sales fall
- From WhatsApp to Greggs - why is tech going down more?
- TikTok warns US ban would 'trample free speech'
- Influential women's tech network shuts down unexpectedly
The Register – Science
- SpaceX workplace injury rates are rocketing
- Voyager 1 regains sanity after engineers patch around problematic memory
- FAA now requires reentry vehicles to get licensed before launch
- A knotty problem: Boffins working on fuel-efficient trajectories for space travel
- NASA solar sail to be Siriusly visible in orbit from Earth
- NASA will send astronauts to patch up leaky ISS telescope
- Mars helicopter sends final message, but will keep collecting data
- Boston Dynamics' humanoid Atlas is dead, long live the ... new commercial Atlas
- NASA confirms nuclear-powered Dragonfly drone is going to Titan
- China scientists talk of powering hypersonic weapon with cheap Nvidia chip
The Register – Networks
- Miles of optical fiber crafted aboard ISS marks manufacturing first
- AI at the edge: from theory to practice
- SpaceX, Northrop Grumman reportedly working on US spy sat program
- Some smart meters won't be smart at all once 2/3G networks mothballed
- 911 goes MIA across multiple US states, cause unclear
- Cisco creates architecture to improve security and sell you new switches
- America may end up with paid-for 5G fast lanes under net neutrality anyway
- Global IT spending forecast to reach $5.0T this year
- Fire in the Cisco! Networking giant's Duo MFA message logs stolen in phish attack
- Alibaba Cloud reveals network telemetry tool that helped cut number of engineers needed by 86%
Dragkh's Blog 