🍓
Raspberry Piでprimary/2replica構成のMySQL serverを建てる
業務でデータベースを扱う機会が増えたので、より身近にデータベース 特にMySQLを触れる環境が欲しくなりました。ローカルマシンなりDockerコンテナなり、手元でMySQL severを建てることは容易ですが、せっかくなので家に余っているRaspberry Pi上で動かしてみようと思います。
環境
- Raspberry Pi 4 x 3台
- 1台をprimary, 残りをreplicaにします
- SDは全台128GiB
- OS: Debian Bookworm 64GiB
- MySQL version: 8.0.37
- 2024/05/05時点で8.0系の最新
- 社内標準が8.0系であるため
$ lsb_release -a
No LSB modules are available.
Distributor ID: Debian
Description: Debian GNU/Linux 12 (bookworm)
Release: 12
Codename: bookworm
- sshの許可
- IPアドレス固定
- cmakeのインストール
まで実施済み。
ネットワーク構成
作業は全てローカルマシン(Macbook)のterminalからsshして実行しています。以下プロンプトが ❯ ならMacbook、$ ならssh先のラズパイ上で実行したものです。
Macbookもラズパイ全台もWifiに繋げているので、ssh接続情報はこんな感じ。
❯ vi .ssh/config
Host koutei
HostName 192.168.11.11
User kumashun
Host iwatobi
HostName 192.168.11.17
User kumashun
Host adelie
HostName 192.168.11.16
User kumashun
❯ ssh koutei
Warning: Permanently added '192.168.11.11' (ED25519) to the list of known hosts.
kumashun@192.168.11.11's password:
Linux raspberrypi 6.6.20+rpt-rpi-v8 #1 SMP PREEMPT Debian 1:6.6.20-1+rpt1 (2024-03-07) aarch64
The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
Last login: Sat May 18 11:08:50 2024 from 192.168.11.5
Wi-Fi is currently blocked by rfkill.
Use raspi-config to set the country before use.
kumashun@raspberrypi:~ $
/etc/hosts も書いてるので名前解決もできている状態です。
❯ cat /etc/hosts
(他省略)
192.168.11.11 koutei
192.168.11.17 iwatobi
192.168.11.16 adelie
❯ ping iwatobi
PING iwatobi (192.168.11.17): 56 data bytes
64 bytes from 192.168.11.17: icmp_seq=0 ttl=64 time=2.843 ms
64 bytes from 192.168.11.17: icmp_seq=1 ttl=64 time=1.199 ms
64 bytes from 192.168.11.17: icmp_seq=2 ttl=64 time=1.094 ms
64 bytes from 192.168.11.17: icmp_seq=3 ttl=64 time=2.605 ms
^C
--- iwatobi ping statistics ---
4 packets transmitted, 4 packets received, 0.0% packet loss
round-trip min/avg/max/stddev = 1.094/1.935/2.843/0.794 ms
MySQL serverのインストール
sshできればVSCodeのremote sshが使えるので、デバッグも兼ねてソースコードを落としてビルドしたserverを起動させます。なおshellの実行ログは全台、primary、replicaなど実行したラズパイも記しておきます。
まずはgit clone。リポジトリがめちゃでかいのと、ネットワークが重かったのでdepthを指定して取得するデータを最小限にしました。
全台
$ mkdir workspace
$ cd workspace/
$ git clone https://github.com/mysql/mysql-server.git --depth=1
その後ビルドしたいバージョンにチェックアウト。
全台
$ cd mysql-server
$ git fetch origin --tags mysql-8.0.37 --depth=1
$ git checkout mysql-8.0.37
あとはDocに沿ってcmak -> makeの順に実行して、バイナリを生成するだけです。
...だけですと言いつつ、必要なオプションやライブラリが見つかるたびにやり直したので、結果必要だったものだけを載せます。
全台
# cmakeでlld linkerを使っていたので、そのためのsymlinkを貼る (よくわからん)
$ sudo ln -s /usr/bin/ld.lld-* /usr/bin/ld.lld
$ sudo apt-get install -y libssl-dev bison
$ cd ..
$ mkdir mysql-build && cd $_
# -DWITH_DEBUG=1 デバッグモードでビルド
# -DDOWNLOAD_BOOST=1, -DWITH_BOOST=mysql-8.0.37/boost 指定のboostライブラリをインストール
$ cmake ../mysql-server -DWITH_DEBUG=1 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=mysql-8.0.37/boost
$ make
[ 0%] Built target lz4_lib
(略)
[100%] Linking CXX executable ../../../runtime_output_directory/routertest_integration_routing_sharing_restart
[100%] Built target routertest_integration_routing_sharing_restart
$ bin/mysql --version
bin/mysql Ver 8.0.37 for Linux on aarch64 (Source distribution)
やり直しが気になる人は以下。
試行錯誤ログ
> cd <some build directory>
cmake <path to source directory>
make
This will give you a release (actually RelWithDebInfo) build,
with compiler options taken from
../cmake/build_configurations/compiler_options.cmake
Adding -DWITH_DEBUG=1 to the cmake command line gives you a debug build.
```shell:全台
$ cmake . -DWITH_DEBUG=1
-- Running cmake version 3.25.1
-- Found Git: /usr/bin/git (found version "2.39.2")
-- CMAKE_MODULE_PATH is /home/kumashun/workspace/mysql-server/cmake
-- MySQL 8.0.37
-- The C compiler identification is GNU 12.2.0
-- The CXX compiler identification is GNU 12.2.0
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- Check for working C compiler: /usr/bin/cc - skipped
-- Detecting C compile features
-- Detecting C compile features - done
-- Detecting CXX compiler ABI info
-- Detecting CXX compiler ABI info - done
-- Check for working CXX compiler: /usr/bin/c++ - skipped
-- Detecting CXX compile features
-- Detecting CXX compile features - done
-- Source directory /home/kumashun/workspace/mysql-server
-- Binary directory /home/kumashun/workspace/mysql-server
CMake Error at CMakeLists.txt:617 (MESSAGE):
Please do not build in-source. Out-of source builds are highly
recommended: you can have multiple builds for the same source, and there is
an easy way to do cleanup, simply remove the build directory (note that
'make clean' or 'make distclean' does *not* work)
You *can* force in-source build by invoking cmake with
-DFORCE_INSOURCE_BUILD=1
-- Configuring incomplete, errors occurred!
See also "/home/kumashun/workspace/mysql-server/CMakeFiles/CMakeOutput.log".
ググっていると同じハマり方をしているのを見つけたので、倣う。
全台
$ mv CMakeCache.txt _CMakeCache.txt
$ cd ..
$ mkdir mysql-build
$ cd mysql-build
全台
$ cmake ../mysql-server -DWITH_DEBUG=1
-- Running cmake version 3.25.1
-- Found Git: /usr/bin/git (found version "2.39.2")
-- CMAKE_MODULE_PATH is /home/kumashun/workspace/mysql-server/cmake
-- MySQL 8.0.37
-- The C compiler identification is GNU 12.2.0
-- The CXX compiler identification is GNU 12.2.0
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- Check for working C compiler: /usr/bin/cc - skipped
-- Detecting C compile features
-- Detecting C compile features - done
-- Detecting CXX compiler ABI info
-- Detecting CXX compiler ABI info - done
-- Check for working CXX compiler: /usr/bin/c++ - skipped
-- Detecting CXX compile features
-- Detecting CXX compile features - done
-- Source directory /home/kumashun/workspace/mysql-server
-- Binary directory /home/kumashun/workspace
-- CMAKE_GENERATOR: Unix Makefiles
-- Looking for SHM_HUGETLB
-- Looking for SHM_HUGETLB - found
-- Looking for sys/types.h
-- Looking for sys/types.h - found
-- Looking for stdint.h
-- Looking for stdint.h - found
-- Looking for stddef.h
-- Looking for stddef.h - found
-- Check size of void *
-- Check size of void * - done
-- SIZEOF_VOIDP 8
-- Performing Test HAVE_C_FLOATING_POINT_FUSED_MADD
-- Performing Test HAVE_C_FLOATING_POINT_FUSED_MADD - Success
-- Performing Test HAVE_CXX_FLOATING_POINT_FUSED_MADD
-- Performing Test HAVE_CXX_FLOATING_POINT_FUSED_MADD - Success
-- Packaging as: mysql-8.0.37-Linux-aarch64
-- Performing Test C_LD_lld_RESULT
-- Performing Test C_LD_lld_RESULT - Failed
-- Performing Test CXX_LD_lld_RESULT
-- Performing Test CXX_LD_lld_RESULT - Failed
-- Cannot use lld on this platform
-- Performing Test HAVE_OUTLINE_ATOMICS
-- Performing Test HAVE_OUTLINE_ATOMICS - Success
-- Looked for boost/version.hpp in and
-- BOOST_INCLUDE_DIR BOOST_INCLUDE_DIR-NOTFOUND
-- LOCAL_BOOST_DIR
-- LOCAL_BOOST_ZIP
-- Could not find (the correct version of) boost.
-- MySQL currently requires boost_1_77_0
CMake Error at cmake/boost.cmake:109 (MESSAGE):
You can download it with -DDOWNLOAD_BOOST=1 -DWITH_BOOST=<directory>
This CMake script will look for boost in <directory>. If it is not there,
it will download and unpack it (in that directory) for you.
You can also download boost manually, from
https://archives.boost.io/release/1.77.0/source/boost_1_77_0.tar.bz2
If you are inside a firewall, you may need to use an https proxy:
export https_proxy=http://example.com:80
Call Stack (most recent call first):
cmake/boost.cmake:278 (COULD_NOT_FIND_BOOST)
CMakeLists.txt:1563 (INCLUDE)
-- Configuring incomplete, errors occurred!
See also "/home/kumashun/workspace/CMakeFiles/CMakeOutput.log".
See also "/home/kumashun/workspace/CMakeFiles/CMakeError.log".
うーんまだだめ。エラーログを見てみます。
$ cat CMakeFiles/CMakeError.log
Performing C SOURCE FILE Test C_LD_lld_RESULT failed with the following output:
Change Dir: /home/kumashun/workspace/CMakeFiles/CMakeScratch/TryCompile-NDxtEv
Run Build Command(s):/usr/bin/gmake -f Makefile cmTC_23e75/fast && /usr/bin/gmake -f CMakeFiles/cmTC_23e75.dir/build.make CMakeFiles/cmTC_23e75.dir/build
gmake[1]: ディレクトリ '/home/kumashun/workspace/CMakeFiles/CMakeScratch/TryCompile-NDxtEv' に入ります
Building C object CMakeFiles/cmTC_23e75.dir/src.c.o
/usr/bin/cc -DC_LD_lld_RESULT -fno-omit-frame-pointer -ffp-contract=off -fPIE -o CMakeFiles/cmTC_23e75.dir/src.c.o -c /home/kumashun/workspace/CMakeFiles/CMakeScratch/TryCompile-NDxtEv/src.c
Linking C executable cmTC_23e75
/usr/bin/cmake -E cmake_link_script CMakeFiles/cmTC_23e75.dir/link.txt --verbose=1
/usr/bin/cc -fno-omit-frame-pointer -ffp-contract=off CMakeFiles/cmTC_23e75.dir/src.c.o -o cmTC_23e75 -fuse-ld=lld
collect2: fatal error: cannot find ‘ld’
compilation terminated.
gmake[1]: *** [CMakeFiles/cmTC_23e75.dir/build.make:99: cmTC_23e75] エラー 1
gmake[1]: ディレクトリ '/home/kumashun/workspace/CMakeFiles/CMakeScratch/TryCompile-NDxtEv' から出ます
gmake: *** [Makefile:127: cmTC_23e75/fast] エラー 2
Source file was:
int main() {}
Performing C++ SOURCE FILE Test CXX_LD_lld_RESULT failed with the following output:
Change Dir: /home/kumashun/workspace/CMakeFiles/CMakeScratch/TryCompile-TSSXPQ
Run Build Command(s):/usr/bin/gmake -f Makefile cmTC_012de/fast && /usr/bin/gmake -f CMakeFiles/cmTC_012de.dir/build.make CMakeFiles/cmTC_012de.dir/build
gmake[1]: ディレクトリ '/home/kumashun/workspace/CMakeFiles/CMakeScratch/TryCompile-TSSXPQ' に入ります
Building CXX object CMakeFiles/cmTC_012de.dir/src.cxx.o
/usr/bin/c++ -DCXX_LD_lld_RESULT -std=c++17 -fno-omit-frame-pointer -ffp-contract=off -fPIE -o CMakeFiles/cmTC_012de.dir/src.cxx.o -c /home/kumashun/workspace/CMakeFiles/CMakeScratch/TryCompile-TSSXPQ/src.cxx
Linking CXX executable cmTC_012de
/usr/bin/cmake -E cmake_link_script CMakeFiles/cmTC_012de.dir/link.txt --verbose=1
/usr/bin/c++ -std=c++17 -fno-omit-frame-pointer -ffp-contract=off CMakeFiles/cmTC_012de.dir/src.cxx.o -o cmTC_012de -fuse-ld=lld
collect2: fatal error: cannot find ‘ld’
compilation terminated.
gmake[1]: *** [CMakeFiles/cmTC_012de.dir/build.make:99: cmTC_012de] エラー 1
gmake[1]: ディレクトリ '/home/kumashun/workspace/CMakeFiles/CMakeScratch/TryCompile-TSSXPQ' から出ます
gmake: *** [Makefile:127: cmTC_012de/fast] エラー 2
Source file was:
int main() {}
ここかな
/usr/bin/c++ -std=c++17 -fno-omit-frame-pointer -ffp-contract=off CMakeFiles/cmTC_012de.dir/src.cxx.o -o cmTC_012de -fuse-ld=lld
collect2: fatal error: cannot find ‘ld’
なんかsymlink貼ればいいっぽい。
$ sudo ln -s /usr/bin/ld.lld-* /usr/bin/ld.lld
$ cmake ../mysql-server -DWITH_DEBUG=1
-- Running cmake version 3.25.1
-- CMAKE_MODULE_PATH is /home/kumashun/workspace/mysql-server/cmake
-- MySQL 8.0.37
-- Source directory /home/kumashun/workspace/mysql-server
-- Binary directory /home/kumashun/workspace
-- CMAKE_GENERATOR: Unix Makefiles
-- SIZEOF_VOIDP 8
-- Packaging as: mysql-8.0.37-Linux-aarch64
-- Cannot use lld on this platform
-- Looked for boost/version.hpp in and
-- BOOST_INCLUDE_DIR BOOST_INCLUDE_DIR-NOTFOUND
-- LOCAL_BOOST_DIR
-- LOCAL_BOOST_ZIP
-- Could not find (the correct version of) boost.
-- MySQL currently requires boost_1_77_0
CMake Error at cmake/boost.cmake:109 (MESSAGE):
You can download it with -DDOWNLOAD_BOOST=1 -DWITH_BOOST=<directory>
This CMake script will look for boost in <directory>. If it is not there,
it will download and unpack it (in that directory) for you.
You can also download boost manually, from
https://archives.boost.io/release/1.77.0/source/boost_1_77_0.tar.bz2
If you are inside a firewall, you may need to use an https proxy:
export https_proxy=http://example.com:80
Call Stack (most recent call first):
cmake/boost.cmake:278 (COULD_NOT_FIND_BOOST)
CMakeLists.txt:1563 (INCLUDE)
-- Configuring incomplete, errors occurred!
See also "/home/kumashun/workspace/CMakeFiles/CMakeOutput.log".
See also "/home/kumashun/workspace/CMakeFiles/CMakeError.log".
boostというライブラリが必要で、一緒にインストールしたい場合はオプション引数を渡す必要がありそうです。
You can download it with -DDOWNLOAD_BOOST=1 -DWITH_BOOST=<directory>
kumashun@raspberrypi:~/workspace $ cmake mysql-server -DWITH_DEBUG=1 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=mysql-8.0.37/boost
-- Running cmake version 3.25.1
-- CMAKE_MODULE_PATH is /home/kumashun/workspace/mysql-server/cmake
-- MySQL 8.0.37
-- Source directory /home/kumashun/workspace/mysql-server
-- Binary directory /home/kumashun/workspace
-- CMAKE_GENERATOR: Unix Makefiles
-- SIZEOF_VOIDP 8
-- Packaging as: mysql-8.0.37-Linux-aarch64
-- Cannot use lld on this platform
-- Downloading boost_1_77_0.tar.bz2 to /home/kumashun/workspace/mysql-8.0.37/boost
-- [download 0% complete]
-- [download 1% complete]
-- [download 2% complete]
(略)
-- ZSTD_LEGACY_SUPPORT not defined!
--
Not a supported openssl version in WITH_SSL=system.
Make sure you have specified a supported SSL version.
Valid options are :
openssl[0-9]+ (use alternative system library)
yes (synonym for system)
</path/to/custom/openssl/installation>
CMake Error at cmake/ssl.cmake:84 (MESSAGE):
Please install the appropriate openssl developer package.
Call Stack (most recent call first):
cmake/ssl.cmake:380 (FATAL_SSL_NOT_FOUND_ERROR)
CMakeLists.txt:1796 (MYSQL_CHECK_SSL)
-- Configuring incomplete, errors occurred!
See also "/home/kumashun/workspace/CMakeFiles/CMakeOutput.log".
See also "/home/kumashun/workspace/CMakeFiles/CMakeError.log".
わかりやすいエラーになった。openssl-devやら必要なライブラリを都度インストールしていきます。
全台
$ sudo apt-get -install -y libssl-dev libncurses5-dev
$ cmake ../mysql-server -DWITH_DEBUG=1 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=mysql-8.0.37/boost
-- Performing Test HAVE_CXX_W_error_maybe_uninitialized
-- Performing Test HAVE_CXX_W_error_maybe_uninitialized - Success
-- LINK_FLAGS for mysqld -Wl,--build-id=sha1 -Wl,-rpath,'$ORIGIN/../lib/private'
CMake Error at sql/CMakeLists.txt:1320 (MESSAGE):
Cannot find /home/kumashun/workspace/mysql-server/sql/sql_yacc.h
-- Configuring incomplete, errors occurred!
See also "/home/kumashun/workspace/mysql-build/CMakeFiles/CMakeOutput.log".
See also "/home/kumashun/workspace/mysql-build/CMakeFiles/CMakeError.log".
git cloneしたてのリポジトリ内で sql/sql_yacc.h が不足している??
$ ls ../mysql-server/sql | grep sql_yacc
sql_yacc.yy
エラーメッセージからCMakeListが落ちた箇所を見ると、USE_BISON_RESULTS_FROM_MAKE_DIST がonの時の挙動らしい。github
mysql-server/sql/CMakeLists.txt
IF(USE_BISON_RESULTS_FROM_MAKE_DIST)
# No bison found, see if generated files are available as sources.
FOREACH(genfile sql_yacc.h sql_yacc.cc sql_hints.yy.h sql_hints.yy.cc)
IF(NOT EXISTS "${CMAKE_CURRENT_SOURCE_DIR}/${genfile}")
MESSAGE(FATAL_ERROR "Cannot find ${CMAKE_CURRENT_SOURCE_DIR}/${genfile}")
ENDIF()
さらに読むと、BISON_FOUNDがfalseの時に同変数がONにされそう。github
mysql-server/sql/CMakeLists.txt
# We have already included cmake/bison.cmake, and done FIND_PACKAGE(BISON)
IF(BISON_FOUND)
SET(USE_BISON_RESULTS_FROM_MAKE_DIST_DEFAULT OFF)
ELSE()
SET(USE_BISON_RESULTS_FROM_MAKE_DIST_DEFAULT ON)
ENDIF()
...
# bison should be REQUIRED, but we have to support source tarball build.
# https://dev.mysql.com/doc/refman/8.0/en/source-installation.html
OPTION(USE_BISON_RESULTS_FROM_MAKE_DIST
"Use bison results from source distribution tarball"
${USE_BISON_RESULTS_FROM_MAKE_DIST_DEFAULT}
)
そしてBISON_FOUNDがtrueになることはなさそう?なので、徐にbisonをインストール。
$ sudo apt-get install -y bison
Got a KOTONAKI!!
全台
-- CMAKE_BUILD_TYPE: Debug
-- COMPILE_DEFINITIONS: _GNU_SOURCE;_FILE_OFFSET_BITS=64;BOOST_NO_CXX98_FUNCTION_BASE;HAVE_CONFIG_H;__STDC_LIMIT_MACROS;__STDC_FORMAT_MACROS;_USE_MATH_DEFINES;LZ4_DISABLE_DEPRECATE_WARNINGS;HAVE_TLSv13
-- CMAKE_C_FLAGS: -fno-omit-frame-pointer -ffp-contract=off -moutline-atomics -Wall -Wextra -Wformat-security -Wvla -Wundef -Wmissing-format-attribute -Wwrite-strings -Wjump-misses-init -Wstringop-truncation -Wmissing-include-dirs -Werror
-- CMAKE_CXX_FLAGS: -std=c++17 -fno-omit-frame-pointer -ffp-contract=off -moutline-atomics -march=armv8-a+crc -Wall -Wextra -Wformat-security -Wvla -Wundef -Wmissing-format-attribute -Woverloaded-virtual -Wcast-qual -Wimplicit-fallthrough=5 -Wstringop-truncation -Wsuggest-override -Wmissing-include-dirs -Wextra-semi -Wlogical-op -Werror
-- CMAKE_CXX_FLAGS_DEBUG: -DSAFE_MUTEX -DENABLED_DEBUG_SYNC -g
-- CMAKE_CXX_FLAGS_RELWITHDEBINFO: -ffunction-sections -fdata-sections -O2 -g -DNDEBUG -g1
-- CMAKE_CXX_FLAGS_RELEASE: -ffunction-sections -fdata-sections -O3 -DNDEBUG
-- CMAKE_CXX_FLAGS_MINSIZEREL: -ffunction-sections -fdata-sections -Os -DNDEBUG
-- CMAKE_C_LINK_FLAGS:
-- CMAKE_CXX_LINK_FLAGS:
-- CMAKE_EXE_LINKER_FLAGS
-- CMAKE_MODULE_LINKER_FLAGS
-- CMAKE_SHARED_LINKER_FLAGS
-- Configuring done
-- Generating done
-- Build files have been written to: /home/kumashun/workspace/mysql-build
しかしmakeでもこける...
全台
$ make
[ 0%] Built target lz4_lib
[ 0%] Built target gmock
[ 0%] Built target gtest
(略)
[ 56%] Built target mf_iocache_test
[ 56%] Linking CXX executable ../runtime_output_directory/build_id_test
Verifying build-id
make[2]: *** [mysys/CMakeFiles/build_id_test.dir/build.make:116: runtime_output_directory/build_id_test] エラー 1
make[2]: *** ファイル 'runtime_output_directory/build_id_test' を削除します
make[1]: *** [CMakeFiles/Makefile2:12295: mysys/CMakeFiles/build_id_test.dir/all] エラー 2
make: *** [Makefile:166: all] エラー 2
一応落ちてる箇所 mysys/CMakeFiles/build_id_test.dir/build.make:116はここ。
cd /home/kumashun/workspace/mysql-build/runtime_output_directory && LANG=C /usr/bin/readelf -n ./build_id_test | grep "Build ID:" | egrep -o [0-9a-f]{40} > build_id_test_2
前後をもう少し見てみる。何をテストしているかはわからないけど、build_id_test_1とbuild_id_test_2を生成してからdiffを取るロジックで、後者の生成に失敗しているらしい。
runtime_output_directory/build_id_test: mysys/CMakeFiles/build_id_test.dir/link.txt
@$(CMAKE_COMMAND) -E cmake_echo_color --switch=$(COLOR) --green --bold --progress-dir=/home/kumashun/workspace/mysql-build/CMakeFiles --progress-num=$(CMAKE_PROGRESS_3) "Linking CXX executable ../runtime_output_directory/build_id_test"
cd /home/kumashun/workspace/mysql-build/mysys && $(CMAKE_COMMAND) -E cmake_link_script CMakeFiles/build_id_test.dir/link.txt --verbose=$(VERBOSE)
@$(CMAKE_COMMAND) -E cmake_echo_color --switch=$(COLOR) --blue --bold "Verifying build-id"
cd /home/kumashun/workspace/mysql-build/runtime_output_directory && ./build_id_test | egrep -o [0-9a-f]{40} > build_id_test_1
cd /home/kumashun/workspace/mysql-build/runtime_output_directory && LANG=C /usr/bin/readelf -n ./build_id_test | grep "Build ID:" | egrep -o [0-9a-f]{40} > build_id_test_2
cd /home/kumashun/workspace/mysql-build/runtime_output_directory && diff build_id_test_1 build_id_test_2
一旦再実行してみるか...でまさかの成功。
全台
$ make clean
$ make
(略)
[ 56%] Built target mf_iocache_test
[ 56%] Built target build_id_test
[ 56%] Building CXX object libmysql/CMakeFiles/clientlib_objlib.dir/libmysql.cc.o
(略)
[100%] Linking CXX executable ../../../runtime_output_directory/routertest_integration_routing_sharing_restart
[100%] Built target routertest_integration_routing_sharing_restart
$ bin/mysql --version
bin/mysql Ver 8.0.37 for Linux on aarch64 (Source distribution)
とりあえず次に進みます。
MySQL serverの起動
バイナリを生成したので、ドキュメント通りに初期化してサーバーを起動させます。ビルドしたディレクトリのまま作業を続けます。
まずデータディレクトリの初期化から。
全台
$ pwd
/path/to/workspace/mysql-build
$ mkdir mysql-files
$ sudo useradd -r -g mysql -s /bin/false mysql
$ bin/mysqld --initialize --user=mysql
$ sudo chown mysql:mysql mysql-files
$ sudo chmod 750 mysql-files
$ bin/mysqld --initialize --user=mysql
2024-05-07T16:03:57.108888Z 0 [System] [MY-013169] [Server] /home/kumashun/workspace/mysql-build/runtime_output_directory/mysqld (mysqld 8.0.37-debug) initializing of server in progress as process 77981
2024-05-07T16:03:57.121603Z 0 [Warning] [MY-010122] [Server] One can only use the --user switch if running as root
2024-05-07T16:03:57.177297Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-05-07T16:04:02.204701Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-05-07T16:04:24.752673Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: <rootユーザーの初期バスワード>
いよいよmysqldをデーモンとして立ち上げて、MySQL serverを起動させます!!
全台
$ bin/mysqld --user=mysql &
[1] 78051
16:12:40.080067Z 0 [System] [MY-010116] [Server] /home/kumashun/workspace/mysql-build/runtime_output_directory/mysqld (mysqld 8.0.37-debug) starting as process 78051
2024-05-07T16:12:40.090785Z 0 [Warning] [MY-010122] [Server] One can only use the --user switch if running as root
2024-05-07T16:12:40.487861Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-05-07T16:12:41.828659Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-05-07T16:12:48.205201Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-05-07T16:12:48.205399Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-05-07T16:12:48.808632Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2024-05-07T16:12:48.809502Z 0 [System] [MY-010931] [Server] /home/kumashun/workspace/mysql-build/runtime_output_directory/mysqld: ready for connections. Version: '8.0.37-debug' socket: '/tmp/mysql.sock' port: 3306 Source distribution.
$ bin/mysql -uroot -p
Enter password: <rootユーザーの初期バスワード>
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.37-debug
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
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ユーザーとしてログインできました。
あとはrootユーザーのパスワードをリセットすることでクエリを実行可能にします。
全台
mysql> \s
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.13 sec)
mysql> ^DBye
$ bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.37-debug Source distribution
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
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> \s
--------------
bin/mysql Ver 8.0.37 for Linux on aarch64 (Source distribution)
Connection id: 10
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.37-debug Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /tmp/mysql.sock
Binary data as: Hexadecimal
Uptime: 22 hours 22 min 14 sec
Threads: 2 Questions: 12 Slow queries: 0 Opens: 130 Flush tables: 4 Open tables: 10 Queries per second avg: 0.000
--------------
mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 81161 | Waiting on empty queue | NULL |
| 11 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
2 rows in set, 1 warning (0.00 sec)
mysql>
ヨシッ!!
ついでにずっとrootで作業するわけにもいかないのでoperatorユーザーを作ります。
全台
mysql> SHOW GRANTS FOR operator\G
*************************** 1. row ***************************
Grants for operator@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO `operator`@`%` WITH GRANT OPTION
1 row in set (0.00 sec)
Macbookから直接mysql clientに繋ぐ時はこんな感じ。
❯ mysql -hkoutei -uoperator -poperator
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 70
Server version: 8.0.37-debug Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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>
初期データの用意
空っぽだと面白くないので、よく使うサンプルデータを流しておきます。
全台
$ cd /path/to/workspace
$ git clone https://github.com/datacharmer/test_db.git
$ cd test_db
$ ../mysql-build/bin/mysql -uroot -p < ./employees.sql
$ ../mysql-build/bin/mysql -uroot -p employees
mysql> show tables;
+----------------------+
| Tables_in_employees |
+----------------------+
| current_dept_emp |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| salaries |
| titles |
+----------------------+
8 rows in set (0.01 sec)
primary のセットアップ
ここからreplicationの設定を行います。初期化同様にドキュメント通りに進めればOK。
まずはprimaryで、replication用のユーザーを作成します。
primary
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
これもトライアンドエラーで分かったことですが、replユーザーの認証プラグインを mysql_native_password に変更します。デフォルト認証プラグインがよりセキュアな caching_sha2_password で、ドキュメント通りにサクッとreplicationを設定できなかったからです。仕事ならまだしもお家環境なので早さ優先ということで何卒,,,
primary
mysql> prompt prm>
PROMPT set to 'prm> '
prm> SELECT User, Host, Plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| User | Host | Plugin |
+------------------+-----------+-----------------------+
| operator | % | caching_sha2_password |
| repl | % | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
6 rows in set (0.01 sec)
prm> ALTER USER 'repl' IDENTIFIED WITH mysql_native_password BY 'repl';
Query OK, 0 rows affected (0.07 sec)
prm> SELECT User, Host, Plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| User | Host | Plugin |
+------------------+-----------+-----------------------+
| operator | % | caching_sha2_password |
| repl | % | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
6 rows in set (0.02 sec)
最後に SHOW MASTER STATUS でreplicationを開始するbinlogの位置を特定します。
primary
prm> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000004 | 66380401 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
replica のセットアップ
残るはreplica側の設定です。
replicationのトポロジー内では server_id をユニークにする必要があるので、デフォルトの1からそれぞれ2,3に変更します。
replica
mysql> SET GLOBAL server_id = 2; #もう片方は3
mysql> prompt rpl1> # もう片方はrpl2>
PROMPT set to 'rpl1> '
続いて CHANGE REPLICATION SOURCE TO でprimaryへの接続情報、binlogの開始位置を設定します。
replica
rpl1> CHANGE REPLICATION SOURCE TO
-> SOURCE_HOST='192.168.11.11',
-> SOURCE_USER='repl',
-> SOURCE_PASSWORD='repl',
-> SOURCE_LOG_FILE='binlog.000004',
-> SOURCE_LOG_POS=66381381;
Query OK, 0 rows affected, 2 warnings (0.14 sec)
rpl1> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.11.11
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 66381381
Relay_Log_File: raspberrypi-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: binlog.000004
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 66381381
Relay_Log_Space: 157
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 698b03f3-0c8b-11ef-b165-e45f010063b7
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
SHOW REPLICA STATUS がエラーを吐いてないので、replicationプロセスをスタートします。
replica
rpl1> START REPLICA;
Query OK, 0 rows affected (0.14 sec)
mysql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.11.11
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 66381381
Relay_Log_File: raspberrypi-relay-bin.000002
Relay_Log_Pos: 323
Relay_Master_Log_File: binlog.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 66381381
Relay_Log_Space: 539
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 698b03f3-0c8b-11ef-b165-e45f010063b7
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.01 sec)
replicationを開始したことで、primaryからのreplication遅延時間を表す Seconds_Behind_Master がnullから0に変わりました!!
無事3台のMySQL serverの起動、replication設定まで完了です 🎉
試しにprimaryに書き込んだ内容がreplica2台ともに反映されていることも確認。
prm> SELECT COUNT(*) FROM employees;
+----------+
| COUNT(*) |
+----------+
| 300027 |
+----------+
1 row in set (0.66 sec)
rpl1> SELECT COUNT(*) FROM employees;
+----------+
| COUNT(*) |
+----------+
| 300027 |
+----------+
1 row in set (0.66 sec)
rpl2> SELECT COUNT(*) FROM employees;
+----------+
| COUNT(*) |
+----------+
| 300027 |
+----------+
1 row in set (0.66 sec)
prm> INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date)
-> VALUES (500003, '1990-10-01', 'Shiro', 'Amada', 'M', '2019-04-04');
Query OK, 1 row affected (0.03 sec)
prm> SELECT COUNT(*) FROM employees;
+----------+
| COUNT(*) |
+----------+
| 300028 |
+----------+
1 row in set (0.76 sec)
rpl1> SELECT COUNT(*) FROM employees;
+----------+
| COUNT(*) |
+----------+
| 300028 |
+----------+
1 row in set (0.77 sec)
rpl2> SELECT COUNT(*) FROM employees;
+----------+
| COUNT(*) |
+----------+
| 300028 |
+----------+
1 row in set (0.77 sec)
画面録画も撮ったので良ければ!!
終わりに
cmakeでけっこう詰まってしまいましたが、あとの流れは公式ドキュメント通りに進めることができました!! ビルド済みバイナリを使う手もありましたが、Github経由でコードを取ってきたことでバージョンごとの挙動の違いをサクッと確認できそうな点が良かったと思います。
この記事の時点ではreplicaが2台いる意味があまりないので、これからの検証で活用していきます!!
Discussion
良い記事ですね〜