MySQL

Tricks and traps for JDBC application

zero timestamp etc..

Docker and MySQL series

1. http://severalnines.com/blog/mysql-docker-containers-understanding-basics
2. http://severalnines.com/blog/mysql-docker-building-container-image
3. http://severalnines.com/blog/mysql-docker-single-host-networking-mysql-containers
4. http://severalnines.com/blog/mysql-docker-introduction-docker-swarm-mode-and-multi-host-networking

Installation

Official download link

$ wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.27.tar.gz
$ wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.9.tar.gz
$ pwd
/home/workspace/mysql-5.6.16
$ cmake -DCMAKE_BUILD_TYPE=Debug -DCMAKE_INSTALL_PREFIX=/usr/local/mysql
$ make clean; make; make install;

$ cd /usr/local/mysql
$ cat my.cnf 
[mysqld]
innodb_file_per_table=1
datadir=/usr/local/mysql/data
general-log-file=/usr/local/mysql/general.log
log-error=/usr/local/mysql/mysqld.log
pid-file=/usr/local/mysql/mysqld.pid
slow-query-log-file=/usr/local/mysql/slow.log
socket=/usr/local/mysql/mysql.sock
$ mkdir data
$ scripts/mysql_install_db --defaults-file=my.cnf
$ /usr/bin/mysql_install_db --datadir=/var/lib/mysql --defaults-file=/usr/my.cnf

Useful cmake config options:

cmake -DCMAKE_BUILD_TYPE=Debug               \
    -DCMAKE_INSTALL_PREFIX=/usr/local/mysql  \
    -DMYSQL_DATADIR=/usr/local/mysql/data    \
    -DWITH_DEBUG=1                           \
    -DWITH_DEBUG_SYNC=1                      \
    -DWITH_VALGRIND=1                        \
    -DENABLED_PROFILING=1                    \
    -DWITH_EXTRA_CHARSETS=all                \
    -DWITH_BOOST=/usr/local/boost            \
    -DENABLED_LOCAL_INFILE=1                 \

For 5.7/8.0: Initialization of data dir

$ bin/mysqld --defaults-file=my.cnf --initialize-insecure
$ bin/mysqld --defaults-file=my.cnf
$ bin/mysql -uroot -S/usr/local/mysql/mysql.sock
mysql> select version();
$ bin/mysqld --defaults-file=my.cnf --skip-grant-tables
$ bin/mysql -uroot -S/usr/local/mysql5.7/mysql.sock

Facebook MySQL 5.6

The official steps: build steps

$ more go.sh
#!/bin/bash

# Remove the cmake cache
rm -rf CMakeCache.txt

# Set CC/CXX
CC=/opt/rh/devtoolset-3/root/usr/bin/gcc \
CXX=/opt/rh/devtoolset-3/root/usr/bin/g++ \
cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DWITH_SSL=system \
-DWITH_ZLIB=bundled -DMYSQL_MAINTAINER_MODE=0 -DENABLED_LOCAL_INFILE=1 \
-DENABLE_DTRACE=0

make -j

1. If lz4 is missing, visit lz4.
2. Windows not supported.
3. With RocksDB
4. Depends on boost, zstd etc.
5. Configure ZSTD with path: cmake . -DWITH_ZSTD=/usr

Oracle MySQL 8.0

The official steps: build steps. Each minor version may depend on different version of boost. For example, the earlier versions want 1.60, and is not compatible with boost 1.61.0, while 8.0.13 want boost>=1.67.0, 8.0.14 wants 1.68.0 or above.

Notes:

1. Add -DWITH_BOOST=/usr/local/boost_1_60_0
2. CXXFLAGS=-Woverloaded-virtual
3. make VERBOSE=1
4. make help

MariaDB 10.x

New contributer presentation Optimizer

sysbench

$ git clone https://github.com/akopytov/sysbench.git
$ brew install autotool
$ brew install automake
$ brew install pkg-config
$ brew install luajit
$ ./autogen.sh
$ ./configure

$ ./sysbench --test=cpu run

$ ./sysbench --test=fileio --file-test-mode=seqrd prepare
$ ./sysbench --test=fileio --file-test-mode=seqrd run
$ ./sysbench --test=fileio --file-test-mode=seqrd cleanup

$ ./sysbench --test=tests/db/select.lua --mysql-db=test --mysql-user=root --mysql-socket=/usr/local/mysql/mysql.sock prepare
$ ./sysbench --test=tests/db/select.lua --mysql-db=test --mysql-user=root --mysql-socket=/usr/local/mysql/mysql.sock run
$ ./sysbench --test=tests/db/select.lua --mysql-db=test --mysql-user=root --mysql-socket=/usr/local/mysql/mysql.sock cleanup

Install boost from source by hand

https://stackoverflow.com/questions/27848105/how-to-force-compilation-of-boost-to-use-fpic

Official MySQL only need boost include headers, if want boost *.so, build it.

1. Download the source code of boost from boost.org.
2. Read the installation guide in index.html.
3. Unzip it and build it. a) Make sure to add -fPIC if want to use boost in your own *.so. b) Create the destination dir first to avoid potential privileges problems.

$ cd path/to/boost_1_67_0
$ ./bootstrap.sh --help
$ ./bootstrap.sh --prefix=path/to/installation/prefix --with-icu
$ ./b2 toolset=clang cxxflags=-fPIC cflags=-fPIC install

List make target and install only part of them

Handwriting Makefiles is hard to get the targets. But CMake generated Makefiles are easy: just call `make help’.

$ make help
$ make list_install_components
Available install components are: "Client" "DebugBinaries" "Development" "Documentation" "Info" "ManPages" "Readme" "Router" "Server" "Server_Scripts" "SharedLibraries" "SupportFiles" "Test" "TestReadme" "Unspecified"
$ cmake -DCOMPONENT="Server" -P cmake_install.cmake

Bison/Flex generated files not found or not up-to-date

$ cd bld-release
$ touch ../sql/*yy*
$ make VERBOSE=1 GenServerSource

Windows

C:\> cmake -G "Visual Studio 15 2017 Win64" -DDOWNLOAD_BOOST=1 -DWITH_BOOST=C:\Tools\boost .

ps with thread names

$ ps H -C mysqld -o 'pid tid cmd comm' | grep 'bin\/mysqld'

Valgrind

$ ./mtr --suite=xxx --max-test-fail=0 --valgrind-mysqld --valgrind-option=--leak-check=full --valgrind-option=--gen-suppressions=all --valgrind-option=--track-origins=yes --valgrind-option=-v