Mariadb Galera与MaxScale代理配置参考

Galera集群参考 MariaDB Galera Cluster配置使用MaxScale配置参考 MaxScale1.4.5中间件读写分离示例 中的进行安装其他参考：

https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-14/maxscale-administration-tutorial/
https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-14/maxscale-configuration-usage-scenarios/

使用MaxScale来作为zabbix读写分离中间件，后面使用Galera集群来做，配置上和之前的示例中相比只需要修改Monitor的一段配置，大致如下：

#[MySQL Monitor]  #注释掉mysql
[Galera Monitor]  #启用Galera
type=monitor
#module=mysqlmon  #注释掉mysql的模块
module=galeramon  #启用Galera
servers=server1,server2,server3
user=mybk
passwd=D53C7669B34FF2AE96DB6E21C98A6A54
# 监控心态为 10s
monitor_interval=10000
# 当复制slave全部断掉时，maxscale仍然可用，将所有的访问指向master节点
detect_stale_master=true
# 监控主从复制延迟，可用后续指定router service的(配置此参数请求会永远落在 master)
# detect_replication_lag=true

完整的配置文件如下：

[maxscale]
threads=auto
ms_timestamp=1
syslog=0
maxlog=1
log_to_shm=0
log_warning=1
log_notice=0
log_info=0
log_debug=0
log_augmentation=1
#相关目录设置
logdir=/mydata/maxscale/logs/trace/
datadir=/mydata/maxscale/data/
libdir=/usr/lib64/maxscale/
cachedir=/mydata/maxscale/cache/
piddir=/mydata/maxscale/pid/
execdir=/usr/bin/

[server1]
type=server
address=10.10.240.113
port=3306
protocol=MySQLBackend
serv_weight=3
priority=1

[server2]
type=server
address=10.0.1.61
port=3306
protocol=MySQLBackend
serv_weight=3
priority=2

[server3]
type=server
address=10.0.1.49
port=3306
protocol=MySQLBackend
serv_weight=3
priority=3

#[MySQL Monitor]
[Galera Monitor]
type=monitor
#module=mysqlmon
module=galeramon
servers=server1,server2,server3
user=mybk
passwd=D53C7669B34FF2AE96DB6E21C98A6A54
# 监控心态为 10s
monitor_interval=10000
# 当复制slave全部断掉时，maxscale仍然可用，将所有的访问指向master节点
detect_stale_master=true
# 监控主从复制延迟，可用后续指定router service的(配置此参数请求会永远落在 master)
# detect_replication_lag=true

#[Read-Only Service]
#type=service
#router=readconnroute
#servers=server1,server2
#user=mybk
#passwd=D53C7669B34FF2AE96DB6E21C98A6A54
#router_options=slave
#enable_root_user=1
#weightby=serv_weight

[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=mybk
passwd=D53C7669B34FF2AE96DB6E21C98A6A54
max_slave_connections=100%
# sql语句中的存在变量只指向master中执行
use_sql_variables_in=master
# 允许root用户登录执行
enable_root_user=1
# 允许主从最大间隔(s)
#max_slave_replication_lag=3600
weightby=serv_weight

#maxscale管理节点信息
[MaxAdmin Service]
type=service
router=cli
#各个请求的端口信息
#[Read-Only Listener]
#type=listener
#service=Read-Only Service
#protocol=MySQLClient
#port=4008
[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006
[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
#socket=/mydata/maxscale/tmp/maxadmin.sock
port=6603

maxscale启动脚本

这次添加了启动脚本

[root@DS-VM-Linuxea-117 ~]# cat /etc/init.d/maxctld 
#!/bin/bash
####################Description########################
# You need to know the pid file location              #
# and the MAXSCALE_HOME location                       #
# and the MAXSCALE_CNF location                          #
# Set default variables                                  #
# Author: www.linuxea.com                              #
# Created Time: 2017-07-25 19:53:25                      #
#######################################################
NOWAIT=0
HELP=0
QUIET=0
MAXSCALE_PID=0
MAXSCALE_PIDFILE=/mydata/maxscale/pid/maxscale.pid
MAXSCALE_HOME=/usr/local/maxscale/usr
MAXSCALE_CNF=/etc/maxscale.cnf
# Get pid of MaxScale if it is running.
# Check that the pidfile exists.
if [ -e $MAXSCALE_PIDFILE ]; then
   MAXSCALE_PID=`cat $MAXSCALE_PIDFILE`
# Check if the process is running.
   if [ `ps --no-heading -p $MAXSCALE_PID | wc -l` -eq 0 ]; then
      MAXSCALE_PID=0
   fi
fi


# Function to print output
printmax() {
   if [ $QUIET -eq 0 ]; then
      echo $* >&2
   fi
}

# Function to print help
helpmax() {
    echo "Usage: $0 start|stop|status|restart"
    echo "Options:"
    echo "-f - MaxScale config file"
    echo "-h - Show this help"
    echo "-n - Don't wait for operation to finish before exiting"
    echo "-q - Quiet operation"
}


# Function to start maxscale
startmax() {
# Check if MaxScale is already running.
   if [ $MAXSCALE_PID -ne 0 ]; then
      printmax "MaxScale is already running"
      exit 1
   fi

# Check that we are running as root if a user to run as is specified.
   if [ "x$MAXSCALE_USER" != "x" -a `id -u` -ne 0 ]; then
      printmax "$0 must be run as root"
      exit 1
   fi

# Check that we can find maxscale
   if [ ! -e $MAXSCALE_HOME/bin/maxscale ]; then
      printmax "Cannot find MaxScale executable ($MAXSCALE_HOME/bin/maxscale)"
      exit 1
   fi

# Check that the config file exists, if specified.
   if [ "x$MAXSCALE_CNF" != "x" -a ! -e "$MAXSCALE_CNF" ]; then
      printmax "MaxScale configuration file ($MAXSCALE_CNF) not found"
      exit 1
   fi

# Start MaxScale
   if [ "x$MAXSCALE_USER" == "x" ]; then
      $MAXSCALE_HOME/bin/maxscale -f $MAXSCALE_HOME ${MAXSCALE_CNF:+-f $MAXSCALE_CNF}
#      $MAXSCALE_HOME/bin/maxscale -c $MAXSCALE_HOME ${MAXSCALE_CNF:+-f $MAXSCALE_CNF}
   else
      su $MAXSCALE_USER -m -c "$MAXSCALE_HOME/bin/maxscale -c $MAXSCALE_HOME ${MAXSCALE_CNF:+-f $MAXSCALE_CNF}"
   fi
}


# Function to stop maxscale
stopmax() {
   NOWAIT=1
   if [ "x$1" == "-n" ]; then
      NOWAIT=0
   fi

# Check that we are running as root if a user to run as is specified.
   if [ "x$MAXSCALE_USER" != "x" -a `id -u` -ne 0 ]; then
      printmax "$0 must be run as root"
      exit 1
   fi

# Check that the pidfile exists.
   if [ ! -e $MAXSCALE_PIDFILE ]; then
      printmax "Can't find MaxScale pidfile ($MAXSCALE_PIDFILE)"
      exit 1
   fi
   MAXSCALE_PID=`cat $MAXSCALE_PIDFILE`

# Kill MaxScale
   kill $MAXSCALE_PID

   if [ $NOWAIT -ne 0 ]; then
# Wait for maxscale to die.
      while [ `ps --no-heading -p $MAXSCALE_PID | wc -l` -ne 0 ]; do
      usleep 100000
      done
      MAXSCALE_PID=0
   fi
}


# Function to show the status of MaxScale
statusmax() {
# Check that the pidfile exists.
   if [ $MAXSCALE_PID -ne 0 ]; then
      printmax "MaxScale is running (pid: $MAXSCALE_PID user: `ps -p $MAXSCALE_PID --no-heading -o euser`)"
      exit 0
   fi
   printmax "MaxScale is not running"
   exit 1
}

# Process options.
while getopts ":f:hnq" OPT; do
   case $OPT in
      f)
         MAXSCALE_CNF=$OPTARG
         ;;
      h)
         helpmax
         exit 0
         ;;
      n)
         NOWAIT=1
         ;;
      q)
         QUIET=1
         ;;
      ?)
         echo "Invalid option: -$OPTARG"
         ;;
   esac
done

# Process arguments following options.
shift $((OPTIND - 1))
OPER=$1

# Check that an operation was passed
if [ "x$1" == "x" ]; then
   echo "$0: your must enter an operation: start|stop|restart|status" >&2
   exit 1
fi


# Handle the operations.
case $OPER in
   start)
      startmax
      ;;
   stop)
      stopmax
      ;;
   status)
      statusmax
      ;;
   restart)
      if [ $MAXSCALE_PID -ne 0 ]; then
         NOWAITSAVE=$NOWAIT
         NOWAIT=0
         stopmax
         NOWAIT=$NOWAITSAVE
      fi
      startmax
      ;;
   *)
      echo "Unknown operation: $OPER. Use start|stop|restart|status"
      exit 1
esac