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
$ mkdir bld && cd bld
$ 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 12

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

Since MySQL 8.0 use caching_sha2_password by default, to make sure sys bench works, install libmysqlclient to the default path or tell sysbench to use which libmysqlclient when configure: –with-mysql-libs, –with-mysql-includes. See https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html

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

0.x series

$ cd /usr/local/bin
$ ./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

1.0 series

$ /usr/local/share/sysbench/oltp_read_only.lua --mysql-db=test --mysql-user=root --mysql-socket=/usr/local/mysql/mysql.sock prepare 

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 and verify the shasum.
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_source
$ ./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