Linux之CentOS 6.7上编译+YUM两种方法配置MariaDB Galera多主集群实现HA

摘要

Galera本质是一个wsrep提供者(provider),运行依赖于wsrep的API接口。Wsrep API定义了一系列应用回调和复制调用库,来实现事务数据库同步写集(writeset)复制以及相似应用。目的在于从应用细节上实现抽象的,隔离的复制。虽然这个接口的主要目标是基于认证的多主复制,但同样适用于异步和同步的主从复制。

个人感觉MariaDB Galera做跨机房多主还是很方便的,比直接用MySQL做主从好。跟其他集群一样,为了避免节点出现脑裂而破坏数据,建议Galera集群最低添加3个节点。Galera 集群在高并发的情况下,多主同时写入时可能会发生事务冲突,此时只有一个事务请求会成功,其他的全部失败。可以在写入/更新失败时,自动重试一次,再返回结果。节点中每个节点的地位是平等的,没有主次,向任何一个节点读写效果都是一样的。实际可以配合VIP/LVS或HA使用,实现高可用性。如果集群中的机器全部重启,如机房断电,第一台启动的服务器必须以空地址启动。

Galera Cluster是一套基于同步复制的多主MySQL集群解决方案,使用简单,没有单点故障,可用性高,能很好保证业务不断增长时我们数据的安全和随时的扩展。

Galera集群的复制功能基于Galeralibrary实现,为了让MySQL与Galera library通讯,特别针对MySQL开发了wsrep API。

Galera插件保证集群同步数据,保持数据的一致性,靠的就是可认证的复制,工作原理如下图:
Linux之CentOS 6.7上编译+YUM两种方法配置MariaDB Galera多主集群实现HA

主要功能:

  1. 同步复制
  2. 真正的multi-master,即所有节点可以同时读写数据库
  3. 自动的节点成员控制,失效节点自动被清除
  4. 新节点加入数据自动复制
  5. 真正的并行复制,行级
  6. 用户可以直接连接集群,使用感受上与MySQL完全一致

优势:

  1. 因为是多主,所以不存在Slavelag(延迟)
  2. 不存在丢失事务的情况
  3. 同时具有读和写的扩展能力
  4. 更小的客户端延迟
  5. 节点间数据是同步的,而Master/Slave模式是异步的,不同slave上的binlog可能是不同的

一、下面说说编译安装MariaDB Galera 多主集群
Linux之CentOS 6.7上编译+YUM两种方法配置MariaDB Galera多主集群实现HA

下文中,如果命令前面没有PS1就是代表3个节点都需要执行相同的动作,如果有就表示在指定的节点上执行动作

1、Node1 Node2 Node3节点上安装ntpdate设置定时时间同步和ssh互相

yum install ntpdate -y
echo "*/5 * * * * /usr/sbin/ntpdate pool.ntp.org >/dev/null 2>&1" >> /var/spool/cron/root
/usr/sbin/ntpdate pool.ntp.org
cat >>/etc/hosts<< EOF
51.254.102.19    vps197119.ovh.net    vps197119    node1
51.254.102.190    vps197120.ovh.net    vps197120    node2
51.254.102.191    vps197121.ovh.net    vps197121    node3
EOF
###下面是生产密钥
ssh-keygen  -t rsa -f ~/.ssh/id_rsa  -P ''
###下面是在每个节点上执行,让本机的公钥放到其他节点上
awk '{if ($0!~/'"$(hostname)"'|localhost/)print $NF}' /etc/hosts |xargs -i ssh-copy-id -i ~/.ssh/id_rsa.pub root@{}

2、安装基础的编译环境

rpm -ivh http://mirrors.aliyun.com/epel/epel-release-latest-6.noarch.rpm
rpm --import http://mirrors.dwhd.org/epel/RPM-GPG-KEY-EPEL-6
rpm -ivh http://pkgs.repoforge.org/rpmforge-release/rpmforge-release-0.5.3-1.el6.rf.x86_64.rpm
rpm --import http://mirrors.dwhd.org/repoforge/RPM-GPG-KEY.dag.txt
yum clean all && yum makecache
yum groupinstall "Development tools" "Server Platform Development" -y
yum install libxml2-devel lz4 lz4-devel libpcap nmap lsof socat -y

3、Node1上编译安装MariaDB Galera

###下载cmake源码包
[root@vps197119 ~]# wget http://cmake.org/files/v3.3/cmake-3.3.2.tar.gz
###下载mariadb-galera源码包
[root@vps197119 ~]# wget http://mirrors.dwhd.org/SQL/MariaDB/mariadb-galera-10.0.21/source/mariadb-galera-10.0.21.tar.gz
###下载kytea源码包
[root@vps197119 ~]# wget http://www.phontron.com/kytea/download/kytea-0.4.7.tar.gz
###将cmake、mariadb-galera、kytea源码包传到其他节点上
[root@vps197119 ~]# awk '{if ($0!~/'"$(hostname)"'|localhost/)print $NF}' /etc/hosts |xargs -i scp cmake-3.3.2.tar.gz mariadb-galera-10.0.21.tar.gz kytea-0.4.7.tar.gz root@{}:/root
###开始编译cmake
[root@vps197119 ~]# tar xf cmake-3.3.2.tar.gz
[root@vps197119 ~]# cd cmake-3.3.2/
[root@vps197119 ~/cmake-3.3.2]# ./bootstrap
[root@vps197119 ~/cmake-3.3.2]# make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd ../ && which cmake
###开始编译kytea
[root@vps197119 ~]# tar xf kytea-0.4.7.tar.gz
[root@vps197119 ~]# cd kytea-0.4.7/
[root@vps197119 ~/kytea-0.4.7]# ./configure
[root@vps197119 ~/kytea-0.4.7]# make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd ..
###安装Galera
[root@vps197119 ~]# rpm --import https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
[root@vps197119 ~]# rpm -ivh http://mirrors.dwhd.org/SQL/MariaDB/mariadb-galera-10.0.21/galera-25.3.9/rpm/galera-25.3.9-1.rhel6.el6.x86_64.rpm
###开始编译mariadb-galera
[root@vps197119 ~]# findUidGid() { for i in `seq 400 500`;do if [ -z "$(awk -F: '{print$3,$4}' /etc/passwd | grep "$i")" -a -z "$(awk -F: '{print$3}' /etc/group | grep "$i")" ]; then ugidNo=$i; break; fi; done; groupadd -g $ugidNo $1 && useradd -M -u $ugidNo -g $ugidNo -s /sbin/nologin $1; }
[root@vps197119 ~]# findUidGid mysql
[root@vps197119 ~]# mkdir -p /data/mariadb-galera-10.0.21
[root@vps197119 ~]# chown -R mysql.mysql /data/mariadb-galera-10.0.21
[root@vps197119 ~]# tar xf mariadb-galera-10.0.21.tar.gz
[root@vps197119 ~]# cd mariadb-10.0.21/
[root@vps197119 ~/mariadb-10.0.21]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mariadb \
-DMYSQL_DATADIR=/data/mariadb-galera-10.0.21 \
-DWITH_SSL=system -DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_SPHINX_STORAGE_ENGINE=1 -DWITH_ARIA_STORAGE_ENGINE=1 \
-DWITH_XTRADB_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FEDERATEDX_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 -DWITH_EXTRA_CHARSETS=all \
-DWITH_EMBEDDED_SERVER=1 -DWITH_READLINE=1 -DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 -DEXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=1 \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_WSREP=1 -DWITH_INNODB_DISALLOW_WRITES=1  ###这两项很重要不能少
[root@vps197119 ~/mariadb-10.0.21]# make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd ..
[root@vps197119 ~]# 

4、Node2上编译安装MariaDB Galera

###编译安装cmake
[root@vps197120 ~]# tar xf cmake-3.3.2.tar.gz
[root@vps197120 ~]# cd cmake-3.3.2/
[root@vps197120 ~/cmake-3.3.2]# ./bootstrap
[root@vps197120 ~/cmake-3.3.2]# make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd ../
###编译安装kytea
[root@vps197120 ~]# tar xf kytea-0.4.7.tar.gz 
[root@vps197120 ~]# cd kytea-0.4.7/
[root@vps197120 ~/kytea-0.4.7]# ./configure && make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd ..
###安装Galera
[root@vps197120 ~]# rpm --import https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
[root@vps197121 ~]# rpm -ivh http://mirrors.dwhd.org/SQL/MariaDB/mariadb-galera-10.0.21/galera-25.3.9/rpm/galera-25.3.9-1.rhel6.el6.x86_64.rpm
###编译安装MariaDB Galera
[root@vps197120 ~]# mkdir -p /data/mariadb-galera-10.0.21
[root@vps197120 ~]# chown -R mysql.mysql /data/mariadb-galera-10.0.21
[root@vps197120 ~]# tar xf mariadb-galera-10.0.21.tar.gz 
[root@vps197120 ~]# cd mariadb-10.0.21/
[root@vps197120 ~/mariadb-10.0.21]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mariadb \
-DMYSQL_DATADIR=/data/mariadb-galera-10.0.21 \
-DWITH_SSL=system -DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_SPHINX_STORAGE_ENGINE=1 -DWITH_ARIA_STORAGE_ENGINE=1 \
-DWITH_XTRADB_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FEDERATEDX_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 -DWITH_EXTRA_CHARSETS=all \
-DWITH_EMBEDDED_SERVER=1 -DWITH_READLINE=1 -DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 -DEXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=1 \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_WSREP=1 -DWITH_INNODB_DISALLOW_WRITES=1
[root@vps197120 ~/mariadb-10.0.21]# make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd ..
[root@vps197120 ~]# 

5、Node3上编译安装MariaDB Galera

###编译安装cmake
[root@vps197121 ~]# tar xf cmake-3.3.2.tar.gz 
[root@vps197121 ~]# cd cmake-3.3.2/
[root@vps197121 ~/cmake-3.3.2]# ./bootstrap && make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd ..
###编译安装kytea
[root@vps197121 ~]# tar xf kytea-0.4.7.tar.gz 
[root@vps197121 ~]# cd kytea-0.4.7/
[root@vps197121 ~/kytea-0.4.7]# ./configure && make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd ..
###安装Galera
[root@vps197121 ~]# rpm --import https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
[root@vps197121 ~]# rpm -ivh http://mirrors.dwhd.org/SQL/MariaDB/mariadb-galera-10.0.21/galera-25.3.9/rpm/galera-25.3.9-1.rhel6.el6.x86_64.rpm
###编译安装MariaDB Galera
[root@vps197121 ~]# mkdir -p /data/mariadb-galera-10.0.21
[root@vps197121 ~]# chown -R mysql.mysql /data/mariadb-galera-10.0.21
[root@vps197121 ~]# tar xf mariadb-galera-10.0.21.tar.gz 
[root@vps197121 ~]# cd mariadb-10.0.21/
[root@vps197121 ~/mariadb-10.0.21]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mariadb \
-DMYSQL_DATADIR=/data/mariadb-galera-10.0.21 \
-DWITH_SSL=system -DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_SPHINX_STORAGE_ENGINE=1 -DWITH_ARIA_STORAGE_ENGINE=1 \
-DWITH_XTRADB_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FEDERATEDX_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 -DWITH_EXTRA_CHARSETS=all \
-DWITH_EMBEDDED_SERVER=1 -DWITH_READLINE=1 -DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 -DEXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=1 \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_WSREP=1 -DWITH_INNODB_DISALLOW_WRITES=1
[root@vps197121 ~/mariadb-10.0.21]# make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd ..
[root@vps197121 ~]# 

6、配置所有节点上的启动脚本、环境变量

cd /usr/local/mariadb/
cp support-files/mysql.server /etc/rc.d/init.d/mysqld
chmod +x /etc/rc.d/init.d/mysqld
\cp /usr/local/mariadb/support-files/my-large.cnf /etc/my.cnf
sed -i '/query_cache_size/a datadir = /data/mariadb-galera-10.0.21/' /etc/my.cnf
echo "export PATH=/usr/local/mariadb/bin:\$PATH" > /etc/profile.d/mariadb_galera_10.0.21.sh
source /etc/profile.d/mariadb_galera_10.0.21.sh
sed -i "$(awk '$1=="MANPATH"{i=NR}END{print i}' /etc/man.config)a \MANPATH\tMANPATH /usr/local/mariadb/man" /etc/man.config

7、配置Node1节点

[root@vps197119 /usr/local/mariadb]# /usr/local/mariadb/scripts/mysql_install_db --user=mysql --datadir=/data/mariadb-galera-10.0.21/
[root@vps197119 /usr/local/mariadb]# cd && service mysqld start --wsrep-new-cluster #第一个节点的启动参数需要加上--wsrep-new-cluster
Starting MySQL.. SUCCESS! 
[root@vps197119 ~]# ss -tnl | grep :3306
LISTEN     0      128                      :::3306                    :::*     
[root@vps197119 ~]# mysql -uroot -p <<< "USE mysql;
update user set password=PASSWORD('lookback') WHERE USER='root';
DELETE FROM user WHERE User='';
GRANT ALL PRIVILEGES ON *.* TO 'cluster'@'%' IDENTIFIED BY 'lookback' WITH GRANT OPTION;
FLUSH PRIVILEGES;
SELECT USER,PASSWORD,HOST FROM user;"
Enter password: ####注意这里密码是空,直接回车就好了
USER    PASSWORD        HOST
root    *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C       localhost
root    *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C       vps197119.ovh.net
root    *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C       127.0.0.1
root    *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C       ::1
cluster *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C        %
[root@vps197119 ~]# 

Linux之CentOS 6.7上编译+YUM两种方法配置MariaDB Galera多主集群实现HA

[root@vps197119 ~]# service mysqld stop
Shutting down MySQL... SUCCESS! 
[root@vps197119 ~]# chkconfig mysqld on
[root@vps197119 ~]# sed -i '/binlog_format/d' /etc/my.cnf
[root@vps197119 ~]# sed -i '/log_bin/d' /etc/my.cnf
[root@vps197119 ~]# sed -i '/^datadir = /a \\n\nquery_cache_size = 0\nlog_bin = mysql-bin\nbinlog_format = ROW\nexpire_logs_days = 30\
default_storage_engine = InnoDB\ninnodb_autoinc_lock_mode = 2\nwsrep_provider = /usr/lib64/galera/libgalera_smm.so\
wsrep_cluster_address = "gcomm://"\nwsrep_cluster_name = LegionMariadbGaleraCluster1\nwsrep_node_address = node1\
wsrep_sst_method = rsync\nwsrep_sst_auth = cluster:lookback' /etc/my.cnf
[root@vps197119 ~]# service  mysqld start
Starting MySQL.. SUCCESS!
[root@vps197119 ~]# ss -tnl | grep -E ':(3306|4567)'
LISTEN     0      128                      :::3306                    :::*     
LISTEN     0      128                       *:4567                     *:*     

如果开启了防火墙请设置放行规则

###我测试时候的规则,参见高亮行
[root@vps197121 ~]# iptables -t filter -A INPUT -s 158.69.87.84 -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
[root@vps197121 ~]# iptables -t filter -A INPUT -s 158.69.87.84 -p tcp -m state --state NEW -m tcp --dport 4444 -j ACCEPT
[root@vps197121 ~]# iptables -t filter -A INPUT -s 158.69.87.84 -p tcp -m state --state NEW -m tcp --dport 4567 -j ACCEPT
[root@vps197121 ~]# iptables -t filter -A INPUT -s 158.69.87.84 -p udp -m udp --dport 4567 -j ACCEPT
[root@vps197121 ~]# iptables -t filter -A INPUT -s 167.114.230.243 -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
[root@vps197121 ~]# iptables -t filter -A INPUT -s 167.114.230.243 -p tcp -m state --state NEW -m tcp --dport 4444 -j ACCEPT
[root@vps197121 ~]# iptables -t filter -A INPUT -s 167.114.230.243 -p tcp -m state --state NEW -m tcp --dport 4567 -j ACCEPT
[root@vps197121 ~]# iptables -t filter -A INPUT -s 167.114.230.243 -p udp -m udp --dport 4567 -j ACCEPT
[root@vps197121 ~]# service iptables save
[root@vps197121 ~]# iptables -t filter -L INPUT -n --line-numbers 
Chain INPUT (policy DROP)
num  target     prot opt source               destination         
1    ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0           
2    ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0            state RELATED,ESTABLISHED
3    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0            state NEW tcp dpt:22
4    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0            state NEW tcp dpt:80
5    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0            state NEW tcp dpt:21
6    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0            state NEW tcp dpts:20000:30000
7    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0            state NEW tcp dpt:443
8    ACCEPT     tcp  --  158.69.87.84         0.0.0.0/0            state NEW tcp dpt:873
9    ACCEPT     tcp  --  167.114.230.243      0.0.0.0/0            state NEW tcp dpt:873
10   ACCEPT     tcp  --  195.154.167.106      0.0.0.0/0            state NEW tcp dpt:873
15   ACCEPT     tcp  --  158.69.87.84         0.0.0.0/0            state NEW tcp dpt:3306
16   ACCEPT     tcp  --  158.69.87.84         0.0.0.0/0            state NEW tcp dpt:4444
17   ACCEPT     tcp  --  158.69.87.84         0.0.0.0/0            state NEW tcp dpt:4567
18   ACCEPT     udp  --  158.69.87.84         0.0.0.0/0            udp dpt:4567
19   ACCEPT     tcp  --  167.114.230.243      0.0.0.0/0            state NEW tcp dpt:3306
20   ACCEPT     tcp  --  167.114.230.243      0.0.0.0/0            state NEW tcp dpt:4444
21   ACCEPT     tcp  --  167.114.230.243      0.0.0.0/0            state NEW tcp dpt:4567
22   ACCEPT     udp  --  167.114.230.243      0.0.0.0/0            udp dpt:4567
23   ACCEPT     icmp --  0.0.0.0/0            0.0.0.0/0            limit: avg 100/sec burst 100
24   ACCEPT     icmp --  0.0.0.0/0            0.0.0.0/0            limit: avg 1/sec burst 10
25   syn-flood  tcp  --  0.0.0.0/0            0.0.0.0/0            tcp flags:0x17/0x02
26   REJECT     all  --  0.0.0.0/0            0.0.0.0/0            reject-with icmp-host-prohibited
27   ACCEPT     tcp  --  158.69.87.84         0.0.0.0/0            state NEW tcp dpt:873
[root@vps197121 ~]#

8、配置Node2节点

[root@vps197120 /usr/local/mariadb]# /usr/local/mariadb/scripts/mysql_install_db --user=mysql --datadir=/data/mariadb-galera-10.0.21/
[root@vps197120 /usr/local/mariadb]# cd && service mysqld start
Starting MySQL.. SUCCESS! 
[root@vps197120 ~]# mysql -uroot -p <<< "USE mysql;
update user set password=PASSWORD('lookback') WHERE USER='root';
DELETE FROM user WHERE User='';
GRANT ALL PRIVILEGES ON *.* TO 'cluster'@'%' IDENTIFIED BY 'lookback' WITH GRANT OPTION;
FLUSH PRIVILEGES;
SELECT USER,PASSWORD,HOST FROM user;"
[root@vps197120 ~]# service mysqld stop
Shutting down MySQL.. SUCCESS! 
[root@vps197120 ~]# chkconfig mysqld on
[root@vps197120 ~]# sed -i '/binlog_format/d' /etc/my.cnf
[root@vps197120 ~]# sed -i '/log.bin/d' /etc/my.cnf
[root@vps197120 ~]# sed -ri 's/^(server-id).*/\1 = 2/' /etc/my.cnf
[root@vps197120 ~]# sed -i '/^datadir = /a \\n\nquery_cache_size = 0\nlog_bin = mysql-bin\nbinlog_format = ROW\nexpire_logs_days = 30\
default_storage_engine = InnoDB\ninnodb_autoinc_lock_mode = 2\nwsrep_provider = /usr/lib64/galera/libgalera_smm.so\
wsrep_cluster_address = "gcomm://node1,node2,node3"\nwsrep_cluster_name = LegionMariadbGaleraCluster1\n\
wsrep_node_address = node2\nwsrep_sst_method = rsync\nwsrep_sst_auth = cluster:lookback' /etc/my.cnf
[root@vps197120 ~]# service mysqld start
Starting MySQL... SUCCESS! 
[root@vps197120 ~]# ss -tnl | grep -E ':(3306|4567)'
LISTEN     0      128                      :::3306                    :::*     
LISTEN     0      128                       *:4567                     *:*     
[root@vps197120 ~]# 

node2节点my.cnf参考

[client]
port            = 3306
socket          = /tmp/mysql.sock

[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
query_cache_size=0

log-bin=mysql-bin
binlog_format=ROW
expire_logs_days = 30

basedir = /usr/local/mariadb-galera
datadir = /data/mariadb-galera/
log_error = /data/mariadb-galera/mysql-error.log

default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://51.254.102.19,51.254.102.190,51.254.102.191"
wsrep_cluster_name='LegionMariadbGaleraCluster1'
wsrep_node_address='51.254.102.190'
wsrep_node_name='node2'
wsrep_sst_method=rsync
wsrep_sst_auth=cluster:lookback
thread_concurrency = 8
server-id       = 1

[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

9、配置Node3节点

[root@vps197121 /usr/local/mariadb]# /usr/local/mariadb/scripts/mysql_install_db --user=mysql --datadir=/data/mariadb-galera-10.0.21/
[root@vps197121 /usr/local/mariadb]# cd && service mysqld start
Starting MySQL.. SUCCESS! 
[root@vps197121 ~]# mysql -uroot -p <<< "USE mysql; > update user set password=PASSWORD('lookback') WHERE USER='root';
> DELETE FROM user WHERE User='';
> GRANT ALL PRIVILEGES ON *.* TO 'cluster'@'%' IDENTIFIED BY 'lookback' WITH GRANT OPTION;
> FLUSH PRIVILEGES;
> SELECT USER,PASSWORD,HOST FROM user;"
Enter password: 
USER    PASSWORD        HOST
root    *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C       localhost
root    *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C       vps197121.ovh.net
root    *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C       127.0.0.1
root    *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C       ::1
cluster *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C        %
[root@vps197121 ~]# service mysqld stop
Shutting down MySQL... SUCCESS! 
[root@vps197121 ~]# chkconfig mysqld on
[root@vps197121 ~]# sed -i '/binlog_format/d' /etc/my.cnf
[root@vps197121 ~]# sed -i '/log.bin/d' /etc/my.cnf
[root@vps197121 ~]# sed -ri 's/^(server-id).*/\1 = 3/' /etc/my.cnf
[root@vps197121 ~]# sed -i '/^datadir = /a \\n\nquery_cache_size = 0\nlog_bin = mysql-bin\nbinlog_format = ROW\nexpire_logs_days = 30\
> default_storage_engine = InnoDB\ninnodb_autoinc_lock_mode = 2\nwsrep_provider = /usr/lib64/galera/libgalera_smm.so\
> wsrep_cluster_address = "gcomm://node1,node2,node3"\nwsrep_cluster_name = LegionMariadbGaleraCluster1\n\
> wsrep_node_address = node3\nwsrep_sst_method = rsync\nwsrep_sst_auth = cluster:lookback' /etc/my.cnf
[root@vps197121 ~]# service mysqld start
Starting MySQL..SST in progress, setting sleep higher.. SUCCESS! 
[root@vps197121 ~]# ss -tnl | grep -E ':(3306|4567)'
LISTEN     0      128                      :::3306                    :::*     
LISTEN     0      128                       *:4567                     *:*     
[root@vps197121 ~]# 

10、修改Node1节点配置

[root@vps197119 ~]# sed -ri 's/^(wsrep_cluster_address).*/\1 = "gcomm:\/\/node1,node2,node3"/' /etc/my.cnf
[root@vps197119 ~]# service mysqld restart 
Shutting down MySQL.... SUCCESS! 
Starting MySQL... SUCCESS! 
[root@vps197119 ~]# 

11、测试集群多主同步

[root@vps197119 ~]# mysql -uroot -plookback <<< 'USE mysql;
CREATE DATABASE LegionTestDataBase;
USE LegionTestDataBase;
CREATE TABLE equipment ( id INT NOT NULL AUTO_INCREMENT, type VARCHAR(50), quant INT, color VARCHAR(25), PRIMARY KEY(id));
INSERT INTO equipment (type, quant, color) VALUES ("slide", 2, "blue");
FLUSH PRIVILEGES;
SELECT * FROM LegionTestDataBase.equipment;'
id      type    quant   color
2       slide   2       blue
[root@vps197119 ~]# 
[root@vps197120 ~]# mysql -uroot -plookback <<< "USE LegionTestDataBase;
SELECT * FROM LegionTestDataBase.equipment;"
id      type    quant   color
2       slide   2       blue
[root@vps197120 ~]# mysql -uroot -plookback <<< "USE LegionTestDataBase;
INSERT INTO equipment (type, quant, color) VALUES ('swing', 10, 'yellow');
SELECT * FROM LegionTestDataBase.equipment;"
id      type    quant   color
2       slide   2       blue
4       swing   10      yellow
[root@vps197120 ~]# 
[root@vps197121 ~]# mysql -uroot -plookback <<< "USE LegionTestDataBase;
SELECT * FROM LegionTestDataBase.equipment;"
id      type    quant   color
2       slide   2       blue
4       swing   10      yellow
[root@vps197121 ~]# mysql -uroot -plookback <<< "USE LegionTestDataBase;
INSERT INTO equipment (type, quant, color) VALUES ('Legion', 20, 'red');
SELECT * FROM LegionTestDataBase.equipment;"
id      type    quant   color
2       slide   2       blue
4       swing   10      yellow
6       Legion  20      red
[root@vps197121 ~]# 
[root@vps197119 ~]# mysql -uroot -plookback <<< "USE LegionTestDataBase; > SELECT * FROM LegionTestDataBase.equipment;"
id      type    quant   color
2       slide   2       blue
4       swing   10      yellow
6       Legion  20      red
[root@vps197119 ~]# 

经过上面的一轮测试,多节点的全主MariaDB集群是OK的。。


二、使用yum来配置MariaDB Galera的多主集群

下文中,如果命令前面没有PS1就是代表3个节点都需要执行相同的动作,如果有就表示在指定的节点上执行动作

1、Node1 Node2 Node3节点上安装ntpdate设置定时时间同步和ssh互相

yum install ntpdate -y
echo "*/5 * * * * /usr/sbin/ntpdate pool.ntp.org >/dev/null 2>&1" >> /var/spool/cron/root
/usr/sbin/ntpdate pool.ntp.org
cat >>/etc/hosts<< EOF
51.254.102.19    vps197119.ovh.net    vps197119    node1
51.254.102.190    vps197120.ovh.net    vps197120    node2
51.254.102.191    vps197121.ovh.net    vps197121    node3
EOF
###下面是生产密钥
ssh-keygen  -t rsa -f ~/.ssh/id_rsa  -P ''
###下面是在每个节点上执行,让本机的公钥放到其他节点上
awk '{if ($0!~/'"$(hostname)"'|localhost/)print $NF}' /etc/hosts |xargs -i ssh-copy-id -i ~/.ssh/id_rsa.pub root@{}

2、安装MariaDB 和 Galera

yum install MariaDB-Galera-server MariaDB-client galera -y

3、配置MariaDB
Linux之CentOS 6.7上编译+YUM两种方法配置MariaDB Galera多主集群实现HA

[root@vps197119 ~]# service mysql start
Starting MySQL.. SUCCESS!
[root@vps197119 ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
[root@vps197119 ~]#
[root@vps197120 ~]# service mysql start
Starting MySQL.. SUCCESS! 
[root@vps197120 ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
[root@vps197120 ~]# 
[root@vps197121 ~]# service mysql start
Starting MySQL.. SUCCESS! 
[root@vps197121 ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
[root@vps197121 ~]# 

4、停止MariaDB和设置开机启动

mysql -u root -plookback <<< "USE mysql;  ###这里的lookback是数据库的root密码
GRANT ALL PRIVILEGES ON *.* TO 'cluster'@'%' IDENTIFIED BY 'lookback' WITH GRANT OPTION; ###这里的lookback是cluster的密码
FLUSH PRIVILEGES;"
###设置完毕来验证下
mysql -u root -plookback <<< "USE mysql;
SELECT USER,PASSWORD,HOST FROM user;"
USER    PASSWORD        HOST
root    *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C       localhost
root    *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C       127.0.0.1
root    *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C       ::1
cluster *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C        %
###验证通过之后停止MariaDB
service mysql stop
###设置MariaDB开机启动
chkconfig mysql on

5、修改3个节点的配置信息

####node1####
[root@vps197119 ~]# sed -ne '/\[mariadb-10.0\]/,//p' /etc/my.cnf.d/server.cnf
[mariadb-10.0]
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
###这里需要注意,如果集群是第一次启动请用当前配置,等其他节点启动起来后将下面两行的注释对换重启本节点就OK
wsrep_cluster_address="gcomm://"
#wsrep_cluster_address="gcomm://51.254.102.19,51.254.102.190,51.254.102.191"
wsrep_cluster_name='cluster1'
wsrep_node_address='51.254.102.19'
wsrep_node_name='node1'
wsrep_sst_method=rsync
wsrep_sst_auth=cluster:lookback
####node2####
[root@vps197120 ~]# sed -ne '/\[mariadb-10.0\]/,//p' /etc/my.cnf.d/server.cnf
[mariadb-10.0]
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://51.254.102.19,51.254.102.190,51.254.102.191"
wsrep_cluster_name='cluster1'
wsrep_node_address='51.254.102.190'
wsrep_node_name='node2'
wsrep_sst_method=rsync
wsrep_sst_auth=cluster:lookback
####node3####
[root@vps197121 ~]# sed -ne '/\[mariadb-10.0\]/,//p' /etc/my.cnf.d/server.cnf
[mariadb-10.0]
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://51.254.102.19,51.254.102.190,51.254.102.191"
wsrep_cluster_name='cluster1'
wsrep_node_address='51.254.102.191'
wsrep_node_name='node3'
wsrep_sst_method=rsync
wsrep_sst_auth=cluster:lookback

6、测试集群

[root@vps197119 ~]# mysql -uroot -plookback
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.0.21-MariaDB-wsrep MariaDB Server, wsrep_25.10.r4144

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> SHOW STATUS LIKE 'wsrep%';
+------------------------------+------------------------------------------------------------+
| Variable_name                | Value                                                      |
+------------------------------+------------------------------------------------------------+
| wsrep_local_state_uuid       | 7173605a-6299-11e5-b7a2-bb91b9fd6d7a                       |
| wsrep_protocol_version       | 7                                                          |
| wsrep_last_committed         | 0                                                          |
| wsrep_replicated             | 0                                                          |
| wsrep_replicated_bytes       | 0                                                          |
| wsrep_repl_keys              | 0                                                          |
| wsrep_repl_keys_bytes        | 0                                                          |
| wsrep_repl_data_bytes        | 0                                                          |
| wsrep_repl_other_bytes       | 0                                                          |
| wsrep_received               | 2                                                          |
| wsrep_received_bytes         | 284                                                        |
| wsrep_local_commits          | 0                                                          |
| wsrep_local_cert_failures    | 0                                                          |
| wsrep_local_replays          | 0                                                          |
| wsrep_local_send_queue       | 0                                                          |
| wsrep_local_send_queue_max   | 1                                                          |
| wsrep_local_send_queue_min   | 0                                                          |
| wsrep_local_send_queue_avg   | 0.000000                                                   |
| wsrep_local_recv_queue       | 0                                                          |
| wsrep_local_recv_queue_max   | 1                                                          |
| wsrep_local_recv_queue_min   | 0                                                          |
| wsrep_local_recv_queue_avg   | 0.000000                                                   |
| wsrep_local_cached_downto    | 18446744073709551615                                       |
| wsrep_flow_control_paused_ns | 0                                                          |
| wsrep_flow_control_paused    | 0.000000                                                   |
| wsrep_flow_control_sent      | 0                                                          |
| wsrep_flow_control_recv      | 0                                                          |
| wsrep_cert_deps_distance     | 0.000000                                                   |
| wsrep_apply_oooe             | 0.000000                                                   |
| wsrep_apply_oool             | 0.000000                                                   |
| wsrep_apply_window           | 0.000000                                                   |
| wsrep_commit_oooe            | 0.000000                                                   |
| wsrep_commit_oool            | 0.000000                                                   |
| wsrep_commit_window          | 0.000000                                                   |
| wsrep_local_state            | 4                                                          |
| wsrep_local_state_comment    | Synced                                                     |
| wsrep_cert_index_size        | 0                                                          |
| wsrep_causal_reads           | 0                                                          |
| wsrep_cert_interval          | 0.000000                                                   |
| wsrep_incoming_addresses     | 51.254.102.190:3306,51.254.102.191:3306,51.254.102.19:3306 |
| wsrep_evs_delayed            |                                                            |
| wsrep_evs_evict_list         |                                                            |
| wsrep_evs_repl_latency       | 0/0/0/0/0                                                  |
| wsrep_evs_state              | OPERATIONAL                                                |
| wsrep_gcomm_uuid             | bbbec594-6299-11e5-bd04-065febe4bd92                       |
| wsrep_cluster_conf_id        | 5                                                          |
| wsrep_cluster_size           | 3                                                          |
| wsrep_cluster_state_uuid     | 7173605a-6299-11e5-b7a2-bb91b9fd6d7a                       |
| wsrep_cluster_status         | Primary                                                    |
| wsrep_connected              | ON                                                         |
| wsrep_local_bf_aborts        | 0                                                          |
| wsrep_local_index            | 2                                                          |
| wsrep_provider_name          | Galera                                                     |
| wsrep_provider_vendor        | Codership Oy <info@codership.com>                          |
| wsrep_provider_version       | 25.3.9(r3385)                                              |
| wsrep_ready                  | ON                                                         |
| wsrep_thread_count           | 2                                                          |
+------------------------------+------------------------------------------------------------+
57 rows in set (0.00 sec)

MariaDB [mysql]> CREATE DATABASE LegionTestDataBase;
Query OK, 1 row affected (0.01 sec)

MariaDB [mysql]> USE LegionTestDataBase;
Database changed
MariaDB [LegionTestDataBase]> CREATE TABLE equipment ( id INT NOT NULL AUTO_INCREMENT, type VARCHAR(50), quant INT, color VARCHAR(25), PRIMARY KEY(id));
Query OK, 0 rows affected (0.04 sec)

MariaDB [LegionTestDataBase]> INSERT INTO equipment (type, quant, color) VALUES ("slide", 2, "blue");
Query OK, 1 row affected (0.01 sec)

MariaDB [LegionTestDataBase]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

MariaDB [LegionTestDataBase]> \q
Bye
[root@vps197119 ~]# 
[root@vps197120 ~]# mysql -uroot -plookback <<< "SHOW STATUS LIKE 'wsrep%'\g"
Variable_name   Value
wsrep_local_state_uuid  7173605a-6299-11e5-b7a2-bb91b9fd6d7a
wsrep_protocol_version  7
wsrep_last_committed    4
wsrep_replicated        0
wsrep_replicated_bytes  0
wsrep_repl_keys 0
wsrep_repl_keys_bytes   0
wsrep_repl_data_bytes   0
wsrep_repl_other_bytes  0
wsrep_received  10
wsrep_received_bytes    2718
wsrep_local_commits     0
wsrep_local_cert_failures       0
wsrep_local_replays     0
wsrep_local_send_queue  0
wsrep_local_send_queue_max      1
wsrep_local_send_queue_min      0
wsrep_local_send_queue_avg      0.000000
wsrep_local_recv_queue  0
wsrep_local_recv_queue_max      1
wsrep_local_recv_queue_min      0
wsrep_local_recv_queue_avg      0.000000
wsrep_local_cached_downto       1
wsrep_flow_control_paused_ns    0
wsrep_flow_control_paused       0.000000
wsrep_flow_control_sent 0
wsrep_flow_control_recv 0
wsrep_cert_deps_distance        1.000000
wsrep_apply_oooe        0.000000
wsrep_apply_oool        0.000000
wsrep_apply_window      1.000000
wsrep_commit_oooe       0.000000
wsrep_commit_oool       0.000000
wsrep_commit_window     1.000000
wsrep_local_state       4
wsrep_local_state_comment       Synced
wsrep_cert_index_size   4
wsrep_causal_reads      0
wsrep_cert_interval     0.000000
wsrep_incoming_addresses        51.254.102.190:3306,51.254.102.191:3306,51.254.102.19:3306
wsrep_evs_delayed 
wsrep_evs_evict_list 
wsrep_evs_repl_latency  0/0/0/0/0
wsrep_evs_state OPERATIONAL
wsrep_gcomm_uuid        773b733f-6299-11e5-ba5d-7b3058f177bc
wsrep_cluster_conf_id   5
wsrep_cluster_size      3
wsrep_cluster_state_uuid        7173605a-6299-11e5-b7a2-bb91b9fd6d7a
wsrep_cluster_status    Primary
wsrep_connected ON
wsrep_local_bf_aborts   0
wsrep_local_index       0
wsrep_provider_name     Galera
wsrep_provider_vendor   Codership Oy <info@codership.com>
wsrep_provider_version  25.3.9(r3385)
wsrep_ready     ON
wsrep_thread_count      2
[root@vps197120 ~]# mysql -uroot -plookback <<< "USE LegionTestDataBase;
SELECT * FROM LegionTestDataBase.equipment;"
id      type    quant   color
3       slide   2       blue
[root@vps197120 ~]# mysql -uroot -plookback <<< "USE LegionTestDataBase;
INSERT INTO equipment (type, quant, color) VALUES ('swing', 10, 'yellow');"
[root@vps197120 ~]# 
[root@vps197121 ~]# mysql -uroot -plookback <<< "SHOW STATUS LIKE 'wsrep%'\g"
Variable_name   Value
wsrep_local_state_uuid  7173605a-6299-11e5-b7a2-bb91b9fd6d7a
wsrep_protocol_version  7
wsrep_last_committed    5
wsrep_replicated        0
wsrep_replicated_bytes  0
wsrep_repl_keys 0
wsrep_repl_keys_bytes   0
wsrep_repl_data_bytes   0
wsrep_repl_other_bytes  0
wsrep_received  10
wsrep_received_bytes    2768
wsrep_local_commits     0
wsrep_local_cert_failures       0
wsrep_local_replays     0
wsrep_local_send_queue  0
wsrep_local_send_queue_max      1
wsrep_local_send_queue_min      0
wsrep_local_send_queue_avg      0.000000
wsrep_local_recv_queue  0
wsrep_local_recv_queue_max      1
wsrep_local_recv_queue_min      0
wsrep_local_recv_queue_avg      0.000000
wsrep_local_cached_downto       1
wsrep_flow_control_paused_ns    0
wsrep_flow_control_paused       0.000000
wsrep_flow_control_sent 0
wsrep_flow_control_recv 0
wsrep_cert_deps_distance        1.400000
wsrep_apply_oooe        0.000000
wsrep_apply_oool        0.000000
wsrep_apply_window      1.000000
wsrep_commit_oooe       0.000000
wsrep_commit_oool       0.000000
wsrep_commit_window     1.000000
wsrep_local_state       4
wsrep_local_state_comment       Synced
wsrep_cert_index_size   5
wsrep_causal_reads      0
wsrep_cert_interval     0.000000
wsrep_incoming_addresses        51.254.102.190:3306,51.254.102.191:3306,51.254.102.19:3306
wsrep_evs_delayed 
wsrep_evs_evict_list 
wsrep_evs_repl_latency  0/0/0/0/0
wsrep_evs_state OPERATIONAL
wsrep_gcomm_uuid        7fd4ab67-6299-11e5-88fb-3fd93ceed5f7
wsrep_cluster_conf_id   5
wsrep_cluster_size      3
wsrep_cluster_state_uuid        7173605a-6299-11e5-b7a2-bb91b9fd6d7a
wsrep_cluster_status    Primary
wsrep_connected ON
wsrep_local_bf_aborts   0
wsrep_local_index       1
wsrep_provider_name     Galera
wsrep_provider_vendor   Codership Oy <info@codership.com>
wsrep_provider_version  25.3.9(r3385)
wsrep_ready     ON
wsrep_thread_count      2
[root@vps197121 ~]# mysql -uroot -plookback <<< "USE LegionTestDataBase;
SELECT * FROM LegionTestDataBase.equipment;"
id      type    quant   color
3       slide   2       blue
4       swing   10      yellow
[root@vps197121 ~]# 
[root@vps197119 ~]# mysql -uroot -plookback <<< "USE LegionTestDataBase;
SELECT * FROM LegionTestDataBase.equipment;"
id      type    quant   color
3       slide   2       blue
4       swing   10      yellow
[root@vps197119 ~]# 

====END========

您可以选择一种方式赞助本站

支付宝扫一扫赞助

微信钱包扫描赞助

lookback

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: