特色
mysql/galera 是一種多主同步叢集，但隻限于使用 mysql/innodb 引擎，并具有下面特點
同步複制
多個主伺服器的拓撲結構
可以在任意節點上進行讀寫
自動控制成員，自動删除故障節點
自動加入節點
真正給予行級别的并發複制
排程客戶連接配接
優勢
參考下面基于 dbms 叢集的解決方法
不存在從伺服器角色
不存在事務丢失
讀寫操作可根據需要進行随意擴充
更少的闩操作
知識點
mysql/galera 叢集使用 galera 庫執行複制，對應 galera 複制接口，我們需要mysql 伺服器支援 wsrep api 接口
<a target="_blank" href="http://www.codership.com/products/mysql-write-set-replication-project">http://www.codership.com/products/mysql-write-set-replication-project</a>
是否可以使用 mysql 而不使用 mariadb?
不可以，因為 mysql 中沒有支援 wsrep_ 資料複制的參數，當然代碼級别上也具有很大差别
工作原理
mariadb 可以看做是常見的資料庫，負責連接配接應用(web, api 等）
單純的 mariadb 無法實作多個主伺服器資料同步
多台資料庫中資料同步由 wsrep 接口實作
最終目标，實作多個 mysql 同時讀寫
wsrep api
wsrep api 是一種資料庫插件接口，比較類似一種應用程式，主要針對寫複制
該程式主要用于定義應用程式如何調用複制庫實作回寫
wsrep api 由支援改庫的應用程式動态裝載
全局事務id(gtid)
wsrep api 描述下面複制模型，一個應用程式，如資料庫目前的一個對象，目前被用戶端修改，對象改變導緻事務産生一系列的原子性改變， 在叢集中所有的節點都具備相同的對象，并由同步複制應用都各自節點，按照相同的順序産生相同變化進而實作資料同步
到最後，wsrep api 将會配置設定一個全局事務id 該 id 具有下面功能
辨別對象的改變
辨別對象自身 id 最後狀态（正常情況下，id 是連續不中斷的）
gtid 包含
一個 uuid 作為對象辨別及經曆改變的序号，序号會發生連續的改變
gtid 允許比較應用程式狀态，建立對象改變的順序，決定對象的變化是否需要更新 gtid
通常 gtid 會卑記錄成下面格式
45eec521-2f34-11e0-0800-2a36050b826b:94530586304
言歸正傳，我們需要編譯 mariadb-mysql 及 galera 插件
galera/mysql 編譯步驟
<a target="_blank" href="https://downloads.mariadb.org/interstitial/mariadb-galera-5.5.33a/kvm-tarbake-jaunty-x86/mariadb-galera-5.5.33a.tar.gz/from/http://mirrors.scie.in/mariadb">https://downloads.mariadb.org/interstitial/mariadb-galera-5.5.33a/kvm-tarbake-jaunty-x86/mariadb-galera-5.5.33a.tar.gz/from/http://mirrors.scie.in/mariadb</a>
yum install -y cmake
tar xf mariadb-galera-5.5.33a.tar.gz
cd mariadb-5.5.33a/
cmake -lah
參考 cmakecache.txt 檔案中的配置資訊
預設情況下， mariadb 安裝在 /usr/local/mysql
galera 編譯
<a target="_blank" href="https://launchpad.net/galera/2.x/23.2.7/+download/galera-23.2.7-src.tar.gz">https://launchpad.net/galera/2.x/23.2.7/+download/galera-23.2.7-src.tar.gz</a>
添加資料源
添加下面軟體包
解壓 galera-23.2.7-src.tar.gz 并進行編譯
注: scons 為編譯指令
編譯後能生成 libgalera_smm.so
複制編譯好的庫至下面位置 /usr/local/galera/lib/libgalera_smm.so
複制 啟動腳本 /usr/src/galera-23.2.7-src/scripts/mysql/mysql-galera 到 /usr/local
建立 /usr/local/mysql/etc/my.cnf
maridb 啟動測試
初始化資料庫
啟動腳本 /etc/rc.d/init.d/mysql5 確定檔案可執行權限
basedir=/usr/local/mysql
datadir=/mdb
# default value, in seconds, afterwhich the script should timeout waiting
# for server start.
# value here is overriden by value in my.cnf.
# 0 means don't wait at all
# negative numbers mean to wait indefinitely
service_startup_timeout=900
startup_sleep=1
# lock directory for redhat / suse.
lockdir='/var/lock/subsys'
lock_file_path="$lockdir/mysql"
# the following variables are only set for letting mysql.server find things.
# set some defaults
mysqld_pid_file_path=/var/run/mysqld/mysql5.pid
if test -z "$basedir"
then
basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin
if test -z "$datadir"
then
datadir=/usr/local/mysql/data
fi
sbindir=/usr/local/mysql/bin
libexecdir=/usr/local/mysql/bin
else
bindir="$basedir/bin"
datadir="$basedir/data"
sbindir="$basedir/sbin"
if test -f "$basedir/bin/mysqld"
libexecdir="$basedir/bin"
else
libexecdir="$basedir/libexec"
fi
# datadir_set is used to determine if datadir was set (and so should be
# *not* set inside of the --basedir= handler.)
datadir_set=
#
# use lsb init script functions for printing messages, if possible
lsb_functions="/lib/lsb/init-functions"
if test -f $lsb_functions ; then
. $lsb_functions
log_success_msg()
{
echo " success! $@"
}
log_failure_msg()
echo " error! $@"
path="/sbin:/usr/sbin:/bin:/usr/bin:$basedir/bin"
export path
mode=$1 # start or stop
[ $# -ge 1 ] && shift
other_args="$*" # uncommon, but needed when called from an rpm upgrade action
# expected: "--skip-networking --skip-grant-tables"
# they are not checked here, intentionally, as it is the resposibility
# of the "spec" file author to give correct arguments only.
case `echo "testing\c"`,`echo -n testing` in
*c*,-n*) echo_n= echo_c= ;;
*c*,*) echo_n=-n echo_c= ;;
*) echo_n= echo_c='\c' ;;
esac
parse_server_arguments() {
for arg do
case "$arg" in
--basedir=*) basedir=`echo "$arg" | sed -e 's/^[^=]*=//'`
bindir="$basedir/bin"
if test -z "$datadir_set"; then
datadir="$basedir/data"
fi
sbindir="$basedir/sbin"
if test -f "$basedir/bin/mysqld"
then
libexecdir="$basedir/bin"
else
libexecdir="$basedir/libexec"
libexecdir="$basedir/libexec"
;;
--datadir=*) datadir=`echo "$arg" | sed -e 's/^[^=]*=//'`
datadir_set=1
--pid-file=*) mysqld_pid_file_path=`echo "$arg" | sed -e 's/^[^=]*=//'` ;;
--service-startup-timeout=*) service_startup_timeout=`echo "$arg" | sed -e 's/^[^=]*=//'` ;;
esac
done
}
wait_for_pid () {
verb="$1" # created | removed
pid="$2" # process id of the program operating on the pid-file
pid_file_path="$3" # path to the pid file.
sst_progress_file=$datadir/sst_in_progress
i=0
avoid_race_condition="by checking again"
while test $i -ne $service_startup_timeout ; do
case "$verb" in
'created')
# wait for a pid-file to pop into existence.
test -s "$pid_file_path" && i='' && break
'removed')
# wait for this pid-file to disappear
test ! -s "$pid_file_path" && i='' && break
*)
echo "wait_for_pid () usage: wait_for_pid created|removed pid pid_file_path"
exit 1
# if server isn't running, then pid-file will never be updated
if test -n "$pid"; then
if kill -0 "$pid" 2>/dev/null; then
: # the server still runs
else
# the server may have exited between the last pid-file check and now.
if test -n "$avoid_race_condition"; then
avoid_race_condition=""
continue # check again.
fi
# there's nothing that will affect the file.
log_failure_msg "the server quit without updating pid file ($pid_file_path)."
return 1 # not waiting any more.
fi
fi
if test -e $sst_progress_file && [ $startup_sleep -ne 100 ];then
echo $echo_n "sst in progress, setting sleep higher"
startup_sleep=100
echo $echo_n ".$echo_c"
i=`expr $i + 1`
sleep $startup_sleep
if test -z "$i" ; then
log_success_msg
return 0
log_failure_msg
return 1
# get arguments from the my.cnf file,
# the only group, which is read from now on is [mysqld]
if test -x ./bin/my_print_defaults
print_defaults="./bin/my_print_defaults"
elif test -x $bindir/my_print_defaults
print_defaults="$bindir/my_print_defaults"
elif test -x $bindir/mysql_print_defaults
print_defaults="$bindir/mysql_print_defaults"
# try to find basedir in /etc/my.cnf
conf=/usr/local/mysql/etc/my.cnf
print_defaults=
if test -r $conf
subpat='^[^=]*basedir[^=]*=\(.*\)$'
dirs=`sed -e "/$subpat/!d" -e 's//\1/' $conf`
for d in $dirs
do
d=`echo $d | sed -e 's/[ ]//g'`
if test -x "$d/bin/my_print_defaults"
then
print_defaults="$d/bin/my_print_defaults"
break
if test -x "$d/bin/mysql_print_defaults"
print_defaults="$d/bin/mysql_print_defaults"
done
# hope it's in the path ... but i doubt it
test -z "$print_defaults" && print_defaults="my_print_defaults"
# read defaults file from 'basedir'. if there is no defaults file there
# check if it's in the old (depricated) place (datadir) and read it from there
extra_args=""
if test -r "$basedir/my.cnf"
extra_args="-e $basedir/my.cnf"
if test -r "$datadir/my.cnf"
extra_args="-e $datadir/my.cnf"
parse_server_arguments `$print_defaults $extra_args mysqld server mysql_server mysql.server`
# set pid file if not given
if test -z "$mysqld_pid_file_path"
mysqld_pid_file_path=$datadir/`hostname`.pid
case "$mysqld_pid_file_path" in
/* ) ;;
* ) mysqld_pid_file_path="$datadir/$mysqld_pid_file_path" ;;
esac
case "$mode" in
'start')
# start daemon
# safeguard (relative paths, core dumps..)
cd $basedir
echo $echo_n "starting mysql"
if test -x $bindir/mysqld_safe
then
# give extra arguments to mysqld with the my.cnf file. this script
# may be overwritten at next upgrade.
$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &
wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?
# make lock for redhat / suse
if test -w "$lockdir"
touch "$lock_file_path"
exit $return_value
else
log_failure_msg "couldn't find mysql server ($bindir/mysqld_safe)"
;;
'stop')
# stop daemon. we use a signal here to avoid having to know the
# root password.
if test -s "$mysqld_pid_file_path"
mysqld_pid=`cat "$mysqld_pid_file_path"`
if (kill -0 $mysqld_pid 2>/dev/null)
echo $echo_n "shutting down mysql"
kill $mysqld_pid
# mysqld should remove the pid file when it exits, so wait for it.
wait_for_pid removed "$mysqld_pid" "$mysqld_pid_file_path"; return_value=$?
log_failure_msg "mysql server process #$mysqld_pid is not running!"
rm "$mysqld_pid_file_path"
# delete lock for redhat / suse
if test -f "$lock_file_path"
rm -f "$lock_file_path"
log_failure_msg "mysql server pid file could not be found!"
'restart')
# stop the service and regardless of whether it was
# running or not, start it again.
if $0 stop $other_args; then
$0 start $other_args
log_failure_msg "failed to stop running server, so refusing to try to start."
exit 1
'reload'|'force-reload')
if test -s "$mysqld_pid_file_path" ; then
read mysqld_pid < "$mysqld_pid_file_path"
kill -hup $mysqld_pid && log_success_msg "reloading service mysql"
touch "$mysqld_pid_file_path"
log_failure_msg "mysql pid file could not be found!"
'status')
# first, check to see if pid file exists
read mysqld_pid < "$mysqld_pid_file_path"
if kill -0 $mysqld_pid 2>/dev/null ; then
log_success_msg "mysql running ($mysqld_pid)"
exit 0
log_failure_msg "mysql is not running, but pid file exists"
# try to find appropriate mysqld process
mysqld_pid=`pidof $libexecdir/mysqld`
# test if multiple pids exist
pid_count=`echo $mysqld_pid | wc -w`
if test $pid_count -gt 1 ; then
log_failure_msg "multiple mysql running but pid file could not be found ($mysqld_pid)"
exit 5
elif test -z $mysqld_pid ; then
if test -f "$lock_file_path" ; then
log_failure_msg "mysql is not running, but lock file ($lock_file_path) exists"
exit 2
log_failure_msg "mysql is not running"
exit 3
log_failure_msg "mysql is running but pid file could not be found"
exit 4
'configtest')
echo $echo_n "testing mysql configuration syntax"
daemon=$bindir/mysqld
if test -x $libexecdir/mysqld
daemon=$libexecdir/mysqld
elif test -x $sbindir/mysqld
daemon=$sbindir/mysqld
elif test -x `which mysqld`
daemon=`which mysqld`
log_failure_msg "unable to locate the mysqld binary!"
help_out=`$daemon --help 2>&1`; r=$?
if test "$r" != 0 ; then
log_failure_msg "$help_out"
log_failure_msg "there are syntax errors in the server configuration. please fix them!"
log_success_msg "syntax ok"
exit $r
'bootstrap')
# bootstrap the cluster, start the first node
# that initiate the cluster
echo $echo_n "bootstrapping the cluster"
$0 start $other_args --wsrep-new-cluster
;;
*)
# usage
basename=`basename "$0"`
echo "usage: $basename {start|stop|restart|reload|force-reload|status|configtest|bootstrap} [ mysql server options ]"
exit 0
啟動每一台資料庫
在每台資料庫中建立下面使用者， 用于 sst 認證 (以 root 登入 mysql 資料庫後執行下面的 sql 語句)
關閉所有資料庫, 叢集啟動前， 不需要啟動任何一台的資料庫
建立并加入叢集
叢集中第一個節點啟動 (192.168.200.163)
建立軟連結，并啟動叢集，叢集啟動過程中會自動啟動 mariadb
測試是否成功啟動方法, 查詢是否會自動啟動 4567 端口
登入 mysql 之後，查詢目前是否啟用 galera 插件
注，on 為已經啟動插件狀态
關閉方法
其他節點加入叢集方法
第一台 (192.168.200.163) 節點已經啟動成功
第二台 (192.168.200.171) 需要加入叢集
可按上述方法進行叢集啟動測試, 也可以參照下面方法, 觀察叢集位址是否增加兩個伺服器位址
第三台 (172.18.8.49) 需要加入叢集
第四台 (172.18.8.50) 需要加入叢集
常見 wsrep 參數注釋
時間關系，還沒有時間進行壓力測試，也沒有比對 galera 與 percona xtradb cluster 叢集之間差別
另，如使用 rpm 則十分友善，網路很多教程， 不較長的描述
auto_increment
當更多的 mariadb 加入到叢集之後，叢集中的資料庫會自動進行協調，并且自動定義偏移量， 這個比較人性化，自動化，如下描述
db1:
db2:
db3:
db4:
目前加入叢集中共 4 個節點， 如上所見，每個叢集中都會每次在數字遞增時候遞增 4 位， 而數字起始值為加入叢集的順序
模拟測試1
建立測試表
在每台電腦中建立對應的資料插入腳本 (太大量的并發插入會導緻伺服器不斷脫離叢集，最終隻剩下一次，是以減少資料插入量)
目的：同時在 4 台電腦中進行資料插入，每台插入 1000 行（并發執行)
插入過程中, 會出現鎖，有一個資料庫叢集會自動脫離叢集 >_<"
參考其他三台 時間傳回值
db1 (使用 13 秒)
db2 (使用 24秒)
db3(使用14秒)
db4(寫入 35 條資料後 crash)